Embedded Query
Week 10: Tutorials
Embedded Query
Learning Outcomes
- Learn how to use embedded queries.
- Learn how to use an embedded query to access a nested table.
Lesson Materials
Embedded queries act like runtime tables inside UPDATE statements. They’re designed to allow you to use a single UPDATE statement that changes elements of a nested table. They appear in Object-Relational databases, like Oracle and PostgreSQL.
Nested tables are lists, which are like arrays but without a maximum number of rows. As such, lists mimic database tables when they’re defined by object types. Object types act like record data structures in an Oracle database. This is possible because Oracle is an object relational database.
The original SQL design didn’t consider the concept of object types or collections of object types. This leaves Oracle with the responsibility to fit calls to these object types within SQL extensions. The interface is rather straightforward but has limitations as to what you can perform on nested tables and arrays through INSERT and UPDATE statements. You can insert or update complete nested tables, but you cannot replace only certain elements of the nested tables. PL/SQL lets you access and manipulate the elements of nested tables and arrays.
This example uses an employee table, which you can’t define without first defining two Oracle object types. The first is an address_type record structure and the second is a address_list list. The address_list is a list of the address_type record structure.
Oracle’s object types require that you drop dependents first, which means you need to prepend a set of DROP statements that remove objects from the most to the least dependent. Here’s the list for this small example:
DROP TABLE employee; DROP TYPE address_list; DROP TYPE address_type; |
Create an address_type structure, like this:
SQL> CREATE OR REPLACE 2 TYPE address_type IS OBJECT 3 ( address_id NUMBER 4 , street_address VARCHAR2(30) 5 , city VARCHAR2(30) 6 , state VARCHAR2(2) 7 , postal_code VARCHAR2(10)); 8 / |
The semicolon in the address_type example terminates the definition of the address_type record structure and the forward slash (/) dispatches the statement to the PL/SQL Statement Engine for processing.
You create a list collection of the address_type with the following syntax:
SQL> CREATE OR REPLACE 2 TYPE address_list IS TABLE OF address_type; 3 / |
You create the employee table with a nested table, as shown:
SQL> CREATE TABLE employee 2 ( employee_id NUMBER 3 , first_name VARCHAR2(20) 4 , middle_name VARCHAR2(20) 5 , last_name VARCHAR2(20) 6 , home_address ADDRESS_LIST) 7 NESTED TABLE home_address STORE AS address_table; |
Line 7 defines the nested table storage for the home_address column as a list of the address_type record structure. It is possible to support multiple layers of nested tables but generally not a good idea to implement them. Multiple-layers of nested tables adds too much complexity for too little gain.
You can describe the employee table to inspect its structure with the DESCRIBE command, and you would see:
Name Null? Type ------------------------- -------- ---------------------------- EMPLOYEE_ID NUMBER FIRST_NAME VARCHAR2(20) MIDDLE_NAME VARCHAR2(20) LAST_NAME VARCHAR2(20) HOME_ADDRESS ADDRESS_LIST |
You can describe the home_address column’s address_list data type with the same DESCRIBE command. It will show you the following nested table definition of the address_type record structure:
address_list TABLE OF ADDRESS_TYPE Name Null? Type ------------------------- -------- ---------------------------- ADDRESS_ID NUMBER STREET_ADDRESS VARCHAR2(30) CITY VARCHAR2(30) STATE VARCHAR2(2) POSTAL_CODE VARCHAR2(10) |
This collection is a nested table. You can tell that because it says TABLE OF. An ADT (Attribute Data Type) or UDT (User Defined Type) array would print a VARRAY(n) OF phrase before the respective object structure’s name.
As mentioned, this type of table structure is called a nested table architecture. It is inherently complex. This type of design also presents migration issues when you want to modify the UDTs. You must put the data some place, drop the table, and then add the UDTs in the reverse order of how you created them—at least until you arrive at the UDT that you want to change. After making the change, you’ll need to re-create all data types and tables and migrate the data back into the new table.
Before you insert records into the employee table, you need to create an employee_s sequence, like:
CREATE SEQUENCE employee_s; |
Now you can insert a single row into the employee table with two nested home_address values, like:
SQL> INSERT INTO employee 2 ( employee_id 3 , first_name 4 , last_name 5 , home_address ) 6 VALUES 7 ( employee_s.NEXTVAL 8 ,'Sam' 9 ,'Yosemite' 10 , address_list( 11 address_type( 12 1 13 ,'1111 Broadway, Suite 322' 14 ,'Oakland' 15 ,'CA' 16 ,'94612') 17 , address_type( 18 2 19 , '1111 Broadway , Suite 525' 20 ,'Oakland' 21 ,'CA' 22 ,'94612'))); 1 ROW created. |
Now, lets submit another row into the employee table with only one nested home_address value:
SQL> INSERT INTO employee 2 ( employee_id 3 , first_name 4 , middle_name 5 , last_name 6 , home_address ) 7 VALUES 8 ( employee_s.NEXTVAL 9 ,'Wiley' 10 ,'E' 11 ,'Coyote' 12 , address_list( 13 address_type( 14 1 15 , NULL 16 ,'Phoenix' 17 ,'AZ' 18 ,'85087'))); 1 ROW created. |
As mentioned, this type of table structure is called a nested table architecture. An ordinary query would fail when you try to query the results without recognizing that the home_address column is a list. A CROSS JOIN allows us to link nested tables to their containing rows, like this:
SQL> COLUMN employee_id FORMAT 999 HEADING "ID|EMP" SQL> COLUMN full_name FORMAT A16 HEADING "Full Name" SQL> COLUMN address_id FORMAT 999 HEADING "ID|UDT" SQL> COLUMN st_address FORMAT A20 HEADING "Street Address" SQL> COLUMN city FORMAT A10 HEADING "City" SQL> COLUMN state FORMAT A5 HEADING "State" SQL> COLUMN postal_code FORMAT A5 HEADING "Zip|Code" SQL> SELECT e.employee_id 2 , e.first_name || ' ' || e.last_name AS full_name 3 , st.address_id 4 , st.street_address AS st_address 5 , st.city 6 , st.state 7 , st.postal_code 8 FROM employee e CROSS JOIN TABLE(e.home_address) st; |
It displays the following:
ID ID Zip EMP Full Name UDT Street Address City State Code ---- ---------------- ---- ---------------------------- -------- ----- ----- 1 Yosemite Sam 1 1111 Broadway, Suite 322 Oakland CA 94612 1 Yosemite Sam 2 1111 Broadway , Suite 525 Oakland CA 94612 2 Wiley Coyote 1 Phoenix AZ 85087 |
While querying the nested table is complex, updating is more complex unless you master the concept of an embedded query. The following UPDATE statement uses an embedded query to find and change a given row in the nested table for any given row in the employee table.
SQL> UPDATE TABLE(SELECT e.home_address 2 FROM employee e 3 WHERE e.last_name = 'Coyote') a 4 SET a.street_address = '74 N. Euclid Avenue' 5 , a.city = 'Tucson' 6 , a.postal_code = '85704' 7 WHERE a.city = 'Phoenix'; 1 ROW updated. |
An embedded query relies on the TABLE function, which lets you cast an object collection into a SQL result set. A SQL result set is formally called an aggregate result set but all we need to know is that its a result set that works in SQL.
Line 3 contains the WHERE clause that identifies the unique row in the employee table. Lines 4, 5, and 6 touches only the results of the embedded query found in the nested table represented by the home_address column of the employee table.
Re-running the prior query, you would see the changes in nested city and postal_code column values of the employee table:
ID ID Zip EMP Full Name UDT Street Address City State Code ---- ---------------- ---- ---------------------------- -------- ----- ----- 1 Yosemite Sam 1 1111 Broadway, Suite 322 Oakland CA 94612 1 Yosemite Sam 2 1111 Broadway, Suite 525 Oakland CA 94612 2 Wiley Coyote 1 74 N. Euclid Avenue Tucson AZ 85704 |
You can also update any member of a list in a nested table. The following syntax lets you update the address for the second record for Yosemite Sam:
SQL> UPDATE TABLE(SELECT e.home_address 2 FROM employee e 3 WHERE e.last_name = 'Yosemite') a 4 SET a.street_address = '10 Main Street' 5 , a.city = 'Flagstaff' 6 , a.postal_code = '86001' 7 WHERE a.address_id = 2 8 AND a.city = 'Oakland'; 1 ROW updated. |
It would display with the same diagnostic query:
ID ID Zip EMP Full Name UDT Street Address City State Code ---- ---------------- ---- ---------------------------- ---------- ----- ----- 1 Yosemite Sam 1 1111 Broadway, Suite 322 Oakland CA 94612 1 Yosemite Sam 2 10 Main Street Flagstaff CA 86001 2 Wiley Coyote 1 74 N. Euclid Avenue Tucson AZ 85704 |
What you can’t do with a nested table is add a member to the nested table in SQL. You must use the imperative PL/SQL language to add a row to the nested table. That limitation exists because the embedded query result is a immutable list, like an array in the context of SQL. However, the elements of the list are mutable record types when the list is a UDT.
