Oracle Stored Procedure With Examples

Learning PL/SQL Procedure with examples is the best way to learn Stored Procedure. You may be new to PL/SQL Procedure but you may have used Anonymous Block before. A PL/SQL block without a name is Anonymous block. A block that has a name is called a Stored Procedure. Stored Procedure is a database objects that stores the PL/SQL code inside a database which you can execute, modify or re-run anytime. Anonymous block isn't stored inside a database, therefore it should be stored somewhere in a hard drive. Giving  name to a block that you use frequently make an Anonymous block a Stored Procedure. Isn't that simple? Indeed, it is as simple as that.

Before learning from examples, you need to know how to pass IN/OUT parameter to and from procedure. Both Functions and Procedure accepts three parameters.

1. IN type parameter sends values to a Stored Procedure.
2. OUT type parameter gets values from the Stored Procedure.
3. IN OUT type parameter sends and gets values from the procedure.

Like most programming language, PL/SQL procedure has defined IN type as default parameter. The OUT parameter is a write-only for procedure as it does not pass the value OUT while executing the procedure but passes value back to a calling Procedure or a Block. We have few examples below to show how the passing parameters works within Stored Procedure.

Stored Procedure Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name [(
  parameter [,
  parameter]) ]
IS
 [VARIABLE declaration goes here] 
BEGIN
 [Executable SQL statements goes here]
EXCEPTION
 [Error handling goes here]
END [procedure_name];

INSERT Stored Procedure : The Stored Procedure in this example accepts 8 input parameters and inserts those input value into an employee table.

CREATE OR REPLACE PROCEDURE insert_employee(
  empno_in     IN NUMBER,
  ename_in     IN VARCHAR2,
  job_in       IN VARCHAR2,
  mgr_in       IN NUMBER,
  hiredate_in  IN DATE,
  sal_in       IN NUMBER,
  comm_in      IN NUMBER,
  deptno_in    IN NUMBER) 
-– Just the data type, don’t define the data length
IS
 ecode   NUMBER;
 emesg   VARCHAR2(200);
BEGIN
 INSERT INTO baniya.emp(
  empno,
  ename,
  job,
  mgr,
  hiredate,
  sal,
  comm,
  deptno)
 VALUES(
  empno_in,
  ename_in,
  job_in,
  mgr_in,
  hiredate_in,
  sal_in,
  comm_in,
  deptno_in);
 COMMIT;
EXCEPTION
 WHEN OTHERS THEN
  ecode := SQLCODE;
  emesg := SQLERRM;
  dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
END insert_employee;
/

We have successful compiled the insert_employee procedure. Next, we will execute the procedure to insert some employee data using the procedure we just created.

Executing Procedure:

declare
           empno_in        NUMBER          := 1234;
           ename_in        VARCHAR2(20)    := 'SCHEMID';
           job_in          VARCHAR2(50)    := 'VP';
           mgr_in          NUMBER          := 456;
           hiredate_in     DATE            := '01-FEB-81';
           sal_in          NUMBER          := 5500;
           comm_in         NUMBER          := 5;
           deptno_in       NUMBER          := 33;
begin
     baniya.insert_employee(empno_in, ename_in, 
   job_in, mgr_in,
   hiredate_in,
   sal_in,
   comm_in,
   deptno_in);
end;
/

Output:

SELECT Stored Procedure: The Stored Procedure in this example accepts 1 input parameter and outputs 8 parameters back to a calling procedure or a block.

CREATE OR REPLACE PROCEDURE  baniya.get_employee_detail(
   empno_io      IN OUT employee.empno%TYPE,
   ename_out     OUT    employee.ename%TYPE,
   job_out       OUT    employee.job%TYPE,
   mgr_out       OUT    employee.mgr%TYPE,
   hiredate_out  OUT    employee.hiredate%TYPE,
   sal_out       OUT    employee.sal%TYPE,
   comm_out      OUT    employee.comm%TYPE,
   deptno_out    OUT    employee.deptno%TYPE)
IS
   ecode   NUMBER;
   emesg   VARCHAR2(200);
BEGIN
 SELECT 
  empno,
  ename,
  job,
  mgr,
  hiredate,
  sal,
  comm,
  deptno
 INTO 
  empno_io,
  ename_out,
  job_out,
  mgr_out,
  hiredate_out,
  sal_out,
  comm_out,
  deptno_out
 FROM baniya.employee
 WHERE empno = empno_io;

EXCEPTION
 WHEN OTHERS THEN
  ecode := SQLCODE;
  emesg := SQLERRM;
  dbms_output.put_line( 'Here you go baniya'
    ||TO_CHAR(ecode) || '-' || emesg);
END get_employee_detail;

Calling Stored Procedure from PL/SQL block: Calling a procedure from another procedure or PL/SQL block. The example below demonstrates how to call the existing procedure from PL/SQL Anonymous or named block. Here we will call the procedure from a block.

DECLARE
  empno_io baniya.employee.empno%TYPE := 7521;
  ename_out baniya.employee.ename%TYPE;
  job_out baniya.employee.job%TYPE;
  mgr_out baniya.employee.mgr%TYPE;
  hiredate_out baniya.employee.hiredate%TYPE;
  sal_out baniya.employee.sal%TYPE;
  comm_out baniya.employee.comm%TYPE;
  deptno_out baniya.employee.deptno%TYPE;
BEGIN
  baniya.get_employee_detail(empno_io, ename_out,
   job_out,
   mgr_out, 
   hiredate_out,
   sal_out, 
   comm_out, 
   deptno_out);

 dbms_output.put_line('Here is your detail on your search : ' 
  || empno_io || ', '
  || ename_out || ', 
  || job_out || ', ' 
  || mgr_out || ', ' 
  || hiredate_out || ', '
  || sal_out || ', '
  || comm_out || ', '
  || deptno_out);
END;
/

Output:

Some Mistakes To Avoid: To explain common mistakes with procedure, we will use a procedure called spin that takes two numbers as input parameter. Let’s experiment a procedure with null, empty string and wrong parameter to study the behavior PL/SQL error.

exec baniya.spin (5,5); -- Passing correct params, works
exec baniya.spin(null, null); -- Passing NULL as param, works
exec baniya.spin('',''); --Passing empty string as params, throws error
exec baniya.spin(); -- Passing nothing as param, throws error

Output: showing error for exec baniya.spin();

How about empty value for each parameters?

exec baniya.spin('','','');

Output:

Error starting at line : 19 in command -
exec baniya.spin()
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SPIN'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

While learning PL/SQL, the Procedure gets confusing when you begin to learn Function or vice versa. To fully understand Stored Procedure concepts and application, you will also need to learn PL/SQL Function which I have covered on my blog.

How Function is different from Procedure?

1. Function can return only one value whereas Procedure can return multiple values.
2. Function can be called from SQL Statements but Procedures cannot be called from SQL statement. Procedure can be called from another Procedures or PL/SQL blocks. 
3. DML statement cannot be used inside Functions but Procedure accepts DML , DDL and TLC operations.
4. Function is mostly used for computations whereas Procedure is more for implementing business rules, loading data, selecting data, automate repeating task etc.

Limitations:The Stored Procedure cannot be called within a SQL statement. It has to be called within PL/SQL to call or execute.

Why is there a need to create a Function when we already have Stored Procedure objects? What is the difference between Stored Procedure and Function? They both can return values, they both take input parameters then what are the differences? These are the frequently asked questions. After reading my blog on Procedure and Function, readers will have the solid understanding of these objects and answers to these frequently asked questions. I hope you enjoyed reading these as much as I do writing them.