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