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
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
INOUT mode. The parameter mode specifies whether a parameter can be read from or write to.
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
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
INOUT parameter is both readable and writable. The procedure can read and modify it.
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
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:
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);
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:
Here is the output:
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 OK button.
3) The following shows the result
Connecting to the database Local. Elisha Lloyd<email@example.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.
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;
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.
In this tutorial, you have learned how to create a PL/SQL procedure and execute it from Oracle SQL Developer.