Inserting Objects:
You can use the INSERT statement to add objects to an object table. In the illustration below, you insert a Person object into the object table persons:
BEGIN
INSERT INTO persons
VALUES ('Jenifer', 'Lapidus', ...);
Or else, you can use the constructor for the object type Person to insert an object into the object table persons:
BEGIN
INSERT INTO persons
VALUES (Person('Albert', 'Brooker', ...));
In the later illustration, you use the RETURNING clause to store the Person refs in local variables. Note that how the clause mimics a SELECT statement. The RETURNING clause can also use in the UPDATE and DELETE statements.
DECLARE
p1_ref REF Person;
p2_ref REF Person;
...
BEGIN
INSERT INTO persons p
VALUES (Person('Paul', 'Chang', ...))
RETURNING REF(p) INTO p1_ref;
INSERT INTO persons p
VALUES (Person('Ana', 'Thorne', ...))
RETURNING REF(p) INTO p2_ref;
To insert the objects into an object table, you can use a sub query which returns objects of the similar type. The illustration is as shown below:
BEGIN
INSERT INTO persons2
SELECT VALUE(p) FROM persons p
WHERE p.last_name LIKE '%Jones';
The rows copied to another object table persons2 are given the new object identifiers. The object identifiers are not copied from the object table persons. The scripts below create a relational table named department that has a column of the type Person, and then inserts a row into the table. Note that how constructor Person () gives a value for the column manager.
CREATE TABLE department (
dept_name VARCHAR2(20),
manager Person,
location VARCHAR2(20))
/
INSERT INTO department
VALUES ('Payroll', Person('Alan', 'Tsai', ...), 'Los Angeles')
/