Nested Table

Nested Table - holds an arbitrary number of elements and use  sequential number as subscripts. it can be stored in a database column Collection Methods

•     EXISTS
•     COUNT
•     FIRST and LAST
•     PRIOR and NEXT
•     EXTEND
•     TRIM
•     DELETE

    DECLARE
        TYPE employee_table IS TABLE OF employees%ROWTYPE;
        employee_info  employee_table  := employee_table();

        CURSOR C1_employees_last_name is
            SELECT    employee_id,
                    RPAD(first_name,10)  first_name ,
                    RPAD(last_name,10) last_name,
                    TO_CHAR(salary,99999.99) salary
                FROM employees
                where rownum < 10;

          l_counter    BINARY_INTEGER  := 0;
    BEGIN
       
        FOR r_employees_last_name IN C1_employees_last_name
        LOOP
            employee_info.EXTEND;
            l_counter := l_counter + 1;
            employee_info(l_counter).employee_id        :=  r_employees_last_name.employee_id;
            employee_info(l_counter).salary            :=  r_employees_last_name.salary;
            employee_info(l_counter).first_name        :=  r_employees_last_name.first_name;
            employee_info(l_counter).last_name        :=  r_employees_last_name.last_name;
        END LOOP;

                -- Print Employee informations from nested table
        FOR l_counter in employee_info.FIRST.. employee_info.LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE( employee_info(l_counter).employee_id||' '||
            employee_info(l_counter).first_name ||' '||
            employee_info(l_counter).last_name ||' '||
            employee_info(l_counter).salary);
        END LOOP;
       
        DBMS_OUTPUT.PUT_LINE ('No of Records ' ||' -  '||employee_info.COUNT);

        -- Change salary Locally
        FOR l_counter in employee_info.FIRST.. employee_info.LAST
        LOOP
            IF employee_info(l_counter).salary BETWEEN 0 AND 4999 THEN
                    employee_info(l_counter).salary := employee_info(l_counter).salary * 1.20;
            ELSIF employee_info(l_counter).salary BETWEEN 5000 AND 9999 THEN
                    employee_info(l_counter).salary := employee_info(l_counter).salary * 1.10;
            ELSE
                    employee_info(l_counter).salary := employee_info(l_counter).salary * 1.05;
            END IF;
           
            UPDATE employees SET salary = employee_info(l_counter).salary
                WHERE employee_id = employee_info(l_counter).employee_id;
        END LOOP;

        FOR l_counter in employee_info.FIRST.. employee_info.LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE( employee_info(l_counter).employee_id||' '||
            employee_info(l_counter).first_name ||' '||
            employee_info(l_counter).last_name ||' '||
            employee_info(l_counter).salary);
        END LOOP;

        /* PRIOR and NEXT Demostration */
        FOR l_counter in employee_info.FIRST.. employee_info.LAST
        LOOP
            IF l_counter <> 1 THEN
                DBMS_OUTPUT.PUT_LINE( 'Prior emplyee id is     '||' <-- '
                ||employee_info(employee_info.PRIOR(l_counter)).employee_id);
            END IF;
            DBMS_OUTPUT.PUT_LINE( 'Current emplyee id is '||' --- ' ||employee_info(l_counter).employee_id);
            IF l_Counter <> employee_info.LAST THEN
                DBMS_OUTPUT.PUT_LINE( 'Next emplyee id is      '
                ||' --> ' ||employee_info(employee_info.NEXT(l_counter)).employee_id);
            END IF;
        END LOOP;
       
                -- Exception SUBSCRIPT_BEYOND_COUNT Demostration - Possible error 1
            DECLARE
                TYPE emp_email_table IS TABLE OF VARCHAR2(25);
                email_info emp_email_table := emp_email_table();
            BEGIN
                FOR l_counter in employee_info.FIRST.. employee_info.LAST
                LOOP
                    --email_info.EXTEND; -- Exception cause
                    SELECT email INTO email_info(l_counter)
                        FROM employees
                        WHERE EMPLOYEE_ID = employee_info(l_counter).employee_id;
                DBMS_OUTPUT.PUT_LINE( 'Empoyee No --> '||email_info(l_counter));
                END LOOP;
            EXCEPTION
                WHEN SUBSCRIPT_BEYOND_COUNT THEN
                    DBMS_OUTPUT.PUT_LINE ('Exception ****   SUBSCRIPT_BEYOND_COUNT exception raised');
            END;
       
            -- Exception SUBSCRIPT_BEYOND_COUNT Demostration - Possible error 2
            DECLARE
                TYPE emp_email_table IS TABLE OF EMPLOYEES.EMAIL%TYPE;
                email_info emp_email_table := emp_email_table();
            BEGIN
                FOR l_counter in employee_info.FIRST.. employee_info.LAST
                LOOP
                    IF NOT email_info.EXISTS(l_counter) THEN   
                        email_info.EXTEND;
                    END IF;
                    email_info(l_counter) := trunc(employee_info(l_counter).first_name)
                    ||'.'|| trunc(employee_info(l_counter).last_name)||'@yahoo.com';
                   
                DBMS_OUTPUT.PUT_LINE( 'Empoyee Email address --> '||email_info(l_counter));
                END LOOP;
            EXCEPTION
                WHEN VALUE_ERROR THEN
                    DBMS_OUTPUT.PUT_LINE ('Exception ****   Email address  is longer than 25 characters identified ');
            END;
           
            -- Exception COLLECTION_IS_NULL Demostration - Possible error 3
            DECLARE
                TYPE emp_email_table IS TABLE OF EMPLOYEES.EMAIL%TYPE;
                email_info emp_email_table;
                --:= emp_email_table();
            BEGIN
                FOR l_counter in employee_info.FIRST.. employee_info.LAST
                LOOP
                    email_info.EXTEND;
                    email_info(l_counter) := trunc(employee_info(l_counter).first_name)||'@yahoo.com';
                    DBMS_OUTPUT.PUT_LINE( 'Empoyee Email address --> '||email_info(l_counter));
                END LOOP;
            EXCEPTION
                WHEN COLLECTION_IS_NULL THEN
                    DBMS_OUTPUT.PUT_LINE ('Exception ****   COLLECTION_IS_NULL exception raised');
            END;
        ROLLBACK;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE (' OTHETS exception raised'|| SQLERRM);
    END;
    /
    198 Donald     OConnell   2600
199 Douglas    Grant      2600
200 Jennifer   Whalen     4400
201 Michael    Hartstein  13000
202 Pat        Fay        6000
203 Susan      Mavris     6500
204 Hermann    Baer       10000
205 Shelley    Higgins    12000
206 William    Gietz      8300
No of Records  -  9
198 Donald     OConnell   3120
199 Douglas    Grant      3120
200 Jennifer   Whalen     5280
201 Michael    Hartstein  13650
202 Pat        Fay        6600
203 Susan      Mavris     7150
204 Hermann    Baer       10500
205 Shelley    Higgins    12600
206 William    Gietz      9130
Current emplyee id is  --- 198
Next emplyee id is       --> 199
Prior emplyee id is      <-- 198
Current emplyee id is  --- 199
Next emplyee id is       --> 200
Prior emplyee id is      <-- 199
Current emplyee id is  --- 200
Next emplyee id is       --> 201
Prior emplyee id is      <-- 200
Current emplyee id is  --- 201
Next emplyee id is       --> 202
Prior emplyee id is      <-- 201
Current emplyee id is  --- 202
Next emplyee id is       --> 203
Prior emplyee id is      <-- 202
Current emplyee id is  --- 203
Next emplyee id is       --> 204
Prior emplyee id is      <-- 203
Current emplyee id is  --- 204
Next emplyee id is       --> 205
Prior emplyee id is      <-- 204
Current emplyee id is  --- 205
Next emplyee id is       --> 206
Prior emplyee id is      <-- 205
Current emplyee id is  --- 206
Exception ****   SUBSCRIPT_BEYOND_COUNT exception raised
Exception ****   Email address  is longer than 25 characters identified
Exception ****   COLLECTION_IS_NULL exception raised

PL/SQL procedure successfully completed.

Associated array (index-by table / PL-SQL table)

·         Unbounded set of key-value pairs. The subscript of the associated array can be integer or string. Subscripts are stored in sort order not creation order. For string subscript sort order is determined by NLS_SORT and NLS_COMP initialization parameters.

·         Associated array created empty but not null.

·         Does not need space and network operation

·         Stored data of the associated array cannot be manipulated by DML statement.

DECLARE

  -- Associative array index by string

  TYPE population  IS TABLE OF NUMBER INDEX BY VARCHAR2(25);

  -- Associative array variable 

  metro_population population;

 

  l_metro VARCHAR2(25);

 

BEGIN

   metro_population('Delhi')   := 18916890;

   metro_population('Mumbai')  := 21900967;

   metro_population('Chennai') := 7413779;

   metro_population('Kolkata') := 15644040;

  

   l_metro := metro_population.FIRST;

   WHILE l_metro IS NOT NULL

   LOOP

     DBMS_OUTPUT.PUT_LINE ('Population of '||

     l_metro||' is  '||  metro_population(l_metro));

     l_metro := metro_population.NEXT(l_metro);

   END LOOP;

END;

/

Population of Chennai is  7413779

Population of Delhi is  18916890

Population of Kolkata is  15644040

Population of Mumbai is  21900967

Collection

Internal components of the collection are called elements. Each element of the collection can be access by using unique subscripts. To create a collection type it is necessary to define a collection type and then create a variable of that type.

There are three types of collections

1.       Associated Array ( index-by table/ PL/SQL table ) à An unbounded collection

2.       VARRAY ( variable size array) à Bounded collection

3.       Nested table à Unbounded collection

Parameterized cursor

DECLARE
    v_deptno NUMBER(4);
    -- department cursor declaration
     CURSOR c_dept IS
            SELECT deptno FROM dept;
     -- employee cursor declaration
     CURSOR c_emp(l_deptno NUMBER) IS
            SELECT empno, ename,sal
            FROM emp
            WHERE deptno = l_deptno;
         
BEGIN
     OPEN c_dept;
     LOOP
          FETCH c_dept INTO v_deptno;
          EXIT WHEN c_dept%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE ('Department No is '|| v_deptno);
         
          FOR r_emp IN c_emp(v_deptno) LOOP
              DBMS_OUTPUT.PUT_LINE('Original record '|| r_emp.empno||' - '||
              RPAD(r_emp.ename,7)||' - '||TO_CHAR(r_emp.sal,99999.99) );
              UPDATE emp SET sal = sal * 1.05 WHERE empno = r_emp.empno;
          END LOOP;
         
          FOR r_emp1 IN c_emp(v_deptno) LOOP
                  DBMS_OUTPUT.PUT_LINE('Updated record- '|| r_emp1.empno||' - '||
                  RPAD(r_emp1.ename,7)||' - '||TO_CHAR(r_emp1.sal,99999.99) );
          END LOOP;
     END LOOP;
    
     CLOSE c_dept;
     ROLLBACK;
EXCEPTION
    WHEN OTHERS THEN
        IF C_DEPT%ISOPEN THEN
           CLOSE c_dept;
        END IF;
        RAISE_APPLICATION_ERROR(-20001,'An error occured  -' ||
        SQLCODE ||'- Error - '||SQLERRM);
END;
/
Department No is 10
Original record 7782 - CLARK   -   2450.00
Original record 7839 - KING    -   5000.00
Original record 7934 - MILLER  -   1300.00
Updated record- 7782 - CLARK   -   2572.50
Updated record- 7839 - KING    -   5250.00
Updated record- 7934 - MILLER  -   1365.00
Department No is 20
Original record 7369 - SMITH   -    800.00
Original record 7566 - JONES   -   2975.00
Original record 7788 - SCOTT   -   3000.00
Original record 7876 - ADAMS   -   1100.00
Original record 7902 - FORD    -   3000.00
Updated record- 7369 - SMITH   -    840.00
Updated record- 7566 - JONES   -   3123.75
Updated record- 7788 - SCOTT   -   3150.00
Updated record- 7876 - ADAMS   -   1155.00
Updated record- 7902 - FORD    -   3150.00
Department No is 30
Original record 7499 - ALLEN   -   1600.00
Original record 7521 - WARD    -   1250.00
Original record 7654 - MARTIN  -   1250.00
Original record 7698 - BLAKE   -   2850.00
Original record 7844 - TURNER  -   1500.00
Original record 7900 - JAMES   -    950.00
Updated record- 7499 - ALLEN   -   1680.00
Updated record- 7521 - WARD    -   1312.50
Updated record- 7654 - MARTIN  -   1312.50
Updated record- 7698 - BLAKE   -   2992.50
Updated record- 7844 - TURNER  -   1575.00
Updated record- 7900 - JAMES   -    997.50
Department No is 40
       

Nested Cursor

DECLARE
     -- local variable declaration
     v_deptno dept.deptno%TYPE;  
    
    -- department cursor declaration
     CURSOR c_dept IS
            SELECT deptno FROM dept;
     -- employee cursor declaration
     CURSOR c_emp IS
            SELECT empno, ename,sal
            FROM emp
            WHERE deptno = v_deptno;
         
BEGIN
     OPEN c_dept;
     LOOP
          FETCH c_dept INTO v_deptno;
          EXIT WHEN c_dept%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE ('Department No is '|| v_deptno);
          FOR r_emp IN c_emp LOOP
              DBMS_OUTPUT.PUT_LINE('Original record '|| r_emp.empno||' - '||
              RPAD(r_emp.ename,6)||' - '||TO_CHAR(r_emp.sal,99999.99) );
              UPDATE emp SET sal = sal * 1.05 WHERE empno = r_emp.empno;
          END LOOP;
          FOR r_emp1 IN c_emp LOOP
                  DBMS_OUTPUT.PUT_LINE('Updated record  '|| r_emp1.empno||' - '||
                  RPAD(r_emp1.ename,6)||' - '||TO_CHAR(r_emp1.sal,99999.99) );
          END LOOP;
     END LOOP;
     CLOSE c_dept;
     ROLLBACK;
EXCEPTION
    WHEN OTHERS THEN
        IF C_DEPT%ISOPEN THEN
           CLOSE c_dept;
        END IF;
        RAISE_APPLICATION_ERROR(-20001,'An error occured  -' ||
        SQLCODE ||'- Error - '||SQLERRM);
END;
/
Department No is 10
Original record 7782 - CLARK - 2450.00
Original record 7839 - KING - 5000.00
Original record 7934 - MILLER - 1300.00
Updated record- 7782 - CLARK - 2572.50
Updated record- 7839 - KING - 5250.00
Updated record- 7934 - MILLER - 1365.00
Department No is 20
Original record 7369 - SMITH - 800.00
Original record 7566 - JONES - 2975.00
Original record 7788 - SCOTT - 3000.00
Original record 7876 - ADAMS - 1100.00
Original record 7902 - FORD - 3000.00
Updated record- 7369 - SMITH - 840.00
Updated record- 7566 - JONES - 3123.75
Updated record- 7788 - SCOTT - 3150.00
Updated record- 7876 - ADAMS - 1155.00
Updated record- 7902 - FORD - 3150.00
Department No is 30
Original record 7499 - ALLEN - 1600.00
Original record 7521 - WARD - 1250.00
Original record 7654 - MARTIN - 1250.00
Original record 7698 - BLAKE - 2850.00
Original record 7844 - TURNER - 1500.00
Original record 7900 - JAMES - 950.00
Updated record- 7499 - ALLEN - 1680.00
Updated record- 7521 - WARD - 1312.50
Updated record- 7654 - MARTIN - 1312.50
Updated record- 7698 - BLAKE - 2992.50
Updated record- 7844 - TURNER - 1575.00
Updated record- 7900 - JAMES - 997.50
Department No is 40

          

Cursor FOR Loop

Cursor for loop simplifies the PL/SQL program where PL/SQL itself take care most of the things which includes cursor steps and variable declaration.

Cursor for loop can be an implicit or explicit cursor. If the sole purpose to use the SQL statement once in the program then SQL statement can be specified inside the cursor “For Loop”. This form of the cursor is called Implicit Cursor for Loop.

If our requirement to use the SQL multiple time as explained in below example, then it is good to use Explicit Cursor for Loop, to avoid the re-writing of the SQL. Even through Explicit cursor is associated with a Cursor for Loop, its execution cycle is controlled by managed by Cursor for Loop internally.

Cursor "FOR LOOP" implicitly declares a loop index of %ROWTYPE record variable, which is act as a pointer to the result set processed by associated SQL statement. Value of the result set accessed by using this index variable followed by (.) dot and column name from the select list. This variable is local to the loop.

SET SERVEROUTPUT ON
DECLARE
  CURSOR C_EMP IS
    SELECT empno,RPAD(ename,10) ename, sal
    FROM emp
    WHERE deptno = 20;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Salary before update');
  FOR r_emp in C_EMP LOOP
    DBMS_OUTPUT.PUT_LINE(r_emp.empno||' - '||r_emp.ename||' - '||TO_CHAR(r_emp.sal,99999.99));
  END LOOP;
  FOR r_emp in C_EMP LOOP
    UPDATE emp SET sal = sal * 1.05 WHERE empno = r_emp.empno;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'Salary after update');
  FOR r_emp in C_EMP LOOP
    DBMS_OUTPUT.PUT_LINE(r_emp.empno||' - '||r_emp.ename||' - '||TO_CHAR(r_emp.sal,99999.99));
  END LOOP;
  ROLLBACK;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception - OTHERS');
END;
/

anonymous block completed
Salary before update
7369 - SMITH      -    800.00
7566 - JONES      -   2975.00
7788 - SCOTT      -   3000.00
7876 - ADAMS      -   1100.00
7902 - FORD       -   3000.00

Salary after update
7369 - SMITH      -    840.00
7566 - JONES      -   3123.75
7788 - SCOTT      -   3150.00
7876 - ADAMS      -   1155.00
7902 - FORD       -   3150.00

Tips on cursor

1. The number of variables must be equal to the number of column or expression in the SELECT list.
2. The number of component in record must match the column or expression in the SELECT list.
3. The scope of cursor declared in the main block extends to the sub block.
4. PL/SQL variable , expression and SQL function can be included in the cursor SELECT List.
5. Alias name is required for calculated column when it is referenced in the program.

Explicit Cursor - User Defined Data type - CASE statement

SET SERVEROUTPUT ON
DECLARE
  --Cursor declaration
  CURSOR C_EMP is
      SELECT  INITCAP(ename),
      (CASE 
          WHEN sal + nvl(comm,0) > 4000 THEN 'High Earning'
          WHEN sal + nvl(comm,0) BETWEEN 2000 AND 3999 THEN 'Medium Earning'
          ELSE 'Low Earning'
        END) Earning
        FROM emp;
 
  --TYPE declaration
  TYPE emp_salary_info IS RECORD
  (name emp.ename%TYPE,
  earning VARCHAR2(20));
  --Variable declaration
  v_emp_sal_info emp_salary_info;
BEGIN
  -- open cursor
  OPEN C_EMP;
  LOOP
   -- fetch cursor
    FETCH C_EMP INTO v_emp_sal_info;
      EXIT WHEN C_EMP%NOTFOUND;
      IF C_EMP%ROWCOUNT = 1 THEN
        DBMS_OUTPUT.PUT_LINE( 'ENAME'||'---->'||'EARNING DETAIL' );
      END IF;
      DBMS_OUTPUT.PUT_LINE( v_emp_sal_info.name||'---->'||v_emp_sal_info.earning);
  END LOOP;
  --close cursor
  CLOSE C_EMP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found exception raised');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Others Exception raised');
    IF C_EMP%ISOPEN THEN
      CLOSE C_EMP;
    END IF;
END;

anonymous block completed
ENAME---->EARNING DETAIL
Smith---->Low Earning
Allen---->Low Earning
Ward---->Low Earning
Jones---->Medium Earning
Martin---->Medium Earning
Blake---->Medium Earning
Clark---->Medium Earning
Scott---->Medium Earning
King---->High Earning
Turner---->Low Earning
Adams---->Low Earning
James---->Low Earning
Ford---->Medium Earning
Miller---->Low Earning

Explicit Cursor

The mean of generating an explicit cursor is to name the cursor in the declaring section of the PL/SQL block.

The only advantage of declaring explicit cursor over implicit cursor is that programmer have more control. The process of working with the explicit cursor consist of the following steps

Declaring – This initialize the cursor into memory

Open – Declared cursor is open and memory is allocated

Fetch – Now cursor can retrieve data

Close - Cursor must be closed to release the allocated memory.

SET SERVEROUTPUT ON
DECLARE
  --Cursor declaration
  CURSOR C_EMP is
      SELECT  INITCAP(ename), sal, NVL(comm,0)
      FROM emp;
  --Variable declaration
  v_ename   emp.ename%TYPE;
  v_sal     emp.sal%TYPE;
  v_comm    emp.comm%TYPE;
  v_earning BINARY_INTEGER;
BEGIN
  -- open cursor
  OPEN C_EMP;
  LOOP
   -- fetch cursor
    FETCH C_EMP INTO v_ename,v_sal,v_comm;
      EXIT WHEN C_EMP%NOTFOUND;
      IF C_EMP%ROWCOUNT = 1 THEN
        DBMS_OUTPUT.PUT_LINE( 'ENAME'||'---->'||'EARNING' );
      END IF;
      v_earning := v_sal + v_comm;
      DBMS_OUTPUT.PUT_LINE( v_ename||'---->'||v_earning);
  END LOOP;
  --close cursor
  CLOSE C_EMP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found exception raised');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Others Exception raised');
    IF C_EMP%ISOPEN THEN
      CLOSE C_EMP;
    END IF;
END;
/

anonymous block completed
ENAME---->EARNING
Smith---->800
Allen---->1900
Ward---->1750
Jones---->2975
Martin---->2650
Blake---->2850
Clark---->2450
Scott---->3000
King---->5000
Turner---->1500
Adams---->1100
James---->950
Ford---->3000
Miller---->1300

Implicit Cursor - SQL%ROWCOUNT attribute

An implicit cursor can tell you how many row were affected by an update statement.

SET SERVEROUTPUT ON
BEGIN
  UPDATE emp SET sal = sal * 1.05;
  DBMS_OUTPUT.PUT_LINE('No. of records updated : ' ||SQL%ROWCOUNT);
END;

anonymous block completed
No. of records updated : 14