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.