Oracle PL/SQL Procedures and Procedure Parameters

Yusuf SEZER

4 years ago

This article contains information about PL/SQL Procedures and parameters in procedures such as IN, OUT, IN OUT.

What is Procedure in Oracle?

PL/SQL Procedures are the structures that keep Oracle PL/SQL commands under a name and run them when needed.

Procedures are compiled the first time they run and a query plan is created.

When you run stored procedures next time, they use this query plan.

The keywords to be used when creating the procedure are as follows.

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] data_type [, ...])]
{IS | AS}
BEGIN
    -- Commands
END procedure_name;

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN | OUT | IN OUT] data_type [, ...])]

{IS | AS}

BEGIN

-- Commands

END procedure_name;

The REPLACE keyword included in the command is not mandatory. However, it would be beneficial to use it as it will throw an error if you try to recreate an existing procedure. Let’s create a sample procedure.

SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE First_SP
AS
BEGIN
   DBMS_OUTPUT.put_line('Hello Oracle!');
END;

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE First_SP

BEGIN

DBMS_OUTPUT.put_line('Hello Oracle!');

END;

Once the procedure is created, we can run it as follows.

EXECUTE First_SP

1	EXECUTE First_SP

EXEC First_SP

1	EXEC First_SP

The commands in the procedure will be executed each time the procedure is run.

Procedure Parameters in Oracle

Another important feature of the procedure structure is the possibility to take parameters.

IN Parameter in PL/SQL Procedures

If the parameter is marked with IN, the parameter is passed only to the procedure.

OUT Parameter in PL/SQL Procedures

If the parameter is marked OUT, the parameter sends out data.

IN OUT Parameter in PL/SQL Procedures

If the parameter is marked with IN OUT, it takes both IN and OUT.

PL/SQL Procedure example with parameters

In the example below, the loop start and end values are taken as parameters.

SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE MY_LOOP (
    START_VALUE IN PLS_INTEGER,
    END_VALUE IN PLS_INTEGER
) AS
BEGIN
    FOR v_number IN START_VALUE .. END_VALUE LOOP
        DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.');
    END LOOP;
END;

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE MY_LOOP (

START_VALUE IN PLS_INTEGER,

END_VALUE IN PLS_INTEGER

) AS

BEGIN

FOR v_number IN START_VALUE .. END_VALUE LOOP

DBMS_OUTPUT.put_line('Command executed ' ||v_number || ' times.');

END LOOP;

END;

EXECUTE MY_LOOP(45,50)

1	EXECUTE MY_LOOP(45,50)

SYS.all_procedures table can be used to get information about the created procedures.

SELECT * FROM SYS.all_procedures WHERE OWNER = 'USER_NAME';

1	SELECT * FROM SYS.all_procedures WHERE OWNER = 'USER_NAME';

The following command is used to delete the created procedure.

DROP PROCEDURE procedure_name;

1	DROP PROCEDURE procedure_name;

DROP PROCEDURE MY_LOOP;

1	DROP PROCEDURE MY_LOOP;

The reason for using the procedures is to ensure that the written commands are securely stored in the database management system.

Also, the created procedures will work better because they are compiled beforehand.

When data is received from a table with any programming language, the SQL sentence must first be written and sent to the database.

The sent SQL statement is executed after it is checked by the database management system.

However, since the procedures are compiled beforehand, it is operated without recompilation, thus ensuring performance.

The procedures are similar to function structures, causing confusion.

While procedures are not required to return values, functions must return values.

Procedures are generally used in CRUD processes.

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

You can find more information about procedures at docs.oracle.com