产品表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/