The object_products table is an object table consisting of product_typ objects. This table was created using the following statement:
CREATE TABLE object_products OF product_typ;
In this section, you ll see how to perform SQL DML statements to insert, update, and delete rows in the object_products table. You ll also see how to select rows from the object_products table.
When inserting a row into an object table, you can choose whether to use a constructor to supply attribute values, or to supply the values in the same way that you would supply column values in a relational table. The following example inserts a row into the object_products table using the constructor for product_typ :
INSERT INTO object_products VALUES (product_typ(1, 'Pasta', '20 oz bag of pasta', 3.95, 10));
The next example omits the constructor for product_typ when inserting a row into object_ products. Notice that the attribute values for product_typ are supplied in the same way that columns would be in a relational table:
INSERT INTO object_products (id, name, description, price, days_valid) VALUES (2, 'Sardines', '12 oz box of sardines', 2.99, 5);
The following example selects all the rows from the object_products table:
SELECT * FROM object_products; ID NAME DESCRIPTION PRICE DAYS_VALID ---------- ---------- ---------------------- ---------- ---------- 1 Pasta 20 oz bag of pasta 3.95 10 2 Sardines 12 oz box of sardines 2.99 5
You can use the built-in Oracle database VALUE() function to select a row from an object table. This treats the row as an actual object and returns the attributes for the object within a constructor for the object type. The VALUE() function accepts a parameter containing a table alias, and the next example uses the VALUE() function when selecting the rows from object_products :
SELECT VALUE(op) FROM object_products op; VALUE(OP)(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID) ------------------------------------------------------------ PRODUCT_TYP(1, 'Pasta', '20 oz bag of pasta', 3.95, 10) PRODUCT_TYP(2, 'Sardines', '12 oz box of sardines', 2.99, 5)
The following example updates a row in the object_products table. Notice that the attributes are treated like columns in a relational table:
UPDATE object_products SET description = '25 oz bag of pasta' WHERE id = 1; 1 row updated.
The following example deletes a row from the object_products table. Notice that the id attribute is again treated like a relational column:
DELETE FROM object_products WHERE id = 2; 1 row deleted. ROLLBACK;