[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.

--//第一次看到这样的使用是上https://connor-mcdonald.com/网站，不知道对方如何实现的。后来对方给我一个脚本，好像是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.

[20210428]改进pr.sql脚本.txt

标签：一个   size   name   when   脚本   ===   功能   title   ISE