Oracle 存储过程导出大量数据

   现场提了一个需求,要执行一条很复杂的SQL,由于是省级的系统,同样的SQL,只是一个条件不一样,需要执行60次,每次查出的数据有上百万,在网上搜索了一个方法是eagle写的:  

1.main.sql脚本:
[oracle@jumper utl_file]$ more main.sql
set linesize 200 
set term off verify off feedback off pagesize 999 
set markup html on entmap ON spool on preformat off
spool tables.xls
@get_tables.sql
spool off
exit

2.get_tables.sql脚本:
[oracle@jumper utl_file]$ more get_tables.sql 
select owner,table_name,tablespace_name,blocks,last_analyzed
from all_tables order by 1,2;

3.执行并获得输出:
[oracle@jumper utl_file]$ sqlplus "/ as sysdba" @main
试验了一下,有问题,导出5万条还可以,几十万的数据,excel打不开。

   在itpub论坛中找到newid的存储过程,试验简单的SQL是可以的,当试验非常复杂的SQL的时候,总会遇到报错,'号的问题,大几百行的SQL很难定位问题,然后试验了一种方法解决了这个问题:
1.建一张表,把SQL从excel copy到表中。不过要避开一些坑,如中文的括号,--备注信息去掉,把要替换的字段换成绑定变量,SQL最后的;去掉。
drop table test purge;
create table test (id number,name varchar(1000));
select * from test for update;
  ID    NAME
----- --------------------------------------------------
1 SELECT 'ID='||D.DEVICE_CODE 身份证编码,
2      D.DEVICE_NAME 设备名称,
3       c.full_name 设备分类全路径,
4      '' 县局,
5      ''所,
6        tm.site_name 线,
7        tm.full_path 全路径,
8        DECODE(d.is_capital_assets, 1, '是', 2, '否') 是否资产级,
.....................................................................

2.--存储过程来自于itpub 版主newid,红色的部分是我改动的,这种方法非常高效,实测50万的数据15s。
CREATE OR REPLACE PROCEDURE SQL_TO_CSV2
(
          p_POWER_GRID_FLAG IN number,                        
          p_BUREAU_CODE in VARCHAR2,
          P_DIR IN VARCHAR2,                          -- 导出的文件放置目录
          P_FILENAME IN VARCHAR2                      -- CSV名
)
IS
          L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
          L_COLCNT NUMBER := 0;
          L_SEPARATOR VARCHAR2(1) := ',';
          L_DESCTBL DBMS_SQL.DESC_TAB;
          P_MAX_LINESIZE NUMBER := 32000;
          lv_sql VARCHAR2(32000);
          v_sql varchar(32000);
          cursor c_cursor is select replace(replace(name,':1',p_POWER_GRID_FLAG),':2',''''||p_BUREAU_CODE||'''') name from test order by id;
          LV_ROW VARCHAR2(32000);
          LV_HEADER VARCHAR2(32000);
BEGIN
          v_sql :=' ';
          for c_row in c_cursor loop
             v_sql := v_sql ||' '||c_row.name;
          end loop;

          --dbms_output.put_line(v_sql);
          DBMS_SQL.PARSE( L_THECURSOR, v_sql, DBMS_SQL.NATIVE );
          DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );
          DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
          
          LV_HEADER := 'UTL_FILE.put_line(L_OUTPUT,';
          FOR I IN 1 .. L_COLCNT LOOP
              IF i>1 THEN
                 LV_ROW := LV_ROW||'||'''||L_SEPARATOR||'''||';
                 LV_HEADER := LV_HEADER ||'||'''||L_SEPARATOR||'''||';
              END IF;
              LV_ROW := LV_ROW||'r(i).'||L_DESCTBL(I).COL_NAME;
              LV_HEADER := LV_HEADER||''''||L_DESCTBL(I).COL_NAME||'''';
          END LOOP;
          LV_HEADER := LV_HEADER||');';
          
lv_sql := '
DECLARE
          L_OUTPUT UTL_FILE.FILE_TYPE;
          L_ROW Varchar2(32000) := NULL;
    CURSOR c IS '||v_sql||';
    TYPE tp_rows IS TABLE OF c%ROWTYPE INDEX BY PLS_INTEGER;
    r tp_rows;
BEGIN
          L_OUTPUT := UTL_FILE.FOPEN('''||P_DIR||''', '''||P_FILENAME||'.CSV'', ''W'', '||P_MAX_LINESIZE||');
          '||LV_HEADER||'
          OPEN c;
          LOOP
          FETCH c BULK COLLECT INTO r LIMIT 10000;
          FOR i IN 1..r.COUNT LOOP
              L_ROW := '||LV_ROW||';
              UTL_FILE.put_line(L_OUTPUT,L_ROW);

          END LOOP;
              EXIT WHEN c%NOTFOUND;
          END LOOP;
          CLOSE c;

          UTL_FILE.FCLOSE( L_OUTPUT );
EXCEPTION
          WHEN OTHERS THEN
          UTL_FILE.FCLOSE( L_OUTPUT );
          DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
          RAISE;
END;';
     --DBMS_OUTPUT.PUT_LINE(lv_sql);
     EXECUTE IMMEDIATE lv_sql;
END;
/

3.call SQL_TO_CSV2(1,'0306','ZZ_0421','device_1_0306'); --ZZ_0421是directory

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值