HomeOracle Page 3 - Database Interaction with PL/SQL: Nested Tables
Working with PL/SQL using NESTED TABLE with OBJECT TYPE - Oracle
This series has been focusing on database interactions with Oracle PL/SQL. Having just covered OBJECT TYPE in Oracle and both SQL and PL/SQL ways of working with OBJECTs in the last article, we will now learn about using NESTED TABLE with OBJECT TYPES from both SQL and PL/SQL point of views. We will also discuss FOR loops with an inline SELECT statement. This article is the fifth in this series.
PL/SQL nested tables represent sets of values. You can think of them as one-dimensional arrays with no upper bound. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables. Within the database, nested tables are column types that hold sets of values. Oracle stores the rows of a nested table in no particular order. When you retrieve a nested table from the database into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows.
The PL/SQL would be almost similar to the example presented in part-3 of my article, but with some simple enhancements. Consider the following example:
declare v_experiences t_experience_tbl; v_name varchar2(20) := '&name'; begin select experiences into v_experiences from employees where name=v_name; dbms_output.put_line('Experience list of ' || v_name); dbms_output.put_line('-----------------------'); for i in v_experiences.first .. v_experiences.last loop dbms_output.put(v_experiences(i).Position); dbms_output.put_line (',' || v_experiences(i).NoOfYears); end loop; end;
I don’t think there exists anything new from the above program. You are just creating a TABLE based variable ‘v_experiences’, storing all the values into that variable using the SELECT statement and displaying them using a FOR loop. It seems quite comfortable to work with single row of information (as above). Let us consider another issue. How to display all employees with all their experiences? The following example shows the solution:
declare v_experiences t_experience_tbl; TYPE t_name is TABLE OF varchar2(20); v_name t_name;
begin select name bulk collect into v_name from employees; for j in v_name.first .. v_name.last loop select experiences into v_experiences from employees where name=v_name(j); dbms_output.put_line('-----------------------'); dbms_output.put_line('Experience list of ' || v_name); dbms_output.put_line('-----------------------'); for i in v_experiences.first .. v_experiences.last loop dbms_output.put(v_experiences(i).Position); dbms_output.put_line (',' || v_experiences(i).NoOfYears); end loop; end loop; end;
In the above program, I declared a new TABLE type ‘t_name’ and a variable ‘v_name’ based on it to hold all employee names as a bulk. I populated all the employee names into that variable using the first SELECT statement. I am using two FOR loops. One loop for the employee names and the other for experience list of employee retrieved by the outer FOR loop. It is something like the parent-child display.
The above program is NOT the only solution. We can develop in several other ways based on the appropriate situation.
Can we INSERT, UPDATE or DELETE from NESTED TABLES using PL/SQL? Why not? We can do it in several ways. We can use RECORD types, TABLE types or even individual variables to do all such tasks. The syntax will be almost similar to the examples given in the previous section (SQL statements). But to be bit enhanced with PL/SQL based variables in whatever way we like. I leave it to you to try and investigate the maximum number of ways to achieve them.