ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle创建类型之object

oracle创建类型之object

原创 Linux操作系统 作者:regonly1 时间:2009-03-17 17:35:34 0 删除 编辑

产品表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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: script win
请登录后发表评论 登录
全部评论

注册时间:2008-05-10

  • 博文量
    257
  • 访问量
    1048835