Mega Code Archive
This script demonstrates column objects
<![CDATA[
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>
]]>