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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12932950/viewspace-571247/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12932950/viewspace-571247/