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;
/
存储过程和游标一起使用的例子
最新推荐文章于 2021-04-14 16:29:22 发布