blog.mclaughlinsoftware.comMacLochlainns Weblog

Michael McLaughlin's Technical Blog User Name: Password: Site Admin Updating Nested ADTs without comments The first part of this series showed how you can leverage Oracle’s SQL syntax with UDT columns and collection columns. It would be nice if Oracle gave you some SQL to work with the elements of ADT collections, but they don’t. After all, that’s why you have this article. While you could change the setup of the prior example table, it’s easier to create a new customer table. The new customer table drops the address column. There’s also a new pizza table. The pizza table includes an ingredient ADT collection column, which by design holds a unique set of ingredients for each pizza. Realistically, ADT collections of numbers, characters, and dates have little value by themselves. That’s because those data types typically don’t have much meaning. A set of unique strings can be useful for certain use cases. You create the list ADT type with this syntax: SQLCREATE OR REPLACE 2 TYPE list IS TABLE OF VARCHAR2 ( 20 ) ; 3 / SQL CREATE OR REPLACE 2 TYPE list IS TABLE OF VARCHAR2(20); 3 / You create the customer and pizza tables, and customer_s and pizza_s sequences with the following syntax: SQLCREATE TABLE customer 2 ( customer_id NUMBER 3 , first_name VARCHAR2 ( 20 ) 4 , last_name VARCHAR2 ( 20 ) 5 , CONSTRAINT pk_customer PRIMARY KEY ( customer_id ) ) ; SQLCREATE SEQUENCE customer_s; SQLCREATE TABLE pizza 2 ( pizza_id NUMBER 3 , customer_id NUMBER 4 , pizza_size VARCHAR2 ( 10 ) 5 , ingredients LIST 6 , CONSTRAINT pk_pizza PRIMARY KEY ( pizza_id ) 7 , CONSTRAINT ck_pizza_size 8 CHECK ( pizza_size IN ( 'Mini' , 'Small' , 'Medium' , 'Large' , 'Very Large' ) ) ) 9 NESTED TABLE ingredients STORE AS ingredient_table; SQLCREATE SEQUENCE pizza_s; SQL CREATE TABLE customer 2 ( customer_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , CONSTRAINT pk_customer PRIMARY KEY (customer_id)); SQL CREATE SEQUENCE customer_s; SQL CREATE TABLE pizza 2 ( pizza_id NUMBER 3 , customer_id NUMBER 4 , pizza_size VARCHAR2(10) 5 , ingredients LIST 6 , CONSTRAINT pk_pizza PRIMARY KEY (pizza_id) 7 , CONSTRAINT ck_pizza_size 8 CHECK (pizza_size IN ('Mini','Small','Medium','Large','Very Large'))) 9 NESTED TABLE ingredients STORE AS ingredient_table; SQL CREATE SEQUENCE pizza_s; The customer table only has scalar columns. The pizza table has the ingredient ADT collection column. Line 9 creates a nested ingredient_table for the ingredient ADT collection column. There is a primary and foreign key relationship between the customer and pizza tables. That relationship between the tables requires that you insert rows into the customer table before you insert rows into the pizza table. The sample script populates the customer table with characters from the Green Arrow television show, as follows: Customer ID # Last Name First Name1 Queen Oliver 2 Queen Thea 3 Queen Moira 4 Lance Dinah 5 Lance Quentin 6 Diggle John 7 Wilson Slade Customer ID # Last Name First Name1 Queen Oliver 2 Queen Thea 3 Queen Moira 4 Lance Dinah 5 Lance Quentin 6 Diggle John 7 Wilson Slade Next, you can insert three rows into the pizza table. Each has different ingredients in the ingredient ADT column. The following is the syntax for the INSERT statements: SQLINSERT INTO pizza 2 VALUES 3 ( pizza_s. NEXTVAL 4 , ( SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'Quentin' AND c.last_name = 'Lance' ) 6 , 'Large' 7 , list ( 'Cheese' , 'Marinara Sauce' , 'Sausage' , 'Salami' ) ) ; SQLINSERT INTO pizza 2 VALUES 3 ( pizza_s. NEXTVAL 4 , ( SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'Thea' AND c.last_name = 'Queen' ) 6 , 'Medium' 7 , list ( 'Cheese' , 'Marinara Sauce' , 'Canadian Bacon' , 'Pineapple' ) ) ; SQLINSERT INTO pizza 2 VALUES 3 ( pizza_s. NEXTVAL 4 , ( SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'John' AND c.last_name = 'Diggle' ) 6 , 'Small' 7 , list ( 'Cheese' , 'BBQ Sauce' , 'Chicken' ) ) ; SQL INSERT INTO pizza 2 VALUES 3 ( pizza_s.nextval 4 ,(SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'Quentin' AND c.last_name = 'Lance') 6 ,'Large' 7 , list('Cheese','Marinara Sauce','Sausage','Salami')); SQL INSERT INTO pizza 2 VALUES 3 ( pizza_s.nextval 4 ,(SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'Thea' AND c.last_name = 'Queen') 6 ,'Medium' 7 , list('Cheese','Marinara Sauce','Canadian Bacon','Pineapple')); SQL INSERT INTO pizza 2 VALUES 3 ( pizza_s.nextval 4 ,(SELECT c.customer_id FROM customer c 5 WHERE c.first_name = 'John' AND c.last_name = 'Diggle') 6 ,'Small' 7 , list('Cheese','BBQ Sauce','Chicken')); Querying results from tables with nested ADT columns provides interesting results. An ordinary query, like this: SQLCOL pizza_id FORMAT 99999 HEADING "Pizza|ID #" SQLCOL pizza_size FORMAT A6 HEADING "Pizza|Size" SQLCOL ingredients FORMAT A64 HEADING "Ingredients" SQLSELECT pizza_id 2 , pizza_size 3 , ingredients 4 FROM pizza; SQL COL pizza_id FORMAT 99999 HEADING "Pizza|ID #" SQL COL pizza_size FORMAT A6 HEADING "Pizza|Size" SQL COL ingredients FORMAT A64 HEADING "Ingredients" SQL SELECT pizza_id 2 , pizza_size 3 , ingredients 4 FROM pizza; … returns the following results with a flattened object type: Pizza Pizza ID # Size Ingredients1 Large LIST('Cheese', 'Marinara Sauce', 'Sausage', 'Salami') 2 Medium LIST('Cheese', 'Marinara Sauce', 'Canadian Bacon', 'Pineapple') 3 Small LIST('Cheese', 'BBQ Sauce', 'Chicken') Pizza Pizza ID # Size Ingredients1 Large LIST('Cheese', 'Marinara Sauce', 'Sausage', 'Salami') 2 Medium LIST('Cheese', 'Marinara Sauce', 'Canadian Bacon', 'Pineapple') 3 Small LIST('Cheese', 'BBQ Sauce', 'Chicken') If you use a CROSS JOIN it multiplies each row times the number of items in the ADT collection column. The multiplication hides the results. The best solution for displaying results from an ADT collection requires that you serialize the results. The following serialize_set PL/SQL function creates a serialized comma separated list: SQLCREATE OR REPLACE 2 FUNCTION serialize_set ( pv_list LIST ) RETURN VARCHAR2 IS 3 /* Declare a return string as large as you need. */ 4 lv_comma_string VARCHAR2 ( 60 ) ; 5 BEGIN 6 /* Read list of values and serialize them in a string. */ 7 FOR i IN 1 ..pv_list. COUNT LOOP 8 IF NOT i = pv_list. COUNT THEN 9 lv_comma_string : = lv_comma_string || pv_list ( i ) || ', ' ; 10 ELSE 11 lv_comma_string : = lv_comma_string || pv_list ( i ) ; 12 END IF ; 13 END LOOP ; 14 RETURN lv_comma_string; 15 END serialize_set; SQL CREATE OR REPLACE 2 FUNCTION serialize_set (pv_list LIST) RETURN VARCHAR2 IS 3 /* Declare a return string as large as you need. */ 4 lv_comma_string VARCHAR2(60); 5 BEGIN 6 /* Read list of values and serialize them in a string. */ 7 FOR i IN 1..pv_list.COUNT LOOP 8 IF NOT i = pv_list.COUNT THEN 9 lv_comma_string := lv_comma_string || pv_list(i) || ', '; 10 ELSE 11 lv_comma_string := lv_comma_string || pv_list(i); 12 END IF; 13 END LOOP; 14 RETURN lv_comma_string; 15 END serialize_set; You can now write a query that uses your PL/SQL function to format the ADT collection column values into a single row. The syntax for the query is: SQLSELECT pizza_id 2 , pizza_size 3 , serialize_set ( ingredients ) AS ingredients 4 FROM pizza; SQL SELECT pizza_id 2 , pizza_size 3 , serialize_set(ingredients) AS ingredients 4 FROM pizza; It returns: Pizza Pizza ID # Size Ingredients - 1 Large Cheese, Marinara Sauce, Sausage, Salami 2 Medium Cheese, Marinara Sauce, Canadian Bacon, Pineapple 3 Small Cheese, BBQ Sauce, Chicken Pizza Pizza ID # Size...