[20251014]建立完善通用的prx.sql腳本.txt

[20251014]建立完善通用的prx.sql腳本.txt

--//前幾天更改了tpt的prr.sql，想實現(xiàn)一個更加通用pr.sql的版本。自己做一些嘗試：

--//參數(shù)1支持2種格式，第1種格式使用數(shù)字序列使用,分開,輸出對應(yīng)字段。第2種格式使用正則表達式輸出對應(yīng)字段.
--//參數(shù)2支持參數(shù)2|n，2表示使用dbms_sql.desc_tab2。n 輸出帶字段的順序號，

$ cat -v prx.sql
-- Notes:   This script is based on Tom Kyte's original printtbl code ( http://asktom.oracle.com )
--          For coding simplicity (read: lazyness) I'm using custom quotation marks ( q'\ ) so
--          this script works only from Oracle 10gR2 onwards

def _pr_tmpfile=&_tpt_tempdir/pr_&_tpt_tempfile..tmp
def _set_tmpfile=&_tpt_tempdir/set_&_tpt_tempfile..sql

@@saveset
set serverout on size 1000000 termout off
save &_pr_tmpfile replace

col tpt_pr   new_value _tpt_pr  format a10
col tpt_pr2  new_value _tpt_pr2  format a10

col tpt_prn  new_value _tpt_prn  format a10
col tpt_prnn new_value _tpt_prnn  format a10

col tpt_pri  new_value _tpt_pri  format a10
col tpt_prr  new_value _tpt_prr  format a10

col 1 new_value 1
col 2 new_value 2

SELECT NULL "1", NULL "2" FROM DUAL WHERE 1 = 2;

SELECT CASE WHEN INSTR (LOWER ('&2'), '2') > 0 THEN '--' ELSE '  ' END tpt_pr
      ,CASE WHEN INSTR (LOWER ('&2'), '2') > 0 THEN '  ' ELSE '--' END tpt_pr2
      ,CASE WHEN INSTR (LOWER ('&2'), 'n') > 0 THEN '--' ELSE '  ' END tpt_prnn
      ,CASE WHEN INSTR (LOWER ('&2'), 'n') > 0 THEN '  ' ELSE '--' END tpt_prn
      ,CASE WHEN INSTR (LOWER ('&1'), ',') > 0 THEN '  ' WHEN '&1' IS NULL THEN '--' ELSE '--' END tpt_pri
      ,CASE WHEN INSTR (LOWER ('&1'), ',') > 0 THEN '--' WHEN '&1' IS NULL THEN '--' ELSE '  ' END tpt_prr
  FROM DUAL;
set termout on

get &_pr_tmpfile nolist
.

0 c clob := q'^F
0 declare

999999      ^F';;
999999      l_theCursor     integer default dbms_sql.open_cursor;;
999999      l_columnValue   varchar2(4000);;
999999      l_status        integer;;
999999      &_tpt_pr  l_descTbl       dbms_sql.desc_tab;;
999999      &_tpt_pr2 l_descTbl       dbms_sql.desc_tab2;;
999999      l_colCnt        number;;
999999  begin
999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );;
999999      &_tpt_pr  dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );;
999999      &_tpt_pr2 dbms_sql.describe_columns2( 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              &_tpt_prr if regexp_like(lower(l_descTbl(i).col_name), lower('&1')) then
999999              &_tpt_pri if  i in (&1) then
999999                  dbms_sql.column_value( l_theCursor, i,l_columnValue );;
999999                  &_tpt_prnn dbms_output.put_line ( rpad( l_descTbl(i).col_name,30 ) || ': ' || l_columnValue );;
999999                  &_tpt_prn  dbms_output.put_line ( lpad(i,3,'0')||' '||rpad( l_descTbl(i).col_name,30 ) || ': ' || l_columnValue );;
999999              &_tpt_pri end if;;
999999              &_tpt_prr end if;;
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 serverout off term on
@@loadset

get &_pr_tmpfile nolist

host &_delete &_pr_tmpfile &_set_tmpfile

--//注：在get與host兩行之際最后存在1個空行,里面的^F在vim下按ctrl+v，ctrl+f輸入。

2.簡單測試：

$ cat tt.txt
SELECT UPPER(NVL(PROGRAM, 'null'))
         , UPPER(MODULE)
         , TYPE
         , DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, ':') - 1))
         , OSUSER
         , MACHINE
         , SCHEMANAME
         , USERNAME
         , SERVICE_NAME
         , SID
         , SERIAL#
  FROM SYS.V_$SESSION
 WHERE SID = SYS_CONTEXT('userenv', 'sid');

SCOTT@book01p> @ tt.txt
UPPER(NVL(PROGRAM,'NULL'))                                                           UPPER(MODULE)                                                    TYPE                           DECODE(NVL(INSTR(PROCESS,':'),0),0,NVL(PROCESS,1 OSUSER
------------------------------------------------------------------------------------ ---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ------------------------------
MACHINE              SCHEMANAME                     USERNAME                       SERVICE_NAME                          SID    SERIAL#
-------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ----------
SQLPLUS@CENTTEST (TNS V1-V3)                                                         SQL*PLUS                                                         USER                           3566                                             oracle
centtest             SCOTT                          SCOTT                          book01p                               146      53724

SCOTT@book01p> @ pr
ORA-06512: at "SYS.DBMS_SQL", line 2129
ORA-06512: at line 24

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 43
ORA-06512: at "SYS.DBMS_SQL", line 2129
ORA-06512: at line 24

--//直接執(zhí)行pr報錯，因為DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS,
--//':') - 1)沒有定義別名，字段名太長，程序報錯，要使用dbms_sql.desc_tab2.

SCOTT@book01p> @ prx '' 2
==============================
UPPER(NVL(PROGRAM,'NULL'))    : SQLPLUS@CENTTEST (TNS V1-V3)
UPPER(MODULE)                 : SQL*PLUS
TYPE                          : USER
DECODE(NVL(INSTR(PROCESS,':'),: 3566
OSUSER                        : oracle
MACHINE                       : centtest
SCHEMANAME                    : SCOTT
USERNAME                      : SCOTT
SERVICE_NAME                  : book01p
SID                           : 146
SERIAL#                       : 53724
PL/SQL procedure successfully completed.

SCOTT@book01p> @ prx name$ 2
==============================
SCHEMANAME                    : SCOTT
USERNAME                      : SCOTT
SERVICE_NAME                  : book01p
PL/SQL procedure successfully completed.
--//輸出name結(jié)尾的字段名。

SCOTT@book01p> @ prx name$ 2n
==============================
007 SCHEMANAME                    : SCOTT
008 USERNAME                      : SCOTT
009 SERVICE_NAME                  : book01p
PL/SQL procedure successfully completed.
--//參數(shù)2加入n，支持輸出字段順序號。

SCOTT@book01p> @ prx 7,8,9 2n
==============================
007 SCHEMANAME                    : SCOTT
008 USERNAME                      : SCOTT
009 SERVICE_NAME                  : book01p
PL/SQL procedure successfully completed.
--//參數(shù)1使用數(shù)字序列，輸出7，8，9字段信息。

SCOTT@book01p> select * from v$database
  2  @ prx ^dbid|supp n
==============================
001 DBID                          : 1617337831
030 SUPPLEMENTAL_LOG_DATA_MIN     : YES
031 SUPPLEMENTAL_LOG_DATA_PK      : NO
032 SUPPLEMENTAL_LOG_DATA_UI      : NO
040 SUPPLEMENTAL_LOG_DATA_FK      : NO
041 SUPPLEMENTAL_LOG_DATA_ALL     : NO
052 SUPPLEMENTAL_LOG_DATA_PL      : NO
059 SUPPLEMENTAL_LOG_DATA_SR      : NO
PL/SQL procedure successfully completed.
--//輸出dbid開頭的字段名以及包含supp的字段名。

--//如果有一些腳本使用參數(shù)1，參數(shù)2就不行了。例子如下：

SCOTT@book01p> @ tpt/seg2 dept
    SEG_MB OWNER                SEGMENT_NAME                   SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
         0 SCOTT                DEPT                           TABLE                USERS                                   8         12        130

SCOTT@book01p> @ prx '' ''
PL/SQL procedure successfully completed.
--//seg2.sql腳本參數(shù)1是dept。而prx執(zhí)行時設(shè)置參數(shù)1=''

--//繼續(xù)執(zhí)行：
SCOTT@book01p> @ pr
PL/SQL procedure successfully completed.
--//因為參數(shù)1已經(jīng)重置，沒有輸出。

SCOTT@book01p> @ pr dept
==============================
SEG_MB                        : 0
SEG_OWNER                     : SCOTT
SEG_SEGMENT_NAME              : DEPT
SEG_PARTITION_NAME            :
SEG_SEGMENT_TYPE              : TABLE
SEG_TABLESPACE_NAME           : USERS
BLOCKS                        : 8
HDRFIL                        : 12
HDRBLK                        : 130
PL/SQL procedure successfully completed.

3.補充說明：
--//為什么在get與host兩行之間最后存在1個空行。
--//測試遇到的問題，通過例子演示：

SCOTT@book01p> select * from emp where mgr is null
  2  ;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10

--//注意分號在第2行。

SCOTT@book01p> @ prx 1,2,3 ''
==============================
EMPNO                         : 7839
ENAME                         : KING
JOB                           : PRESIDENT
PL/SQL procedure successfully completed.
--//第1次執(zhí)行沒有問題

SCOTT@book01p> @ prx 1,2,3 ''
ORA-06512: at "SYS.DBMS_SQL", line 1244
ORA-06512: at line 13

declare
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 36
ORA-06512: at "SYS.DBMS_SQL", line 1244
ORA-06512: at line 13
--//第2次執(zhí)行沒有問題.

SCOTT@book01p>edit
select * from emp where mgr is null
host &_delete &_pr_tmpfile &_set_tmpfile
/

--//edit打開后看到的內(nèi)容如上，明顯執(zhí)行錯誤。

--//在get與host兩行之間最后存在1個空行，就不存在這個問題，建議pr系列的版本都在這兩行之間加入1個空行。

SCOTT@book01p> select * from emp where mgr is null
  2  ;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10

SCOTT@book01p> @ prx 1,2,3 ''
==============================
EMPNO                         : 7839
ENAME                         : KING
JOB                           : PRESIDENT
PL/SQL procedure successfully completed.

SCOTT@book01p> @ prx 1,2,3 ''
==============================
EMPNO                         : 7839
ENAME                         : KING
JOB                           : PRESIDENT
PL/SQL procedure successfully completed.

--//還有1個問題就是我通過判斷參數(shù)2是否存在逗號是否是數(shù)字順序，如果僅僅輸入1個數(shù)字沒有逗號會存在問題，可以簡單加入,0規(guī)避
--//這個問題或者輸入一個很大的數(shù)字比如1001代替0.