oracle语句块调用
如果要写一个临时的语句块调用某个过程，可以参照以下方式：
declare
cursor v_is is
select distinct aac001 from sic84 where aab001=511500000999 ;
pi_data lew_pub.data;
po_fhz varchar2(1000);
po_msg varchar2(2000);
begin
for x in v_is loop
--select x.aac001 into PI_DATA.AAC001 from dual;
--select '110' into PI_DATA.AAE140 from dual ;
PI_DATA.AAC001 := x.aac001;
PI_DATA.AAE140 := '110';
lew_pub.repair_ac20_by_sj(pi_data, po_fhz, po_msg);
if po_fhz <> '1' then
dbms_output.put_line('出错了：'||po_msg);
end if;
end loop;
end;
循环修改序列号。
1 DECLARE 2 3 CURSOR v_c IS SELECT * FROM sac15_tmp_wyl; 4 v_caz045 sac15.caz045%TYPE; 5 v_aac001 sac15.aac001%TYPE; 6 BEGIN 7 FOR x IN v_c LOOP 8 v_aac001 := x.aac001; 9 SELECT seq_bxgx_caz045.nextval INTO v_caz045 FROM dual; 10 UPDATE sac15_tmp_wyl SET CAZ045 = v_caz045 WHERE aac001 = v_aac001 AND aae140 = '310'; 11 END LOOP; 12 END;
批量授权语句：
1 DECLARE 2 CURSOR V_OPER IS 3 SELECT * 4 FROM FW_OPERATOR 5 WHERE LENGTH(BAE001) = 8 6 AND BAE001 LIKE '511521%'; 7 V_ID NUMBER(12); 8 9 BEGIN 10 FOR V_O IN V_OPER LOOP 11 SELECT SEQ_FW_OPERATOR2RIGHT.NEXTVAL INTO V_ID FROM DUAL; 12 INSERT INTO FW_OPERATOR2RIGHT 13 VALUES 14 (V_ID, V_O.OPERID, '305002', 1, 1, 20150923170000, NULL, 1, NULL); 15 SELECT SEQ_FW_OPERATOR2RIGHT.NEXTVAL INTO V_ID FROM DUAL; 16 INSERT INTO FW_OPERATOR2RIGHT 17 VALUES 18 (V_ID, V_O.OPERID, '305032', 1, 1, 20150923170000, NULL, 1, NULL); 19 END LOOP; 20 END;
1 declare 2 cursor v_is is 3 select distinct aac001 from sic84 where aab001=511500000999 ; 4 pi_data lew_pub.data; 5 po_fhz varchar2(1000); 6 po_msg varchar2(2000); 7 begin 8 for x in v_is loop 9 --select x.aac001 into PI_DATA.AAC001 from dual; 10 --select '110' into PI_DATA.AAE140 from dual ; 11 PI_DATA.AAC001 := x.aac001; 12 PI_DATA.AAE140 := '110'; 13 lew_pub.repair_ac20_by_sj(pi_data, po_fhz, po_msg); 14 if po_fhz <> '1' then 15 dbms_output.put_line('出错了：'||po_msg); 16 end if; 17 end loop; 18 end;
调用过程的另一个例子，20160801加，
declare
pi_aac002 varchar2(20) := '51112219560108xxxx';
v_cnt number(2);
v_cnt_sic84 number(2);
v_aac001 ac01.aac001%type;
v_bae001 ac01.bae001%type;
v_cae122_min sic84.cae122%type;
v_cae122_max sic84.cae122%type;
v_aae180_avg sic84.aae180%type;
v_aab001 sic84.aab001%type;
v_procname varchar2(200);
v_procparams varchar2(500);
PO_FHZ varchar2(400);
po_msg varchar2(400);
begin
select count(1) into v_cnt from ac01 a where a.aac002 = pi_aac002;
if v_cnt > 0 then
select aac001 into v_aac001 from ac01 a where a.aac002 = pi_aac002;
select bae001 into v_bae001 from ac01 a where a.aac002 = pi_aac002;
for nf in 1990 .. 1995 loop
select count(1)
into v_cnt_sic84
from sic84 a
where aac001 = v_aac001
and substr(a.aae002, 1, 4) = nf;
--只有sic84 有数据才更新ac20
if v_cnt_sic84 > 0 then
select min(a.cae122)
into v_cae122_min
from sic84 a
where aac001 = v_aac001
and substr(a.aae002, 1, 4) = nf;
select max(a.cae122)
into v_cae122_max
from sic84 a
where aac001 = v_aac001
and substr(a.aae002, 1, 4) = nf;
select aab001
into v_aab001
from sic84 a
where aac001 = v_aac001
and substr(a.aae002, 1, 4) = nf
and rownum = 1;
select avg(nvl(a.aae180, 0))
into v_aae180_avg
from sic84 a
where aac001 = v_aac001
and substr(a.aae002, 1, 4) = nf;
-- 插入ac20
delete from ac20 a
where aac001 = v_aac001
and substr(a.aae041, 1, 4) = nf;
insert into ac20
(AAZ157,
AAZ159,
BAE001,
AAB001,
AAC001,
AAE140,
AAC013,
CAC012,
AAE041,
AAE042,
AAE180,
CAC036,
AAA041,
AAA042,
AAA043,
AAC402,
AAB301)
values
(seq_bxgx_aaz157.nextval,
null,
v_bae001,
v_aab001,
v_aac001,
'110',
null,
null,
v_cae122_min,
v_cae122_max,
v_aae180_avg,
null,
0.0800,
0.1200,
0.0000,
'0',
null);
end if;
end loop;
-- 循环调用修复sic86 的过程
pkg_zhgl.Ylgrzh_Cxtj(v_aac001, '110', PO_FHZ, PO_MSG);
end if;
end;
