126.Oracle数据库SQL开发之 数据库对象——PLSQL中使用对象

126.Oracle数据库SQL开发之 数据库对象——PLSQL中使用对象

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50087065

PLSQL中可以创建和操作对象。

看一个product_package 的包，如下：

CREATE PACKAGE product_package AS

  TYPEt_ref_cursor IS REF CURSOR;

  FUNCTIONget_products RETURN t_ref_cursor;

  PROCEDUREdisplay_product(

    p_id IN object_products.id%TYPE

  );

  PROCEDUREinsert_product(

    p_id          IN object_products.id%TYPE,

    p_name        IN object_products.name%TYPE,

   p_description IN object_products.description%TYPE,

   p_price       INobject_products.price%TYPE,

    p_days_valid IN object_products.days_valid%TYPE

  );

  PROCEDUREupdate_product_price(

    p_id     IN object_products.id%TYPE,

    p_factor INNUMBER

  );

  FUNCTIONget_product(

    p_id INobject_products.id%TYPE

  ) RETURNt_product;

  PROCEDURE update_product(

    p_productt_product

  );

  FUNCTIONget_product_ref(

    p_id INobject_products.id%TYPE

  ) RETURN REFt_product;

  PROCEDUREdelete_product(

    p_id INobject_products.id%TYPE

  );

END product_package;

/

1.  Get_products函数

返回一个指向object_products表中对象的REF CURSOR。

2.  Display_product过程

过程显示object_products表中单个对象的属性。

3.  Insert_product过程

向object_products表中增加一个对象。

4.  Update_product_price过程

更新object_products表中对象的price属性。

5.  Get_product函数

返回object_products表的单个对象。

6.  Update_product过程

更新object_products表中对象的所有属性。

7.  Get_product_ref函数

返回对object_products表的单个对象的引用。

8.  Delete_product过程

删除object_products表的单个对象。

9.  Product_lifecycle2 过程

使用对象引用来访问产品。

10.        附件

CREATE PACKAGE BODY product_package AS

  FUNCTIONget_products

  RETURNt_ref_cursor IS

    -- declarea t_ref_cursor object 

   v_products_ref_cursor t_ref_cursor;

  BEGIN

    -- get theREF CURSOR

    OPENv_products_ref_cursor FOR

      SELECTVALUE(op)

      FROMobject_products op

      ORDER BYop.id;

 

    -- returnthe REF CURSOR

    RETURNv_products_ref_cursor;

  ENDget_products;

 

  PROCEDUREdisplay_product(

    p_id INobject_products.id%TYPE

  ) AS

    -- declarea t_product object named v_product

    v_productt_product;

  BEGIN

    -- attemptto get the product and store it in v_product

    SELECTVALUE(op)

    INTOv_product

    FROMobject_products op

    WHERE id =p_id;

 

    -- displaythe attributes of v_product

   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.price=' ||

     v_product.price);

DBMS_OUTPUT.PUT_LINE('v_product.days_valid=' ||

     v_product.days_valid);

 

    -- callv_product.get_sell_by_date() and display the date

   DBMS_OUTPUT.PUT_LINE('Sell by date=' ||

     v_product.get_sell_by_date());

  ENDdisplay_product;

 

  PROCEDUREinsert_product(

    p_id          IN object_products.id%TYPE,

    p_name        IN object_products.name%TYPE,

   p_description IN object_products.description%TYPE,

   p_price       INobject_products.price%TYPE,

   p_days_valid  INobject_products.days_valid%TYPE

  ) AS

    -- create at_product object named v_product

    v_productt_product :=

      t_product(

        p_id,p_name, p_description, p_price, p_days_valid

      );

  BEGIN

    -- addv_product to the object_products table

    INSERT INTOobject_products VALUES (v_product);

    COMMIT;

  EXCEPTION

    WHEN OTHERSTHEN

      ROLLBACK;

  END insert_product;

 

  PROCEDUREupdate_product_price(

    p_id     IN object_products.id%TYPE,

    p_factor INNUMBER

  ) AS

    -- declarea t_product object named v_product

    v_productt_product;

  BEGIN

    -- attemptto select the product for update and

    -- storethe product in v_product

    SELECTVALUE(op)

    INTOv_product

    FROMobject_products op

    WHERE id =p_id

    FOR UPDATE;

 

    -- displaythe current price of v_product

   DBMS_OUTPUT.PUT_LINE('v_product.price=' ||

     v_product.price);

 

    -- multiplyv_product.price by p_factor

   v_product.price := v_product.price * p_factor;

   DBMS_OUTPUT.PUT_LINE('New v_product.price=' ||

     v_product.price);

 

    -- updatethe product in the object_products table

    UPDATE object_productsop

    SET op =v_product

    WHERE id =p_id;

    COMMIT;

  EXCEPTION

    WHEN OTHERSTHEN

      ROLLBACK;

  ENDupdate_product_price;

 

  FUNCTIONget_product(

    p_id INobject_products.id%TYPE

  )

  RETURNt_product IS

    -- declarea t_product object named v_product

    v_productt_product;

  BEGIN

    -- get theproduct and store it in v_product

    SELECTVALUE(op)

INTOv_product

    FROM object_products op

    WHERE op.id = p_id;

 

    -- return v_product

    RETURN v_product;

  END get_product;

 

  PROCEDURE update_product(

    p_product IN t_product

  ) AS

  BEGIN

    -- update the product in theobject_products table

    UPDATE object_products op

    SET op = p_product

    WHERE id = p_product.id;

    COMMIT;

  EXCEPTION

    WHEN OTHERS THEN

      ROLLBACK;

  END update_product;

 

  FUNCTION get_product_ref(

    p_id IN object_products.id%TYPE

  )

  RETURN REF t_product IS

    -- declare a reference to a t_product

    v_product_ref REF t_product;

  BEGIN

    -- get the REF for the product and

    -- store it in v_product_ref

    SELECT REF(op)

    INTO v_product_ref

    FROM object_products op

    WHERE op.id = p_id;

 

    -- return v_product_ref

    RETURN v_product_ref;

  END get_product_ref;

 

  PROCEDURE delete_product(

    p_id IN object_products.id%TYPE

  ) AS

  BEGIN

    -- delete the product

    DELETE FROM object_products op

    WHERE op.id = p_id;

    COMMIT;

  EXCEPTION

    WHEN OTHERS THEN

      ROLLBACK;

  END delete_product;

ENDproduct_package;

/

 

CREATEPROCEDURE product_lifecycle AS

  -- declare object

  v_product t_product;

BEGIN

  -- insert a new product

  product_package.insert_product(4, 'beef',

   '25 lb pack of beef', 32, 10);

 

  -- display the product

  product_package.display_product(4);

 

  -- get the new product and store it inv_product

  SELECT product_package.get_product(4)

  INTO v_product

  FROM dual;

 

  -- change some attributes of v_product

  v_product.description := '20 lb pack ofbeef';

  v_product.price := 36;

  v_product.days_valid := 8;

  -- update the product

  product_package.update_product(v_product);

 

  -- display the product

  product_package.display_product(4);

 

  -- delete the product

  product_package.delete_product(4);

ENDproduct_lifecycle;

/

 

CREATEPROCEDURE product_lifecycle2 AS

  -- declare object

  v_product t_product;

 

  -- declare object reference

  v_product_ref REF t_product;

BEGIN

  -- insert a new product

  product_package.insert_product(4, 'beef',

   '25 lb pack of beef', 32, 10);

 

  -- display the product

  product_package.display_product(4);

 

  -- get the new product reference and store itin v_product_ref

  SELECT product_package.get_product_ref(4)

  INTO v_product_ref

  FROM dual;

 

  -- dereference v_product_ref using thefollowing query

  SELECT DEREF(v_product_ref)

  INTO v_product

  FROM dual;

 

  -- change some attributes of v_product

  v_product.description := '20 lb pack ofbeef';

  v_product.price := 36;

  v_product.days_valid := 8;

 

  -- update the product

  product_package.update_product(v_product);

 

  -- display the product

  product_package.display_product(4);

 

  -- delete the product

  product_package.delete_product(4);

ENDproduct_lifecycle2;

/