ORACLE PL/SQL 對象(object)學習筆記(三)

最後更新：2018-12-03 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

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 viewCREATE 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;

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More

ORACLE PL/SQL 對象(object)學習筆記(三)

聯繫我們

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support