PL/SQL Procedure

Summary: in this tutorial, you will learn about PL/SQL procedure. We will show you how to create a PL/SQL procedure and how to call it.

Introduction to PL/SQL Procedure

Like a PL/SQL function, a PL/SQL procedure is a named block that does a specific task. PL/SQL procedure allows you to encapsulate complex business logic and reuse it in both database layer and application layer.

The following illustrates the PL/SQL procedure’s syntax:

PROCEDURE [schema.]name[( parameter[, parameter...] ) ]
[AUTHID DEFINER | CURRENT_USER]
IS
[--declarations  statements]
BEGIN
--executable statements
[ EXCEPTION
---exception handlers]
END [name];Code language: SQL (Structured Query Language) (sql)

Let’s examine the PL/SQL syntax in more detail. We can divide the PL/SQL procedure into two sections: header and body.

PL/SQL Procedure Header

The section before IS keyword is called procedure header or procedure signature. The elements in the procedure’s header are described as follows:

• schema: the optional name of the schema that the procedure belongs to. The default is the current user. If you specify a different user, the current user must have privileges to create a procedure in that schema.
• name: The name of the procedure. The name of the procedure, by convention, should start with a verb e.g., put_line
• parameters: the optional list of parameters. Please refer to the PL/SQL function for more information on parameters with different modes IN, OUTand IN OUT.
• AUTHID: The optional AUHTIDdetermines whether the procedure will execute with the privileges of the owner ( DEFINER) of the procedure or with the privileges of the current user specified by CURRENT_USER.

PL/SQL Procedure Body

Everything after the ISkeyword is known as procedure body. The procedure body has similar syntax with an anonymous block which consists of the declaration, execution and exception sections.

The declaration and exception sections are optional. You must have at least one executable statement in the execution section. The execution section is the where you put the code to implement a given business logic to perform a specific task.

In PL/SQL procedure you can have a RETURN statement. However, unlike the RETURN statement in the function that returns a value to calling program, the RETURN statement in the procedure is used only to halt the execution of the procedure and return control to the caller. The RETURNstatement in procedure does not take any expression or constant.

Example of PL/SQL Procedure

We’re going to develop a procedure named adjust_salary() in HR sample database provided by Oracle. We’ll update the salary information of employees in the employees table by using SQL UPDATE statement.

The following is the source code of the adjust_salary() procedure :

CREATE OR REPLACE PROCEDURE adjust_salary(
    in_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
    in_percent IN NUMBER
) IS
BEGIN
   -- update employee's salary
   UPDATE employees
   SET salary = salary + salary * in_percent / 100
   WHERE employee_id = in_employee_id;
END;Code language: SQL (Structured Query Language) (sql)

How it works.

• The procedure has two parameters: IN_EMPLOYEE_ID and IN_PERCENT.
• The procedure adjusts the salary of a particular employee specified the IN_EMPLOYEE_ID by a given percentage IN_PERCENT.
• In the procedure body, we use the UPDATE statement to update the salary information.

Let’s take a look at how to call this procedure in various context.

Calling PL/SQL Procedure

A procedure can call other procedures. A procedure without parameters can be called directly by using EXEC statement or EXECUTE statement followed by the name of the procedure as follows:

EXEC procedure_name();
EXEC procedure_name;Code language: SQL (Structured Query Language) (sql)

A procedure with parameters can be called by using EXECor EXECUTEstatement followed by procedure’s name and its parameters in the order corresponding to the parameters list of the procedure as shown below:

EXEC procedure_name(param1,param2…paramN);Code language: SQL (Structured Query Language) (sql)

Now, we can call adjust_salary()procedure as the following statements:

-- before adjustment
SELECT salary FROM employees WHERE employee_id = 200;
-- call procedure
exec adjust_salary(200,5);
-- after adjustment
SELECT salary FROM employees WHERE employee_id = 200;Code language: SQL (Structured Query Language) (sql)

In this tutorial, we have introduced you to PL/SQL procedure and shown you step by step how to develop and call a PL/SQL procedure that adjusts the salary of employees in the HR database.