工作中遇到的oracle 过程示例（公务员考核特殊身份人员的请假审批人设置）

CREATE OR REPLACE PROCEDURE JOB_SC50 AS
/**
* 公务员考核特殊身份人员的请假审批人设置
*
*
**/
v_cz varchar2(8);--处长
v_n_cz number(3);--处长
v_fgtld varchar2(8);--分管厅领导
v_n_fgtld number(3);--分管厅领导

rettext varchar2(1000);
cursor cur is select * from view_sc05kh sc05 where bsc536 IN ('1','3','7','8','9','10','11','12','13','14') and bsc011 not in('admin','qzc','wp','xq');
BEGIN
delete from sc50 where bsc539 = '2' and bsc100 in (select bsc010 from view_sc05kh where bsc536 IN ('1','3','7','8','9','10','11','12','13','14') );
for v_cur in cur loop
begin
if v_cur.bsc536 = '1'or v_cur.bsc536 = '3' then
insert into sc50 (bsc099,bsc010,bsc100,bsc539,aae017,aae011,aae036,aae101,aae136) values ( seq_bsc099.nextval,'6119',v_cur.bsc010,'2','1','2',sysdate,'2',sysdate);
else
SELECT count(1) into v_n_cz FROM view_sc05kh WHERE BSC001 =v_cur.bsc001 AND BSC536 = '3';
if v_n_cz = '1' then
SELECT bsc010 into v_cz FROM view_sc05kh WHERE BSC001 =v_cur.bsc001 AND BSC536 = '3';

insert into sc50 (bsc099,bsc010,bsc100,bsc539,aae017,aae011,aae036,aae101,aae136) values ( seq_bsc099.nextval,v_cz,v_cur.bsc010,'2','1','2',sysdate,'2',sysdate);

select count(1) into v_n_fgtld from sc05 where bsc010 = (select sc52.bsc010 from sc52,sc50 where sc50.bsc010 = sc52.bsc010 and sc50.bsc539 = '3' and sc50.bsc100 =v_cz and sc52.bsc536 = '1');

if v_n_fgtld = '1' then
select bsc010 into v_fgtld from sc05 where bsc010 = (select sc52.bsc010 from sc52,sc50 where sc50.bsc010 = sc52.bsc010 and sc50.bsc539 = '3' and sc50.bsc100 =v_cz and sc52.bsc536 = '1');

insert into sc50 (bsc099,bsc010,bsc100,bsc539,aae017,aae011,aae036,aae101,aae136) values ( seq_bsc099.nextval,v_fgtld,v_cur.bsc010,'2','1','2',sysdate,'2',sysdate);
end if;
end if;
end if;
end;
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
rettext :=sqlerrm;
END;