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];
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.,
parameters: the optional list of parameters. Please refer to the PL/SQL function for more information on parameters with different modes
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
PL/SQL Procedure Body
Everything after the
IS keyword is known as procedure body. The procedure body has similar syntax with an anonymous block which consists of 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 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 function that returns a value to calling program, the
RETURN statement in procedure is used only to halt the execution of procedure and return control to the caller. The
RETURN statement 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;
How it works.
- The procedure has two parameters:
- The procedure adjusts the salary of a particular employee specified by
IN_EMPLOYEE_IDby a given percentage
- In the procedure body, we use SQL 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 name of the procedure as follows:
EXEC procedure_name(); EXEC procedure_name;
A procedure with parameters can be called by using
EXECUTE statement followed by procedure’s name and its parameters in the order corresponding to the parameters list of the procedure as shown below:
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;
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 salary of employees in the HR database.