| [ Oracle PL/SQL ] |
 | Composite data types: COllections and Records |
Composite data type:
Collection:
Collection Types:
|
Number of Elements: { Specified | Unspecified }
- Whether you need to define the maximum size of the collection at declaration time.
- Specified: VARRAY; Unspecified: Associative array; Nested table
Uninitialized status: { Empty | Null }
- Empty: Exists, but has no elements. (Use
EXTENDmethod to add). - Null: Does not exist. Must be initialized (Made empty or assigned a value).
- Null: VARRAY, Nested Table. Empty: Associative Array
Scope: { Local | Public | Standalone (Schema) }
- Local: Defined and available only within a PL/SQL block.
- Public: Defined in a package specification.(ref:
package_name.type_name) - Standalone: Defined at schema level.(ref:
CREATE TYPE)
| Collections: Associative Array (former PL/SQL TABLE) |
- Name introduced in Oracle 10g
- Oracle 8 - 9i: index-by tables
- Oracle 7: Pl/SQL tables
- Can be indexed by Integer or String (no order here)
- Can be passed as parameter and returned by function
- Can be declared as a CONSTANT
- The AA is only a PL/SQL datatype: can only be referred in a PL/SQL scope.
- AAs are typically defined in package specifications when they are to be used externally from an anonymous or named block program.
- AAs Numeric indexes do not need to be sequential.
- These are sparsely populated structures: can have gaps in index sequences.
- AAs are dynamically sized (like the NESTED TABLE datatype).
 | Who has the highest salary in each department? |
(a) Define a
CURSOR to extract:[ Dept Name, Employee Name, Max Salary ](b) Define a
RECORD TYPE to hold:[ Employee Name, Max Salary ](c) Define an
ASSOCIATIVE ARRAY TYPE (IS TABLE OF) indexed by String.(d) Declares a variable of the
ASSOCIATIVE ARRAY TYPE(e) Open the
CURSOR and populate the variable with data from. (f) Print the data.
SQL> Set serveroutput on
SQL> DECLARE
-- Declare cursor ccur to extract data (dname, ename, maxsal)
Cursor ccur IS
select d.department_name dname,
e.first_name ||' '|| e.last_name ename, e.salary maxsal
from employees e,
( select department_id, max(salary) msal
from employees
group by department_id) s,
departments d
where e.department_id = d.department_id
and e.department_id = s.department_id
and e.salary = s.msal
order by e.department_id;
-- Define a Record type to hold (ename, maxsal)
TYPE EmpSalRec IS RECORD (ename varchar2(60), maxsal employees.salary%TYPE);
-- Define an associative array indexed by department name. The array stores the
-- the composite structure (ename, maxsal)
TYPE MaxSalDep IS TABLE OF EmpSalRec INDEX BY departments.department_name%TYPE;
-- Declare a variable of the associative array type.
v_MaxSalDep MaxSalDep;
BEGIN
FOR deps IN ccur LOOP
v_MaxSalDep(deps.dname).ename := deps.ename;
v_MaxSalDep(deps.dname).maxsal := deps.maxsal;
dbms_output.put_line('MaxSal Dept ' || deps.dname || ': ' ||
' (' || v_MaxSalDep(deps.dname).ename ||
', ' || v_MaxSalDep(deps.dname).maxsal || ');'
);
END LOOP;
END;
/
MaxSal Dept Administration: (Mary Fansom, 25000);
MaxSal Dept Marketing: (Michael Hartstein, 13000);
MaxSal Dept Purchasing: (Den Raphaely, 11000);
MaxSal Dept Human Resources: (Kimberely Grant, 7000);
MaxSal Dept Shipping: (Adam Fripp, 8200);
MaxSal Dept IT: (Alexander Hunold, 9000);
MaxSal Dept Public Relations: (John Fergunson, 26000);
MaxSal Dept Sales: (John Russell, 14000);
MaxSal Dept Executive: (Steven King, 24000);
MaxSal Dept Finance: (Nancy Greenberg, 12000);
MaxSal Dept Accounting: (Shelley Higgins, 12000);
| Using Associative Array as a Constant |
- The example below loads an Associative Array Constant with the names of all departments.
- Using the AA Constant, it then lists the number of employees in each department.
- The AA Constant will be defined with the scope of a package.
(a) In the Package Spec, define the specification of the AA Constant TYPE and the specification of the Function that will initialize the AA Constant.
(b) In the Package Body, write the initialization function.
(c) In an anonymous block, declare constant, intialize it, and loop through the array.
SQL> Set serveroutput on
SQL> CREATE OR REPLACE PACKAGE My_Types AUTHID DEFINER IS
-- Declaring an AA in a package spec makes it available for invoked and invoking
-- subprograms and to anonymous blocks.
TYPE AA_FixDept IS TABLE OF departments.department_name%TYPE
INDEX BY pls_integer;
FUNCTION Init_AA_FixDept RETURN AA_FixDept;
END My_Types;
/
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY My_Types IS
-- Function Init_AA_FixDept will initialize the AA Constant.
FUNCTION Init_AA_FixDept RETURN AA_FixDept IS
CURSOR ccur IS
SELECT department_name FROM departments;
Ret AA_FixDept;
i PLS_INTEGER := 0;
BEGIN
FOR j IN ccur LOOP
Ret(i) := j.department_name;
-- Use dbms_output to check the proper loading of the AA Constant.
-- Comment or remove the line later.
dbms_output.put_line( 'Loading dept '|| i || ': ' || j.department_name);
i := i + 1;
END LOOP;
RETURN Ret;
END Init_AA_FixDept;
END My_Types;
/
Package created.
SQL> Set serveroutput on
SQL> DECLARE
-- Declare the AA Constan c_AAdept.
-- The Constant should be initialized at declaration time.
c_AAdept CONSTANT My_Types.AA_FixDept := My_Types.Init_AA_FixDept();
CURSOR cnumep (namedep departments.department_name%TYPE) IS
SELECT COUNT(*)
FROM employees e, departments d
WHERE e.department_id = d.department_id
and department_name = namedep;
v_index integer;
v_numep integer;
BEGIN
-- Listing the number of employees in each department;
-- Loop through each component of the AA Constant and pass the department name
-- as parameter to the explicit cursor cnumep.
v_index := c_AAdept.First;
LOOP
OPEN cnumep(c_AAdept(v_index));
FETCH cnumep INTO v_numep;
dbms_output.put_line('Dept ' || c_AAdept(v_index) || ' has ' ||
v_numep || ' employees');
CLOSE cnumep;
EXIT WHEN v_index = c_AAdept.Last;
v_index := c_AAdept.next(v_index);
END LOOP;
END;
/
(Edited ouput):
Loading dept 0: Administration
Loading dept 1: Marketing
(...)
Loading dept 26: Payroll
Dept Administration has 2 employees
Dept Marketing has 2 employees
Dept Purchasing has 6 employees
(...)
Dept Payroll has 0 employees| When to use what? |
|---|
|
| Collections: VARRAYS |
- Introduced in Oracle 8
- Can be used in table, record, and object definitions.
- Fixed max size at creation.
- Num elements: Between 0 and declared maximum size.
- May be stored in permanent tables and accessed by SQL.
- If VARRAY variable is less than 4 KB: it resides inside the table of which it is a column; otherwise, it resides outside the table but in the same tablespace.
- Must be initialized. Otherwise is a null collection.
- Densely populated structures: they do not allow gaps in the index values.
- VARRAY and NESTED TABLE datatypes are structures indexed by sequential integers.
| Using Varray: loading values from table |
- Load and print name and hire_date of the three longest employeed workers
(a) Define a Varray of STRING with max 3 rows.
(b) Declare the varray variable. Use constructor to set it to null.
(c) Declare CURSOR to return list of data from database.
(d-f) Print empty varray. Load Varray looping through cursor. Print loaded Varray.
SQL> set serveroutput on
DECLARE
-- Define a varray with a maximum of 3 rows.
TYPE str_varray IS VARRAY(3) OF VARCHAR2(100);
-- Declare the varray with and initialize it with null values.
-- Failure to do so at this point will produce an
-- "ORA-06531: Reference to uninitialized collection".
-- Memory and proper index values are allocated opon initialization of EACH ELEMENT.
-- If you fail to initialize ALL elements, you need to use the EXTEND method or
-- you'll face an "ORA-06533: Subscript beyond count" at run-time.
varray_str str_varray := str_varray(NULL,NULL,NULL);
vstr varchar2(100);
i PLS_INTEGER :=1;
CURSOR ccur IS
select ename, hire_date
from (select first_name || last_name as ename, hire_date,
row_number() over (order by hire_date) r
from employees)
where r between 1 and 3;
BEGIN
-- Print initialized null values.
dbms_output.put_line('Varray initialized as nulls.');
dbms_output.put_line('––––––––––––––--------------');
FOR i IN 1..3 LOOP
dbms_output.put_line ('String Varray ['||i||'] ' ||
'['||varray_str(i)||']';
END LOOP;
-- Assign values to subscripted members of the varray.
FOR j in ccur LOOP
varray_str(i) := j.ename || ' ' || to_char(j.hire_date);
i := i + 1;
END LOOP;
-- Print initialized null values.
dbms_output.put (CHR(10)); -- Visual line break.
dbms_output.put_line('Varray initialized as values.');
dbms_output.put_line('-----------------------------');
FOR j IN 1..3 LOOP
dbms_output.put_line('String Varray ['||j||'] ' ||
'['||varray_str(j)||']');
END LOOP;
END;
/
anonymous block completed
Varray initialized as nulls.
––––––––––––––--------------
String Varray [1] []
String Varray [2] []
String Varray [3] []
Varray initialized as values.
-----------------------------
String Varray [1] [StevenKing 17-JUN-87]
String Varray [2] [JenniferWhalen 17-SEP-87]
String Varray [3] [NeenaKochhar 21-SEP-89]
SQL> CREATE OR REPLACE TYPE str_varray AS
VARRAY(3) of VARCHAR2(100);
/
Type created.
DECLARE
-- initialize without allocating space
varray_str str_varray := str_varray();
(...)
BEGIN
FOR j in ccur LOOP
-- increment index and allocate space
varray_str.EXTEND;
varray_str(i) := j.ename || ' ' || to_char(j.hire_date);
i := i + 1;
END LOOP;
(...)
END;Alternatively:
- You could creat a user-defined object type.
- Benefits: it may be referenced from any programs that have permission to use it, whereas a PL/SQL varray type structure is limited to the program unit.
- You could allocate space for the
VARRAY
at run-time, as you increment the index:
| Collections: Nested Tables |
- Introduced in Oracle 8
- Initially defined as densely populated arrays, but may become sparsely populated as records are deleted.
- May be stored in permanent tables and accessed by SQL.
- May be dynamically extended: act more like bags and sets than arrays.
- May contain a list of one or more compound datatypes (PL/SQL records) when they work exclusively in a PL/SQL scope.
- In the database, a nested table column type stores an unspecified # of rows in no particular order.
- When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1.
(1) Define a composite NESTED TABLE TYPE (IS TABLE OF) of the type of employees table
(2) Create variable of the defined type. Initialize empty.
(3) Read all employees records on Cursor
(4) Iteract through the cursor and
(4.1) Extend the nested table
(4.2) Load three fields of the nested table with data from cursor.
set serveroutput on
DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
emps emp_table := emp_table();
CURSOR ccur IS
select employee_id, first_name, salary
from employees;
j PLS_INTEGER;
BEGIN
-- Print initialized null values.
dbms_output.put_line('Nested table initialized empty.');
dbms_output.put_line('-------------------------------');
j := 1;
dbms_output.put_line('Begin loading...');
FOR i IN ccur LOOP
emps.extend;
emps(j).employee_id := i.employee_id;
emps(j).first_name := i.first_name;
emps(j).salary := i.salary;
dbms_output.put ('Nested Table Extended ['||j||'] ');
dbms_output.put_line('['||emps(j).employee_id ||', '
|| emps(j).first_name || ', ' || emps(j).salary ||']');
j := j + 1;
END LOOP;
END;
/
anonymous block completed
Nested table initialized empty.
-------------------------------
Begin loading...
Nested Table Extended [1] [100, Steven, 24000]
Nested Table Extended [2] [101, Neena, 17000]
Nested Table Extended [3] [102, Lex, 17000]
(...)
Nested Table Extended [108] [210, John, 26000]
Nested Table Extended [109] [211, Mary, 25000]
Collection Methods
| Method | Type | Description |
|---|---|---|
DELETE | Procedure | Deletes elements from collection. |
TRIM | Procedure | Deletes elements from end of varray or nested table. |
EXTEND | Procedure | Adds elements to end of varray or nested table. |
EXISTS | Function | Returns TRUE if and only if specified element of varray or nested table exists. |
FIRST | Function | Returns first index in collection. |
LAST | Function | Returns last index in collection. |
COUNT | Function | Returns number of elements in collection. |
LIMIT | Function | Returns maximum number of elements that collection can have. |
PRIOR | Function | Returns index that precedes specified index. |
NEXT | Function | Returns index that succeeds specified index. |
| Using collection methods |
set serveroutput on
declare
type nbrlast is table of varchar2(10);
n nbrlast := nbrlast('paul','john','ringo','george');
begin
dbms_output.put_line('Num elements held: ' || n.count);
dbms_output.put_line('The last element is ' || n(n.last));
n.extend; -- append a new (null) element
n(n.last) := 'Yoko';
dbms_output.put_line('Added Yoko');
dbms_output.put_line('Now, num elements held: ' || n.count);
dbms_output.put_line('The last element now is ' || n(n.last));
n.extend(3); -- append two null elements to the collection
dbms_output.put_line('appended three null elements');
dbms_output.put_line('Now, num elements held: ' || n.count);
n.delete(6);
dbms_output.put_line('deleted element 6');
dbms_output.put_line('Now, num elements held (count): ' || n.count);
dbms_output.put_line('Now, highest subscription # (last): ' || n.last);
n(7) := 'Ghost';
dbms_output.put_line('element 7 is now: ' || n(7));
dbms_output.put_line('Now, num elements: ' || n.count);
if n.limit is null then
dbms_output.put_line('Max num elements: unlimited.');
end if;
for i in n.first..n.last loop
if n.exists(i) then
if n(i) IS NULL then
dbms_output.put_line('Element # '|| i || ' is NULL');
else
dbms_output.put_line('Element # '|| i || ' is '|| n(i));
end if;
else
dbms_output.put_line('Element # '|| i || ' does not exist');
end if;
end loop;
end;
/
anonymous block completed
Num elements held: 4
The last element is george
Added Yoko
Now, num elements held: 5
The last element now is Yoko
appended three null elements
Now, num elements held: 8
deleted element 6
Now, num elements held (count): 7
Now, highest subscription # (last): 8
element 7 is now: Ghost
Now, num elements: 7
Max num elements: unlimited.
Element # 1 is paul
Element # 2 is john
Element # 3 is ringo
Element # 4 is george
Element # 5 is Yoko
Element # 6 does not exist
Element # 7 is Ghost
Element # 8 is NULL
