Procedures

Stored Procedures

What is a Stored Procedure?

A stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages. A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.

We can pass parameters to procedures in three ways. 

1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters

A procedure may or may not return any value.

General Syntax to create a procedure is:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]

IS   

   Declaration section

BEGIN   

   Execution section

EXCEPTION   

  Exception section

END;

IS - marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.

The syntax within the brackets [ ] indicate they are optional. By using CREATE OR REPLACE together the procedure is created if no other procedure with the same name exists or the existing procedure is replaced with the current code.

The below example creates a procedure ‘employer_details’ which gives the details of the employee.

 CREATE OR REPLACE PROCEDURE employer_details

               IS

                               CURSOR emp_cur IS

                               SELECT first_name, last_name, salary FROM emp_tbl;

                                emp_rec emp_cur%rowtype;

               BEGIN

                               FOR emp_rec in sales_cur

                               LOOP

                               dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name

                                                                                                            || ' ' ||emp_cur.salary);

                               END LOOP;

               END;

 /

Parameters in Procedure and Functions

How to pass parameters to Procedures and Functions in PL/SQL ?

In PL/SQL, we can pass parameters to procedures and functions in three ways.

1) IN type parameter: These types of parameters are used to send values to stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.

NOTE: If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter.

1) IN parameter:

This is similar to passing parameters in programming languages. We can pass values to the stored procedure through these parameters or variables. This type of parameter is a read only parameter. We can assign the value of IN type parameter to a variable or use it in a query, but we cannot change its value inside the procedure.

The General syntax to pass a IN parameter is

CREATE [OR REPLACE] PROCEDURE procedure_name (

  param_name1 IN datatype, param_name12 IN datatype ... )

• param_name1, param_name2... are unique parameter names.
• datatype - defines the datatype of the variable.
• IN - is optional, by default it is a IN type parameter.

2) OUT Parameter:

The OUT parameters are used to send the OUTPUT from a procedure or a function. This is a write-only parameter i.e, we cannot pass values to OUT paramters while executing the stored procedure, but we can assign values to OUT parameter inside the stored procedure and the calling program can recieve this output value.

The General syntax to create an OUT parameter is

CREATE [OR REPLACE] PROCEDURE proc2 (param_name OUT datatype)

The parameter should be explicity declared as OUT parameter.

3) IN OUT Parameter:

The IN OUT parameter allows us to pass values into a procedure and get output values from the procedure. This parameter is used if the value of the IN parameter can be changed in the calling program.

By using IN OUT parameter we can pass values into a parameter and return a value to the calling program using the same parameter. But this is possible only if the value passed to the procedure and output value have a same datatype. This parameter is used if the value of the parameter will be changed in the procedure.

The General syntax to create an IN OUT parameter is

CREATE [OR REPLACE] PROCEDURE proc3 (param_name IN OUT datatype)

The below examples show how to create stored procedures using the above three types of parameters.

Example1:

Using IN and OUT parameter:

Let’s create a procedure which gets the name of the employee when the employee id is passed.

 CREATE OR REPLACE PROCEDURE emp_name (id IN NUMBER, emp_name OUT NUMBER)

 IS

 BEGIN

    SELECT first_name INTO emp_name

    FROM emp_tbl WHERE empID = id;

 END;

 /

We can call the procedure ‘emp_name’ in this way from a PL/SQL Block.

 DECLARE

  empName varchar(20);

  CURSOR id_cur SELECT id FROM emp_ids;

 BEGIN

 FOR emp_rec in id_cur

 LOOP

   emp_name(emp_rec.id, empName);

   dbms_output.putline('The employee ' || empName || ' has id ' || emp-rec.id);

 END LOOP;

 END;

 /

In the above PL/SQL Block
In line no 3; we are creating a cursor ‘id_cur’ which contains the employee id.
In line no 7; we are calling the procedure ‘emp_name’, we are passing the ‘id’ as IN parameter and ‘empName’ as OUT parameter.
In line no 8; we are displaying the id and the employee name which we got from the procedure ‘emp_name’.

Example 2:

Using IN OUT parameter in procedures:

 CREATE OR REPLACE PROCEDURE emp_salary_increase

 (emp_id IN emptbl.empID%type, salary_inc IN OUT emptbl.salary%type)

 IS

    tmp_sal number;

 BEGIN

    SELECT salary

    INTO tmp_sal

    FROM emp_tbl

   WHERE empID = emp_id;

   IF tmp_sal between 10000 and 20000 THEN

      salary_inout := tmp_sal * 1.2;

   ELSIF tmp_sal between 20000 and 30000 THEN

      salary_inout := tmp_sal * 1.3;

   ELSIF tmp_sal > 30000 THEN

      salary_inout := tmp_sal * 1.4;

   END IF;

 END;

 /

The below PL/SQL block shows how to execute the above 'emp_salary_increase' procedure.

 DECLARE

    CURSOR updated_sal is

    SELECT empID,salary

    FROM emp_tbl;

    pre_sal number;

 BEGIN

   FOR emp_rec IN updated_sal LOOP

     pre_sal := emp_rec.salary;

       emp_salary_increase(emp_rec.empID, emp_rec.salary);

       dbms_output.put_line('The salary of ' || emp_rec.empID ||

                ' increased from '|| pre_sal || ' to '||emp_rec.salary);

   END LOOP;

 END;

/

How to execute a Stored Procedure?

There are two ways to execute a procedure.

1) From the SQL prompt.

 EXECUTE [or EXEC] procedure_name;

2) Within another procedure – simply use the procedure name.

  procedure_name;

NOTE: In the examples given above, we are using backward slash ‘/’ at the end of the program. This indicates the oracle engine that the PL/SQL program has ended and it can begin processing the statements.

Parameter Data types

The datatype of a formal parameter consists of one of the following:

·         An unconstrained type name, such as NUMBER or VARCHAR2.

·         A type that is constrained using the %TYPE or %ROWTYPE attributes.

Note:

Numerically constrained types such as NUMBER(2) or VARCHAR2(20) are not allowed in a parameter list.

%TYPE and %ROWTYPE Attributes

Use the type attributes %TYPE and %ROWTYPE to constrain the parameter. For example, the Get_emp_names procedure specification in"Parameters for Procedures and Functions" could be written as the following:

PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)

This has the Dept_num parameter take the same datatype as the Deptno column in the Emp_tab table. The column and table must be available when a declaration using %TYPE (or %ROWTYPE) is elaborated.

Using %TYPE is recommended, because if the type of the column in the table changes, then it is not necessary to change the application code.

If the Get_emp_names procedure is part of a package, then you can use previously-declared public (package) variables to constrain a parameter datatype. For example:

Dept_number    number(2);

...

PROCEDURE Get_emp_names(Dept_num IN Dept_number%TYPE);

Use the %ROWTYPE attribute to create a record that contains all the columns of the specified table. The following example defines theGet_emp_rec procedure, which returns all the columns of the Emp_tab table in a PL/SQL record for the given empno:

Caution:

To execute the following, use CREATE OR REPLACE PROCEDURE...

PROCEDURE Get_emp_rec (Emp_number  IN  Emp_tab.Empno%TYPE,

                       Emp_ret     OUT Emp_tab%ROWTYPE) IS

BEGIN

   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno

      INTO Emp_ret

      FROM Emp_tab

      WHERE Empno = Emp_number;

END;

You could call this procedure from a PL/SQL block as follows:

DECLARE

   Emp_row      Emp_tab%ROWTYPE;     -- declare a record matching a

                                     -- row in the Emp_tab table

BEGIN

   Get_emp_rec(7499, Emp_row);   -- call for Emp_tab# 7499

   DBMS_OUTPUT.PUT(Emp_row.Ename || ' '                || Emp_row.Empno);

   DBMS_OUTPUT.PUT(' '           || Emp_row.Job || ' ' || Emp_row.Mgr);

   DBMS_OUTPUT.PUT(' '           || Emp_row.Hiredate   || ' ' || Emp_row.Sal);

   DBMS_OUTPUT.PUT(' '           || Emp_row.Comm || ' '|| Emp_row.Deptno);

   DBMS_OUTPUT.NEW_LINE;

END;

Stored functions can also return values that are declared using %ROWTYPE. For example:

FUNCTION Get_emp_rec (Dept_num IN Emp_tab.Deptno%TYPE)

   RETURN Emp_tab%ROWTYPE IS ...

Tables and Records

You can pass PL/SQL tables as parameters to stored procedures and functions. You can also pass tables of records as parameters.

Note:

When passing a user defined type, such as a PL/SQL table or record to a remote procedure, to make PL/SQL use the same definition so that the type checker can verify the source, you must create a redundant loop back DBLINK so that when the PL/SQL compiles, both sources pull from the same location.

Default Parameter Values

Parameters can take default values. Use the DEFAULT keyword or the assignment operator to give a parameter a default value. For example, the specification for the Get_emp_names procedure could be written as the following:

PROCEDURE Get_emp_names (Dept_num IN NUMBER DEFAULT 20) IS ...

or

PROCEDURE Get_emp_names (Dept_num IN NUMBER := 20) IS ...

When a parameter takes a default value, it can be omitted from the actual parameter list when you call the procedure. When you do specify the parameter value on the call, it overrides the default value.

Note:

Unlike in an anonymous PL/SQL block, you do not use the keyword DECLARE before the declarations of variables, cursors, and exceptions in a stored procedure. In fact, it is an error to use it.

Creating Stored Procedures and Functions

Use a text editor to write the procedure or function. At the beginning of the procedure, place the following statement:

CREATE PROCEDURE Procedure_name AS   ...

For example, to use the example in "%TYPE and %ROWTYPE Attributes ", create a text (source) file called get_emp.sql containing the following code:

CREATE PROCEDURE Get_emp_rec (Emp_number  IN  Emp_tab.Empno%TYPE,

                              Emp_ret     OUT Emp_tab%ROWTYPE) AS

BEGIN

   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno

      INTO Emp_ret

      FROM Emp_tab

      WHERE Empno = Emp_number;

END;

/

Then, using an interactive tool such as SQL*Plus, load the text file containing the procedure by entering the following statement:

SQL> @get_emp

This loads the procedure into the current schema from the get_emp.sql file (.sql is the default file extension). Note the slash (/) at the end of the code. This is not part of the code; it just activates the loading of the procedure.

Use the CREATE [OR REPLACE] FUNCTION... statement to store functions.

Caution:

When developing a new procedure, it is usually much more convenient to use the CREATE OR REPLACE PROCEDURE statement. This replaces any previous version of that procedure in the same schema with the newer version, but note that this is done without warning.

You can use either the keyword IS or AS after the procedure parameter list.

Altering Stored Procedures and Functions

To alter a stored procedure or function, you must first drop it using the DROP PROCEDURE or DROP FUNCTION statement, then re-create it using the CREATE PROCEDURE or CREATE FUNCTION statement. Alternatively, use the CREATE OR REPLACE PROCEDURE or CREATE ORREPLACE FUNCTION statement, which first drops the procedure or function if it exists, then re-creates it as specified.

Caution:

The procedure or function is dropped without any warning.

Dropping Procedures and Functions

A standalone procedure, a standalone function, a package body, or an entire package can be dropped using the SQL statements 

DROPPROCEDURE, DROP FUNCTION, DROP PACKAGE BODY, and DROP PACKAGE, respectively.

A DROP PACKAGE statement drops both the specification and body of a package.

The following statement drops the Old_sal_raise procedure in your schema:

DROP PROCEDURE Old_sal_raise;