The two types of complex types are collections and records. All of the internal components of a collection are of the same PL/SQL data type. The internal components of a record (fields) may have different data types. PL/SQL has three collection types: index-by table (or associative array), nested table, and varray (or variable-size array).
Collection types have different attributes related to how they allocate and store data that can make them useful in certain circumstances to enhance performance. However, due to the tight integration of SQL with PL/SQL, many tasks that might be handled by a collection simply can be addressed by using SQL queries and other Oracle features such as temporary tables.
Records are often created to correspond with existing database tables. They provide an efficient syntax to ensure that variables used by a PL/SQL program are of the same type and precision of data stored in a table. This prevents various errors that can result when data exceeds the size of the declared type and prevents concern of data loss when passing data between PL/SQL and the database.
DECLARE TYPE personinfo IS RECORD ( pid INT :=0, firstname VARCHAR2(20), lastname VARCHAR2(30), address1 VARCHAR2(50), city VARCHAR2(40), state CHAR(2), zipcode VARCHAR2(10) ); person personinfo; BEGIN -- Using composite data type person.pid := 1; person.firstname := 'Sam'; person.lastname := 'Spade'; person.address1 := '1 Maltese Way'; person.city := 'New York'; person.state := 'NY'; person.zipcode := '10020'; DBMS_OUTPUT.PUT_LINE('ID =' || person.pid || ', First Name = ' || person.firstname); END; /
DECLARE Person employees%ROWTYPE; BEGIN SELECT * INTO person FROM employees WHERE rownum <= 1; DBMS_OUTPUT.PUT_LINE('1st "random" employee is ' || person.first_name || '.'); END; /
The problem with using %rowtype is that it is usually used -- in conjunction with select *, a very slow performing query. -- So, use just the columns you need. DECLARE TYPE personinfo IS record (firstname employees.first_name%TYPE, lastname employees.last_name%TYPE, empsalary employees.salary%TYPE); person personinfo; BEGIN SELECT first_name, last_name, salary INTO person FROM employees WHERE rownum <= 1; DBMS_OUTPUT.PUT_LINE('1st "random" person is ' || person.firstname || ' ' || person.lastname || ', with a salary of ' || person.empsalary); END; / -- Here is an alternative to using a record type. declare firstname employees.first_name%type; lastname employees.last_name%type; empsalary employees.salary%type; begin select first_name, last_name, salary into firstname, lastname, empsalary from employees where rownum <= 1; dbms_output.put_line('1st "random" person is ' || firstname || ' ' || lastname || ', with a salary of ' || empsalary); end;
-- Using a collection (in this case an associative array). DECLARE TYPE population_size IS TABLE OF NUMBER INDEX BY VARCHAR2(64); city_population population_size; state_population population_size; NY_population NUMBER; index_position_id VARCHAR2(64); BEGIN -- Enter the populations of ... city_population('Syracuse') := 300000; state_population('NY') := 3000000; city_population('Albany') := 400000; state_population('CA') := 3500000; -- returns value for index position NY NY_population := state_population('NY'); -- returns name of first index position (Albany) index_position_id := city_population.FIRST; DBMS_OUTPUT.PUT_LINE( 'The name of first index position is ' || index_position_id); DBMS_OUTPUT.PUT_LINE ('The value of state(''NY'') is ' || NY_population); END; /
-- Creating a datatype of associative array with the -- index by clause of number. DECLARE TYPE salaries IS TABLE OF employees.salary%TYPE index BY binary_integer; my_salaries salaries; BEGIN -- We'd really use a loop, but we cover -- loops in the next chapter. In the meantime... select salary into my_salaries(1) from employees where rownum <= 1; dbms_output.put_line('1st "random" salary is ' || my_salaries(1)); select salary into my_salaries(2) from employees where rownum <= 1 and my_salaries(1) <> salary; dbms_output.put_line('2nd "random" salary is ' || my_salaries(2)); END; /
-- Creating a datatype of associative array -- WITHOUT the index by clause. DECLARE TYPE nbrs IS TABLE OF NUMBER; my_numbers nbrs; BEGIN my_numbers := nbrs(99,98,97,96,95); dbms_output.put_line('my_numbers(1) is ' || my_numbers(1)); DBMS_OUTPUT.PUT_LINE('my_numbers(3) is ' || my_numbers(3)); my_numbers(5) := 6; DBMS_OUTPUT.PUT_LINE('my_numbers(5) is now ' || my_numbers(5)); -- We can extend the array past its size of 5: my_numbers.extend; my_numbers(6) := 42; DBMS_OUTPUT.PUT_LINE('my_numbers(6) is ' || my_numbers(6)); END; /
DECLARE TYPE state_abbrev IS varray(10) OF CHAR(2); states state_abbrev; BEGIN states := state_abbrev('NY','CA','MD','ME','FL'); dbms_output.put_line( 'My state is: ' || states(1)); -- Just as with an associative array created WITHOUT an index, -- we're "stuck" with just 5 members in the varray even though -- it has a size of 10. But we can extend here as well. states.extend; states(6) := 'GA'; dbms_output.put_line( 'Your state is: ' || states(6)); states.extend; states(7) := 'IL'; END; /