Live ORACLE

DECLARE

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

                               lastname employees.last_name%TYPE,

                               hiredate employees.hire_date%TYPE);

   -- Array type that can hold information 10 employees

   TYPE emplist_arr IS VARRAY(10) OF EMP_NAME_REC;

   seniorsalespeople  EMPLIST_ARR;

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

   CURSOR c1 IS

     SELECT first_name,

            last_name,

            hire_date

     FROM   employees;

   TYPE nameset IS TABLE OF c1%ROWTYPE;

   seniorten          NAMESET;

   endcounter         NUMBER := 10;

BEGIN

  seniorsalespeople := Emplist_arr();

 

  SELECT   first_name,

           last_name,

           hire_date

  BULK COLLECT INTO seniorten

  FROM     employees

  WHERE    job_id = 'SA_REP'

  ORDER BY hire_date;

 

  IF seniorten.LAST > 0 THEN

    IF seniorten.LAST < 10 THEN

      endcounter := seniorten.LAST;

    END IF;

   

    FOR i IN 1.. endcounter LOOP

      seniorsalespeople.EXTEND(1);

     

      Seniorsalespeople(i) := Seniorten(i);

     

      dbms_output.Put_line(Seniorsalespeople(i).lastname

                           ||', '

                           ||Seniorsalespeople(i).firstname

                           ||', '

                           ||Seniorsalespeople(i).hiredate);

    END LOOP;

  END IF;

END;

/

Assigning Values to VARRAYs with Complex Datatypes