Stored Procedure in 10g

A procedure is a group of PL/SQL statements that you can call by name. Stored procedure is a named PL/SQL block which performs one or more specific task.

Stored procedures offer advantages in the areas of development, integrity, security, performance, and memory allocation.

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.

argument
Specify the name of an argument to the procedure. If the procedure does not accept arguments, you can omit the parentheses following the procedure name.

IN
Specify IN to indicate that you must supply a value for the argument when calling the procedure.


OUT
Specify OUT to indicate that the procedure passes a value for this argument back to its calling environment after execution.


IN OUT
Specify IN OUT to indicate that you must supply a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution.

If you omit IN, OUT, and IN OUT, then the argument defaults to IN.

NOCOPY
Specify NOCOPY to instruct the database to pass this argument as fast as possible. This clause can significantly enhance performance when passing a large value like a record, an index-by table, or a varray to an OUT or IN OUT parameter. IN parameter values are always passed NOCOPY.

When you specify NOCOPY, assignments made to a package variable may show immediately in this parameter, or assignments made to this parameter may show immediately in a package variable, if the package variable is passed as the actual assignment corresponding to this parameter.

Similarly, changes made either to this parameter or to another parameter may be visible immediately through both names if the same variable is passed to both.

If the procedure is exited with an unhandled exception, then any assignment made to this parameter may be visible in the caller's variable.

These effects may or may not occur on any particular call. You should use NOCOPY only when these effects would not matter.

datatype
Specify the datatype of the argument. An argument can have any datatype supported by PL/SQL.

Datatypes cannot specify length, precision, or scale. For example, VARCHAR2(10) is not valid, but VARCHAR2 is valid. Oracle Database derives the length, precision, and scale of an argument from the environment from which the procedure is called.




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.


Stored Procedure without any Parameter

CREATE TABLE LOG_TABLE

( USER_ID VARCHAR2(10 ) , LOG_DATE DATE  )

Table Created.

CREATE OR REPLACE PROCEDURE SCOTT.log_execution
IS
BEGIN
   INSERT INTO log_table (user_id, log_date)
        VALUES (USER, SYSDATE);
 
  END log_execution;
 /

IN  PARAMETER EXAMPLE
1st example:

CREATE OR REPLACE PROCEDURE HR.remove_emp (employee_id NUMBER)
AS
   tot_emps   NUMBER;
BEGIN
   DELETE FROM employees1
         WHERE employees1.employee_id = remove_emp.employee_id;

   tot_emps := tot_emps - 1;
END;
/

SHOW ERRORS;


To execute :

SET SERVEROUTPUT ON

DECLARE
  EMPLOYEE_ID NUMBER;

BEGIN
  EMPLOYEE_ID := 205;
REMOVE_EMP ( EMPLOYEE_ID );
  --COMMIT;
Dbms_output.put_line (EMPLOYEE_ID || ' has been deleted');
END;



2nd Example:

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

CREATE OR REPLACE PROCEDURE employee_details
 IS
 CURSOR emp_cur IS
SELECT first_name, last_name, salary FROM employees1;
--emp_rec emp_cur%rowtype;
BEGIN
FOR emp_rec in emp_cur
LOOP
dbms_output.put_line(emp_rec.first_name || ' ' ||emp_rec.last_name || ' ' ||emp_rec.salary);
END LOOP;
END;

OUT PARAMETER EXAMPLE:

Definition:

In Oracle PL/SQL, the term OUT refers to one of the parameter passing modes. Unlike the IN parameter mode, it must be specified in the parameter declaration. An OUT parameter is a 'pass by value' method. The parameter must be assigned with a value within the subprogram body. This value is then passed to the host environment, from where the subprogram has been called.

Notes
A default value cannot be assigned to the OUT parameter.
The NOCOPY hint can be used in the parameter declaration to convert the parameter calling method from 'Call by Value' method to the 'Call by reference' method.

Example Syntax:

PARAMETER [IN | OUT | IN OUT] [DATA TYPE] [DEFAULT],

Example Usage:

The procedure P_GET_SAL fetches the salary of an employee from EMPLOYEE table for the given employee id and returns it to the calling environment.

CREATE OR REPLACE PROCEDURE P_GET_SAL (P_EMPID NUMBER, P_SAL OUT NUMBER)

IS

BEGIN

SELECT SALARY INTO P_SAL

FROM EMPLOYEES1

WHERE EMPLOYEE_ID=P_EMPID;

END;

/

SQL> VAR G_SAL NUMBER;
SQL> EXEC P_GET_SAL(100,:G_SAL);

PL/SQL procedure successfully completed.

SQL> PRINT G_SAL;

     G_SAL
----------
     26400

OR:

DECLARE
  P_EMPID NUMBER;
  P_SAL NUMBER;

BEGIN
  P_EMPID := 100;
  P_GET_SAL (P_EMPID,P_SAL );
 DBMS_OUTPUT.PUT_LINE(P_EMPID ||' draws salary' ||  P_SAL);
 END;

100 draws salary 26400
PL/SQL procedure successfully completed.


IN OUT PARAMETER EXAMPLE

CREATE OR REPLACE procedure format_phone
(p_phone_no IN OUT varchar2)
is
begin
p_phone_no :='(' || SUBSTR(p_phone_no,1,3) ||
                       '(' || SUBSTR(p_phone_no,4,3) ||      
                       '-' || SUBSTR(p_phone_no,7 );
END format_phone;
/

Procedure created.

SQL> var g_phone_no varchar2(15)

SQL> begin

  2  :g_phone_no :='9880002441';

  3  end;

  4  /

PL/SQL procedure successfully completed.

SQL> print g_phone_no

G_PHONE_NO

--------------------------------

9880002441

SQL> exec format_phone(:g_phone_no);

PL/SQL procedure successfully completed.

SQL> print g_phone_no

G_PHONE_NO

--------------------------------

(988)000-2441

OR 

SET SERVEROUTPUT ON

DECLARE
P_PHONE_NO VARCHAR2(32767);

BEGIN
P_PHONE_NO := '9880002441';

FORMAT_PHONE ( P_PHONE_NO );

DBMS_OUTPUT.PUT_LINE(P_PHONE_NO);

END;

Output:

(988)000-2441

PL/SQL procedure successfully completed.

************************************************************


A subprogram is a program unit/module that performs a particular task.
These subprograms are combined to form larger programs. This is basically called the 'Modular design'.

A subprogram can be invoked by another subprogram or program which is called the calling program.

A subprogram can be created:

At schema level

Inside a package

Inside a PL/SQL block

A schema level subprogram is a standalone subprogram. It is created with the CREATE PROCEDURE or CREATE FUNCTION statement.

It is stored in the database and can be deleted with the DROP PROCEDURE or DROP FUNCTION statement.

A subprogram created inside a package is a packaged subprogram.

It is stored in the database and can be deleted only when the package is deleted with the DROP PACKAGE statement.


PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters. PL/SQL provides two kinds of subprograms:

Functions: these subprograms return a single value, mainly used to compute and return a value.

Procedures: these subprograms do not return a value directly, mainly used to perform an action.

Cheers:
Rajani