[20210428]改進pr.sql指令碼.txt

[20210428]改進pr.sql指令碼.txt

--//tanel poder提供一個指令碼pr.sql,用來實現顯示資訊的縱向顯示。例子：
SCOTT@book> select * from dept where deptno=10
  2  @ pr
Pivoting output using Tom Kyte's printtab....
==============================
DEPTNO                        : 10
DNAME                         : ACCOUNTING
LOC                           : NEW YORK
PL/SQL procedure successfully completed.

--//第一次看到這樣的使用是上網站，不知道對方如何實現的。後來對方給我一個指令碼，好像是windows
--//版本我修改一下，適合linux使用。我這才想起來tanel poder的指令碼包裡面有。

$ cat prxx.sql
.

set termout off
def _pr_tmpfile=/tmp/pr.out

store set &_pr_tmpfile.set replace
set termout on

set serverout on size 1000000 termout off echo off
save &_pr_tmpfile replace
set termout on

0 c clob := q'\
0 declare

999999      \';;
999999      l_theCursor     integer default dbms_sql.open_cursor;;
999999      l_columnValue   varchar2(4000);;
999999      l_status        integer;;
999999      l_descTbl       dbms_sql.desc_tab;;
999999      l_colCnt        number;;
999999  begin
999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );;
999999      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );;
999999      for i in 1 .. l_colCnt loop
999999          dbms_sql.define_column( l_theCursor, i,
999999                                  l_columnValue, 4000 );;
999999      end loop;;
999999      l_status := dbms_sql.execute(l_theCursor);;
999999      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
999999          dbms_output.put_line( '==============================' );;
999999          for i in 1 .. l_colCnt loop
999999                  dbms_sql.column_value( l_theCursor, i,
999999                                         l_columnValue );;
999999                  dbms_output.put_line
999999                      ( rpad( l_descTbl(i).col_name,
999999                        30 ) || ': ' || l_columnValue );;
999999          end loop;;
999999      end loop;;
999999  exception
999999      when others then
999999          dbms_output.put_line(dbms_utility.format_error_backtrace);;
999999          raise;;
999999 end;;
/

set termout off
@&_pr_tmpfile.set

get &_pr_tmpfile nolist
host rm &_pr_tmpfile
set termout on

--//今天我自己再改一下在每個欄位前面加上序號的功能：
$ cat prxxn.sql
.

set termout off
def _pr_tmpfile=/tmp/pr.out

store set &_pr_tmpfile.set replace
set termout on

set serverout on FORMAT WRAPPED size 1000000 termout off echo off
save &_pr_tmpfile replace
set termout on

0 c clob := q'\
0 declare

999999      \';;
999999      l_theCursor     integer default dbms_sql.open_cursor;;
999999      l_columnValue   varchar2(4000);;
999999      l_status        integer;;
999999      l_descTbl       dbms_sql.desc_tab;;
999999      l_colCnt        number;;
999999  begin
999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );;
999999      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );;
999999      for i in 1 .. l_colCnt loop
999999          dbms_sql.define_column( l_theCursor, i,
999999                                  l_columnValue, 4000 );;
999999      end loop;;
999999      l_status := dbms_sql.execute(l_theCursor);;
999999      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
999999          dbms_output.put_line( '==================================' );;
999999          for i in 1 .. l_colCnt loop
999999                  dbms_sql.column_value( l_theCursor, i,
999999                                         l_columnValue );;
999999                  dbms_output.put_line
999999                      (lpad(i,3)||' '|| rpad( l_descTbl(i).col_name,
999999                        30 ) || ': ' || l_columnValue );;
999999          end loop;;
999999      end loop;;
999999  exception
999999      when others then
999999          dbms_output.put_line(dbms_utility.format_error_backtrace);;
999999          raise;;
999999 end;;
/

set termout off
@&_pr_tmpfile.set

get &_pr_tmpfile nolist
host rm &_pr_tmpfile
set termout on
--//注意第一行的點可不是多餘的，不要刪除。

--//測試如下：
SCOTT@book> select * from emp where rownum=1
  2  @ prxxn
==================================
  1 EMPNO                         : 7369
  2 ENAME                         : SMITH
  3 JOB                           : CLERK
  4 MGR                           : 7902
  5 HIREDATE                      : 1980-12-17 00:00:00
  6 SAL                           : 800
  7 COMM                          :
  8 DEPTNO                        : 20
PL/SQL procedure successfully completed.