PL SQL: A Complete Guide

November 25, 2023
By Schenia T
Reading Time: 9 minutes
MySQL

SQL (Structured Query Language) is widely used in managing and manipulating data in database systems. However, in situations where it is necessary to carry out more complex tasks. Such as, for example, large-scale data manipulation, generation of personalized reports. Or integration with external applications, the SQL language may prove to be limited. In this case, it is common to resort to the use of procedural programming languages. Like PL in SQL, which allow greater flexibility and control over the operations performed.

The PL (Procedural Language) in SQL is a combination of these two languages, providing the benefits of both worlds. SQL syntax and semantics are maintained, making data manipulation and querying easier. On the other hand, PL in SQL adds flow control structures, subroutines, and functions. This allows the development of more sophisticated and efficient procedures.

PL SQL is compatible with Oracle databases. MySQL is currently not supported.

This article aims to present the fundamentals of PL in SQL, covering aspects such as syntax and semantics. Also, data types, flow control structures, subroutines, and functions. And finally, exception handling, interaction with the database, check table, scopes and performance.

So, at the end of this article, you will be prepared to use PL in SQL in your data management activities. Providing more efficient and scalable solutions.

Table of Contents

PL syntax and semantics in SQL

PL/SQL (Procedural Language/Structured Query Language) is a dialect of SQL (Structured Query Language). It allows writing procedures in addition to queries and data updates . It is mainly used in relational database management systems such as Oracle Database.

PL/SQL Syntax

PL/SQL syntax is similar to standard SQL. However, it includes object-oriented programming elements such as variables, data types , program flow controllers, loops, and functions. Some examples of PL/SQL syntax include:

Declaration of variables and data types: DECLARE v_name VARCHAR2(50) := 'John';
if-then-else conditional: IF v_age > 18 THEN v_status := ‘Adult’; ELSIF v_age > 65 THEN v_status := ‘Elderly’; END IF;
while loop: WHILE v_count > 0 LOOP
Functions: CREATE OR REPLACE FUNCTION my_function(p_param1 IN VARCHAR2, p_param2 OUT VARCHAR2) RETURN VARCHAR2 IS BEGIN ... END;

PL/SQL syntax allows developers to create procedures. Everything beyond queries and data updates, making them more efficient and flexible.

Additionally, PL/SQL supports object-oriented programming. This allows developers to create reusable function libraries and more complex programming modes.

PL/SQL semantics is the way PL/SQL interprets and executes code. In this way, the PL in SQL is interpreted and executed by the Oracle Database PL/SQL engine. It reads the code and generates the corresponding execution code.

PL/SQL Semantics

PL/SQL semantics include:

Data types: PL/SQL supports many data types, such as numbers, strings, dates, booleans, etc.
Variables: Variables are used to store data temporarily during code execution.
Program flow controllers: PL/SQL supports program flow controllers. For example, like if-then-else, while, for and case, to control code execution.
Functions: Functions are subroutines that can be called within the main code. This way, they can receive parameters and return values.
Procedures: We can call procedures within the main block, as they are subroutines. In this sense, they can receive parameters and execute a series of instructions.
Triggers: Triggers are subroutines that fire automatically in response to certain actions in the database. How to insert, update or delete records.

Thus, the syntax and semantics of PL/SQL allow us to create complex procedures and functions, which we can reuse and optimize, making application development more efficient and effective.

Variables and data types in PL in SQL

PL/SQL supports a wide variety of data types, each with its own semantics and usage. Thus, some examples of supported data types include:

Integers: represent whole numbers without precision. Example: NUMBER(5),NUMBER(10)
Decimal numbers: represent numbers accurately. Example: NUMBER(5,2),NUMBER(10,2)
Strings: represent sequences of characters. Example: VARCHAR2(50),VARCHAR2(100)
Dates: represent dates and times. Example: DATE,TIMESTAMP
Booleans: represent Boolean values (true or false). Example: BOOLEAN,NUMBER(1)
PL/SQL type data types: Represent data types specific to PL/SQL. Such as cursors, generic data types, and range data types.

To define a variable in PL/SQL, we need to specify the data type and a variable. The syntax for defining a variable is:

DECLARE
   v_variable_name VARCHAR2(maximum_size) := 'initial_value';
BEGIN
   -- PL/SQL code
END;

For example, to define a variable called “v_age” of type INTEGER with a maximum size of 5, you would use the following code:

DECLARE
   v_age INTEGER(5) := 25;
BEGIN
   -- PL/SQL code
END;

To use the variable in a SQL or PL/SQL statement, you can access it directly, as in the example below:

SELECT v_age FROM people;

IF v_age > 18 THEN
   dbms_output.put_line('This person is an adult');
END IF;

Therefore, it is important to remember that the supported data types may vary depending on the database you are using. But the syntax for defining and using variables is generally similar.

Flow Control Structures

PL/SQL supports several flow control structures, which allow you to control code execution according to different conditions. Thus, some of the main PL/SQL flow control structures include:

IF-THEN-ELSE: allows you to execute a block of code if a condition is true, another block of code if the condition is false. The syntax is as follows:

IF condition THEN
   -- code to be executed if condition is true
ELSE
   -- code to be executed if condition is false
END IF;

WHILE: allows you to execute a block of code repeatedly while a condition is true. The syntax is as follows:

WHILE LOOP condition
   -- code to be executed
END LOOP;

FOR: allows you to execute a block of code for each value in a sequence. The syntax is as follows:

FOR i IN sequence LOOP
   -- code to execute for each string value
END LOOP;

SWITCH: allows you to choose between several options based on a value. The syntax is as follows:

DECLARE
   v_option NUMBER;
BEGIN
   -- code for each option
   CASE v_option
      WHEN 1 THEN
         -- code for option 1
      WHEN 2 THEN
         -- code for option 2
      WHEN 3 THEN
         -- code for option 3
      ELSE
         -- code for invalid option
   END CASE;
END;

These are some of the main PL/SQL flow control structures. They allow you to control the execution of the code according to different conditions. Therefore, they are extremely useful for creating reusable and modular code, and can be combined to create more complex logic.

Subroutines and functions in PL in SQL

In PL/SQL, subroutines and functions are important programming elements, which allow you to reuse code and simplify program logic. Thus, the main difference between subroutines and functions is that subroutines do not return values whereas functions do return values.

Subroutines are blocks of code that are called within other blocks of code. In this way, they are used to divide the code into smaller, more manageable parts. And they can be called multiple times within the same program. Thus, the syntax for creating a subroutine is as follows:

PROCEDURE IS subroutine_name
BEGIN
   -- subroutine code
END;

Functions are subroutines that return values. Thus, they are used to calculating and return values from input data. The syntax for creating a function is as follows:

FUNCTION function_name RETURN return_type IS
BEGIN
   -- function code
END;

To call a subroutine or function, you use the keyword “CALL” followed by the name of the subroutine or function. The syntax for calling a subroutine is as follows:

CALL subroutine_name;

And the syntax for calling a function is as follows:

DECLARE
   v_result return_type;
BEGIN
   v_result := function_name(param1, param2, ...);
END;

In summary, subroutines and functions are important elements in PL/SQL, which allow you to reuse code and simplify program logic. This way, we use subroutines to divide the code into smaller, more manageable parts. This occurs while we use functions to calculate and return values from input data.

Dealing with errors and exceptions in PL

PL/SQL supports handling errors and exceptions using the “try-catch-finally” structure. Thus, the “try-catch-finally” structure allows you to execute a block of code. Additionally, it allows you to return a specific error value, or execute a block of code regardless of the result of the previous attempt.

The basic syntax of the “try-catch-finally” structure is as follows:

BEGIN
   -- code to be executed
EXCEPTION
   -- code to be executed if an exception occurs
   -- this is only executed if an exception occurs
END;

The “try-catch-finally” structure is composed of three parts:

“try”: contains the code that will be executed.
“catch”: contains the code that will be executed if an exception occurs.
“finally”: contains the code that will be executed regardless of the result of the previous attempt.

The “try-catch-finally” structure is useful for handling errors and exceptions. Allowing you to execute code regardless of the result of the previous attempt and return a specific error value.

For example, here is an example of how to use the “try-catch-finally” structure. It is used to deal with errors when trying to access a record in a database:

DECLARE
   v_id NUMBER;
BEGIN
   -- Try to access a record
   SELECT id FROM table WHERE id = 1 INTO v_id;
   IF v_id IS NULL THEN
      -- Record not found exception
      RAISE_APPLICATION_ERROR(-20001, 'Registration not found');
   END IF;
   -- Code to be executed successfully
   dbms_output.put_line(v_id);
END;

In this example, first the “try-catch-finally” structure is used to try to access a record in the database. This way, if the record is not found, the “Record not found” exception is raised and the code inside the “catch” block is executed. In this sense, if the record is found, the code inside the “finally” block is executed.

In summary, the “try-catch-finally” structure is an effective way to handle errors and exceptions in PL/SQL. It allows you to run code regardless of the result of the previous attempt and return a specific error value.

Interaction with the database

PL/SQL is an object-oriented programming language that is widely used to interact with databases. As such, it provides multiple ways to manipulate tables, perform queries, and update data.

Data manipulation

To manipulate tables, you can use standard SQL syntax to insert, update, and delete records. For example:

Add new data to a table

Therefore, to insert a new record into a table use the following syntax:

INSERT INTO table (column1, column2, column3) VALUES (value1, value2, value3);

Update data in a table

To update an existing record , you can use the following syntax:

UPDATE table SET column1 = value1 WHERE id = 1;

Add existing data to a table

Therefore, we can delete an existing record using the DELETE SQL syntax :

DELETE FROM table WHERE id = 1;

Carry out consultations

To perform queries, you can use standard SQL syntax to select, sort, and filter records.

All data in a table

For example, to select all records from a table, you can use the following syntax:

SELECT * FROM table;

Sort data in a table

To sort records by a specific column, you can use the following syntax:

SELECT * FROM table ORDER BY column1 ASC;

Filter data in a table

To filter records by a specific condition, you can use the following syntax:

SELECT * FROM table WHERE column1 = 'value';

Update data

To update data, you can use the standard SQL UPADATE syntax to update specific column values. For example, to update the value of a column for all records in a table, you can use the following syntax:

UPDATE table SET column1 = 'new value';

Additionally, PL/SQL allows you to create procedures in addition to queries and data updates. This makes the code more efficient and flexible. In this way, PL/SQL supports object-oriented programming. This allows you to create reusable function libraries and more complex programming modes.

Understanding blocks and scopes in PL in SQL

In PL/SQL, blocks and scopes are important concepts in organizing and managing resources.

Blocks

A block is a region of code that executes as a single set of instructions. Thus, blocks are used to group several related instructions in one place. And also to define the specification of a procedure or function.

Example:

Suppose we have a customer table with the following columns: ID, name, email and address. We want to create a procedure that allows us to insert a new customer into the table. So in this procedure we need to get the latest client ID. And also, increment it by 1 to create a new ID for the new customer.

Here is the code to implement this procedure, using blocks:

CREATE OR REPLACE PROCEDURE insert_customer(
   p_name IN VARCHAR,
   p_email IN VARCHAR,
   p_address IN VARCHAR
)
TO THE
BEGIN
   -- Variable declaration block
   DECLARE
      l_id customer_id_type;
      l_new_id customer_id_type;
   BEGIN
      -- Main block
      SELECT MAX(id) INTO l_id
      FROM customers;
      l_new_id := l_id + 1;

      -- Insert the new customer into the table
      INSERT INTO customers (name, email, address, id)
      VALUES (p_name, p_email, p_address, l_new_id);
   END;
END;

In this example, we use two blocks: the variable declaration block and the main block. Therefore, we use the variable declaration block to declare the variables necessary for the procedure. Meanwhile, we use the main block to execute the procedure instructions.

Inside the main block, we use a SELECT to get the most recent customer ID. And also an INSERT to insert the new customer into the table. Additionally, we create a variable l_new_id to store the new ID generated for the entered customer.

Scope

The scope is the region of code in which a variable is active and accessed. Scope is important to ensure that variables are only accessible in certain parts of the code. And also to avoid variable naming conflicts.

Example:

Suppose we have a sales table with the following columns: ID, product, price and date. We want to create a procedure that allows us to calculate the total sales value for a specific product on a specific date. In this procedure, we need to create a variable to store the total sales value and restrict access to this variable only within the main block.

Here is the code to implement this procedure, using scope:

CREATE OR REPLACE PROCEDURE total_sales_by_product_on_date(
   p_product_id IN product_id_type,
   p_date IN date_type,
   p_total_sales OUT sales_total_type
)
TO THE
BEGIN
   -- Variable declaration block
   DECLARE
      l_total_sales_on_date sales_total_type;
   BEGIN
      -- Main block
      SELECT SUM(price) INTO l_total_sales_on_date
      FROM sales_history
      WHERE product_id = p_product_id AND date = p_date;
      
      -- Restrict access to the l_total_sales_on_date variable only within the main block
      p_total_sales := l_total_sales_on_date;
   END;
END;

In this example, we use a variable declaration block and a main block. Thus, the variable declaration block is used to declare the variable l_total_sales_on_date, which stores the total sales value for the specified product on specific date.

The combination of blocks and scopes allows code to be organized clearly and efficiently. Each block can have its own scope, which allows variables to be declared and used only within the block. This increases the readability and maintainability of the code, making it easier to understand and update.

Additionally, using blocks and scopes can help prevent errors and ensure that variables are used correctly. For example, if a variable is declared with too broad a scope, it can be accessed in parts of the code that should not have access to it, which can lead to errors and bugs.

Share the Post:

Schenia T

Data scientist, passionate about technology tools and games. Undergraduate student in Statistics at UFPB. Her hobby is binge-watching series, enjoying good music working or cooking, going to the movies and learning new things!