Oracle 存储过程导出大量数据

傷城~ 2022-06-16 02:53 272阅读 0赞

现场提了一个需求，要执行一条很复杂的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