PL/SQL Procedure

Summary: in this tutorial, you’ll learn how to create, compile, and execute a PL/SQL procedure from the Oracle SQL Developer.

Introduction to PL/SQL procedures #

In PL/SQL, a procedure is a named block that performs a specific task. Unlike an anonymous block, Oracle stores the procedure in the database, and you can execute it repeatedly.

Typically, you create a procedure to encapsulate a reusable code block.

Here’s the syntax for creating a procedure

CREATE [OR REPLACE ] PROCEDURE procedure_name (
    parameter1 IN datatype,
    parameter2 OUT datatype,
    parameter3 IN OUT datatype
)     
IS
   -- declare variables
BEGIN
   -- execute statements
EXCEPTION
   -- handle exeception

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 determines if the procedure can access its initial value or modify its value:

• IN parameter is read-only. You can reference its value within a procedure, but not modify it. If you don’t specify a parameter mode explicitly, PL/SQL automatically uses IN as the default mode.
• OUT parameter is writable. It means you can assign a value to an OUT parameter and return it to the program called the procedure. Note that a procedure ignores the value that you provide for an OUT parameter.
• INOUT parameter is both readable and writable. This means the procedure can access its initial value and also modify it.

The OR REPLACE option allows you to update an existing procedure with the new code without dropping it.

PL/SQL procedure body #

Similar to an anonymous block, the procedure body has three parts:

• Declaration: This section is where you can declare variables, constants, cursors, and other elements. Unlike an anonymous block, a declaration section of a procedure does not start with the DECLARE keyword.
• Execution: This section contains the core logic of the procedure, consisting of one or more statements that perform specific tasks. It can even be as simple as a NULL statement.
• Exception handler: This section contains code for gracefully handling and responding to errors.

Creating a PL/SQL procedure example #

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

CREATE OR REPLACE PROCEDURE print_contact(
    p_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)

Output:

Elisha Lloyd<[email protected]>

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

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

Step 2. Enter a value for the  p_customer_id parameter and click the OK button.

Step 3. The following shows the result:

Elisha Lloyd<[email protected]>

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 statement:

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.

Summary #

• A stored procedure is a named block that performs a specific task.
• Stored procedures are stored in the Oracle Database server.
• Use the CREATE PROCEDURE statement to create a new procedure.
• Use the EXECUTE or EXEC statement to execute a procedure.
• Use the DROP PROCEDURE statement to drop an existing procedure.