存储过程和游标一起使用的例子

create or replace procedure findmiss(issue in varchar2) IS
Cursor cursor is select aac001 from ac01_zhr;
aac001 varchar(10);
salary number(10,2);
months number(10,2);
tmp ic13_zhr.aic231%type;
begin 
    DBMS_OUTPUT.put_line('金额为正数为<欠拨>，为负数则为<多拨>');
    for aac001 in cursor LOOP
        begin
           select count(*) into months from ic10_zhr where aac001 = aac001.aac001;
           if aac001.aac001 = '2910850218' then
              DBMS_OUTPUT.put_line('');
           end if;
           if months >0 then     
              salary := 0;
              select (((SUBSTR(issue,1,4)- 2008)*12) + (SUBSTR(issue,5,2))) into months from dual;
              salary := salary + (months * 450);
              select (SUBSTR(issue,5,2) * 50 ) into months from dual;
              salary := salary + months;
              select y.aic231 into tmp from ic13_zhr y where y.aac001 = aac001.aac001 and y.aic230='09043';
              if tmp <='200801' then
                 tmp := '200801';
              end if;
              if tmp <=issue then
                 select (((SUBSTR(issue,1,4)- SUBSTR(tmp,1,4))*12) + (SUBSTR('200904',5,2)-SUBSTR(tmp,5,2)+1)) into months from dual;
                  salary := salary + months * 50;
              end if;
              select y.aic231 into tmp from ic13_zhr y where y.aac001 = aac001.aac001 and y.aic230='09048';
              if tmp <='200801' then
                 tmp := '200801';
              end if;
              if tmp <=issue then
                 select (((SUBSTR(issue,1,4)- SUBSTR(tmp,1,4))*12) + (SUBSTR('200904',5,2)-SUBSTR(tmp,5,2)+1)) into months from dual;
                  salary := salary + months * 50;
              end if;
                select abs(y.aic121) into months from ic10_zhr y where y.aac001=aac001.aac001;
                salary := salary - months;
                select nvl(sum(aic142),0) into months  from ic15_zhr_zhc where aac001 = aac001.aac001;
                if salary != months then
                   salary := salary - months;
                   DBMS_OUTPUT.put_line('编号>'|| aac001.aac001 || ' 相差>' || salary);
                end if;
           end if;
        end;
    end LOOP;
    commit;
end findmiss;
/

评论

1 楼 TTLtry 2009-12-05  

  自己平时很少用到，不过感觉如果要做一个很有效率的系统的话，数据库的考虑很有必要。。。