PL/SQL procedures

Procedure in PL/SQL:

·       Procedures are used to perform a particular task. The procedure is the subprogram which is combined to perform large programs.
·       These subprograms do not return a value directly, return can be done by using OUT parameters.
·       Procedures are mainly used to perform an Action.
Difference between Procedure and function:

Procedures	Functions
Procedures are used to perform Particular action.	Functions are used to perform Calculation.
These subprograms do not Return a value directly.	These subprograms are a Return A value using the RETURN keyword.

Syntax:

Create [OR Replace] procedure Procedure_name

[(Parameter_Name [IN | OUT |IN OUT] type [,…])]

{Is | As }

  [ Declaration statements ]          /* Optional*/

Begin

  [ Executable statements ]        /* Mandatory */

Exception

 [ Exception handler ]              /* Optional*/

End procedure_name;

Syntax Explanation:

A procedure is created with the CREATE OR REPLACE PROCEDURE statement.

Declaration Section:

• It is an optional part. The declaration part does not start with DECLARE keyword. 
• It contains a declaration of variables, constants, cursors, exceptions....etc.

Executable Section:

• It is a mandatory part.
• The executable part is used to perform the particular designated action or task.

Exception Handling:

• It is an optional part. 
• The executable part is used to perform the particular designated action or task.

 Example:

1. Create or Replace procedure Ex_p1
   As
   Begin
    Dbms_Output.Put_Line(‘ This is the First Example’);
   End;

Following Procedure for delete, insert, update on Emp table using parameters:

1. CREATE OR REPLACE PROCEDURE xxdtj_update_emp (
      p_empno    IN   emp.empno%TYPE,
      p_ename    IN   emp.ename%TYPE,
      p_job      IN   emp.job%TYPE,
      p_mgr      IN   emp.mgr%TYPE,
      p_sal      IN   emp.sal%TYPE,
      p_deptno   IN   emp.deptno%TYPE
   )
   IS
   BEGIN
      DELETE FROM emp
            WHERE emp.empno = p_empno;
           IF SQL%FOUND
          THEN
              Dbms_Output.Put_Line ('Delete succeeded for empno: ' ||         p_empno);
          INSERT INTO emp
                     (ename, job, mgr, sal, deptno
                     )
              VALUES (p_ename, p_job, p_mgr, p_sal, p_deptno
                     );
          ELSE
         Dbms_Output.Put_Line ('no emp of no ' || p_empno || 'is found.');
         Dbms_Output.Put_Line ('The emp of no is not found so' || p_empno || 'is added into the emp table.');
                 UPDATE emp
                SET ename = 'xxx'
                WHERE empno = p_empno;
        END IF;
   END;

Execution Method:

Method 1:

BEGIN

   xxdtj_update_emp(1001);

END;

Method 2:

Begin

xxdtj_update_emp (p_ename     => 'sana'

                                p_job           => 'ADMIN',

                                  p_mgr           =>  123,

                                  p_sal             =>  20000

                                p_deptno       =>  20);

End; 

Drop Procedure:

 Drop procedure procedure_name;

Share: