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:

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, OUT and IN OUT.
  • AUTHID: The optional AUHTID determines 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 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 :

How it works.

  • The procedure has two parameters: IN_EMPLOYEE_ID and IN_PERCENT.
  • The procedure adjusts the salary of a particular employee specified by IN_EMPLOYEE_ID by a given percentage IN_PERCENT.
  • 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:

A procedure with parameters can be called by using EXEC or 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:

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.