Working With PL/SQL Data Structures

Declaring and Initializing a PL/SQL Record Type

DECLARE  -- declare RECORD type variables
-- the following is a RECORD declaration to hold address information
   TYPE location_rec IS RECORD (
        room_number     NUMBER(4),
        building        VARCHAR2(25)
        );
-- you use the %TYPE attribute to declare the datatype of a table column
-- you can include (nest) a record inside of another record
   TYPE person_rec IS RECORD (
        employee_id  employees.employee_id%TYPE,
        first_name   employees.first_name%TYPE,
        last_name    employees.last_name%TYPE,
        location     location_rec
        );
  person  person_rec; -- declare a person variable of type person_rec
BEGIN
-- insert data in a record, one field at a time
  person.employee_id := 20;
  person.first_name := 'James';
  person.last_name := 'Boynton';
  person.location.room_number := 100;
  person.location.building:= 'School of Education';
-- display data in a record
  DBMS_OUTPUT.PUT_LINE( person.last_name || ', ' || person.first_name );
  DBMS_OUTPUT.PUT_LINE( TO_CHAR(person.location.room_number) || ' ' 
                       || person.location.building );
END;
/

Using %ROWTYPE With a Cursor When Declaring a PL/SQL Record

DECLARE -- declare variables
  CURSOR cursor1 IS
    SELECT * FROM employees
      WHERE department_id = 60; -- declare cursor
-- declare record variable that represents a row fetched from the employees table
-- do not need to use TYPE .. IS RECORD with %ROWTYPE attribute
   employee_rec cursor1%ROWTYPE;
BEGIN
-- open the explicit cursor c1 and use it to fetch data into employee_rec
  OPEN cursor1;
  LOOP 
    FETCH cursor1 INTO employee_rec; -- retrieve entire row into record
    EXIT WHEN cursor1%NOTFOUND;
-- the record contains all the fields for a row in the employees table
-- the following displays the data from the row fetched into the record
   DBMS_OUTPUT.PUT_LINE( ' Department ' || employee_rec.department_id 
     || ', Employee: ' || employee_rec.employee_id || ' - ' 
     || employee_rec.last_name || ', ' || employee_rec.first_name );
  END LOOP;
  CLOSE cursor1;
END;
/

Using a PL/SQL VARRAY Type With Character Elements

DECLARE -- declare variables
  TYPE jobids_array IS VARRAY(20) OF VARCHAR2(10);  -- declare VARRAY
  jobids  jobids_array; -- declare a variable of type jobids_array
  howmany NUMBER;  -- declare a variable to hold employee count
BEGIN
  -- initialize the arrary with some job ID values
  jobids := jobids_array('AC_ACCOUNT', 'AC_MGR', 'AD_ASST', 'AD_PRES', 'AD_VP',
                         'FI_ACCOUNT', 'FI_MGR', 'HR_REP', 'IT_PROG', 'PU_MAN',
                         'SH_CLERK', 'ST_CLERK', 'ST_MAN');
-- display the current size of the array with COUNT
  DBMS_OUTPUT.PUT_LINE('The number of elements (current size) in the array is '
                        || jobids.COUNT);
-- display the maximum number of elements for the array LIMIT
  DBMS_OUTPUT.PUT_LINE('The maximum number (limit) of elements in the array is '
                        || jobids.LIMIT);
-- check whether another element can be added to the array
  IF jobids.LIMIT - jobids.COUNT >= 1 THEN 
     jobids.EXTEND(1); -- add one more element
     jobids(14) := 'PU_CLERK';  -- assign a value to the element
  END IF;
-- loop through all the varray values, starting
-- with the FIRST and ending with the LAST element
  FOR i IN jobids.FIRST..jobids.LAST LOOP 
  -- determine the number of employees for each job ID in the array
    SELECT COUNT(*) INTO howmany FROM employees WHERE job_id = jobids(i);
    DBMS_OUTPUT.PUT_LINE ( 'Job ID: ' || RPAD(jobids(i), 10, ' ') || 
                           ' Number of employees: ' || TO_CHAR(howmany));
  END LOOP;
-- display the current size of the array with COUNT
  DBMS_OUTPUT.PUT_LINE('The number of elements (current size) in the array is '
                        || jobids.COUNT);
END;
/

Using a PL/SQL VARRAY Type With Record Type Elements

DECLARE -- declare variables
  CURSOR cursor1 IS SELECT * FROM jobs; -- create a cursor for fetching the rows
  jobs_rec  cursor1%ROWTYPE; -- create a record to hold the row data
 -- declare VARRAY with enough elements to hold all the rows in the jobs table
  TYPE jobs_array IS VARRAY(25) OF cursor1%ROWTYPE; 
  jobs_arr  jobs_array; -- declare a variable of type jobids_array
  howmany   NUMBER;  -- declare a variable to hold employee count
  i         NUMBER := 1; -- counter for the number of elements in the array
BEGIN
  jobs_arr := jobs_array(); -- initialize the array before using
  OPEN cursor1; -- open the cursor before using
  LOOP
    FETCH cursor1 INTO jobs_rec; -- retrieve a row from the jobs table
    EXIT WHEN cursor1%NOTFOUND; -- exit when no data is retrieved
    jobs_arr.EXTEND(1); -- add another element to the varray with EXTEND
    jobs_arr(i) := jobs_rec; -- assign the fetched row to an element the array 
    i := i + 1; -- increment the element count
  END LOOP;
  CLOSE cursor1; -- close the cursor when finished with it
  FOR j IN jobs_arr.FIRST..jobs_arr.LAST LOOP -- loop through the varray elements
  -- determine the number of employees for each job ID in the array
    SELECT COUNT(*) INTO howmany FROM employees WHERE job_id = jobs_arr(j).job_id;
    DBMS_OUTPUT.PUT_LINE ( 'Job ID: ' || RPAD(jobs_arr(j).job_id, 11, ' ') || 
                           RPAD(jobs_arr(j).job_title, 36, ' ') ||
                           ' Number of employees: ' || TO_CHAR(howmany));
  END LOOP;
END;
/