PL-SQL Tables- Learn the implementation difference between arrays and PL-SQL Tables with syntax and examples. Also Learn about some predefined Collection Methods used in collections with Examples.
PL-SQL Tables
This are similar to arrays, only difference is implementation.
This PL-SQL table is similar to a database table that contains 2 fields, one is primary key and other is to store the corresponding value.
Primary key is a binary integer or varchar2.
Tables in oracle have no limit on the number of rows.
The elements in the table are not ordered. they do not have to be sequential.
A table is defined as a TYPE field in the Declare section using the syntax
Syntax
TYPE type_name IS TABLE OF data_type [NOT NULL] INDEX BY Data_type;
table_name type_name;Note
That the data type has to be a scalar or record datatype and the index has to be binary_integer or varchar2
Example program to Print ID and Name using PL-SQL Tables
DECLARE
TYPE t_employee
IS
TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER;
v_employee_list t_employee;
name VARCHAR2(20);
BEGIN
v_employee_list(5) := 'scott';
v_employee_list(2) := 'locket';
v_employee_list(12) := 'jeasd';
v_employee_list(6) := 'drift';
name :=v_employee_list.first;
WHILE name IS NOT NULL
LOOP
dbms_output.put_line('The employee id is: '||name||
' employee name is '||v_employee_list(name));
name :=v_employee_list.next(name);
END LOOP;
END;Output
The employee id is: 2 employee name is locket
The employee id is: 5 employee name is scott
The employee id is: 6 employee name is drift
The employee id is: 12 employee name is jeasd
Collection Methods and Description
| Method Name | Method Description |
|---|---|
| DELETE(m,n) | Removes all elements in the range m..n from an associative array or nested table.If m is larger than n or if m or n is null, DELETE(m,n) does nothing. |
| DELETE(n) | Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing. |
| DELETE | Removes all elements from a collection, setting COUNT to 0. |
| TRIM(n) | Removes n elements from the end of a collection. |
| TRIM | Removes one element from the end of a collection. |
| EXTEND(n,i) | Appends n copies of the ith element to a collection. |
| EXTEND(n) | Appends n null elements to a collection. |
| EXTEND | Appends one null element to a collection. |
| NEXT(n) | Returns the index number that succeeds index n. |
| COUNT | will return the number of rows in the table |
| DELETE | will delete rows from a table |
| EXISTS(n) | will return a boolean true if the entry requested exists in the table and a boolean false if it does not exist |
| FIRST | will return the binary_integer index of the first row |
| LAST | will return the binary_integer index of the last row |
| NEXT(n) | will return the binary_integer index of the next row in the table – the one after the specified row |
| PRIOR(n) | will return the binary_integer index of the previous row in the table – the one before the specified row |
Example Program to show usage of Collection Method
DECLARE
TYPE t_employee
IS
TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER;
v_employee_list t_employee;
v_count_result NUMBER;
v_index_first BINARY_INTEGER;
v_index_next BINARY_INTEGER;
v_index_prior BINARY_INTEGER;
v_index_last BINARY_INTEGER;
BEGIN
v_employee_list(5) := 'scott';
v_employee_list(2) := 'locket';
v_employee_list(12) := 'jeasd';
v_employee_list(6) := 'drift';
v_count_result :=v_employee_list.count;
dbms_output.put_line('The count is: '||v_count_result);
v_index_first := v_employee_list.FIRST;
dbms_output.put_line('The first record is: '||v_index_first);
v_index_next := v_employee_list.NEXT(v_index_first);
dbms_output.put_line('The next record is: '||v_index_next);
v_index_prior := v_employee_list.PRIOR(v_index_next);
dbms_output.put_line('The prior record is: '||v_index_prior);
v_index_last := v_employee_list.last;
dbms_output.put_line('The last record is: '||v_index_last);
v_index_prior := v_employee_list.PRIOR(v_index_last);
dbms_output.put_line('The prior record is: '||v_index_prior);
v_index_first := v_employee_list.FIRST;
dbms_output.put_line('The first record is: '||v_index_first);
END;Output
The count is: 4
The first record is: 2
The next record is: 5
The prior record is: 2
The last record is: 12
The prior record is: 6
The first record is: 2
Example Program Index by Table With %ROWTYPE
DECLARE
CURSOR e_employees
IS
SELECT name FROM employee_tbl1;
TYPE e_list
IS
TABLE OF employee_tbl1.Name%type INDEX BY binary_integer;
name_list e_list;
counter INTEGER :=0;
BEGIN
FOR n IN e_employees
LOOP
counter := counter +1;
name_list(counter) := n.name;
dbms_output.put_line('employee('||counter||'):'||name_list(counter));
END LOOP;
END;Output
employee(1):Tom
employee(2):Pam
employee(3):John
employee(4):Sam
employee(5):Todd
employee(6):Ben
employee(7):Sara
employee(8):Valarie
employee(9):James
employee(10):Russell