What is Procedure and its Examples

Procedure:-
            Procedure is a named PL/SQL block. It may or may not return value.
           
Syntax:-
            CREATE OR REPLACE PROCEDURE procedure_name
            IS/AS
            BEGIN
                STATEMENT;
            EXCEPTION;    --(optional)   
            END procedure_name;
           
After creation of procedure, we execute it for see the output.

There are many different ways for execute the procedure
   
1.
    EXECUTE/EXEC procedure_name;

2.   
    DECLARE
    BEGIN
        procedure_name;
    END;

   
We can use different parameter mode in procedure

1. IN
2. OUT
3. IN OUT
4. OUT NOCOPY
5. IN OUT NOCOPY
   
Note:-    a) By default parameter mode is IN.
              b) NOCPOY pass the reference of variable not value. NOCOPY use only with OUT and
                   IN OUT mode.


Examples:-

1.
   
    CREATE OR REPLACE PROCEDURE XXC05_PROC
    IS
      ID NUMBER;
    BEGIN
        ID:=10;
        DBMS_OUTPUT.PUT_LINE('ID:- '||ID);
    END XXC05_PROC;
   
   

2.

    CREATE OR REPLACE PROCEDURE XXC05_PROC
    IS
     CURSOR C1 IS
                SELECT EMPLOYEE_ID,LAST_NAME
                FROM EMPLOYEES;
     L_ID NUMBER;
     L_NAME VARCHAR2(20);               
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO L_ID,L_NAME;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
     END LOOP;
    END XXC05_PROC;

   

3. We can use cursor within Procedure....

    CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER)
    IS
     CURSOR C1 IS
                SELECT EMPLOYEE_ID,LAST_NAME
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = P_EMP_ID;
     L_ID NUMBER;
     L_NAME VARCHAR2(20);               
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO L_ID,L_NAME;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
     END LOOP;
    END XXC05_PROC;
   
    exec XXC05_PROC(100);
   
   
4.

    CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER,P_SAL OUT NUMBER)
    IS
     CURSOR C1 IS
                SELECT EMPLOYEE_ID,LAST_NAME ,SALARY
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = P_EMP_ID;
     L_ID NUMBER;
     L_NAME VARCHAR2(20);               
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO L_ID,L_NAME,P_SAL;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME||'Salary:- '||CHR(9)||P_SAL);
     END LOOP;
    END XXC05_PROC;   
   
   
Note:-  If we use OUT or IN OUT mode parameter in procedure then it call only within BEGIN block.
        Example given below.

    DECLARE
    L_SAL NUMBER;
    BEGIN
        XXC05_PROC(100,L_SAL);
        DBMS_OUTPUT.PUT_LINE('Salary:- '||CHR(9)||L_SAL);
    END;
   
5.

    CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER,P_SAL OUT NOCOPY NUMBER)
    IS
     CURSOR C1 IS
                SELECT EMPLOYEE_ID,LAST_NAME ,SALARY
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = P_EMP_ID;
     L_ID NUMBER;
     L_NAME VARCHAR2(20);               
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO L_ID,L_NAME,P_SAL;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME||'Salary:- '||CHR(9)||P_SAL);
     END LOOP;
    END XXC05_PROC;
   
6.

    CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN OUT NUMBER)
    IS
     CURSOR C1 IS
                SELECT EMPLOYEE_ID,LAST_NAME
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = P_EMP_ID;
     L_NAME VARCHAR2(20);               
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO P_EMP_ID,L_NAME;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||P_EMP_ID||CHR(9)||'Last Name:- '||CHR(9)||L_NAME);
     END LOOP;
    END XXC05_PROC;
   
   
7.

    CREATE OR REPLACE PROCEDURE XXC05_PROC(P_EMP_ID IN NUMBER)
    IS
     CURSOR C1 IS
                SELECT *
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = P_EMP_ID;
     L_EMP_REC EMPLOYEES%ROWTYPE;
    BEGIN
     OPEN C1;
     LOOP
        FETCH C1 INTO L_EMP_REC ;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID:- '||CHR(9)||L_EMP_REC.EMPLOYEE_ID||CHR(9)||'Last Name:- '||CHR(9)||L_EMP_REC.LAST_NAME);
     END LOOP;
    END XXC05_PROC;   
   

   
   
   
Some Important Point for Procedure..........

   
Ques:- Can we create procedure within procedure?
Ans:- YES, Refer below example.

Example:-
   
    CREATE OR REPLACE PROCEDURE XXC05_OUTER_PROC
    IS
     PROCEDURE XXC05_INNER_PROC
     IS
     BEGIN
        DBMS_OUTPUT.PUT_LINE('INNER PROEDURE');
     END XXC05_INNER_PROC;                    
    BEGIN
     DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE');
     XXC05_INNER_PROC;
    END XXC05_OUTER_PROC;
   

   
Quese:- Can we use RETURN clause within procedure?
Ans:-  YES, But no statement are executed which written after the RETURN clause.

In below example only first message print.


Example:-

    CREATE OR REPLACE PROCEDURE XXC05_OUTER_PROC
    IS
    BEGIN
     DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE 1');
     RETURN;
     DBMS_OUTPUT.PUT_LINE('OUTER PROEDURE 2');
    END XXC05_OUTER_PROC;
   
    Output:-
                OUTER PROEDURE 1                   
           

Filed Under: PL SQL Thursday 27 February 2014