A nested table is a non-permanent table that can be referenced and simulate an array in a PL program.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
You can define a table type and declare this type of variables. Then, you can add records to your PL table and reference the records in a way similar to that of referencing array elements.
The syntax is as follows:
TYPE type_name IS TABLE OF
{column_type | variable%TYPE | table.column%TYPE } [NOT NULL] | table%ROWTYPE
The following table describes the methods supported for PL nested tables.
| Method | Description |
|---|---|
| EXISTS(n) | Returns True if the nth row exists. |
| COUNT | Returns the number of rows in the PL table. |
| FIRST | LAST | Returns the row numbers of the first and last rows. If the PL table contains no data, a null value is returned. |
| PRIOR(n) | Returns the row number of the record prior to the nth record in the PL table. |
| NEXT(n) | Returns the row number of the record after the nth record in the PL table. |
| DELETE |
|
The following shows a sample nested table. After a record is deleted from the PL table, the row numbers of other records do not change.
obclient> CREATE TABLE departments(department_id number(4),department_name varchar2(15));
Query OK, 0 rows affected
obclient> INSERT INTO departments VALUES(10,'Administration');
Query OK, 1 row affected
obclient> INSERT INTO departments VALUES(20,'Marketing');
Query OK, 1 row affected
obclient> INSERT INTO departments VALUES(30,'Purchasing');
Query OK, 1 row affected
obclient> INSERT INTO departments VALUES(40,'Human Resources');
Query OK, 1 row affected
obclient> INSERT INTO departments VALUES(50,'Shipping');
Query OK, 1 row affected
obclient> INSERT INTO departments VALUES(60,'IT');
Query OK, 1 row affected
obclient> DECLARE
TYPE t_dept_table IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER;
tab_department T_DEPT_TABLE;
v_count number(2) :=6;
BEGIN
-- Assign values to the table.
FOR int IN 1 .. v_count LOOP
SELECT * INTO tab_department(int) FROM departments WHERE department_id=int*10;
END LOOP;
-- Output the total number of rows by using the COUNT method.
DBMS_OUTPUT.PUT_LINE(tab_department.COUNT||' ROW(S): ');
-- Output all rows by using the FIRST and LAST methods.
FOR int IN TAB_department.FIRST .. TAB_department.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Department number: '||tab_department(int).department_id);
DBMS_OUTPUT.PUT_LINE('Department name: '|| tab_department(int).department_name);
END LOOP;
-- Check records by using the EXISTS method.
IF tab_department.EXISTS(5) THEN
DBMS_OUTPUT.PUT_LINE('ROW 5 EXISTS');
ELSE
DBMS_OUTPUT.PUT_LINE('ROW 5 NOT EXISTS');
END IF;
-- Delete a range by using the DELETE method.
DBMS_OUTPUT.PUT_LINE('Delete row 2-3');
tab_department.DELETE(2,3);
-- Output records in a forward sequence by using the NEXT method.
DBMS_OUTPUT.PUT_LINE('Looping from first');
v_count := 1;
WHILE v_count IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(tab_department(v_count).department_id);
v_count := tab_department.next(v_count);
END LOOP;
-- Output records in a backward sequence by using the PRIOR method. Note that the value of COUNT is 4 in this case.
DBMS_OUTPUT.PUT_LINE('Looping from last');
v_count := tab_department.COUNT;
WHILE v_count IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(tab_department(v_count).department_id);
v_count := tab_department.prior(v_count);
END LOOP;
END;
/
Query OK, 0 rows affected
6 ROW(S):
Department number: 10
Department name: Administration
Department number: 20
Department name: Marketing
Department number: 30
Department name: Purchasing
Department number: 40
Department name: Human Resources
Department number: 50
Department name: Shipping
Department number: 60
Department name: IT
ROW 5 EXISTS
Delete row 2-3
Looping from first
10
40
50
60
Looping from last
40
10
Major differences between nested tables and arrays
In terms of concept, a nested table is a one-dimensional array with any number of elements. Nested tables and arrays are different in the following aspects:
An array can declare the number of elements but a nested table cannot. The size of a nested table can be dynamically increased.
An array is always dense. A nested table is initially dense and can become sparse after elements are deleted from it.
Application scenarios of nested tables
Nested tables are applicable in the following scenarios:
The number of elements is not specified.
The index values are not continuous.
Some elements need to be deleted or updated, but you cannot delete or update all elements at the same time.