Oracle PL/SQL object (object) Study Notes (3)

7. Object type inheritance

 

Object Type implementation allows us to create a base type, or a parent type. attributes or methods of this type can be inherited by another object type. Then you can create a child type, or the child type, to directly use the inherited attributes or methods, or use your own attributes and methods to override the attributes or methods of the parent type.

 

The instantiable keyword indicates that we can instantiate or create an object instance from this type.

The final keyword indicates that there are no child types under the object type, and no other types will inherit the object type.

 

The following example illustrates the inheritance relationship. inventory_obj is inherited by book_obj.

 

Create or replace type inventory_obj as object (</P> <p> item_id number (10), </P> <p> num_in_stock number (10 ), </P> <p> reorder_status varchar2 (20 char), </P> <p> price number (10, 2 ), </P> <p> constructor function inventory_obj (</P> <p> item_id in number, </P> <p> num_in_stock in number, </P> <p> price in number </P> <p>) </P> <p> return self as result, </P> <p> member procedure print_inventory, </P> <p> member procedure print_status, </P> <p> member procedure print_price </P> <p>) </P> <p> instantiable not final; </P> <p>/</P> <p> Create or replace type body inventory_obj </P> <p> as </P> <p> constructor function inventory_obj (</P> <p> item_id in number, </P> <p> num_in_stock in number, </P> <p> price in number </P> <p>) </P> <p> return self as result </P> <p> is </P> <p> begin </P> <p> self. item_id: = item_id; </P> <p> self. num_in_stock: = num_in_stock; </P> <p> self. price: = price; </P> <p> return; </P> <p> end; </P> <p> member procedure print_inventory </P> <p> is </P> <p> begin </P> <p> dbms_output.put_line ('inventory for bookstore1' ); </P> <p> dbms_output.put_line ('=================== '); </P> <p> dbms_output.put_line ('item number' </P> <p> | self. item_id </P> <p> | 'has' </P> <p> | self. num_in_stock </P> <p> | 'in stock' </P> <p>); </P> <p> end print_inventory; </P> <p> member procedure print_status </P> <p> is </P> <p> v_status varchar2 (20 ); </P> <p> begin </P> <p> If self. num_in_stock> 0 </P> <p> then </P> <p> v_status: = 'in stock '; </P> <p> else </P> <p> v_status: = 'out of stocks'; </P> <p> end if; </P> <p> dbms_output.put_line ('ventory status for bookstore1 '); </P> <p> dbms_output.put_line ('========================== = '); </P> <p> dbms_output.put_line ('item number' | self. item_id | 'is '</P> <p> | v_status </P> <p>); </P> <p> end print_status; </P> <p> member procedure print_price </P> <p> is </P> <p> v_discount_price discount_price_obj </P> <p>: = discount_price_obj (self. price); </P> <p> begin </P> <p> dbms_output.put_line ('bookstore1 prices '); </P> <p> dbms_output.put_line ('=================== '); </P> <p> dbms_output.put_line ('item number' | self. item_id); </P> <p> dbms_output.put_line ('retail cost: '| self. price | 'us dollars '); </P> <p> dbms_output.put_line ('ur low-low discount price: '</P> <p> | v_discount_price.price </P> <p> | 'us dollars' </P> <p> ); </P> <p> end print_price; </P> <p> end; </P> <p>/</P> <p> -- Inherit </P> <p> Create or replace type book_obj </P> <p> under inventory_obj (-- note that the keyword under is used in this place, instead of as object </P> <p> ISBN char (10 char), </P> <p> category varchar2 (20 char ), </P> <p> title varchar2 (100 char), </P> <p> num_pages number, </P> <p> constructor function book_obj (</P> <p> item_id number, </P> <p> num_in_stock number, </P> <p> price number, </P> <p> ISBN char, </P> <p> title varchar2, </P> <p> num_pages number </P> <p>) </P> <p> return self as result, </P> <p> member procedure print_book_information, </P> <p> overriding member procedure print_price -- overriding: Make the method in the Child type take precedence over the method with the same name in the parent type </P> <p>) </P> <p> instantiable final; </P> <p>/</P> <p> Create or replace type body book_obj </P> <p> is </P> <p> constructor function book_obj (</P> <p> item_id number, </P> <p> num_in_stock number, </P> <p> price number, </P> <p> ISBN char, </P> <p> title varchar2, </P> <p> num_pages number </P> <p>) </P> <p> return self as result </P> <p> is </P> <p> begin </P> <p> self. item_id: = item_id; </P> <p> self. num_in_stock: = num_in_stock; </P> <p> self. price: = price; </P> <p> self. ISBN: = ISBN; </P> <p> self. title: = title; </P> <p> self. num_pages: = num_pages; </P> <p> return; </P> <p> end book_obj; </P> <p> member procedure print_book_information </P> <p> is </P> <p> begin </P> <p> dbms_output.put_line ('book information '); </P> <p> dbms_output.put_line ('=================== '); </P> <p> dbms_output.put_line ('title: '| self. title); </P> <p> dbms_output.put_line ('# pages:' | self. num_pages); </P> <p> dbms_output.put_line ('# in Stock:' | self. num_in_stock); </P> <p> end print_book_information; </P> <p> overriding member procedure print_price </P> <p> is </P> <p> begin </P> <p> dbms_output.put_line ('bookstore1 prices' ); </P> <p> dbms_output.put_line ('=================== '); </P> <p> dbms_output.put_line ('title: '| self. title); </P> <p> dbms_output.put_line ('always low price of: '| self. price); </P> <p> end print_price; </P> <p> end; </P> <p>/</P> <p>

 

Dynamic Method Scheduling

 

Let's take a look at the following example.

 

 

Create or replace type abbrev_inventory_obj as object (</P> <p> item_id number (10), </P> <p> price number (10, 2 ), </P> <p> member procedure print_price </P> <p>) </P> <p> not final instantiable; </P> <p>/</P> <p> Create or replace type body abbrev_inventory_obj </P> <p> as </P> <p> member procedure print_price </P> <p> is </P> <p> v_price Number: = self. price *. 80; </P> <p> begin </P> <p> dbms_output.put_line ('wholesale Cost: '| v_price); </P> <p> end print_price; </P> <p> end; </P> <p>/</P> <p> Create or replace type abbrev_book_obj </P> <p> under abbrev_inventory_obj (</P> <p> ISBN varchar2 (50 ), </P> <p> overriding member procedure print_price </P> <p>) </P> <p> final instantiable; </P> <p>/</P> <p> Create or replace type body abbrev_book_obj </P> <p> as </P> <p> overriding member procedure print_price </P> <p> is </P> <p> begin </P> <p> DBMS _ Output. put_line ('retail cost: '| self. price); </P> <p> end print_price; </P> <p> end; </P> <p>/</P> <p> -- test code </P> <p> set serveroutput on size 1000000 </P> <p> declare </ p> <p> v_wholesale abbrev_inventory_obj: = abbrev_inventory_obj (22, 54.95); </P> <p> v_retail abbrev_book_obj: = abbrev_book_obj (22, 54.95, 23022843 ); </P> <p> begin </P> <p> dbms_output.put_line ('subtype execution-full price'); </P> <p> DBMS _ Output. put_line ('============================= '); </P> <p> v_retail.print_price; </P> <p> dbms_output.put_line (''); </P> <p> dbms_output.put_line ('base type execution-ced CED price '); </P> <p> dbms_output.put_line ('========================== = '); </P> <p> v_wholesale.print_price; </P> <p> dbms_output.put_line (''); </P> <p> dbms_output.put_line ('example of dynamic dispatch '); </P> <p> dbms_output. Put_line ('subtype method run when base type is executed '); </P> <p> dbms_output.put_line ('========================== ================ '); </P> <p> v_wholesale: = v_retail; </P> <p> v_wholesale.print_price; </P> <p> end; </P> <p>/</P> <p> the returned result is </P> <p> -- some irrelevant outputs are omitted </P> <p>> retail cost: 54.95 </P> <p> wholesale cost: 43.96 </P> <p> retail cost: 54.95/* as shown in the following figure, the final price is executed using the object type abbrev_inventory_obj (v_wholesale.print_pri CE;), but the result is obtained from abbrev_book_obj (v_wholesale: = v_retail ;). */</P> <p>

 

8. Object View

Create or replace type discount_price_obj as object (</P> <p> discount_rate number (10, 4), </P> <p> price number (10, 2 ), </P> <p> member function discount_price </P> <p> return number </P> <p>) </P> <p> instantiable final; </P> <p>/</P> <p> Create or replace type body discount_price_obj </P> <p> as </P> <p> member function discount_price </P> <p> return number </P> <p> is </P> <p> begin </P> <p> return (self. price * (1-self. discount_rate); </P> <p> end discount_price; </P> <p> end; </P> <p>/</P> <p> -- creates the inventory_tbl relational table that includes des </P> <p> -- a column object. </P> <p> Create Table inventory_tbl (-- create an object table </P> <p> item_id number (10) primary key, </P> <p> num_in_stock number (10), </P> <p> reorder_status varchar2 (20 char), </P> <p> price discount_price_obj) </P> <p>/</P> <p> -- recreates the inventory_obj object type to match the </P> <p> -- inventory_tbl table for use with an object view. </P> <p> Create or replace type inventory_obj as object (</P> <p> item_id number (10 ), </P> <p> reorder_status varchar2 (20 char), </P> <p> price discount_price_obj, </P> <p> member procedure print_inventory, </P> <p> member procedure print_status, </P> <p> member procedure print_price </P> <p>) </P> <p> instantiable not final; </P> <p>/</P> <p> -- creates the inventory_vie view </P> <p> Create view inventory_vie </P> <p> of inventory_obj </P> <p> with object identifier (item_id) </P> <p> as </P> <p> select I. item_id, I. reorder_status, I. price </P> <p> from inventory_tbl I </P> <p>/</P> <p> -- insert some data into the underlying table inventory_tbl to facilitate the next use of the View query </P> <p> insert into inventory_tbl </P> <p> values (1, 10, 'in stock', discount_price_obj (. 1, 75); </P> <p> insert into inventory_tbl </P> <p> values (2, 13, 'in stock', discount_price_obj (. 1, 54.95); </P> <p> insert into inventory_tbl </P> <p> values (3, 24, 'in stock', discount_price_obj (. 15, 43.95); </P> <p> insert into inventory_tbl </P> <p> values (4, 13, 'in stock', discount_price_obj (. 1, 60); </P> <p> insert into inventory_tbl </P> <p> values (5, 5, 'in stock', discount_price_obj (. (20, 42.95); </P> <p> commit; </P> <p> -- select all records from the inventory_vie object view </P> <p> select * </P> <p> from inventory_vie </P> <p> >/</P> <p> -- select columns from the view, attributes from the column object, and use the discount_price method. </P> <p> select I. item_id, I. price. price, I. price. discount_rate, </P> <p> I. price. discount_price () </P> <p> from inventory_vie I </P> <p>/</P> <p>

 

Insert and update

Although the object view does not support insert and update operations directly on it, you can create an instead of trigger to perform these two operations.

 

Create or replace trigger inventory_trg </P> <p> instead of insert </P> <p> On inventory_vie </P> <p> for each row </P> <p> begin </P> <p> insert into inventory_tbl </P> <p> values (: new. item_id, null,: New. reorder_status,: New. price); </P> <p> end; <br/>