oracle中print_table存储过程介绍

一直以来，觉得MySQL中使用\G参数改变输出结果集的显示方式非常好用，尤其是在命令行界面。但是ORACLE数据库没有这个功能，今天在搜索到Tom大师的一篇博文时，发现大师用一个存储过程print_table实现了类似这样的功能。只是我们这些凡夫俗子不知道而已，特意整理在此，方便自己或有需要的人以后查看。

CREATE OR REPLACE PROCEDURE print_table(p_query IN VARCHAR2) 
AUTHID  CURRENT_USER 
IS 
  l_thecursor   INTEGER DEFAULT dbms_sql.open_cursor; 
  l_columnvalue VARCHAR2(4000); 
  l_status      INTEGER; 
  l_desctbl     dbms_sql.desc_tab; 
  l_colcnt      NUMBER; 
BEGIN 
    EXECUTE IMMEDIATE 'alter session set  nls_date_format=''dd-mon-yyyy hh24:mi:ss'' '; 
 
    dbms_sql.parse(l_thecursor, p_query, dbms_sql.native); 
 
    dbms_sql.describe_columns (l_thecursor, l_colcnt, l_desctbl); 
 
    FOR i IN 1 .. l_colcnt LOOP 
        dbms_sql.define_column (l_thecursor, i, l_columnvalue, 4000); 
    END LOOP; 
 
    l_status := dbms_sql.EXECUTE(l_thecursor); 
 
    WHILE ( dbms_sql.Fetch_rows(l_thecursor) > 0 ) LOOP 
        FOR i IN 1 .. l_colcnt LOOP 
            dbms_sql.column_value (l_thecursor, i, l_columnvalue); 
 
            dbms_output.Put_line (RPAD(L_desctbl(i).col_name, 30) 
                                  || ': ' 
                                  || l_columnvalue); 
        END LOOP; 
 
        dbms_output.put_line('-----------------'); 
    END LOOP; 
 
    EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-MON-rr'' '; 
EXCEPTION 
  WHEN OTHERS THEN 
             EXECUTE IMMEDIATE 
             'alter session set nls_date_format=''dd-MON-rr'' '; 
 
             RAISE; 
END; 
/ 

如下测试所示：

SQL> set serveroutput on size 99999;
SQL> execute print_table('select * from v$session where sid=997');
SADDR                         : 000000085FA35CA0
SID                           : 997
SERIAL#                       : 1
AUDSID                        : 0
PADDR                         : 000000085F6B7E70
USER#                         : 0
USERNAME                      :
COMMAND                       : 0
OWNERID                       : 2147483644
TADDR                         :
LOCKWAIT                      :
STATUS                        : ACTIVE
SERVER                        : DEDICATED
SCHEMA#                       : 0
SCHEMANAME                    : SYS
OSUSER                        : oracle
PROCESS                       : 5036
MACHINE                       : xxxx
PORT                          : 0
TERMINAL                      : UNKNOWN
PROGRAM                       : oracle@xxxxx (DBW0)
TYPE                          : BACKGROUND
SQL_ADDRESS                   : 00
SQL_HASH_VALUE                : 0
SQL_ID                        :
SQL_CHILD_NUMBER              : 0
PREV_SQL_ADDR                 : 00
PREV_HASH_VALUE               : 0
PREV_SQL_ID                   :
PREV_CHILD_NUMBER             : 0
PLSQL_ENTRY_OBJECT_ID         :
PLSQL_ENTRY_SUBPROGRAM_ID     :
PLSQL_OBJECT_ID               :
PLSQL_SUBPROGRAM_ID           :
MODULE                        :
MODULE_HASH                   : 0
ACTION                        :
ACTION_HASH                   : 0
CLIENT_INFO                   :
FIXED_TABLE_SEQUENCE          : 0
ROW_WAIT_OBJ#                 : -1
ROW_WAIT_FILE#                : 0
ROW_WAIT_BLOCK#               : 0
ROW_WAIT_ROW#                 : 0
LOGON_TIME                    : 04-jul-2018 21:15:52
LAST_CALL_ET                  : 5272838
PDML_ENABLED                  : NO
FAILOVER_TYPE                 : NONE
FAILOVER_METHOD               : NONE
FAILED_OVER                   : NO
RESOURCE_CONSUMER_GROUP       :
PDML_STATUS                   : DISABLED
PDDL_STATUS                   : DISABLED
PQ_STATUS                     : DISABLED
CURRENT_QUEUE_DURATION        : 0
CLIENT_IDENTIFIER             :
BLOCKING_SESSION_STATUS       : NO HOLDER
BLOCKING_INSTANCE             :
BLOCKING_SESSION              :
SEQ#                          : 34697
EVENT#                        : 3
EVENT                         : rdbms ipc message
P1TEXT                        : timeout
P1                            : 300
P1RAW                         : 000000000000012C
P2TEXT                        :
P2                            : 0
P2RAW                         : 00
P3TEXT                        :
P3                            : 0
P3RAW                         : 00
WAIT_CLASS_ID                 : 2723168908
WAIT_CLASS#                   : 6
WAIT_CLASS                    : Idle
WAIT_TIME                     : 0
SECONDS_IN_WAIT               : 107
STATE                         : WAITING
SERVICE_NAME                  : SYS$BACKGROUND
SQL_TRACE                     : DISABLED
SQL_TRACE_WAITS               : FALSE
SQL_TRACE_BINDS               : FALSE
ECID                          :
-----------------
PL/SQL procedure successfully completed.
 
SQL> 

参考资料:

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1035431863958,%7Bprint_table%7D