Saturday 12 March 2016

Oracle PLSQL

PLSQL

     INDEX

Features of PL/SQL


PL/SQL Block


  • Variable Declaration:
  • Executable Statements:
  • Anonymous Blocks:
  • Named Blocks
  • Attribute Declaration:
Conditional Constructs

  • IF
  • Simple IF
  • Complex IF
  • CASE
Iteration Control Statements

  • Simple LOOP
  • WHILE LOOP
  • FOR LOOP
Cursors

  • Implicit Cursor
  • Explicit Cursor
  • Cursor FOR Loop
  • Cursors with Parameters
Exceptions

  • Pre Defined Exceptions
  • USER DEFINED EXCEPTIONS
  • Non Pre Defined Exceptions
PRAGMA
Autonomous Transaction
Sub Programs

  • Procedure
  • Function
  • Packages
Composite Data Types
Collections
Dynamic SQL
BULK COLLECT
Ref Cursor
File Input  and Output

  • UTL_FILE Operations
  • Modes
Triggers

  • NEW and OLD
  • RAISE_APPLICATION_ERROR
  • Instead of Triggers


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



PL/SQL: Programming Language of SQL


    Features of PL/SQL:
  • Supports to execute a block of statements as a unit.
  • Supports Variables and Constants.
  • Supports conditional constructs (IF).
  • Supports iteration control statements (FOR and WHILE Loops).
  • Supports error handling.
  • Supports to define user defined data types
  • Supports Event driven programming thru triggers.
  • Supports to share code with other users thru sub programs. 

It is an collection of SQL and Programming Language Statements
We have two types of PL/SQL Blocks

     Variable Declaration:

  • Supports all SQL data types and Boolean data types.
  • Data types are

Scalar Data types:
      1. NUMBER
      2. CHAR
      3. VARCHAR2
      4. DATE
      5. CONSTANT
      6. BOOLEAN etc 
Composite Data types:
            1. RECORDS
            2. TABLES
            3. VARRAYS
     LOB Datatypes: 
            1. BLOB
            2. BFILE
            3. CLOB
     Reference Data types
            1. REF CURSORS
              
               DECLARE
                  v_empno              number (10);
                  v_ename              varchar2 (15);
                  c_pin constant      number(6):=600101;
                  v_doj date            default    sysdate;
            v_flag                  boolean:= true;

Executable Statements: 
  • DML, TCL are allowed
  • DDL, DCL are allowed through Dynamic SQL
  • SELECT <column list> INTO <variables> from <variable list> WHERE <condition> (It is used to retrieve the columns values into variables)
  •   :=  <==> Assignment Operator
  •  =  <==>Comparison Operator
  •  --  <==> Comment
  •   /*       */    <==> Multiple Line Comment
  • DBMS_OUTPUT.PUT_LINE('Message Text'|| variables);  (It is used to print messages and variable contents on the screen)
Anonymous Blocks: Blocks without any standard name
            DECLARE
                                 <Variable Declaration> ;----Optional
      BEGIN
               <Executable Statements>;
                                    --
                                    --
     EXCEPTION
               <Executable Statements> ;----Optional
                                   --
      END;

Named Blocks: Blocks with a permanent name
            EX:Sub Programs and Triggers
           CREATE or REPLACE PROCEDURE proc as
                      <Variable Declaration>;----Optional   
                 BEGIN
                      <Executable Statements>;
                       --
                       --
                EXCEPTION
                   <Executable Statements>;----Optional
                    --
                    --
                END proc;    


This PL/SQL Block retrieves the employee details and calculates net salary and prints:
    DECLARE
                   v_empno number(10):=7900;
                   v_ename varcaher2 (15);
                   v_sal  number(12, 2);
                   v_comm number(10, 2);
       v_net number(12, 2);
     BEGIN
                --Retrieving employee details
                   SELECT ename, sal, comm INTO v_ename, v_sal, v_comm FROM emp
                    WHERE empno = v_empno;
                --Calculating net salary
                  v_net := v_sal + nvl (v_comm, 0);
                  DBMS_OUTPUT.PUT_LINE ('Employee Details are :');
                 DBMS_OUTPUT.PUT_LINE (v_empno||','||v_ename||','||v_net);
     END;

Attribute Declaration:

Supports to define the variables dynamically according to table structure

%Type: Used to define the variable according to column structure
       Variable <table>.<column>%type;
       Example: v_empno emp.empno%type;
%Rowtype: Used to define the variable representing complete table structure
          variable <table name>%rowtype;
          Example: l_emp   emp%rowtype;


Conditional Constructs:     
IF  CASE  
   Simple IF:
            IF <condition> THEN
                 <Executable Statements>;
            END IF;
      Complex IF:
       IF <Condition1> THEN
        <Executable Statements> ;
            ELSIF <Condition2> THEN
                   <Executable Statements> ;
                       -
                       -
             ELSE
                  <Executable Statements> ;


             END IF;
This PL/SQL Block checks for existing commission and provides new commission:
          DECLARE
               v_eno emp.empno%type:=&empno;
               v_ename emp.ename%type;
               v_comm emp.comm%type;
          BEGIN
              SELECT ename, comm INTO v_ename, v_comm 
                 FROM emp 
               WHERE  empno=v_eno;
              IF v_comm is NULL THEN
                  v_comm := 500;
              ELSIF v_comm <500 THEN
                         v_comm := v_comm+200;
              ELSE
                        v_comm := v_comm + v_comm *  .25;
              END IF;
                       UPDATE emp SET comm = v_comm WHERE empno = v_eno;
                       DBMS_OUTPUT.PUT_LINE (v_eno||','||v_ename||','||v_comm);
          END;


    CASE:
                   CASE <variable>
                   WHEN <value1> THEN
                     <Executable Statements>;
                   WHEN <value2> THEN
                     <Executable Statements>;
                    -
                    -
                   OTHERS
                   <Executable Statements>;


                   END CASE;


This PL/SQL Block checks for Grade      
       DECLARE
                   grade char (1) := '&grade';
        BEGIN
             CASE grade
                    WHEN 'A' THEN
                    DBMS_OUTPUT.PUT_LINE ('Grade in A');
                    WHEN 'B' THEN
                    DBMS_OUTPUT.PUT_LINE ('Grade in B');
                    WHEN 'C' THEN
                    DBMS_OUTPUT.PUT_LINE ('Grade in C');
                    ELSE
                    DBMS_OUTPUT.PUT_LINE ('Grade in D');
             END CASE;
         END;
Iteration Control Statements:
  •  Supports to execute the block of statements repeatedly
  •  Four types of Iterations are there
1. Simple Loop
2. WHILE Loop
3. Numeric FOR Loop
4. Cursor FOR Loop

Simple LOOP:
           LOOP
              < Executable statements>;
           EXIT WHEN <condition>;
           END LOOP;

This PL/SQL Block prints the first 10 Numbers:
          DECLARE
                 N Number (2):=1;
          BEGIN
                DBMS_OUTPUT.PUT_LINE ('The Numbers are :');
              LOOP
                 DBMS_OUTPUT.PUT_LINE (N);
                 N: = N+1;
                 EXIT WHEN (N > 10);
              END LOOP;
         END;
WHILE LOOP: It is pre tested Loop       
         WHILE <condition> LOOP
              < Executable statements>;
         END LOOP;          
This PL/SQL Block prints the first 10 Numbers:
     DECLARE
                      N Number: =1;
          BEGIN
                      DBMS_OUTPUT.PUT_LINE ('The Numbers are :');
               WHILE (N <= 10) LOOP
                     DBMS_OUTPUT.PUT_LINE (N);
                      N: = N+1;
               END LOOP;
          END;
FOR LOOP
  • No need to DECLARE the variables
  • No need to assign the values
  • No need to Increment the value
 FOR <variable> IN [REVERSE] <lower bound>.. <upper bound> 
  LOOP
                < Executable statements>;
         END LOOP;
This PL/SQL Block prints the first 10 Numbers:
    BEGIN
                    DBMS_OUTPUT.PUT_LINE ('The Numbers are:');
              FOR N IN 1..10 LOOP
                    DBMS_OUTPUT.PUT_LINE (N);
              END LOOP;
        END;
Cursors:
  • It is a temporary memory used to hold the transactional data
  • It is valid in PL/SQL block only
  • It will be created in logical memory of server
  • They are not stored in data base and they are not reusable
  • Two types of cursors are there
                 1. Implicit Cursor
                 2.Explicit Cursor

Implicit Cursor: 

  • Automatically created by Oracle when ever DML statements are performed.
  • It has Standard name 'SQL'
  • Cursor Attribute are
                   SQL%FOUND               
                   SQL%NOTFOUND
                   SQL%ROWCOUNT
                   SQL%ISOPEN
  • It will give the status of DML operation
  • Valid in PL/SQL block only
This PL/SQL Block gives the no of employees are updated:
  DECLARE
                   v_deptno emp.deptno%type: =&deptno;
      BEGIN
                   UPDATE emp SET sal := sal + 300 WHERE deptno = v_deptno;
             IF SQL%NOTFOUND THEN
                   DBMS_OUTPUT.PUT_LINE (' No Employees in Department:'||v_deptno);
             ELSE
                   DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT||'Employees are Updated');
             COMMIT;
             END IF;
     END;

Explicit Cursor:
  • Created by user in PL/SQL block
  • Declaration of Cursor
              Cursor <cursor name> is <select statement>;
  • Cursor Operations are
           1. OPEN <Cursor name> : 
              Open the Cursor (Memory will be allocated to Cursor after opening it only)
           2. FETCH <Cursor name>  INTO <variable list>:
              Used to retrieve the  rows from Cursor to Variables at a time. It Returns only one row. 
              Generally it will be provided in LOOP
           3. CLOSE <Cursor name> <==> Close the Cursor (Memory allocated will be  Deallocated)
  • Cursor Attributes are
                  1. <Curcor name>%ISOPEN <==> Return true if Cursor is opened Successfully 
                  2. <Cursor name>%FOUND <==> Return true if fetch statement is Successful
                  3. <Cursor name>%NOTFOUND <==> Return true if fetch statement is not successful
                  4. <Cursor name>%ROWCOUNT <==> Gives the number of rows fetched  from Cursor

This PL/SQL Block checks for existing commission and provides new commission:
           DECLARE
                     v_eno emp.empno%type;
                     v_ename emp.ename%type;
                     v_comm emp.comm%type;
                     CURSOR emp_cursor is SELECT empno, ename, comm FROM emp;
            BEGIN
                  --Opening Cursor
              OPEN emp_cursor;
              LOOP
                --Fetching Rows
                    FETCH emp_cursor INTO v_eno, v_ename, v_comm;
                    EXIT WHEN emp_cursor%NOTFOUND;                         
              IF v_coom is NULL THEN
                    v_comm := 500;
              ELSIF v_comm = 0 THEN
                     v_comm:= 200;
              ELSE
                    v_comm:= v_comm + v_comm * .25;
             END IF;
                    UPDATE emp SET comm = v_comm WHERE empno = v_eno;
                    DBMS_OUTPUT.PUT_LINE (v_eno||','||v_ename||','||v_comm);
              END LOOP;
                    DBMS_OUTPUT.PUT_LINE ('No of rows manipulated with new comm:'||  
                                                            emp_cursor%ROWCOUNT);
             --Closing Cursor
             CLOSE emp_cursor;
             COMMIT;
       END;


Cursor FOR Loop:
  • No need to OPEN, FETCH and CLOSE the Cursor
  • No need to declare variables
  • No need to Check for end
  • Syntax of Cursor FOR Loop is
                  FOR <variable> IN <Cursor name> LOOP
                        < Executable statements>;  
                  END LOOP;

This PL/SQL Block gives Employee Details
      DECLARE
               CURSOR emp_cursor is SELECT empno, ename, comm FROM emp;
          BEGIN
              FOR C IN emp_cursor LOOP
              DBMS_OUTPUT.PUT_LINE (C.empno||','||C.ename||','||C.comm);
          END LOOP;
      END;
Cursors with Parameters:
  • Supports to define Cursors with arguments
  • Cursor accept values dynamically while opening them
  • Maximum 32 parameters can be passed to cursor
Example for Cursors with Parameters:
      DECLARE
              CURSOR emp_cursor (dno number) IS
              SELECT * 
                FROM emp 
               WHERE deptno = dno;
               i emp_cursor%ROWTYPE;
               incr number(10);
               exp number(10);
        BEGIN
               OPEN emp_cursor(&dno);
                LOOP
                FETCH emp_cursor INTO i;
                EXIT WHEN emp_cursor%NOTFOUND;
                    exp := ROUND (MONTHS_BETWWEN(SYSDATE, i.hiredate)/12;
                 IF exp <= 2 THEN
                     incr := i.sal + 300;
                ELSIF exp > 2 AND exp <= 4 THEN
                    incr := i.sal + 500;
              ELSE
                    incr := i.sal + 700;
              END IF;
                    UPDATE emp SET sal := sal+ incr WHERE empno = i.empno;
                    DBMS_OUTPUT.PUT_LINE (i.empno||','||i.ename||','||i.sal||','||exp||','||incr);
              END LOOP;
              CLOSE emp_cursor;
              COMMIT;
     END;