Performing DML on the products Table

In this section, you ll see how to perform SQL DML statements to insert, select, update, and delete rows in the products table. The products table was created using the following statement:

 CREATE TABLE products (product product_typ,  quantity_in_stock NUMBER); 

Inserting Rows into the products Table

When inserting a row into a table containing a column object, you must supply the attribute values for that object using a constructor . The constructor for the object has the same name as the object type and accepts parameters for the attributes of the object. The following examples insert two rows into the products table. Notice the use of the product_typ constructor to supply the attribute values for the product column object:

 INSERT INTO products (product,  quantity_in_stock) VALUES (product_typ(1, 'Pasta', '20 oz bag of pasta', 3.95, 10),  50); INSERT INTO products (product,  quantity_in_stock) VALUES (product_typ(2, 'Sardines', '12 oz box of sardines', 2.99, 5),  25); 

Note  

The SQL*Plus script object_schema.sql contains these two INSERT statements, along with the other INSERT statements featured in this chapter.

Selecting Rows from the products Table

The following example selects all the rows from the products table. Notice that the product column object s attributes are displayed within a constructor for product_typ :

  SELECT *   FROM products;  PRODUCT(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID) ------------------------------------------------------------ QUANTITY_IN_STOCK ----------------- PRODUCT_TYP(1, 'Pasta', '20 oz bag of pasta', 3.95, 10)  50 PRODUCT_TYP(2, 'Sardines', '12 oz box of sardines', 2.99, 5)  25 

You can select an individual column object from a table. To do this, you must supply a table alias through which you select the object. The following example selects a single product column object from the products table. Notice the use of the table alias p through which the product object s id attribute is specified:

  SELECT p.product   FROM products p   WHERE p.product.id = 1;  PRODUCT(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID) ------------------------------------------------------- PRODUCT_TYP(1, 'Pasta', '20 oz bag of pasta', 3.95, 10) 

Earlier, you saw that the product_typ object type contains a function named get_sell_by_date() that calculates and returns the date by which the product must be sold. It does this by adding the days_valid attribute to the current date, which is obtained from the database using the sysdate variable. You can call the get_sell_by_date() function using a table alias, for example:

  SELECT p.product.get_sell_by_date()   FROM products p;  P.PRODUCT --------- 12-OCT-03 07-OCT-03 

Of course, if you run this example your dates will be different because they are calculated using SYSDATE .

Updating a Row in the products Table

The following example updates a row in the products table. Notice that a table alias is used to access the product column object:

  UPDATE products p   SET p.product.description = '30 oz bag of pasta'   WHERE p.product.id = 1;  1 row updated. 

Deleting a Row from the products Table

The following example deletes a row from the products table. Notice that a table alias is used to access the product column object:

  DELETE FROM products p   WHERE p.product.id = 2;  1 row deleted.  ROLLBACK;  

Note  

If you re entering this UPDATE and the previous DELETE statements in SQL*Plus, make sure you execute the ROLLBACK , or you can run the object_schema.sql script again to re-create the schema and populate the tables.