创建存储过程和函数

1创建函数

SQL> create or replace function mgs(
  2  man in number,girl in number)
  3  return number as
  4  sul number;
  5  begin
  6  sul:=man*girl;
  7  return sul;
  8  end mgs;
  9  /
 
Function created

2调用函数

select mgs(5,6) from dual;

3删除函数 drop function mgs;

4创建存储过程

create or replace procedure update_product_price(

p_product_id in products.product_id%type,p_factor in number)

as

 v_product_count integer;

begin

select count(*)

into v_product_count

from products

where product_id=p_product_id;

if v_product_count=1 then

update products

set price=price*p_factor

where product_id=p_product_id;

commit;

end if;

exception

when  others then

rollback;

end update_product_price;

/

调用过程

call update_product_price(1,1.5);