图灵小队—Oracle存储过程导出表的明细_UTL_FILE(各工厂适合）

图灵小队—Oracle存储过程导出表的明细_UTL_FILE

1、创建路径

create or replace directory "dir_dest" as '/home/oracle'; 
grant read on directory "dir_dest" to sys; 
grant write on directory "dir_dest" to sys;

2、创建存储过程

注意：在PL/SQL中是没有数组(Array)概念的,但是如果程序员想用Array的话,可以用TYPE和 Table of Record变通一下,来代替多维数组.

create or replace procedure p_table_count
is 
  Num_Count number(20);
  str_Count_middle varchar(2000);
  str_Sql varchar(200);
  str_All varchar(400);
  vFileName varchar2(100); 
  OutputFile UTL_FILE.FILE_TYPE; 

-- 声明数组类型
  TYPE ts_varray IS VARRAY(110) OF varchar2(100); --120是最大的下标
-- 声明数组变量
  vs_varray ts_varray := ts_varray();

begin 
  OutputFile := utl_file.fopen('dir_dest','count_table.txt','a'); 
  vs_varray.extend; vs_varray(1) := 'SELECT count(*) FROM mes_prd.BK_ERROR_BANHAO';
  vs_varray.extend; vs_varray(2) := 'SELECT count(*) FROM mes_prd.MES_SO_PROCESSFLOW';
  vs_varray.extend; vs_varray(3) := 'SELECT count(*) FROM mes_prd.MES_SO_PFITEM';
  vs_varray.extend; vs_varray(4) := 'SELECT count(*) FROM mes_prd.MES_SO_PFITEMREL';
  vs_varray.extend; vs_varray(5) := 'SELECT count(*) FROM mes_prd.MES_PLAN_SOPFITEM';
  vs_varray.extend; vs_varray(6) := 'SELECT count(*) FROM mes_prd.MES_SO_PFGXMAT';
  vs_varray.extend; vs_varray(7) := 'SELECT count(*) FROM mes_prd.MES_SO_PFGXMAT_R';
  vs_varray.extend; vs_varray(8) := 'SELECT count(*) FROM mes_prd.MES_PRD_JIGONG';
  vs_varray.extend; vs_varray(9) := 'SELECT count(*) FROM mes_prd.MES_SO_PFITEMSEC';
  vs_varray.extend; vs_varray(10) := 'SELECT count(*) FROM mes_prd.MES_PRDSUBMIT_HIS';
  vs_varray.extend; vs_varray(11) := 'SELECT count(*) FROM mes_prd.MES_PRDSUBMIT';
  vs_varray.extend; vs_varray(12) := 'SELECT count(*) FROM mes_prd.MES_PRDSUBMIT_ITEM';
  vs_varray.extend; vs_varray(13) := 'SELECT count(*) FROM mes_prd.MES_PRD_PARA';
  vs_varray.extend; vs_varray(14) := 'SELECT count(*) FROM mes_prd.MES_DZ_STATUS_HIS';
  vs_varray.extend; vs_varray(15) := 'SELECT count(*) FROM mes_prd.MES_PZAUTOASSIGNWORK';
  vs_varray.extend; vs_varray(16) := 'SELECT count(*) FROM mes_prd.NEIP_LOG';
  vs_varray.extend; vs_varray(17) := 'SELECT count(*) FROM mes_prd.NEIP_TASK_HISTORY';
  vs_varray.extend; vs_varray(18) := 'SELECT count(*) FROM mes_prd.SYS_LOG';
  vs_varray.extend; vs_varray(19) := 'SELECT count(*) FROM mes_prd.MES_JMT_TRANSPORTCOST';
  vs_varray.extend; vs_varray(20) := 'SELECT count(*) FROM mes_prd.MES_JMT_DELIVERSHEETS';
  vs_varray.extend; vs_varray(21) := 'SELECT count(*) FROM mes_prd.MES_JMT_VOUCHERITEM';
  vs_varray.extend; vs_varray(22) := 'SELECT count(*) FROM mes_prd.MES_JMT_MATERIALDETAILS';
  vs_varray.extend; vs_varray(23) := 'SELECT count(*) FROM mes_prd.MES_JMT_CUSITEM';
  vs_varray.extend; vs_varray(24) := 'SELECT count(*) FROM mes_prd.MES_JMT_SEND';
  vs_varray.extend; vs_varray(25) := 'SELECT count(*) FROM mes_prd.MES_DC_WASTE_DEPOT';
  vs_varray.extend; vs_varray(26) := 'SELECT count(*) FROM mes_prd.MES_JMT_PROCESSCOST';
  vs_varray.extend; vs_varray(27) := 'SELECT count(*) FROM mes_prd.MES_JMT_BACKWAREHOUSE';
  vs_varray.extend; vs_varray(28) := 'SELECT count(*) FROM mes_prd.MES_PCDEPT_DAYWORKTIME';
  vs_varray.extend; vs_varray(29) := 'SELECT count(*) FROM mes_prd.MES_JMT_DAYWORKTIME';
  vs_varray.extend; vs_varray(30) := 'SELECT count(*) FROM mes_prd.MES_INTEGRATEDSERVICE';
  vs_varray.extend; vs_varray(31) := 'SELECT count(*) FROM mes_prd.MES_PLAN_STATELOCK';
  vs_varray.extend; vs_varray(32) := 'SELECT count(*) FROM mes_prd.MES_DC_ZHUANGBAN';
  vs_varray.extend; vs_varray(33) := 'SELECT count(*) FROM mes_prd.MES_DC_ZB_BANGUN';
  vs_varray.extend; vs_varray(34) := 'SELECT count(*) FROM mes_prd.MES_DC_ZB_JIGONG';
  
  FOR i IN 1 .. 34 LOOP
    -- 自增数组大小
    vs_varray.extend; --注释该行的话,会报下标越界的错误
    -- 数组赋值
    --vs_varray(i) := 'select count(*) from test.test01';
    execute IMMEDIATE vs_varray(i) into Num_Count;
    str_Count_middle:=str_Count_middle||vs_varray(i)||chr(9)||chr(9)||chr(9)||to_char(Num_Count)||chr(10); 
  END LOOP;
  str_All:=chr(10)||chr(13)||'************************************'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'***********************************'||chr(10)||chr(13);
  str_Count_middle:=str_All||str_Count_middle;
  utl_file.put(OutputFile,str_Count_middle); 
  utl_file.fflush(OutputFile); 
  utl_file.fclose(OutputFile); 
end ; 
/