This script demonstrates column objects. : Object Column « Object Oriented Database « Oracle PL / SQL






This script demonstrates column objects.

    

SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BookType AS OBJECT (
  2     rebate   NUMBER (10, 4),
  3     price           NUMBER (10, 2),
  4     MEMBER FUNCTION discount_price
  5        RETURN NUMBER
  6  )
  7  INSTANTIABLE FINAL;
  8  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE BODY BookType
  2  AS
  3     MEMBER FUNCTION discount_price
  4        RETURN NUMBER
  5     IS
  6     BEGIN
  7        RETURN (SELF.price * (1 - SELF.rebate));
  8     END discount_price;
  9  END;
 10  /

Type body created.

SQL>
SQL> CREATE TABLE bookTable (
  2      item_id NUMBER(10) PRIMARY KEY,
  3      num_in_stock NUMBER(10),
  4      reorder_status VARCHAR2(20 CHAR),
  5      price BookType
  6  );

Table created.

SQL>
SQL> INSERT INTO bookTable VALUES (1, 10, 'IN STOCK', BookType (.1, 75));

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> DECLARE
  2     v_price   BookType;
  3  BEGIN
  4     SELECT price INTO v_price FROM bookTable WHERE item_id = 1;
  5
  6     DBMS_OUTPUT.put_line ('Price BEFORE update: ' || v_price.discount_price);
  7     v_price.rebate := .2;
  8
  9     UPDATE bookTable SET price = v_price;
 10
 11     DBMS_OUTPUT.put_line ('Price AFTER update: ' || v_price.discount_price);
 12     ROLLBACK;
 13  END;
 14  /
Price BEFORE update: 67.5
Price AFTER update: 60

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT i.price.price, i.price.rebate FROM bookTable i;

PRICE.PRICE PRICE.REBATE
----------- ------------
         75           .1

1 row selected.

SQL>
SQL> SELECT i.price.discount_price() FROM bookTable i;

I.PRICE.DISCOUNT_PRICE()
------------------------
                    67.5

1 row selected.

SQL>
SQL> drop table bookTable;

Table dropped.

SQL>

   
    
    
    
  








Related examples in the same category

1.The Object Type Column Objects
2.Format column in the object
3.Alter a table with user-defined object to upgrade including data
4.use user-defined type as the column type
5.Query column with user-defined type
6.Check object table column type
7.Use varray in a table
8.Nested type Column
9.Reference nested data type in select statement
10.Reference type column