Performing DML on the object_products Table


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.

Inserting Rows into 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); 

Selecting Rows from the object_products Table

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) 

Updating a Row in the object_products Table

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. 

Deleting a Row from the object_products Table

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;  



Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net