|
现场提了一个需求，要执行一条很复杂的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 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
