寻求一个Thomas Kyte大师的一个脚本

newkid · 发表于 2017-9-21 23:09

本帖最后由 newkid 于 2017-9-21 23:10 编辑

写完了，好好学习吧：

CREATE OR REPLACE PROCEDURE display_record
(
      p_query IN VARCHAR2
)
IS
      lv_cursor INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
      lv_col_cnt NUMBER := 0;
      lv_desc_tbl DBMS_SQL.DESC_TAB;
      lv_sql VARCHAR2(32000);

      lv_row VARCHAR2(32000);
BEGIN
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYYMMDD HH24:MI:SS''';

      DBMS_SQL.PARSE( lv_cursor, p_query, DBMS_SQL.NATIVE );
      DBMS_SQL.DESCRIBE_COLUMNS( lv_cursor, lv_col_cnt, lv_desc_tbl );
      DBMS_SQL.CLOSE_CURSOR(lv_cursor);

      lv_row :='DBMS_OUTPUT.PUT_LINE(''-------------------------'');'||CHR(10);
      FOR I IN 1 .. lv_col_cnt LOOP
            lv_row := lv_row||'DBMS_OUTPUT.PUT_LINE('''||lv_desc_tbl(I).COL_NAME||':''||r(i).'||lv_desc_tbl(I).COL_NAME||');'||CHR(10);
      END LOOP;

lv_sql := '

DECLARE
CURSOR c IS '||p_query||';
TYPE tp_rows IS TABLE OF c%ROWTYPE INDEX BY PLS_INTEGER;
r tp_rows;
BEGIN
      OPEN c;

      LOOP
      FETCH c BULK COLLECT INTO r LIMIT 2000;

      FOR i IN 1..r.COUNT LOOP
      '||lv_row||'
      END LOOP;
            EXIT WHEN c%NOTFOUND;
      END LOOP;
      CLOSE c;
END;';

   DBMS_OUTPUT.PUT_LINE(lv_sql);
   EXECUTE IMMEDIATE lv_sql;

END display_record;
/

例子：exec display_record('SELECT * FROM T');

〇〇 · 发表于 2017-9-22 08:25

不错

ocpdba591 · 发表于 2017-9-22 17:56

叫print_table，虽然现在不做oracle了，但是还是给个链接，我看了下接近原版：

https://www.2cto.com/database/201301/185419.html

asktom也找不着了

chen222246lei · 发表于 2017-9-22 19:19

多谢各位, 今天折腾了2个多小时,也基本实现了, 但是上来看到NEWKID大师已经写好了,楼上的朋友已经也帮忙找到TOM大师的print_table了. 满满的感动... 我些的代码自然不能和NEWKID大师的作品相比, 但是这里也贴出来吧, 别喷我

大师的作品是要留着慢慢消化的......
CREATE OR REPLACE PROCEDURE get_record_incol(SQLSTR VARCHAR2)
IS
  v_cursor NUMBER;
  exec_result NUMBER;
  col_cnt PLS_INTEGER;
  rec_tab dbms_sql.desc_tab;
  record_str varchar2(4000) := '';
  row_cnt number := 0;
  V_VARCHAR2                VARCHAR2(4000);
  V_NVARCHAR2             NVARCHAR2(4000);
  V_NUMBER                NUMBER;
  V_DATE                   DATE;
  V_CHAR                   CHAR(4000);
  V_BLOB                   BLOB;
  V_CLOB                   CLOB;
  V_BINARY_DOUBLE          BINARY_DOUBLE;
  V_BINARY_FLOAT          BINARY_FLOAT;
  V_LONG                   LONG;
  V_NCLOB                   NCLOB;
  V_RAW                   RAW(4000);
  V_TIMESTAMP             TIMESTAMP(6);
  V_TIMESTAMP_ZONE_LOCALTIME TIMESTAMP(6) WITH LOCAL TIME ZONE;
  V_TIMESTAMP_ZONE_TIME    TIMESTAMP(6) WITH TIME ZONE;
  V_INTERVAL_DAY2SEC       INTERVAL DAY(2) TO SECOND(6);
  V_INTERVAL_YEAR2MON       INTERVAL YEAR(2) TO MONTH;
BEGIN
  v_cursor:= dbms_sql.open_cursor;
  dbms_sql.parse(v_cursor, SQLSTR, dbms_sql.NATIVE);
  --dbms_sql.bind_variable(v_cursor, ':deptno', 20);
  exec_result := dbms_sql.execute(v_cursor);

  /*查看列名和类型*/
  dbms_sql.describe_columns(v_cursor, col_cnt, rec_tab);
for i in 1 .. rec_tab.count loop
   case rec_tab(i).col_type
      when 1 then /* VARCHAR2 or NVARCHAR2 */
      dbms_sql.define_column(v_cursor, i, V_VARCHAR2, 4000);
      when 2 then /* NUMBER */
      dbms_sql.define_column(v_cursor,i, V_NUMBER);
      when 12 then /* DATE */
      dbms_sql.define_column(v_cursor,i, V_DATE);
      when 96 then /* CHAR */
      dbms_sql.define_column(v_cursor,i, V_CHAR, 4000);
      when 113 then /* BLOB */
      dbms_sql.define_column(v_cursor,i, V_BLOB);
      when 112 then /* CLOB NCLOB*/
      dbms_sql.define_column(v_cursor,i, V_CLOB, 4000);
      when 101 then /* BINARY_DOUBLE */
      dbms_sql.define_column(v_cursor,i, V_BINARY_DOUBLE);
      when 100 then /* BINARY_FLOAT */
      dbms_sql.define_column(v_cursor,i, V_BINARY_FLOAT);
      when 8 then /* LONG */
      dbms_sql.define_column(v_cursor,i, V_LONG);
      when 23 then /* RAW */
      dbms_sql.define_column(v_cursor,i, V_RAW);
      when 180 then /* TIMESTAMP */
      dbms_sql.define_column(v_cursor,i, V_TIMESTAMP);
      when 231 then /* TIMESTAMP(6) WITH LOCAL TIME ZONE */
      dbms_sql.define_column(v_cursor,i, V_TIMESTAMP_ZONE_LOCALTIME);
      when 181 then /* TIMESTAMP(6) WITH TIME ZONE */
      dbms_sql.define_column(v_cursor,i, V_TIMESTAMP_ZONE_TIME);
      when 183 then /* INTERVAL DAY(2) TO SECOND(6)*/
      dbms_sql.define_column(v_cursor,i, V_INTERVAL_DAY2SEC);
      when 182 then /* INTERVAL YEAR(2) TO MONTH*/
      dbms_sql.define_column(v_cursor,i, V_INTERVAL_YEAR2MON);
      else
      dbms_sql.define_column(v_cursor, i, V_VARCHAR2,4000);
   end case;
end loop;

/*输出列的值*/
LOOP
EXIT WHEN dbms_sql.fetch_rows(v_cursor)<=0; /*如果游标到达末尾,返回0*/
row_cnt := row_cnt + 1;
record_str := '';
for i in 1 .. rec_tab.count loop
   case rec_tab(i).col_type
      when 1 then /* VARCHAR2 or NVARCHAR2 */
      dbms_sql.column_value(v_cursor,i, V_VARCHAR2);
      record_str := record_str || rec_tab(i).col_name || ': ' || TRIM(V_VARCHAR2) || chr(13) || chr(10);
      when 2 then /* NUMBER */
      dbms_sql.column_value(v_cursor,i, V_NUMBER);
      record_str := record_str || rec_tab(i).col_name || ': ' || to_char(V_NUMBER) || chr(13) || chr(10);
      when 12 then /* DATE */
      dbms_sql.column_value(v_cursor,i, V_DATE);
      record_str := record_str || rec_tab(i).col_name || ': ' || to_char(V_DATE,'YYYY-MM-DD HH24:MI:SS') || chr(13) || chr(10);
      when 96 then /* CHAR */
      dbms_sql.column_value(v_cursor,i, V_CHAR);
      record_str := record_str || rec_tab(i).col_name || ': ' || TRIM(V_CHAR) || chr(13) || chr(10);
      when 113 then /* BLOB */
      --dbms_sql.column_value(v_cursor,i, V_BLOB);
      record_str := record_str || rec_tab(i).col_name || ': ' || 'This is a Blob Field,Ingore...' || chr(13) || chr(10);
      when 112 then /* CLOB NCLOB*/
      dbms_sql.column_value(v_cursor,i, V_CLOB);
      record_str := record_str || rec_tab(i).col_name || ': ' || substr(V_CLOB,1,60) || chr(13) || chr(10);
      when 101 then /* BINARY_DOUBLE */
      dbms_sql.column_value(v_cursor,i, V_BINARY_DOUBLE);
      record_str := record_str || rec_tab(i).col_name || ': ' || to_char(V_BINARY_DOUBLE) || chr(13) || chr(10);
      when 100 then /* BINARY_FLOAT */
      dbms_sql.column_value(v_cursor,i, V_BINARY_FLOAT);
      record_str := record_str || rec_tab(i).col_name || ': ' || to_char(V_BINARY_FLOAT) || chr(13) || chr(10);
      when 8 then /* LONG */
      --dbms_sql.column_value(v_cursor,i, V_LONG);
      record_str := record_str || rec_tab(i).col_name || ': ' || 'This is a Long Field,Ingore...' || chr(13) || chr(10);
      when 23 then /* RAW */
      --dbms_sql.column_value(v_cursor,i, V_RAW);
      record_str := record_str || rec_tab(i).col_name || ': ' || 'This is a RAW Field,Ingore...' || chr(13) || chr(10);
      when 180 then /* TIMESTAMP */
      dbms_sql.column_value(v_cursor,i, V_TIMESTAMP);
      record_str := record_str || rec_tab(i).col_name || ': ' || to_char(V_DATE,'YYYY-MM-DD HH24:MI:SS:FF3') || chr(13) || chr(10);
      when 231 then /* TIMESTAMP(6) WITH LOCAL TIME ZONE */
      dbms_sql.column_value(v_cursor,i, V_TIMESTAMP_ZONE_LOCALTIME);
      record_str := record_str || rec_tab(i).col_name || ': ' || to_char(V_TIMESTAMP_ZONE_LOCALTIME,'YYYY-MM-DD HH24:MI:SS:FF') || chr(13) || chr(10);
      when 181 then /* TIMESTAMP(6) WITH TIME ZONE */
      dbms_sql.column_value(v_cursor,i, V_TIMESTAMP_ZONE_TIME);
      record_str := record_str || rec_tab(i).col_name || ': ' || to_char(V_TIMESTAMP_ZONE_TIME,'YYYY-MM-DD HH24:MI:SS:FF') || chr(13) || chr(10);
      when 183 then /* INTERVAL DAY(2) TO SECOND(6)*/
      dbms_sql.column_value(v_cursor,i, V_INTERVAL_DAY2SEC);
      record_str := record_str || rec_tab(i).col_name || ': ' || V_INTERVAL_DAY2SEC || chr(13) || chr(10);
      when 182 then /* INTERVAL YEAR(2) TO MONTH*/
      dbms_sql.column_value(v_cursor,i, V_INTERVAL_YEAR2MON);
      record_str := record_str || rec_tab(i).col_name || ': ' || V_INTERVAL_YEAR2MON || chr(13) || chr(10);
      else
      record_str := record_str || rec_tab(i).col_name || ' have not recognize field. the col_type value is : ' || rec_tab(i).col_type || chr(13) || chr(10);
      end case;
end loop;
dbms_output.put_line(record_str);
dbms_output.put_line('----------------------------------------------------------');
  END LOOP;
  dbms_sql.close_cursor(v_cursor);
  dbms_output.put_line(row_cnt || ' rows selected.');
  return;

  EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
dbms_sql.close_cursor(v_cursor);
end;
/

set lines 4000
set serveroutput on
exec GET_RECORD_INCOL('select * from employees where department_id = 30');

〇〇 · 发表于 2017-9-22 19:23

我翻到
create or replace function  dump_csv( p_query    in varchar2,
                                    p_separator in varchar2
                                                default ',',
                                    p_dir    in varchar2 ,
                                    p_filename  in varchar2 )
return number
AUTHID CURRENT_USER
is
l_output       utl_file.file_type;
l_theCursor    integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status       integer;
l_colCnt       number default 0;
l_separator    varchar2(10) default '';
l_cnt          number default 0;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );

dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );

for i in 1 .. 255 loop
      begin
         dbms_sql.define_column( l_theCursor, i,
                                 l_columnValue, 2000 );
         l_colCnt := i;
      exception
         when others then
            if ( sqlcode = -1007 ) then exit;
            else
                  raise;
            end if;
      end;
end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue,
                        2000 );

l_status := dbms_sql.execute(l_theCursor);

loop
      exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
      l_separator := '';
      for i in 1 .. l_colCnt loop
         dbms_sql.column_value( l_theCursor, i,
                                 l_columnValue );
         utl_file.put( l_output, l_separator ||
                                 l_columnValue );
         l_separator := p_separator;
      end loop;
      utl_file.new_line( l_output );
      l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
return l_cnt;
end dump_csv;
/

You would use that for example like this:

create or replace procedure test_dump_csv
as
l_rows  number;
begin
l_rows := dump_csv( 'select *
                        from all_users
                        where rownum < 25',
                     ',', '/tmp', 'test.dat' );
end;
/

Make sure to read about the INIT.ORA parameters (utl_file_dir) you need to setup

solomon_007 · 发表于 2017-9-22 20:06

ops$tkyte%ORA11GR2> create or replace
  2  procedure print_ref_cursor
  3  ( p_query in out sys_refcursor,
  4 p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )
  5  is
  6    l_theCursor    integer;
  7    l_columnValue varchar2(4000);
  8    l_descTbl    dbms_sql.desc_tab2;
  9    l_colCnt       number;
10    l_date       date;
11  begin
12    l_theCursor := dbms_sql.to_cursor_number( p_query );
13    dbms_sql.describe_columns2
14    ( l_theCursor, l_colCnt, l_descTbl );
15
16    -- define all columns to be cast to varchar2's, we
17    -- are just printing them out
18    for i in 1 .. l_colCnt loop
19       if ( l_descTbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) )
20       then
21             dbms_sql.define_column
22             (l_theCursor, i, l_date );
23       else
24             dbms_sql.define_column
25             (l_theCursor, i, l_columnValue, 4000);
26       end if;
27    end loop;
28
29    while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
30    loop
31       for i in 1 .. l_colCnt loop
32             if ( l_descTbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) )
33             then
34                dbms_sql.column_value( l_theCursor, i, l_date );
35                l_columnValue := to_char( l_date, p_date_fmt );
36             else
37                dbms_sql.column_value
38                ( l_theCursor, i, l_columnValue );
39             end if;
40             dbms_output.put_line
41                ( rpad( l_descTbl(i).col_schema_name || '.' ||
42                l_descTbl(i).col_name, 30 ) || ': ' || l_columnValue );
43       end loop;
44       dbms_output.put_line( '-----------------' );
45    end loop;
46    dbms_sql.close_cursor( l_theCursor );
47  end;
48  /

Procedure created.

newkid · 发表于 2017-9-22 21:44

看来看去，我的方法还是独一无二的。大部分脚本用DBMS_SQL取数据，不但是逐行取，而且取到了还要逐个列取值。我是FETCH BULK COLLECT INTO批量取，而且直接输出各列的值。
那个XMLTRACE的方法我也玩了一下，代码是很少，但是NULL列不被输出，造成每行数据转出来的行数就不固定，而且没有简单的解决办法，XMLTRACE这个函数也已经过时，不知道会支持到什么时候。

〇〇 · 发表于 2017-9-22 21:49

可惜tom不干oracle了，没法和你比了

solomon_007 · 发表于 2017-9-22 21:53

newkid 发表于 2017-9-22 21:44
看来看去，我的方法还是独一无二的。大部分脚本用DBMS_SQL取数据，不但是逐行取，而且取到了还要逐个列取值 ...

新的都搞 JSON 格式了

newkid · 发表于 2017-9-22 22:12

solomon_007 发表于 2017-9-22 21:53
新的都搞 JSON 格式了

贴个新的来看看。

[PL/SQL] 寻求一个Thomas Kyte大师的一个脚本