ORACLE PL/SQL 对象(object)学习笔记(三)

7、对象类型继承

 

对象类型实现允许我们创建一个基类型，或叫父类型，这种类型的属性或方法可以被另一个对象类型继承。然后可以创建一个子类型，或叫孩子类型，直接使用继承过来的属性或方法，或者用自己的属性和方法重写父类型的属性或方法。

 

INSTANTIABLE关键字表示我们可以从该类型中实例化或者创建对象实例。

FINAL关键字是指该对象类型下面不再有子类型，没有其他类型会继承该对象类型。

 

下面这个例子说明了继承关系，inventory_obj被book_obj继承。

 

CREATE OR REPLACE TYPE inventory_obj AS OBJECT ( item_id NUMBER (10), num_in_stock NUMBER (10), reorder_status VARCHAR2 (20 CHAR), price NUMBER(10,2), CONSTRUCTOR FUNCTION inventory_obj ( item_id IN NUMBER, num_in_stock IN NUMBER, price IN NUMBER ) RETURN SELF AS RESULT, MEMBER PROCEDURE print_inventory, MEMBER PROCEDURE print_status, MEMBER PROCEDURE print_price)INSTANTIABLE NOT FINAL;/ CREATE OR REPLACE TYPE BODY inventory_objAS CONSTRUCTOR FUNCTION inventory_obj ( item_id IN NUMBER, num_in_stock IN NUMBER, price IN NUMBER ) RETURN SELF AS RESULT IS BEGIN SELF.item_id := item_id; SELF.num_in_stock := num_in_stock; SELF.price := price; RETURN; END; MEMBER PROCEDURE print_inventory IS BEGIN DBMS_OUTPUT.put_line ('INVENTORY FOR BOOKSTORE1'); DBMS_OUTPUT.put_line ('========================'); DBMS_OUTPUT.put_line ( 'Item number ' || SELF.item_id || ' has ' || SELF.num_in_stock || ' in stock' ); END print_inventory; MEMBER PROCEDURE print_status IS v_status VARCHAR2 (20); BEGIN IF SELF.num_in_stock > 0 THEN v_status := 'IN STOCK'; ELSE v_status := 'OUT OF STOCK'; END IF; DBMS_OUTPUT.put_line ('INVENTORY STATUS FOR BOOKSTORE1'); DBMS_OUTPUT.put_line ('==============================='); DBMS_OUTPUT.put_line ('Item number ' || SELF.item_id || ' is ' || v_status ); END print_status; MEMBER PROCEDURE print_price IS v_discount_price discount_price_obj := discount_price_obj (SELF.price); BEGIN DBMS_OUTPUT.put_line ('BOOKSTORE1 PRICES'); DBMS_OUTPUT.put_line ('================='); DBMS_OUTPUT.put_line ('Item number ' || SELF.item_id); DBMS_OUTPUT.put_line ('Retail cost: ' || SELF.price || ' US dollars'); DBMS_OUTPUT.put_line ( 'OUR LOW - LOW - LOW DISCOUNT PRICE: ' || v_discount_price.price || ' US dollars' ); END print_price;END;/ --继承 CREATE OR REPLACE TYPE book_objUNDER inventory_obj ( --注意到这个地方用了关键字UNDER，而不是AS OBJECT isbn CHAR (10 CHAR), CATEGORY VARCHAR2 (20 CHAR), title VARCHAR2 (100 CHAR), num_pages NUMBER, CONSTRUCTOR FUNCTION book_obj ( item_id NUMBER, num_in_stock NUMBER, price NUMBER, isbn CHAR, title VARCHAR2, num_pages NUMBER ) RETURN SELF AS RESULT, MEMBER PROCEDURE print_book_information, OVERRIDING MEMBER PROCEDURE print_price --OVERRIDING使子类型中的方法优先于父类型中的同名方法)INSTANTIABLE FINAL;/ CREATE OR REPLACE TYPE BODY book_objIS CONSTRUCTOR FUNCTION book_obj ( item_id NUMBER, num_in_stock NUMBER, price NUMBER, isbn CHAR, title VARCHAR2, num_pages NUMBER ) RETURN SELF AS RESULT IS BEGIN SELF.item_id := item_id; SELF.num_in_stock := num_in_stock; SELF.price := price; SELF.isbn := isbn; SELF.title := title; SELF.num_pages := num_pages; RETURN; END book_obj; MEMBER PROCEDURE print_book_information IS BEGIN DBMS_OUTPUT.put_line ('BOOK INFORMATION'); DBMS_OUTPUT.put_line ('================'); DBMS_OUTPUT.put_line ('Title: ' || SELF.title); DBMS_OUTPUT.put_line ('# Pages: ' || SELF.num_pages); DBMS_OUTPUT.put_line ('# In Stock: ' || SELF.num_in_stock); END print_book_information; OVERRIDING MEMBER PROCEDURE print_price IS BEGIN DBMS_OUTPUT.put_line ('BOOKSTORE1 PRICES'); DBMS_OUTPUT.put_line ('================'); DBMS_OUTPUT.put_line ('Title: ' || SELF.title); DBMS_OUTPUT.put_line ('Always low price of: ' || SELF.price); END print_price;END;/  

 

动态方法调度

 

看看下面这个示例吧 就什么都明白了

 

 

CREATE OR REPLACE TYPE abbrev_inventory_obj AS OBJECT ( item_id NUMBER (10), price NUMBER (10, 2), MEMBER PROCEDURE print_price)NOT FINAL INSTANTIABLE;/ CREATE OR REPLACE TYPE BODY abbrev_inventory_objAS MEMBER PROCEDURE print_price IS v_price NUMBER := SELF.price * .80; BEGIN DBMS_OUTPUT.put_line ('Wholesale Cost: ' || v_price); END print_price;END;/ CREATE OR REPLACE TYPE abbrev_book_objUNDER abbrev_inventory_obj ( isbn VARCHAR2 (50), OVERRIDING MEMBER PROCEDURE print_price)FINAL INSTANTIABLE;/ CREATE OR REPLACE TYPE BODY abbrev_book_objAS OVERRIDING MEMBER PROCEDURE print_price IS BEGIN DBMS_OUTPUT.put_line ('Retail Cost: ' || SELF.price); END print_price;END;/ --测试代码SET SERVEROUTPUT ON SIZE 1000000 DECLARE v_wholesale abbrev_inventory_obj := abbrev_inventory_obj (22, 54.95); v_retail abbrev_book_obj := abbrev_book_obj (22, 54.95, 23022843); BEGIN DBMS_OUTPUT.put_line ('SUBTYPE EXECUTION - FULL PRICE'); DBMS_OUTPUT.put_line ('=============================='); v_retail.print_price; DBMS_OUTPUT.put_line (' '); DBMS_OUTPUT.put_line ('BASE TYPE EXECUTION - REDUCED PRICE'); DBMS_OUTPUT.put_line ('==================================='); v_wholesale.print_price; DBMS_OUTPUT.put_line (' '); DBMS_OUTPUT.put_line ('EXAMPLE OF DYNAMIC DISPATCH'); DBMS_OUTPUT.put_line ('SUBTYPE METHOD RUN WHEN BASE TYPE IS EXECUTED'); DBMS_OUTPUT.put_line ('============================================='); v_wholesale := v_retail; v_wholesale.print_price; END; / 返回结果为--一些不相关的输出都省略了Retail Cost:54.95Wholesale Cost:43.96Retail Cost:54.95 /*从这里可以看出，最终的价格是用对象类型abbrev_inventory_obj执行的（v_wholesale.print_price;），但是结果却是从abbrev_book_obj中获得的(v_wholesale := v_retail;)。 */             

 

8、对象视图

CREATE OR REPLACE TYPE discount_price_obj AS OBJECT ( discount_rate NUMBER (10, 4), price NUMBER (10, 2), MEMBER FUNCTION discount_price RETURN NUMBER)INSTANTIABLE FINAL;/ CREATE OR REPLACE TYPE BODY discount_price_objAS MEMBER FUNCTION discount_price RETURN NUMBER IS BEGIN RETURN (SELF.price * (1 - SELF.discount_rate)); END discount_price;END;/ -- Creates the inventory_tbl relational table that includes-- a column object. CREATE TABLE inventory_tbl ( --创建对象表 item_id NUMBER (10) PRIMARY KEY, num_in_stock NUMBER (10), reorder_status VARCHAR2 (20 CHAR), price discount_price_obj)/-- Recreates the inventory_obj object type to match the-- inventory_tbl table for use with an object view. CREATE OR REPLACE TYPE inventory_obj AS OBJECT ( item_id NUMBER (10), reorder_status VARCHAR2 (20 CHAR), price discount_price_obj, MEMBER PROCEDURE print_inventory, MEMBER PROCEDURE print_status, MEMBER PROCEDURE print_price)INSTANTIABLE NOT FINAL;/ -- Creates the inventory_vie view CREATE VIEW inventory_vie OF inventory_obj WITH OBJECT IDENTIFIER (item_id)AS SELECT i.item_id, i.reorder_status, i.price FROM inventory_tbl i/ --往底层表inventory_tbl中插入一些数据 以方便接下来使用视图进行查询INSERT INTO inventory_tbl VALUES (1, 10, 'IN STOCK', discount_price_obj (.1, 75));INSERT INTO inventory_tbl VALUES (2, 13, 'IN STOCK', discount_price_obj (.1, 54.95));INSERT INTO inventory_tbl VALUES (3, 24, 'IN STOCK', discount_price_obj (.15, 43.95));INSERT INTO inventory_tbl VALUES (4, 13, 'IN STOCK', discount_price_obj (.1, 60));INSERT INTO inventory_tbl VALUES (5, 5, 'IN STOCK', discount_price_obj (.20, 42.95));COMMIT ; -- SELECT all records from the inventory_vie object viewSELECT * FROM inventory_vie/ --SELECT columns from the view, attributes from the column object,and use the discount_price method.SELECT i.item_id, i.price.price, i.price.discount_rate, i.price.discount_price () FROM inventory_vie i/  

 

插入和更新

虽然对象视图不支持直接在其上进行插入和更新操作，但是可以创建一个INSTEAD OF触发器来执行这两种操作。

 

CREATE OR REPLACE TRIGGER inventory_trg INSTEAD OF INSERT ON inventory_vie FOR EACH ROWBEGIN INSERT INTO inventory_tbl VALUES (:NEW.item_id, NULL, :NEW.reorder_status, :NEW.price);END;