3,491
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure proc(num in number)
as
v_num number:=num;
cursor c1 is select * from tt order by id for update;
begin
for cur1 in c1
loop
v_num:=v_num*cur1.cheng/cur1.chu;
update tt set jisuan=v_num where current of c1;
end loop;
commit;
end proc;
declare
c_id number(20);
v_jisuan number(4,3);
danjia varchar2(20);
cursor cur is select c.id from tab_temp c;
begin
open cur;
loop
fetch cur into c_id;
/*DBMS_OUTPUT.put_line(c_id || ':'||nvl(v_jisuan,0));*/
update tab_temp set jisuan = nvl(v_jisuan,&t)*chen/chu where id=c_id;
select jisuan into v_jisuan from tab_temp where id=c_id;
exit when cur%notfound;
end loop;
close cur;
end;
with tab_temp as
(
select 1 id, 0 pid, 1 chen, 6 chu ,0 jishuan from dual
union all
select 2, 1, 2, 6, 0 from dual
union all
select 3, 1, 3, 6, 0 from dual
)
select t.id,t.pid,t.chen,t.chu,&变量*chen/chu as jisuan from tab_temp t;