oracle建立型別之object

產品表product：
drop table product;
create table product (product_id number(18) primary key, product_name varchar2(255), expire_date date, remark varchar2(255), product_fee_id number(18));

對應產品的費用表是product_fee：
drop table product_fee;
create table product_fee(product_fee_id number(18) primary key,  fee number(10));
其中product.product_fee_id與product_fee.product_fee_id是多對一的關係，即一個費用可以由多個產品共享，也就是說可能存在多個產品費用是一樣的情況。

建立一個型別，用於返回產品名稱和產品費用（透過一個object來實現）：
create or replace type rcd_productinfo is object(product_name varchar2(255), product_fee);

構造資料：
insert into product_fee
select rownum, trunc(dbms_random.value(1,100)) from dual connect by rownum <= 39

insert into product
select rownum, dbms_random.string('X', 10),
sysdate + sign(dbms_random.value(1,100) - 5)*dbms_random.value(1,100), dbms_random.string('X', 20),
trunc(dbms_random.value(1,39))
from dual connect by rownum <= 1000

建立函式：
create or replace function w_func_getprodfee(product_id number)
return rcd_productinfo as
        vr_pinfo rcd_productinfo := rcd_productinfo(null,0);
begin
        select pr.product_name, pf.fee from product pr, product_fee pf
         into vr_pinfo.product_name, vr_pinfo.product_fee
        where pf.product_id = pr.product_id;
return vr_pinfo;
end w_func_getprodfee;

呼叫函式：
可以直接呼叫一起顯示，如：
 select w_func_getprodfee(pr.product_id) from product pr;
也可以分開顯示：
 select w_func_getprodfee(pr.product_id).product_name,
w_func_getprodfee(pr.product_fee).product_fee
 from product pr;
體現了這種型別的兩種用法。

Connected to Oracle Database 10g Release 10.2.0.1.0
Connected as lyon
 
SQL>
SQL> select pr.product_id,
  2  w_func_getprodfee(pr.product_id).product_name name,
  3  w_func_getprodfee(pr.product_id).product_fee fee
  4  from product pr
  5  ;
 
         PRODUCT_ID NAME                                                                                     FEE
------------------- -------------------------------------------------------------------------------- -------------------
                  1 5XACBY5N9W                                                                        77
                  2 XT1S8ZVFKQ                                                                         29
                  3 KHUBRI25AP                                                                         80
                  4 NA8WP815EE                                                                        66