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;