NEWKID：oci确实比存储过程快。

jowenz

newkid 发表于 2012-11-15 22:35
你是怎么调用的？DIRECTORY创建了没有，有没有授权？报的错误信息是什么？

用system用户，报了这个错，ora-06561 ora-06512

不好意思。我自己没设置好。。。DIRECTORY的问题。。


大师能否把字段名也加上来呢。。生成的文件只有数据，没有字段名。。最好还能考虑一上数字（长数字）用excel打开是科学记数法的问题

sz_lee820

yulihua49 发表于 2012-11-16 10:55
程序在前边，不是我写的，也没仔细看。
加载时块操作肯定快，卸载时好像差不多。
你能改出一个，我们 ...

不支持异地写；这是Oracle安全决定的

newkid

yulihua49 发表于 2012-11-16 10:55
程序在前边，不是我写的，也没仔细看。
加载时块操作肯定快，卸载时好像差不多。
你能改出一个，我们 ...

我在29楼不是改了一个？我用了2000行一批，你也可以改大点试试。

newkid

jowenz 发表于 2012-11-16 14:37
用system用户，报了这个错，ora-06561 ora-06512

不好意思。我自己没设置好。。。DIRECTORY的问题。。 ...

加字段名很容易嘛？刚才没看见，现在快下班了，等我有空改一下。
excel 里面可以去把列的类型设置为字符串。

newkid

带列名输出的版本：

CREATE OR REPLACE PROCEDURE SQL_TO_CSV2
(
          P_QUERY IN VARCHAR2,                        -- PLSQL文
          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);
         
          LV_ROW VARCHAR2(32000);
          LV_HEADER VARCHAR2(32000);
BEGIN
          EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYYMMDD HH24:MI:SS''';

          DBMS_SQL.PARSE( L_THECURSOR, P_QUERY, 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 '||P_QUERY||';
    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||'.tmp'', ''W'', '||P_MAX_LINESIZE||');
          '||LV_HEADER||'
          OPEN c;
         
          LOOP
          FETCH c BULK COLLECT INTO r LIMIT 2000;
         
          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;

          -- UTL_FILE.fremove(P_DIR,to_char(sysdate,'yyyymmdd_')|| P_FILENAME||'.csv');
          -- UTL_FILE.frename(P_DIR,P_FILENAME||'.tmp',P_DIR,to_char(sysdate,'yyyymmdd_')|| P_FILENAME||'.csv');


END;
/

yulihua49

newkid 发表于 2012-11-17 06:35
带列名输出的版本：

CREATE OR REPLACE PROCEDURE SQL_TO_CSV2

哈哈，刚看见，copy了，拿给某人，明天看结果。

jowen

newkid 发表于 2012-11-17 06:23
加字段名很容易嘛？刚才没看见，现在快下班了，等我有空改一下。
excel 里面可以去把列的类型设置为字符 ...

CSV 文件双击打开，如是数字超过一定位数（好像15位），就会出现科学记数位，而且是不可还原的
例如：1234567890123456789  会变成1234567890123450000
在excel 里面可以去把列的类型设置为字符，不能解决该 问题。

如果能生成真正的XLS文件就好了。。

newkid

jowen 发表于 2012-11-19 23:55
CSV 文件双击打开，如是数字超过一定位数（好像15位），就会出现科学记数位，而且是不可还原的
例如：12 ...

网上说在列数据后面跟一个TAB即CHR(9)就可解决，你可以试试。

yulihua49

yulihua49 发表于 2012-11-19 17:13
哈哈，刚看见，copy了，拿给某人，明天看结果。

报告结果，NEWKID果真身手不凡。
你那个程序是10分55秒完成。
但是我把程序移到数据库服务器上，避免了网络拖累。结果是8分17秒。
还是oci快。
总结一下：
PL/SQL 单条：30分
PL/SQL 批量：10分55秒
PROC*C 异地：32分
oci 异地：12分12秒（比顶楼的程序改进了些）
oci 本地 8分17秒。

卸载3992639行，5985550246字节。

快慢也差不多少，相同数量级吧。

20013011094

收藏