PL/SQL Block Structure

Introduction to PL/SQL Block Structure

The following article provides an outline for PL/SQL Block Structure. PL/ SQL block structure is the block of code written for the purpose of reuse when required and has certain business logic written inside it which may involve conditional execution or the repetitive code execution in it. PL/SQL block is in oracle databases as the schema object and the block where it is is he named block. The main usage of the PL/SQL blocks is done for reusing a particular business logic again and again as it encapsulates these logical statements in it.

Syntax of PL/SQL Block Structure

The general syntax of creating a block in PL/SQL is as shown below:

Declare
[ statements to be declared] BEGIN
[ statements to be executed] EXCEPTION
[ exception handler used for handling the exceptions] END [name of the block]

The terminologies used above are explained below:

• PL/SQL header for block: There are three different blocks involved while writing a block structure in Pl/ SQL. These blocks involve declaration block, execution block and exception handling block. Out of the three blocks specified here, it is compulsory to specify the execution block while the remaining two are optional in nature. A block which is not provided any name is called as Anonymous block in PL/SQL.
• PL/SQL body of the block: The body of the block is divided into three different parts which include the declaration, execution and finally handling of exceptions. The second part of that block which are executable parts is compulsory while the other two are optional.

Also, it is important to specify at least one statement in the executable part.

Let us see each of the part in detail:

• Declarative part: In this part of the body we can declare all the constants, cursors and the variables. This part of the body does not begins with the keyword DECLARE as in case of other anonymous blocks.
• Executable part: The main business logic and the operations for processing are carried our and written in this part of the body of block. There might be a possibility that this part can contain only a single NULL statement in it.
• Exception handling: In this part of the body, we try to handle those exceptions that might arise while executing the code of executable part.

Types of Blocks in PL/SQL

The PL/SQL block structures are broadly classified into two type which are namely:

• Anonymous Blocks: This are the blocks which do not have any name associated with the block. The most disadvantage of the PL/ SQL anonymous blocks is that this block can be used only once as they are not stored anywhere in the oracle database.
• Named Blocks: This are generally the procedures and functions which can be executed and used again. This are reusable block structures in PL/SQL. Along with the name of the procedure, we can also optionally specify the list of parameters used for input. Each of the individual parameter declared inside the parenthesis can be either OUT/ IN or INOUT parameter. This are called as the modes of parameters. This mode helps us to specify whether the parameter that we have created is used for writing to or reading from the procedure.

Example of PL/SQL Block Structure

Given below is the example mentioned:

Let us have a look at the example which demonstrates us the usage of how the cursor can be used in PL/SQL to retrieve the information about a particular entry in the table.

Consider that we have a table called customer details which stores the details of the customers. To check the contents of the table, we can fire the following query in SQL.

Code:

SELECT * FROM [customers_details];

The output of execution of above query statement is as shown below showing the contents of the table customer details.

Output:

To retrieve the details of the table in such a way that particular customer’s first name is retrieved and the contact details showing its mobile number is retrieved, we can create a block in PL/SQL. In this block, we will retrieve the result set of the customer details table having its f_name and mobile number fields.

Code:

CREATE OR REPLACE PROCEDURE displayContact(
in_customer_id NUMBER
)
IS
customerInfo customers_details%ROWTYPE;
BEGIN
-- Get the mobile numbers of the customers
SELECT *
INTO customerInfo
FROM customers_details
WHERE customer_id = in_customer_id ;
-- display the infomation of the customer name and its mobile number
dbms_output.put_line( '<' || customerInfo.f_name || ' ' ||
customerInfo.mobile_number ||'>' );
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( SQLERRM );
END;

To run the above procedure that is the named block in PL/SQL, you need to click on the run button the SQL editor you are using after which you can see the created block with the name displayContact present inside the node of the database.

In order to execute the above block for a particular value of customer id, you can make the use of following syntax of execution.

Code:

EXECUTE name of procedure/named block (arguments that are to be passed)

In order to call the above block, we can make the use of following query statement any number of times for any customer id as the argument.

Code:

EXECUTE displayContact(101);

The execution of above query statement gives out the following output displaying the mobile number and first name of the customer having customer id as 101.

Output:

Conclusion

The block structure is divided into three different blocks which are declaration block, execution block and exception handling block. It is compulsory to specify the execution block. The PL/SQL executable block are a single unit that contains the business logic written inside it and which can also involve multiple data manipulation and retrieval of database values in its statements. These blocks can be given call multiple times if they are named blocks. The anonymous blocks can be executed only once as they are not stored anywhere in the oracle database.

Recommended Articles

This is a guide to PL/SQL Block Structure. Here we discuss the introduction, types of blocks in PL/SQL and example respectively. You may also have a look at the following articles to learn more –

1. Triggers in PL/SQL
2. PL/SQL Collections
3. PL/SQL Data Types
4. Loops in PL/SQL