130823创建过程

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 errorErrors 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  删除procedureSQL> drop procedure calvin.update_product_price; Procedure dropped