MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Delete removes rows, eh?

with 5 comments

It started as a smart remark, “The DELETE FROM statement always removes one or more rows from a table when any are found, except when the table isn’t a table.” That exception is true when the table isn’t a schema-level table but a nested table in an Oracle database. Then, the DELETE FROM statement may remove a row from the table or a row from collection held by a row.

Naturally, everybody would like an example. It took more than a couple steps to work one up and the Oracle documentation isn’t very helpful on the topic.

  1. Create a SQL nested table collection type of 30-character variable length strings:
1
2

CREATE TYPE street_list IS TABLE OF VARCHAR2(30);
/
  1. Create a SQL user-defined object type for addresses:
1
2
3
4
5
6
7

CREATE OR REPLACE TYPE address_type AS OBJECT
( address_id     NUMBER
, street_address STREET_LIST
, city           VARCHAR2(30)
, state          VARCHAR2(2)
, postal_code    VARCHAR2(10));
/
  1. Create a SQL nested table of the user-defined object type for addresses:
1
2

CREATE OR REPLACE TYPE address_list AS TABLE OF address_type;
/
  1. Create a schema-level table that uses the user-defined nested table of a user-defined object type, and a SEQUENCE for the table:
1
2
3
4
5
6
7
8
9
10

CREATE TABLE employee
( employee_id    NUMBER
, first_name     VARCHAR2(20)
, middle_name    VARCHAR2(20)
, last_name      VARCHAR2(20)
, home_address   ADDRESS_LIST)
  NESTED TABLE home_address STORE AS address_table
   (NESTED TABLE street_address STORE AS street_table);
 
CREATE SEQUENCE employee_s1;
  1. Insert data into the table:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

INSERT INTO employee
( employee_id
, first_name
, last_name
, home_address )
VALUES
( employee_s1.NEXTVAL
,'Sam'
,'Yosemite'
, address_list(
    address_type( 1
                , street_list('1111 Broadway','Suite 322')
                ,'Oakland'
                ,'CA'
                ,'94612')
  , address_type( 2
                , street_list('1111 Broadway','Suite 525')
                ,'Oakland'
                ,'CA'
                ,'94612')));
 
INSERT INTO employee
( employee_id
, first_name
, last_name
, home_address )
VALUES
( employee_s1.NEXTVAL
,'Bugs'
,'Bunny'
, address_list(
    address_type( 1
                , street_list('21 Rodeo Drive','Suite 1000')
                ,'Beverly Hills'
                ,'CA'
                ,'90210')
  , address_type( 2
                , street_list('21 Rodeo Drive','Suite 2010')
                ,'Beverly Hills'
                ,'CA'
                ,'90210')));
  1. Create a SQL view to explode nesting into a meaningful data set:
1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE VIEW explode_employee AS
SELECT   e.employee_id
,        e.first_name || ' ' || e.last_name AS full_name
,        st.address_id
,        sa.column_value AS st_address
,        st.city
,        st.state
,        st.postal_code
FROM     employee e CROSS JOIN
         TABLE(e.home_address) st CROSS JOIN
         TABLE(street_address) sa
ORDER BY 1, 2, 3;
  1. Format output and query view:
1
2
3
4
5
6
7
8
9
10
11

-- SQL*Plus formattting
COLUMN employee_id FORMAT 999 HEADING "ID|EMP"
COLUMN full_name   FORMAT A16 HEADING "Full Name"
COLUMN address_id  FORMAT 999 HEADING "ID|UDT"
COLUMN st_address  FORMAT A16 HEADING "Street Address"
COLUMN city        FORMAT A14 HEADING "City"
COLUMN state       FORMAT A5  HEADING "State"
COLUMN postal_code FORMAT A5  HEADING "Zip|Code"
 
-- Query all from view.
SELECT * FROM explode_employee;

Displays the following (2 base rows times two nested table rows times two scalar nested table rows or 8 rows):

  ID                    ID                                       Zip
 EMP Full Name         UDT Street Address   City           State Code
---- ---------------- ---- ---------------- -------------- ----- -----
   1 Sam Yosemite        1 1111 Broadway    Oakland        CA    94612
   1 Sam Yosemite        1 Suite 322        Oakland        CA    94612
   1 Sam Yosemite        2 1111 Broadway    Oakland        CA    94612
   1 Sam Yosemite        2 Suite 525        Oakland        CA    94612
   2 Bugs Bunny          1 21 Rodeo Drive   Beverly Hills  CA    90210
   2 Bugs Bunny          1 Suite 1000       Beverly Hills  CA    90210
   2 Bugs Bunny          2 21 Rodeo Drive   Beverly Hills  CA    90210
   2 Bugs Bunny          2 Suite 2010       Beverly Hills  CA    90210
  1. UPDATE statement against a nested table:
1
2
3
4
5

UPDATE TABLE (SELECT e.home_address
              FROM   employee e
              WHERE  e.employee_id = 1) e
SET   e.city = 'Fremont'
WHERE e.address_id = 1;
  1. UPDATE statement against a nested table and scalar nested table within it:
1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM   employee e
              WHERE  e.employee_id = 1) e
SET   e.street_address = street_list('1111 Broadway','Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;
  1. DELETE statement against a nested table, which only removes the nested row:
1
2
3
4

DELETE FROM TABLE (SELECT e.home_address
                   FROM   employee e
                   WHERE  e.employee_id = 1) ha
WHERE  ha.address_id = 1;

As always, hope this helps somebody looking for syntax or approach.

Written by maclochlainn

June 5th, 2011 at 11:49 pm

Posted in Oracle,sql

«
»