存储过程多次遍历

学习了一波存储过程，处理 数据非常方便！记录一波：

create or replace procedure sp_dataTable_quality AS
--表质量统计 2018-03-31
  v_yesterdate varchar2(10);
  table_id    varchar2(50);
  table_name  varchar2(100);
  existTable     number(8);
  cumulativeNum     number(18); --累计的数量
  actualNum         number(18); --不为空累计的数量
  cumulativesql  varchar2(500);
  actualsql  varchar2(500);
 -- origintableObject t_origintable%RowType;
  originfieldObject t_originfield%RowType;
  v_cur             SYS_REFCURSOR;
  v_cur_two         SYS_REFCURSOR;
  v_cur_three       SYS_REFCURSOR;
  v_cur_four       SYS_REFCURSOR;

begin
  v_yesterdate := to_char(sysdate - 1, 'yyyymmdd');
-- v_yesterdate :='20180321';
  select count(distinct a.tableid) into existTable from t_collectionlog a;
 if(existTable>0) then
 --动态遍历所有的表
  OPEN v_cur FOR
   select distinct a.tableid from t_collectionlog a;
    LOOP FETCH v_cur into table_id;
     EXIT WHEN v_cur%NOTFOUND;
     select a.name into table_name  from t_origintable a where a.id = table_id;
     --动态遍历所有的字段
     OPEN v_cur_two FOR
        select * from t_originfield a
        where a.tableid = table_id;
        LOOP FETCH v_cur_two into originfieldObject;
          EXIT WHEN v_cur_two%NOTFOUND;
        --  table_name:=origintableObject.storagename;
          --动态得到所有的数量
         cumulativesql :=  'select count('||originfieldObject.name||') from '|| table_name ||'@DATASTORE';
         OPEN v_cur_three FOR cumulativesql;
          LOOP FETCH v_cur_three into cumulativeNum;
           EXIT WHEN v_cur_three%NOTFOUND;
           END LOOP;
            CLOSE v_cur_three;
          --动态得到实际统计数量
         actualsql := 'select sum(decode('||originfieldObject.name||', null, 0, 1)) from '|| table_name ||'@DATASTORE';
          OPEN v_cur_four FOR actualsql;
           LOOP FETCH v_cur_four into actualNum;
           EXIT WHEN v_cur_four%NOTFOUND;
           END LOOP;
             CLOSE v_cur_four;
           --把数据插入目标表
          insert into TABLE_QUALITY_STATISTICS(CREATE_DATE,TABLE_ID, TABLE_NAME, COLUMN_NAME,CN_NAME,CUMULATIVE_NUM, ACTUAL_NUM)
             values(v_yesterdate,originfieldObject.tableid, table_name, originfieldObject.name,originfieldObject.cnname, cumulativeNum, actualNum); 
        END LOOP;
        CLOSE v_cur_two;
  END LOOP;
    CLOSE v_cur;
   -- O_RESULT := 'OK';
  end if;
 commit;
end sp_dataTable_quality;

创建对应的job自动执行：

begin
  sys.dbms_job.submit(job => :job,
                      what => 'SP_DATATABLE_QUALITY;',
                      next_date => 'TRUNC(SYSDATE)+1',
                      interval => 'TRUNC(sysdate) + 1 +1/ (24)');
  commit;
end;