Oracle Stored Procedures

PL/SQL blocks are anonymous, cannot be called from other PL/SQL blocks

Subprogram is a named PL/SQL block that could be either

Procedure, or

Function, or

Package

Syntax for Creating a Procedure

     Oracle Stored Procedure

 A stored procedure is a named PL/SQL block that performs an action. It can be stored in the database as a database object for repeated execution.

CREATE  [ OR REPLACE ] PROCEDURE  proc_name[(argument   [mode]   data type, argument   [mode]  data type,……)]

-- PROCEDURES and FUNCTIONS may or may not accept arguments

IS

       Local PL/SQL variable declarations

BEGIN

       Define action performed by the procedure

EXCEPTION

       Handle exceptions, if any

END [ proc_name];

Advantage of Replace option

This option replaces the previous definition of the FUNCTION, PROCEDURE, PACKAGE in the schema with the same name. The advantage of using the “REPLACE” option over “Dropping and recreating an object with new definition” is that the object privileges granted on the object get automatically revoked when you drop the object and are to be explicitly granted again when you recreate it with the new definition where as when the REPLACE option is used the object privileges remain unchanged.

Proc_name    : Name of the procedure

Argument      : Name of the PL/SQL variable whose value is passed to, populated by the  calling environment or both , depending on the mode being used.

Mode            : Mode of the argument IN , OUT or IN OUT (explained later)

Datatype       : Datatype of the argument

The REPLACE option indicates that if the procedure exist it’s definition will be overwritten by a new procedure body.

Example:

CREATE OR REPLACE PROCEDURE raise_salary (p_eno emp.empno%TYPE)

IS

BEGIN

   UPDATE emp

      SET sal = sal * 0.1

    WHERE empno = p_eno;

END raise_salary;

RAISE_APPLICATION_ERROR Procedure

This procedure is used to display error messages along with error numbers

Example

          RAISE_APPLICATION_ERROR(-20001, ‘Invalid Employee’);

Note:

When called, RAISE_APPLICATION_ERROR ends a subprogram, rolls back any database changes it made, and returns a user-defined error message to the application

Error numbers should be between -20000 and -20999

Parameters Modes in Procedures

Formal parameters can have three modes IN, OUT or IN OUT which decides the behavior of parameters

IN Parameter: Example

CREATE OR REPLACE PROCEDURE raise_salary (p_eno emp.empno%TYPE)

IS

   vsal   emp.sal%TYPE;

BEGIN

   SELECT sal

     INTO vsal

     FROM emp

    WHERE empno = p_eno;

   IF vsal < 2000

   THEN

      UPDATE emp

         SET sal = sal + vsal * 0.1

       WHERE empno = p_eno;

   END IF;

-- An exception handler to raise error if empno is not valid

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      DBMS_OUTPUT.put_line('Empno does not exist');

ENDraise_salary;

 

Calling Oracle Procedure

There are two methods to calling a Procedure

Invoke a Procedure from another PL/SQL block

  Example:

          BEGIN

                   raise_salary(1002);

          END;

Invoke a procedure from SQL*Plus environment by using EXECUTE command

Example

-- PROCEDURE call with parameter

          EXECUTE raise_salary(1002);

Note: If no parameters are specified for a procedure, directly specify procedure name without any parenthesis

          e. g.    EXECUTE procedure_name;

 

OUT Parameter: Example

CREATE OR REPLACE PROCEDURE query_emp (

   p_eno    IN       emp.empno%TYPE,

   p_name   OUT      emp.ename%TYPE,

   p_sal    OUT      emp.sal%TYPE

)

IS

BEGIN

   SELECT ename, sal

     INTO p_name, p_sal

     FROM emp

    WHERE empno = p_eno;

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      raise_application_error (-20001, 'Employee does not exist');

END query_emp;

Note: In this example, p_eno parameter value cannot be changed, since it is defined as IN parameter. It receives a value from the calling environment. Parameters p_name and p_sal do not receive any value from the calling environment. The procedure query_emp assigns value to the two parameters and these values are passed back to the calling environment.

 

Calling Oracle Procedure Having OUT Parameters

SQL> VARIABLE    name    VARCHAR2(20)

SQL> VARIABLE    salary   NUMBER

SQL> EXECUTE  query_emp(1001,:name,:salary)

SQL> PRINT   name   salary

Note: The use of colon (:) is to reference the host variable in the EXECUTE syntax

 

IN OUT Parameter: Example

CREATE OR REPLACE PROCEDURE emp_salary_increase (

   p_emp_id   IN       emp.empno%TYPE,

   p_salary   IN OUT   emp.sal%TYPE

)

IS

BEGIN

   IF p_salary BETWEEN 1000 AND 2000

   THEN

      p_salary := p_salary * 1.2;

   ELSIF p_salary BETWEEN 2000 AND 3000

   THEN

      p_salary := p_salary * 1.3;

   ELSIF p_salary > 3000

   THEN

      p_salary := p_salary * 1.4;

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line(SQLERRM);

END;

/

This PL/SQL block shows how to execute the above 'emp_salary_increase' procedure

DECLARE

   CURSOR updated_sal

   IS

      SELECT empno, sal

        FROM emp;

   pre_sal   emp.sal%TYPE;

BEGIN

   FOR emp_rec INupdated_sal

   LOOP

      pre_sal := emp_rec.sal;

      emp_salary_increase (emp_rec.empno, emp_rec.sal);

      DBMS_OUTPUT.put_line(   ' The salary of '

                            || emp_rec.empno

                            || ' will be increased from '

                            || emp_rec.sal

                            || '  to  '

                            || (emp_rec.sal + pre_sal)

                           );

   END LOOP;

END;

/

Granting Privileges on Oracle stored Procedure

The object privileges applicable to Procedures are

EXECUTE

Grants right to execute the procedure belonging to the schema of the other user

Syntax

          GRANT EXECUTE ON procedure_name

  TO username[, username….]|PUBLIC;

Example

   GRANT EXECUTE ON emp_salary_increase TO PUBLIC;

DEBUG

Grants right to debug the procedure belonging to the schema of the other user

Syntax

          GRANT DEBUG ON procedure_name TO username[, username….]|PUBLIC;

Example

  GRANT DEBUG ON emp_salary_increase TO PUBLIC

Revoking Privileges on procedure

To take back a privilege that is granted to a user

Syntax

   REVOKE EXECUTE/DEBUG ON procedure_name FROM

   Username[,username…..] |PUBLIC;

Example

   REVOKE  EXECUTE ON emp_salary_increase FROM itp_jul_01, itp_jul_02;

Dropping PROCEDURES

To Delete a PROCEDURE:

DROP PROCEDURE <Procedurename>;

e.g.

DROP PROCEDURE emp_salary_increase;

Local Procedures

The Procedure can also be defined within the declarative section of a block, such procedure are known as Local procedure

Local procedure can also be defined within an anonymous block

These procedure follow the same scope and visibility rules as any other PL/SQL identifier

It is only visible in the block in which it is declared

No other block can call local procedure, as they are not visible to any other block

Local Procedures and Functions within a Stored Subprogram

CREATE OR REPLACE PROCEDURE emp_pro

AS

   CURSOR c_allemp

   IS

      SELECT deptno, ename

        FROM emp;

   v_dname   dept.dname%TYPE;

   -- Local function ,local to the procedure which will return the dept name for an employee

   FUNCTION show_deptname (p_dno dept.deptno%TYPE)

      RETURN VARCHAR2

   IS

      v_dname   dept.dname%TYPE;

   BEGIN

      SELECT dname

        INTO v_dname

        FROM dept

       WHERE deptno = p_dno;

      RETURN v_dname;

   END show_deptname;

BEGIN

   FOR v_rec IN c_allemp

   LOOP

      v_dname := show_deptname (v_rec.deptno);

      DBMS_OUTPUT.put_line(v_rec.ename || ' belongs to      ' || v_dname);

   END LOOP;

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      DBMS_OUTPUT.put_line

                          ('Wrong department                          number');

END emp_pro;

Difference between Stand Alone and Local Subprograms

Methods of Passing Parameters

 The various methods available for passing parameters to subprograms are:

Positional

          Pass actual parameters in the same order as formal parameters

Named

          Pass actual parameters in arbitrary order by associating each with its corresponding formal parameter using special syntax (=>)

Combination

          Pass some of the actual parameters as positional and some as named

  Note: While using combination the positional parameters   should be passed first

Methods of Passing Parameters: Example

CREATE TABLE employee AS SELECT ename, sal, comm

                                                              FROM emp;

TRUNCATE            TABLE employee;

CREATE OR REPLACE PROCEDURE add_employee (

   p_name   IN   emp.ename%TYPE DEFAULT 'unknown',

   p_sal    IN   emp.sal%TYPE DEFAULT 1000,

   p_comm   IN   emp.comm%TYPE DEFAULT 0

)

IS

BEGIN

   INSERT INTO employee

               (ename, sal, comm

               )

        VALUES (p_name, p_sal, p_comm

               );

   COMMIT;

ENDadd_employee;

/

Methods of Passing Parameters: Example

BEGIN

   add_employee ;

   add_employee ('SMITH', 2000,600);

   add_employee (p_sal=> 6000,p_comm=>200, p_name =>'STEVE');

   add_employee (p_sal =>4000) ;

   add_employee('MARK', p_sal=> 6000,p_comm=>200); 

END;

/

SELECT * FROM employee;

          

Note: All the positional parameters should precede the named parameters in a subprogram call.

Data Dictionary View

USER_SOURCE

Is used to obtain the text of a stored procedure or a stored function

USER_ERRORS

Is used to find out the compilation errors in the subprogram, currently getting compiled

One can use the SQL*Plus command SHOW ERRORS, instead of firing a SELECT query on USER_ERRORS

USER_OBJECTS

Is used to get the details of all the objects created in a particular schema

USER_PROCEDURES 

Is used to get the details of all procedures in that user’s schema

You May Like:

Oracle PLSQL Functions

PL/SQL Bulk Collect 

PL/SQL Bulk bind

PL/SQL Index by tables (or) PL/SQL tables (or) associative arrays

PL/SQL Nested tables

PLSQL Record

PL/SQL Varray 

PL/SQL Cursors 

Difference between Procedures and Functions