Oracle:数据库对象（二）

在PL/SQL中使用对象

1.product_package

例如：

create package product_package as 
type t_ref_cursor is ref cursor;
function get_products return t_ref_cursor;
procedure display_product (
    p_id in object_products.id%TYPE
);

procedure insert_product(
    p_id in object_products.id%type,
    p_name in object_products.name%type,
    p_description in object_products.describe%type,
    p_price in object_products.price%type,
    p_days_valid in object_products.days_valid%type
);

procedure update_product_price(
    p_id in object_products.id%type,
    p_factor in number
);

function get_product(
    p_id in object_products.id%type
) return t_product;

procedure update_product(
    p_product t_product;
);

function get_product_ref(
    p_id in object_products.id%type 
)return ref t_product;

procedure delete_product(
    p_id in object_products.id%type
);
end product_package;

2.get_products

function get_products
return t_ref_cursor is
v_products_ref_cursor t_ref_cursor;
begin
    open v_products_ref_cursor for

    select value(op)
    from object_products op
    order by op.id;

    return v_products_ref_cursor;
end get_products;

select product_package.get_products
from dual;

3.display_product

procedure display_product(
    p_id in object_products.id%type
)as
    v_product t_product;
begin
    select value(op)
    into v_product
    from object_products op
    where id = p_id;

    dbms_output.put_line('v_product.id='||v_product.id);
    dbms_output.put_line('v_product.name='||v_product.name);
    dbms_output.put_line('v_product.description='||v_product.description);
    dbms_output.put_line('v_product.days_valid='||v_product.days_valid);
    dbms_output.put_line('Sell by date ='||v_product.get_sell_by_date);
end display_product;

call product_package.display_product;

4.insert_product

procedure insert_product(
    p_id in object_products.id%type,
    p_name in object_products.name%type,
    p_description in object_products.describe%type,
    p_price in object_products.price%type,
    p_days_valid in object_products.days_valid%type
) as
    v_product t_product := 
    t_product(p_id,p_name,p_description,p_price,p_days_valid);
begin
    insert into object_products values (v_product);
    commit;
exception
    when others then
        rollback;
end insert_product;

call product_package.insert_product(3,'salsa','15 oz jar of salsa',1.50,20);

5.update_product_price

procedure update_product_price(
    p_id in object_products.id%type,
    p_factor in number
)as
    v_product t_product;
begin
    select value(op)
    into v_product
    from object_products op
    where id = p_id
    for update;

    dbms_output.put_line('v_product.price='||v_product.price);

    v_product.price := v_product.price * p_factor;

    dbms_output.put_line('new v_product.price='||v_product.price);

    update object_products op
    where id = p_id;
    commit;
exception
    when others then
        rollback;
end update_product_price;

call product_package.update_product_price(3, 2.4);

结果:

v_product.price = 1.5
new v_product.price = 3.6

6.get_product

fuction get_product(
    p_id in object_products.id%type
)
return t_product is
    v_product t_product;
begin
    select value(op)
    into v_product
    from object_products op
    where id = p_id;
    return v_product;
end get_product;

select product_package.get_product(3) from dual;

7.update_product

procedure update_product(
    p_product in t_product
)as
begin
    update object_products op
    set op = p_product
    where id = p_product.id;
    commit;
exception
    when others then
        rollback;
end update_product;

call product_package.update_product(t_product(3, 'salsa','25 oz jar of salsa',2.70,15);

8.get_product_ref

function get_product_ref(
    p_id in object_products.id%type
)
return ref t_product is
    v_product_ref ref t_product;
begin
    select ref(op)
    into v_product_ref
    from object_products op
    where op.id = p_id;
    return v_product_ref;
end get_product_ref;

得到引用:

select product_package.get_product_ref(3) from dual;

得到实际产品:

select deref(product_package.get_product_ref(3)) from dual;

9.delete_product

procedure delete_product(
    p_id in object_products.id%type
) as
begin
    delete from object_products op
    where op.id = p_id;
    commit;
exception
    when others then
        rollback;
end delete_product;

call product_package.delete_product(3);

10.product_lifecycle

create procedure product_lifecycle as
    v_product t_product;
begin
    product_package.insert_product(4,'beef','25 lb pack of beef',32,10);
    product_package.display_product(4);

    select product_package.get_product(4)
    into v_product
    from dual;

    v_product.describe := '20lb pack of beef';
    v_product.price := 36;
    v_product.days_valid := 8;

    product_package.update_product(v_product);
    product_package.display_product(4);
    product_package.delete_product(4);
end product_lifecycle;

call product_lifecycle;

11.product_lifecycle2

create procedure product_lifecycle2 as
    v_product t_product;
    v_product_ref ref t_product;
begin
    product_package.insert_product(4,'beef','25 lb pack of beef',32,10);
    product_package.display_product(4);

    select product_package.get_product_ref(4)
    into v_product_ref
    from dual;

    select deref(v_product_ref)
    into v_product
    from dual;

    v_product.describe := '20lb pack of beef';
    v_product.price := 36;
    v_product.days_valid := 8;

    product_package.update_product(v_product);
    product_package.display_product(4);
    product_package.delete_product(4);
end product_lifecycle2;

call product_lifecycle2;