Oracle create or replace Stored Procedure example basics

An Oracle stored procedure is an Oracle object that you create using PL/SQL. PL/SQL is a procedural language which is used by Oracle developers to process and manipulate data stored in an Oracle database.

The basic form of an Oracle stored procedure is

CREATE OR REPLACE PROCEDURE p_procedure1 IS
BEGIN

null;

END p_procedure1;


The stored procedure above will compile successfully.

Now lets discuss the procedure above.

CREATE OR REPLACE: You must use CREATE command whereas REPLACE is optional. But if you do not put REPLACE then you can not recompile the stored procedure if it already exists. By putting CREATE OR REPLACE you are saying that if it already exists then replace it with the new version.

PROCEDURE: This clause specifies the type of object that you want to create. Here we are saying it to be a procedure.

BEGIN and END: This is the body of the procedure. All code that you want to put in the procedure should live here. In the example above I have put null; It means do nothing. However even if you do nothing and just want to create a stored procedure (as above) you still need to put the Begin and End to complete the frame of the stored procedure. For clarity I have put End p_procedure1 which is not required. Instead you could just put End;

Example of Oracle Store Procedure with parameters:

Most of the time when calling an Oracle procedure you would like to pass on some parameter to the procedure to process it or to do something with the data in the parameter. You can pass parameters to a stored procedure as below:

CREATE OR REPLACE PROCEDURE p_procedure2(p_name varchar2) IS
BEGIN

dbms_output.put_line(p_name);

END p_procedure2;

Here I am passing a parameter called p_name to the stored procedure p_procedure1; Then in the body section I am displaying the value of the parameter.

You can use “dbms_output.put_line()” to display the values of parameters in SQL*Plus window. This is same PRINT/ECHO/PRINTLN used in other programming languages.

Now the parameters that you pass to a stored procedure can be of three types (IN, OUT and IN OUT).

IN - is the default type. So if you do not specify the parameter type then IN is used. This is telling that I am supplying a parameter to the stored procedure.

OUT – is used to get values back from the stored procedure.

IN OUT – is used to pass values to a stored procedure and to get values from the stored procedure using the same parameter.

Unlike Oracle functions you can return multiple values using the out parameter.

For example: if you want to get back the employee name of an employee and you have just the emp_no (employee number) then you can pass the emp_no to a stored procedure using an IN parameter and then get the employee name using an out parameter. This is demonstrated below:

CREATE OR REPLACE PROCEDURE p_procedure3(p_emp_no IN number, p_name OUT varchar2) IS
BEGIN

SELECT ename
INTO p_name
FROM emp
WHERE emp_no = p_emp_no;

END p_procedure3;


Here we are passing the emp_no and using that we are getting ename from table emp. The returned value is put into p_name and the calling program will be able to access this p_name.

Now when we use out parameter it becomes a little bit different while executing a stored procedure.

For example:  to execute the first two stored procedures mentioned above you simple login to Oracle using SQL*Plus and run the commands:

SQL>exec p_procedure1;

Or

SQL>set serverout on;

SQL> exec p_procedure2(‘ITSME’);

But when if use an OUT parameter (as in p_procedure3)  you can not simply run execute command like “exec p_procedure3(parameters here). Because the stored procedure is returning a value and you need to read that value.

In such case you can write a very simple PL/SQL block as below

DECLARE
l_name varchar2(20);
BEGIN

— now call the stored procedure

p_procedure3(100, l_name);

— now display the returned value

dbms_output.put_line(l_name);

END;

In the code above I have created a variable called l_name. While calling p_procedure3 I am passing the name of this variable. The procedure will find the name of the employee with emo_no=100 and return the name of the employee in my variable l_name. I can then display or do whatever I want with the value in the variable.



Loading