PLSQL procedure

Introduction to PLSQL procedure

PL/ SQL procedure 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 repetitive code execution in it. PLSQL procedure is stored in Oracle databases as the schema object and the block where it is stored in the named block. The main usage of the PLSQL procedures is done for reusing a particular business logic again and again as it encapsulates these logical statements in it. In this article, we will learn about the syntax, usage, and implementation of PLSQL procedures, along with the help of certain examples.

Syntax –

The general syntax of creating a procedure in PLSQL is as shown below –

CREATE [ OR REPLACE] PROCEDURE name of procedure (list of parameters)
IS
[ statements to be declared] BEGIN
[ statements to be executed] EXCEPTION
[ exception handler used for handling the exceptions] END [name of the procedure]

The terminologies used above are explained one by one in the below section –

PLSQL header for procedure

Along with the name of the procedure, we can also optionally specify the list of parameters used for input. Each of the individual parameters declared inside the parenthesis can be either OUT/ IN or INOUT parameter. These are called the modes of parameters. This mode helps us specify whether the parameter we have created is used for writing to or reading from the procedure. Let us see one by one the usage of each of the modes –

OUT – The parameter having the OUT mode can only be used for writing the values to it, and the value is used further for returning. So even though while calling the procedure, they will be completely ignored as they don’t take the values outside the procedure.

IN – This type of mode is used for specifying that the parameter value is only in the read mode and cannot be modified further inside the PL/ SQL procedure. However, even though you cannot modify it, we can still access and use the value of this parameter inside the procedure in PL/ SQL.

IN/ OUT – When a parameter is given the mode of INOUT, it will be allowed for reading as well as modification and writing by the PL/ SQL procedure.

The important point to be considered here is that when we make the use of the OR REPLACE optional clause, it will lead to the replacement of the current existing procedure in the PL/ SQL database, which will overwrite it and there is a possibility of losing the old procedure.

PL/ SQL body of the procedure

The body of the procedure is divided into three different parts, which include the declaration, execution, and, finally, handling of exceptions. The second part of that procedure which is 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 understand each of the parts in detail –

Declarative part – In this part of the body, we can declare all the constants, cursors, and variables. This part of the body does not begin with the keyword DECLARE as in the case of other anonymous blocks.

Executable part – The main business logic and the operations for processing are carried out and written in this part of the body of the procedure. There might be a possibility that this part can contain only a single NULL statement it.

Exception Handling – In this part of the body, we try to handle those exceptions that might arise while executing the code of the executable part.

Example of PLSQL procedure

Let us learn the implementation of the PL/ SQL stored procedures with the help of some examples –

Let us look at the example that demonstrates 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 –

SELECT * FROM [customers_details];

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

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

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 process, you need to click on the run button of the SQL editor you are using, after which you can see the created procedure with the name displayContact present inside the node of the database.

In order to execute the above-stored procedure for a particular value of customer id, you can make the use of the following syntax of execution –

EXECUTE name of the procedure (arguments that are to be passed)

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

EXECUTE displayContact(101);

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

Conclusion

The PL/ SQL stored procedures 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. This procedure can be given call multiple times.

Recommended Articles

This is a guide to PLSQL procedure. Here we discuss the syntax, usage, and implementation of PL/ SQL procedures along with the help of certain examples. You may also have a look at the following articles to learn more –

1. PLSQL Interview Questions
2. Triggers in PL/SQL
3. Loops in PL/SQL
4. PL/SQL Data Types