学习存储过程时写的个例子-CSDN博客

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;