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