Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / PL/SQL Procedure

PL/SQL Procedure

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

PL/SQL procedure syntax

A PL/SQL procedure is a reusable unit that encapsulates 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 ];

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 write 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 read and modify it.

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;

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

PL/SQL Procedure - compile

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

PL/SQL procedure example

Executing a PL/SQL procedure

The following shows the syntax for executing a procedure:

EXECUTE procedure_name( arguments);

Or

EXEC procedure_name( arguments);

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);

Here is the output:

Elisha Lloyd<elisha.lloyd@verizon.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

Execute PL/SQL procedure - step 1

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

oracle procedure - execute

3) The following shows the result

Connecting to the database Local.
Elisha Lloyd<elisha.lloyd@verizon.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 Procedures node.
  • Step 2. Edit the code of the procedure.
  • Step 3. Click Compile menu option to recompile the procedure.
oracle procedure - editing and compiling

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; 
For example, the following statement drops the print_contact procedure :
DROP PROCEDURE print_contact;

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.
oracle procedure - drop
oracle procedure - drop confirmation

In this tutorial, you have learned how to create a PL/SQL procedure and execute it from Oracle SQL Developer.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle CURSOR FOR UPDATE
Next Oracle Implicit Statement Results

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.