130823创建过程

最新推荐文章于 2021-12-26 21:27:00 发布

calvin82

最新推荐文章于 2021-12-26 21:27:00 发布

阅读量468

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/calvin82/article/details/10275091

版权

SQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

SQL> create or replace procedure update_product_price(
  2   p_product_id IN store.products.product_id%TYPE;
  3   p_factor in number
  4  )
  5  as
  6  v_product_count integer;
  7  
  8  begin
  9    select count(*)
 10    into v_product_count
 11    from store.products
 12    where product_id = p_product_id;
 13  
 14    if v_product_count = 1 then
 15      update store.products
 16      set price = price * p_factor
 17      where product_id = p_product_id;
 18      commit;
 19    end if;
 20  
 21  exception
 22    when others then
 23      rollback;
 24  end update_product_price;
 25  /
 
Warning: Procedure created with compilation errors

使用show error命令显示错误；
SQL> show error
Errors for PROCEDURE CALVIN.UPDATE_PRODUCT_PRICE:
 
LINE/COL ERROR
-------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------
2/48     PLS-00103: Encountered the symbol ";" when expecting one of the following:       := ) , default character  The symbol ", was inserted before ";" to continue.  
 
SQL> 
SQL> create or replace procedure update_product_price(
  2   p_product_id IN store.products.product_id%TYPE,
  3   p_factor in number
  4  )
  5  as
  6  v_product_count integer;
  7  
  8  begin
  9    select count(*)
 10    into v_product_count
 11    from store.products
 12    where product_id = p_product_id;
 13  
 14    if v_product_count = 1 then
 15      update store.products
 16      set price = price * p_factor
 17      where product_id = p_product_id;
 18      commit;
 19    end if;
 20  
 21  exception
 22    when others then
 23      rollback;
 24  end update_product_price;
 25  /
 
Procedure created

创建成功

调用过程，检查效果；

SQL> select price from store.products where product_id =1;
 
  PRICE
-------
  19.95
 
SQL> call calvin.update_product_price(1,1.5);
 
Method called
 
SQL> select price from store.products where product_id =1;
 
  PRICE
-------
  29.93

查询用户下的procedure

SQL> select * from user_procedures;
 
OBJECT_NAME                                                                      PROCEDURE_NAME                  OBJECT_ID SUBPROGRAM_ID OVERLOAD                                 OBJECT_TYPE         AGGREGATE PIPELINED IMPLTYPEOWNER                  IMPLTYPENAME                   PARALLEL INTERFACE DETERMINISTIC AUTHID
-------------------------------------------------------------------------------- ------------------------------ ---------- ------------- ---------------------------------------- ------------------- --------- --------- ------------------------------ ------------------------------ -------- --------- ------------- ------------
UPDATE_PRODUCT_PRICE                                                                                                 75101             1                                          PROCEDURE           NO        NO                                                                      NO       NO        NO            DEFINER
 

 删除procedure
SQL> drop procedure calvin.update_product_price;
 
Procedure dropped