RK表的字段变化时,插入细表的记录到另一个表中:
create or replace procedure mxz2(bill in varchar2) is
cursor curbill is
select style, qty, taxcost,amnt from rks where rkbill = bill;
csstyle. varchar2(15);
csqty number(18, 2);
cscost number(18, 2);
seqno number(18);
sty_seqno number(18);
vdate date;
jamnt number(18,2);
jcqty number(18,2);
jcamnt number(18,2);
begin
-- select operdate into vdate from rk where rkbill=bill;
--当表触发器中调用存储过程时,不能在这里写调用原表的字段信息。
vdate:='2008-10-01';
open curbill;
loop
fetch curbill
into csstyle, csqty, cscost,jamnt;
exit when curbill%notfound;
begin
select max(maxseqno) into sty_seqno from mxz where style. =csstyle;
if (sty_seqno is null) then
begin
jc_qty:=0;
jc_amnt:=0;
end;
else
select jcqty,jctamnt into jcqty,jcamnt from mxz where maxseqno=sty_seqno;
end if ;
select max(maxseqno) into seqno from mxz ;
if (seqno is null) then
seqno:=1;
else
seqno:=seqno+1;
end if ;
insert into mxz
(maxseqno,style,validdate,cost,qty,jamnt,jcqty,jcamnt)
values
(seqno,csstyle,vdate,cscost,csqty,jamnt,jcqty+csqty,jcamnt+jamnt );
end;
end loop;
close curbill;
commit; ---提交事务--触发器调用时,也不允许有事务存在!
exception
when others then
rollback;
end mxz2;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16486600/viewspace-524505/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16486600/viewspace-524505/