Live ORACLE

DECLARE

  TYPE emp_name_rec IS RECORD(firstname employees.first_name%TYPE,

                               lastname employees.last_name%TYPE,

                               hiredate employees.hire_date%TYPE);

   -- Table type that can hold information about employees

   TYPE emplist_tab IS TABLE OF EMP_NAME_REC;

   seniorsalespeople  EMPLIST_TAB;

   -- Declare a cursor to select a subset of columns.

   CURSOR c1 IS

     SELECT first_name,

            last_name,

            hire_date

     FROM   employees;

   endcounter         NUMBER := 10;

   TYPE empcurtyp IS REF CURSOR;

   emp_cv             EMPCURTYP;

BEGIN

  OPEN emp_cv FOR

    SELECT   first_name,

             last_name,

             hire_date

    FROM     employees

    WHERE    job_id = 'SA_REP'

    ORDER BY hire_date;

 

  FETCH emp_cv BULK COLLECT INTO seniorsalespeople;

 

  CLOSE emp_cv;

 

  -- for this example, display a maximum of ten employees

  IF seniorsalespeople.LAST > 0 THEN

    IF seniorsalespeople.LAST < 10 THEN

      endcounter := seniorsalespeople.LAST;

    END IF;

   

    FOR i IN 1.. endcounter LOOP

      dbms_output.Put_line(Seniorsalespeople(i).lastname

                           ||', '

                           ||Seniorsalespeople(i).firstname

                           ||', '

                           ||Seniorsalespeople(i).hiredate);

    END LOOP;

  END IF;

END;

/

Assigning Values to Tables with Complex Datatypes