Inserting Objects You use the INSERT statement to add objects to an object table. In the following example, you insert a Person object into object table persons: BEGIN INSERT INTO persons VALUES ('Jenifer', 'Lapidus', ...); Alternatively, you can use the constructor for object type Person to insert an object into object table persons: BEGIN INSERT INTO persons VALUES (Person('Albert', 'Brooker', ...)); In the next example, you use the RETURNING clause to store Person refs in local variables. Notice how the clause mimics a SELECT statement.You can also use the RETURNING clause in 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 objects into an object table, you can use a subquery that returns objects of the same type. An example follows: BEGIN INSERT INTO persons2 SELECT VALUE(p) FROM persons p WHERE p.last_name LIKE '%Jones'; The rows copied to object table persons2 are given new object identifiers. No object identifiers are copied from object table persons. The script below creates a relational table named department, which has a column of type Person, then inserts a row into the table. Notice how constructor Person() provides a value for column manager. CREATE TABLE department ( dept_name VARCHAR2(20), manager Person, location VARCHAR2(20)) / INSERT INTO department VALUES ('Payroll', Person('Alan', 'Tsai', ...), 'Los Angeles') / The new Person object stored in column manager cannot be referenced because it is stored in a column (not a row) and therefore has no object identifier. Updating Objects To modify the attributes of objects in an object table, you use the UPDATE statement, as the following example shows: BEGIN UPDATE persons p SET p.home_address = '341 Oakdene Ave' WHERE p.last_name = 'Brody'; ... UPDATE persons p SET p = Person('Beth', 'Steinberg', ...) WHERE p.last_name = 'Steinway'; ... END;Deleting Objects You use the DELETE statement to remove objects (rows) from an object table. To remove objects selectively, you use the WHERE clause, as follows: BEGIN DELETE FROM persons p WHERE p.home_address = '108 Palm Dr'; ... END;
You use the INSERT statement to add objects to an object table. In the following example, you insert a Person object into object table persons: BEGIN
INSERT INTO persons
VALUES ('Jenifer', 'Lapidus', ...);
Alternatively, you can use the constructor for object type Person to insert an object into object table persons: BEGIN
INSERT INTO persons
VALUES (Person('Albert', 'Brooker', ...));
In the next example, you use the RETURNING clause to store Person refs in local variables. Notice how the clause mimics a SELECT statement.You can also use the RETURNING clause in 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 objects into an object table, you can use a subquery that returns objects of the same type. An example follows: BEGIN
INSERT INTO persons2
SELECT VALUE(p) FROM persons p
WHERE p.last_name LIKE '%Jones';
The rows copied to object table persons2 are given new object identifiers. No object identifiers are copied from object table persons. The script below creates a relational table named department, which has a column of type Person, then inserts a row into the table. Notice how constructor Person() provides a value for column manager. CREATE TABLE department (
dept_name VARCHAR2(20),
manager Person,
location VARCHAR2(20))
/
INSERT INTO department
VALUES ('Payroll', Person('Alan', 'Tsai', ...), 'Los Angeles')
/
The new Person object stored in column manager cannot be referenced because it is stored in a column (not a row) and therefore has no object identifier. Updating Objects
To modify the attributes of objects in an object table, you use the UPDATE statement, as the following example shows: BEGIN
UPDATE persons p SET p.home_address = '341 Oakdene Ave'
WHERE p.last_name = 'Brody';
...
UPDATE persons p SET p = Person('Beth', 'Steinberg', ...)
WHERE p.last_name = 'Steinway';
...
END;Deleting Objects
You use the DELETE statement to remove objects (rows) from an object table. To remove objects selectively, you use the WHERE clause, as follows: BEGIN
DELETE FROM persons p
WHERE p.home_address = '108 Palm Dr';
...
END;