PL/SQL Procedure

PL/SQL procedure syntax

A PL/SQL procedure is a reusable unit that encapsulates the specific business logic of the application. Technically speaking, a PL/SQL procedure is a named block stored as a schema object in the Oracle Database.

The following illustrates the basic syntax of creating a procedure in PL/SQL:

CREATE [OR REPLACE ] PROCEDURE procedure_name (parameter_list)     
IS
    

[declaration statements]

BEGIN

[execution statements]

EXCEPTION [exception handler] END [procedure_name ];Code language: SQL (Structured Query Language) (sql)

PL/SQL procedure header

A procedure begins with a header that specifies its name and an optional parameter list.

Each parameter can be in either IN, OUT, or INOUT mode. The parameter mode specifies whether a parameter can be read from or written to.

IN

An IN parameter is read-only. You can reference an IN parameter inside a procedure, but you cannot change its value. Oracle uses IN as the default mode. It means that if you don’t specify the mode for a parameter explicitly, Oracle will use the IN mode.

OUT

An OUT parameter is writable. Typically, you set a returned value for the OUT parameter and return it to the calling program. Note that a procedure ignores the value that you supply for an OUT parameter.

INOUT

An INOUT parameter is both readable and writable. The procedure can be read and modified.

Note that OR REPLACE option allows you to overwrite the current procedure with the new code.

PL/SQL procedure body

Similar to an anonymous block, the procedure body has three parts. The executable part is mandatory whereas the declarative and exception-handling parts are optional. The executable part must contain at least one executable statement.

1) Declarative part

In this part, you can declare variables, constants, cursors, etc. Unlike an anonymous block, a declaration part of a procedure does not start with the DECLARE keyword.

2) Executable part

This part contains one or more statements that implement specific business logic. It might contain only a NULL statement.

3) Exception-handling part

This part contains the code that handles exceptions.

Creating a PL/SQL procedure example

The following procedure accepts a customer id and prints out the customer’s contact information including first name, last name, and email:

CREATE OR REPLACE PROCEDURE print_contact(
    in_customer_id NUMBER 
)
IS
  r_contact contacts%ROWTYPE;
BEGIN
  -- get contact based on customer id
  SELECT *
  INTO r_contact
  FROM contacts
  WHERE customer_id = p_customer_id;

  -- print out contact's information
  dbms_output.put_line( r_contact.first_name || ' ' ||
  r_contact.last_name || '<' || r_contact.email ||'>' );

EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line( SQLERRM );
END;
Code language: SQL (Structured Query Language) (sql)

To compile the procedure, you click the Run Statement button as shown in the following picture:

If the procedure is compiled successfully, you will find the new procedure under the Procedures node as shown below:

Executing a PL/SQL procedure

The following shows the syntax for executing a procedure:

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

Or

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

For example, to execute the print_contact procedure that prints the contact information of customer id 100, you use the following statement:

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

Here is the output:

Sanjay Shonak<sanjay.shonak@gmail.com>

You can also execute a procedure from the Oracle SQL Developer using the following steps:

1)  Right-click the procedure name and choose Run… menu item

2) Enter a value for the  in_customer_id parameter and click the OK button.

3) The following shows the result

Connecting to the database Local.
Sanjay Shonak<sanjay.shonak@gmail.com>
Process exited.
Disconnecting from the database Local.

Editing a procedure

To change the code of an existing procedure, you can follow these steps:

• Step 1. Click the procedure name under the Procedures node.
• Step 2. Edit the code of the procedure.
• Step 3. Click the Compile menu option to recompile the procedure.

Removing a procedure

To delete a procedure, you use the DROP PROCEDURE followed by the procedure’s name that you want to drop as shown in the following syntax:

DROP PROCEDURE procedure_name; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement drops the print_contact procedure :

DROP PROCEDURE print_contact;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following illustrates the steps of dropping a procedure using SQL Developer:

• Step 1. Right-click on the procedure name that you want to drop
• Step 2. Choose the Drop… menu option
• Step 3. In the Prompts dialog, click the Apply button to remove the procedure.