第一次写存储过程,有错欢迎指正
/*创建存储过程,没有参数和返回值*/
create or replace procedure p_updateBeanCoin
as
t_discount number;
t_beanNum number;
t_coinNum number;
t_shopperDisCount number;
t_shopperId varchar2(200);
c_row varchar2(200);
begin
/*定义游标,遍历所有店铺id*/
declare cursor temp_shopis select shopperId from ProductOffline;
--游标声明
begin
open temp_shop;
loop
fetch temp_shop into t_shopperId;
exit when temp_shop%notfound; /**如果游标不存在,退出循环*/
/* 定义游标 获取商品商城价和结算价 */
declare cursor temp_price is
select productId,marketprice,discountprice from ProductOffline where shopperId=t_shopperId;
--定义一个游标变量
c_row temp_price%rowtype;
--游标声明
begin
open temp_price;
loop
--提取一行数据到c_row
fetch temp_priceinto c_row;
exit when temp_price%notfound; /**如果游标不存在,退出循环*/
t_discount := c_row.discountprice/c_row.marketprice*100;
/* 获取龙豆的值 */
if t_discount>=95 then
t_beanNum := c_row.marketprice * 0.01;
elsif t_discount>=90 and t_discount<=50then
t_beanNum := c_row.marketprice * 0.05;
elsif t_discount<50 then
t_beanNum := c_row.marketprice * 0.1;
end if;
/* 获取店铺折扣 如果店铺不存在,查出来结果为空,而你想让他继续执行,那就在外面套一层select(...)from dual*/
select( select s.shopperdiscount from ShopperSpecialOffline s where shopperId= t_shopperId ) into t_shopperDisCount from dual;
/* 获取龙点币的值 */
if t_shopperDisCount>=0.85 then
t_coinNum := (c_row.marketprice - c_row.discountprice - t_beanNum) * 0.9;
elsif t_shopperDisCount<0.85 then
t_coinNum := (c_row.marketprice - c_row.discountprice - t_beanNum) * 0.8;
elsif t_shopperDisCount is null then
t_coinNum := 0;
t_beanNum := 0;
end if;
/* 保留两位四舍五入小数,用round(..,2) ,保留两位只舍不入小数,用trunc(..,2)*/
update ProductOffline set beansnum=round(t_beanNum,2),coinNum=round(t_coinNum,2) where productId=c_row.productId;
end loop;
close temp_price;
end;
end loop;
close temp_shop;
end;
COMMIT;
Exception
When others then
Rollback;
End p_updateBeanCoin;
Oracle运行存储过程:
鼠标点在存储过程上,右键选择Test,运行存储过程
Step into /Step over /Step out 区别:
Step into 单步执行,遇到子函数,就进入继续单步执行。
Step over 越过子函数,执行下一行(子函数正常执行)
Step out 执行完当前子函数并跳出,继续执行上一层