oracle产品类型,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