8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Object Views and Nested Tables
Object views allow existing relational data to be mapped to an object-relational model, whilst retaining backwards compatibility with previous relational systems. Nested tables allow a move directly to an object-relational model. This article presents examples of modeling a master-detail relationship using object views and nested tables.
Related articles.
Relational Schema
In order to define an object view representing a master-detail relationship, we must first create a relational schema.
CREATE TABLE masters ( id NUMBER(10) NOT NULL, name VARCHAR2(50) NOT NULL ) / ALTER TABLE masters ADD ( CONSTRAINT masters_pk PRIMARY KEY (id) ) / CREATE SEQUENCE masters_seq; CREATE TABLE details ( id NUMBER(10) NOT NULL, master_id NUMBER(10) NOT NULL, name VARCHAR2(50) NOT NULL ) / ALTER TABLE details ADD ( CONSTRAINT details_pk PRIMARY KEY (id) ) / ALTER TABLE details ADD ( CONSTRAINT details_masters_fk FOREIGN KEY (master_id) REFERENCES masters (id) ) / CREATE INDEX details_masters_fk_i ON details(master_id) / CREATE SEQUENCE details_seq;
Object Types
Next we create the object types necessary to map the relational data to an object-relational model. In this example we want the relevant detail data to be presented as a nested table within the master record, so we must define a details row and table type.
CREATE OR REPLACE TYPE t_details_row AS OBJECT ( id NUMBER(10), master_id NUMBER(10), name VARCHAR2(50) ); / CREATE OR REPLACE TYPE t_details_tab AS TABLE OF t_details_row; /
Object View
With the relational schema and the object types in place, we are able to create an object view to map the relational data to the object-relational model.
CREATE OR REPLACE VIEW masters_v AS
SELECT m.id,
m.name,
CAST (MULTISET (SELECT d.id,
d.master_id,
d.name
FROM details d
WHERE d.master_id = m.id) AS t_details_tab) details
FROM masters m
/
At this point the relational data can be queried in its object-relational form, but in order to perform DML we must create an INSTEAD OF trigger which actually does the work on the nested table column. The following trigger converts an insert against the view into the relevant inserts for the relational tables.
CREATE OR REPLACE TRIGGER masters_v_trg
INSTEAD OF INSERT ON masters_v
FOR EACH ROW
BEGIN
-- Insert the master record.
INSERT INTO masters (id, name)
VALUES (:new.id, :new.name);
-- Loop through the details collection, inserting each record into the base table.
FOR i IN :new.details.first .. :new.details.last LOOP
INSERT INTO details (id, master_id, name)
VALUES (:new.details(i).id, :new.details(i).master_id, :new.details(i).name);
END LOOP;
END;
/
With the view and trigger in place we can now access the data in an object-relational manner. The code below inserts a single master record, which contains two details records.
DECLARE
l_master_id NUMBER(10);
l_details_tab t_details_tab := t_details_tab();
FUNCTION get_next_detail_id RETURN NUMBER AS
l_detail_id details.id%TYPE;
BEGIN
SELECT details_seq.NEXTVAL
INTO l_detail_id
FROM dual;
RETURN l_detail_id;
END get_next_detail_id;
BEGIN
-- Get the id of the master record.
SELECT masters_seq.NEXTVAL
INTO l_master_id
FROM dual;
-- Populate the details collection prior to insert.
l_details_tab.extend;
l_details_tab(l_details_tab.last) := t_details_row(get_next_detail_id, l_master_id, 'Test Child Insert 1');
l_details_tab.extend;
l_details_tab(l_details_tab.last) := t_details_row(get_next_detail_id, l_master_id, 'Test Child Insert 2');
-- Insert the data via the object view.
INSERT INTO masters_v
(id, name, details)
VALUES
(l_master_id, 'Test Master Insert', l_details_tab);
COMMIT;
END;
/
Once run, we can see that the data has been inserted into the relational tables as expected.
SQL> SELECT * FROM masters;
ID NAME
---------- --------------------------------------------------
1 Test Master Insert
1 row selected.
SQL> SELECT * FROM details;
ID MASTER_ID NAME
---------- ---------- --------------------------------------------------
1 1 Test Child Insert 1
2 1 Test Child Insert 2
2 rows selected.
SQL> SELECT * FROM masters_v;
ID NAME
---------- --------------------------------------------------
DETAILS(ID, MASTER_ID, NAME)
----------------------------------------------------------------------------------------------------
1 Test Master Insert
T_DETAILS_TAB(T_DETAILS_ROW(1, 1, 'Test Child Insert 1'), T_DETAILS_ROW(2, 1, 'Test Child Insert 2')
)
1 row selected.
SQL>
Nested Table
The following code creates an object-relational table matching the definition of the masters_v view.
CREATE TABLE masters_2 ( id NUMBER(10) NOT NULL, name VARCHAR2(50) NOT NULL, details t_details_tab ) NESTED TABLE details STORE AS details_2 /
As this is an actual table definition, no trigger is needed to allow DML. The DML operations look identicle to those performed against the object view.
DECLARE
l_master_id NUMBER(10);
l_details_tab t_details_tab := t_details_tab();
FUNCTION get_next_detail_id RETURN NUMBER AS
l_detail_id NUMBER;
BEGIN
SELECT details_seq.NEXTVAL
INTO l_detail_id
FROM dual;
RETURN l_detail_id;
END get_next_detail_id;
BEGIN
-- Get the id of the master record.
SELECT masters_seq.NEXTVAL
INTO l_master_id
FROM dual;
-- Populate the details collection prior to insert.
l_details_tab.extend;
l_details_tab(l_details_tab.last) := t_details_row(get_next_detail_id, l_master_id, 'Test Child Insert 1');
l_details_tab.extend;
l_details_tab(l_details_tab.last) := t_details_row(get_next_detail_id, l_master_id, 'Test Child Insert 2');
-- Insert the data via the object view.
INSERT INTO masters_2
(id, name, details)
VALUES
(l_master_id, 'Test Master Insert', l_details_tab);
COMMIT;
END;
/
Querying the table gives similar results to querying the object view.
SQL> SELECT * FROM masters_2;
ID NAME
---------- --------------------------------------------------
DETAILS(ID, MASTER_ID, NAME)
----------------------------------------------------------------------------------------------------
2 Test Master Insert
T_DETAILS_TAB(T_DETAILS_ROW(3, 2, 'Test Child Insert 1'), T_DETAILS_ROW(4, 2, 'Test Child Insert 2')
)
1 row selected.
SQL>
Cleanup
The following commands remove all the objects created in the above examples.
DROP VIEW masters_v; DROP TABLE details; DROP TABLE masters; DROP TABLE masters_2; DROP SEQUENCE details_seq; DROP SEQUENCE masters_seq; DROP TYPE t_details_tab; DROP TYPE t_details_row; PURGE RECYCLEBIN;
For more information see:
Hope this helps. Regards Tim...