当前位置:Gxlcms > 数据库问题 > [20210428]改进pr.sql脚本.txt

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

时间:2021-07-01 10:21:17 帮助过:22人阅读

[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   

人气教程排行