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;

  循环修改序列号。

 DECLARE 

  CURSOR v_c IS SELECT * FROM sac15_tmp_wyl;
v_caz045 sac15.caz045%TYPE;
v_aac001 sac15.aac001%TYPE;
BEGIN
FOR x IN v_c LOOP
v_aac001 := x.aac001;
SELECT seq_bxgx_caz045.nextval INTO v_caz045 FROM dual;
UPDATE sac15_tmp_wyl SET CAZ045 = v_caz045 WHERE aac001 = v_aac001 AND aae140 = '';
END LOOP;
END;

批量授权语句:

 DECLARE
CURSOR V_OPER IS
SELECT *
FROM FW_OPERATOR
WHERE LENGTH(BAE001) = 8
AND BAE001 LIKE '511521%';
V_ID NUMBER(12); BEGIN
FOR V_O IN V_OPER LOOP
SELECT SEQ_FW_OPERATOR2RIGHT.NEXTVAL INTO V_ID FROM DUAL;
INSERT INTO FW_OPERATOR2RIGHT
VALUES
(V_ID, V_O.OPERID, '', 1, 1, 20150923170000, NULL, 1, NULL);
SELECT SEQ_FW_OPERATOR2RIGHT.NEXTVAL INTO V_ID FROM DUAL;
INSERT INTO FW_OPERATOR2RIGHT
VALUES
(V_ID, V_O.OPERID, '', 1, 1, 20150923170000, NULL, 1, NULL);
END LOOP;
END;
 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 := '';
lew_pub.repair_ac20_by_sj(pi_data, po_fhz, po_msg);
if po_fhz <> '' then
dbms_output.put_line('出错了:'||po_msg);
end if;
end loop;
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;

  

上一篇:ios中safari无痕浏览模式下,localStorage的支持情况


下一篇:C++为了兼容,所以并不是纯面向对象编程语言