130825包

最新推荐文章于 2024-05-10 15:22:36 发布

calvin82

最新推荐文章于 2024-05-10 15:22:36 发布

阅读量350

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/calvin82/article/details/10300209

SQL 专栏收录该内容

13 篇文章

订阅专栏

本文介绍了一个Oracle数据库包的创建过程，包括定义包头和包体，实现产品信息查询与价格更新的功能。通过具体实例展示了如何调用包内的过程来修改特定产品的价格，并验证修改效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建包package

SQL> create or replace package product_package as
  2  type t_ref_cursor is ref cursor;
  3  function get_products_ref_cursor return t_ref_cursor;
  4  procedure update_product_price(
  5    p_product_id in store.products.product_id%type,
  6    p_factor in number
  7    );
  8  
  9  end product_package;
 10  /
 
Package created

然后创建包体：body

SQL> create or replace package body product_package as
  2  function get_products_ref_cursor
  3    return t_ref_cursor is
  4    products_ref_cursor t_ref_cursor;
  5  
  6  begin
  7    open products_ref_cursor for
  8    select product_id, name, price
  9    from store.products;
 10    return products_ref_cursor;
 11  end get_products_ref_cursor;
 12  
 13  procedure update_product_price(
 14    p_product_id in store.products.product_id%type,
 15    p_factor in number ) as
 16    v_product_count integer;
 17  begin
 18    select count(*)
 19    into v_product_count
 20    from store.products
 21    where product_id = p_product_id;
 22  
 23    if v_product_count = 1 then
 24      update store.products
 25      set price = price * p_factor
 26      where product_id = p_product_id;
 27      commit;
 28    end if;
 29    exception
 30      when others then
 31      rollback;
 32  end update_product_price;
 33  end product_package;
 34  /
 
Package body created

调用包里的procedure

SQL> select * from store.products where product_id =3 ;
 
                             PRODUCT_ID                         PRODUCT_TYPE_ID NAME                           DESCRIPTION                                          PRICE
--------------------------------------- --------------------------------------- ------------------------------ -------------------------------------------------- -------
                                      3                                       2 Supernova                      A star explodes                                      25.99

 
SQL> call calvin.product_package.update_product_price(3,1.25);
 
Method called

SQL> select * from store.products where product_id =3 ;
 
                             PRODUCT_ID                         PRODUCT_TYPE_ID NAME                           DESCRIPTION                                          PRICE
--------------------------------------- --------------------------------------- ------------------------------ -------------------------------------------------- -------
                                      3                                       2 Supernova                      A star explodes                                      32.49

查询包相关信息及删除；

SQL> select * from user_procedures;
 
OBJECT_NAME                                                                      PROCEDURE_NAME                  OBJECT_ID SUBPROGRAM_ID OVERLOAD                                 OBJECT_TYPE         AGGREGATE PIPELINED IMPLTYPEOWNER                  IMPLTYPENAME                   PARALLEL INTERFACE DETERMINISTIC AUTHID
-------------------------------------------------------------------------------- ------------------------------ ---------- ------------- ---------------------------------------- ------------------- --------- --------- ------------------------------ ------------------------------ -------- --------- ------------- ------------
PRODUCT_PACKAGE                                                                  GET_PRODUCTS_REF_CURSOR             75105             1                                          PACKAGE             NO        NO                                                                      NO       NO        NO            DEFINER
AVERAGE_PRODUCT_PRICE                                                                                                75103             1                                          FUNCTION            NO        NO                                                                      NO       NO        NO            DEFINER
PRODUCT_PACKAGE                                                                  UPDATE_PRODUCT_PRICE                75105             2                                          PACKAGE             NO        NO                                                                      NO       NO        NO            DEFINER
UPDATE_PRODUCT_PRICE                                                                                                 75101             1                                          PROCEDURE           NO        NO                                                                      NO       NO        NO            DEFINER
PRODUCT_PACKAGE                                                                                                      75105             0                                          PACKAGE             NO        NO                                                                      NO       NO        NO            DEFINER
 
SQL> drop package calvin.product_package;
 
Package dropped