Oracle行转列函数PRINT_TABLE的用法

简介

在sqlplus，如果列比较多，往往会显示不够清晰，这时如果能把查询语句行转列就明了多了，可以用print_table存储过程实现。

存储过程PRINT_TABLE源码

CREATE OR REPLACE PROCEDURE PRINT_TABLE(P_QUERY    IN VARCHAR2,
                                        P_DATE_FMT IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS')
-- THIS UTILITY IS DESIGNED TO BE INSTALLED ONCE IN A DATABASE AND USED
  -- BY ALL.  ALSO, IT IS NICE TO HAVE ROLES ENABLED SO THAT QUERIES BY
  -- DBA'S THAT USE A ROLE TO GAIN ACCESS TO THE DBA_* VIEWS STILL WORK
  -- THAT IS THE PURPOSE OF AUTHID CURRENT_USER
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;
  L_CS          VARCHAR2(255);
  L_DATE_FMT    VARCHAR2(255);
  l_rowCnt      NUMBER := 1;
 
  -- SMALL INLINE PROCEDURE TO RESTORE THE SESSIONS STATE
  -- WE MAY HAVE MODIFIED THE CURSOR SHARING AND NLS DATE FORMAT
  -- SESSION VARIABLES, THIS JUST RESTORES THEM
  PROCEDURE RESTORE IS
  BEGIN
    IF (UPPER(L_CS) NOT IN ('FORCE', 'SIMILAR')) THEN
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING=EXACT';
    END IF;
  
    IF (P_DATE_FMT IS NOT NULL) THEN
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''' ||
                        L_DATE_FMT || '''';
    END IF;
  
    DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
  END RESTORE;
BEGIN
  -- I LIKE TO SEE THE DATES PRINT OUT WITH TIMES, BY DEFAULT, THE
  -- FORMAT MASK I USE INCLUDES THAT.  IN ORDER TO BE "FRIENDLY"
  -- WE SAVE THE DATE CURRENT SESSIONS DATE FORMAT AND THEN USE
  -- THE ONE WITH THE DATE AND TIME.  PASSING IN NULL WILL CAUSE
  -- THIS ROUTINE JUST TO USE THE CURRENT DATE FORMAT
  IF (P_DATE_FMT IS NOT NULL) THEN
    SELECT SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT')
      INTO L_DATE_FMT
      FROM DUAL;
  
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''' || P_DATE_FMT || '''';
  END IF;
 
  -- TO BE BIND VARIABLE FRIENDLY ON THIS AD-HOC QUERIES, WE
  -- LOOK TO SEE IF CURSOR SHARING IS ALREADY SET TO FORCE OR
  -- SIMILAR, IF NOT, SET IT SO WHEN WE PARSE -- LITERALS
  -- ARE REPLACED WITH BINDS
  IF (DBMS_UTILITY.GET_PARAMETER_VALUE('CURSOR_SHARING', L_STATUS, L_CS) = 1) THEN
    IF (UPPER(L_CS) NOT IN ('FORCE', 'SIMILAR')) THEN
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING=FORCE';
    END IF;
  END IF;
 
  -- PARSE AND DESCRIBE THE QUERY SENT TO US.  WE NEED
  -- TO KNOW THE NUMBER OF COLUMNS AND THEIR NAMES.
  DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL);
 
  -- DEFINE ALL COLUMNS TO BE CAST TO VARCHAR2'S, WE
  -- ARE JUST PRINTING THEM OUT
  FOR I IN 1 .. L_COLCNT LOOP
    IF (L_DESCTBL(I).COL_TYPE NOT IN (113)) THEN
      DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000);
    END IF;
  END LOOP;
 
  -- EXECUTE THE QUERY, SO WE CAN FETCH
  L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);
 
  -- LOOP AND PRINT OUT EACH COLUMN ON A SEPARATE LINE
  -- BEAR IN MIND THAT DBMS_OUTPUT ONLY PRINTS 255 CHARACTERS/LINE
  -- SO WE'LL ONLY SEE THE FIRST 200 CHARACTERS BY MY DESIGN...
  WHILE (DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0) LOOP
    FOR I IN 1 .. L_COLCNT LOOP
      IF (L_DESCTBL(I).COL_TYPE NOT IN (113)) THEN
        DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE);
        DBMS_OUTPUT.PUT_LINE(RPAD(L_DESCTBL(I).COL_NAME, 30) || ': ' ||
                             SUBSTR(L_COLUMNVALUE, 1, 200));
      END IF;
    END LOOP;
  
      dbms_output.put_line('[Row:' || l_rowCnt ||']--------------------------------------------------------------------------------------------');
      l_rowCnt := l_rowCnt + 1;

  END LOOP;
 
  -- NOW, RESTORE THE SESSION STATE, NO MATTER WHAT
  RESTORE;
EXCEPTION
  WHEN OTHERS THEN
    RESTORE;
    RAISE;
END;
/

PRINT_TABLE用法

set serveroutput on size 1000000
set line 1000
exec print_table('select * from v$database');
exec print_table('select * from dba_users where rownum<=2');

-- 含单引号时：select * from dba_users where USERNAME='SYSTEM';
exec print_table(Q'[ select * from dba_users where USERNAME='SYSTEM'  ]');
exec print_table( 'select * from dba_users where USERNAME=''SYSTEM'' ');

含单引号时，可以使用Q语法，或者多一层引号也可以。

有关Oracle的Q语法请参考：https://www.xmmup.com/zaioraclezhongshenmeshiquoteq-qyufa.html

PRINT_TABLE示例

SQL> set serveroutput on size 1000000
SQL> set line 1000
SQL> exec print_table('select * from v$database');
DBID                          : 1865761973
NAME                          : EAS
CREATED                       : 2018-01-08 07:34:13
RESETLOGS_CHANGE#             : 11901181731
RESETLOGS_TIME                : 2021-09-22 20:27:42
PRIOR_RESETLOGS_CHANGE#       : 925702
PRIOR_RESETLOGS_TIME          : 2018-01-08 07:34:15
LOG_MODE                      : ARCHIVELOG
CHECKPOINT_CHANGE#            : 16240830676
ARCHIVE_CHANGE#               : 16237756205
CONTROLFILE_TYPE              : CURRENT
CONTROLFILE_CREATED           : 2018-01-08 07:34:13
CONTROLFILE_SEQUENCE#         : 4618967
CONTROLFILE_CHANGE#           : 16242584575
CONTROLFILE_TIME              : 2022-12-05 21:45:57
OPEN_RESETLOGS                : NOT ALLOWED
VERSION_TIME                  : 2021-09-21 16:33:02
OPEN_MODE                     : READ WRITE
PROTECTION_MODE               : MAXIMUM PERFORMANCE
PROTECTION_LEVEL              : MAXIMUM PERFORMANCE
REMOTE_ARCHIVE                : ENABLED
ACTIVATION#                   : 1984818535
SWITCHOVER#                   : 1984818535
DATABASE_ROLE                 : PRIMARY
ARCHIVELOG_CHANGE#            : 16240830676
ARCHIVELOG_COMPRESSION        : DISABLED
SWITCHOVER_STATUS             : NOT ALLOWED
DATAGUARD_BROKER              : DISABLED
GUARD_STATUS                  : NONE
SUPPLEMENTAL_LOG_DATA_MIN     : NO
SUPPLEMENTAL_LOG_DATA_PK      : NO
SUPPLEMENTAL_LOG_DATA_UI      : NO
FORCE_LOGGING                 : YES
PLATFORM_ID                   : 13
PLATFORM_NAME                 : Linux x86 64-bit
RECOVERY_TARGET_INCARNATION#  : 4
LAST_OPEN_INCARNATION#        : 4
CURRENT_SCN                   : 16242584726
FLASHBACK_ON                  : NO
SUPPLEMENTAL_LOG_DATA_FK      : NO
SUPPLEMENTAL_LOG_DATA_ALL     : NO
DB_UNIQUE_NAME                : eas
STANDBY_BECAME_PRIMARY_SCN    : 0
FS_FAILOVER_STATUS            : DISABLED
FS_FAILOVER_CURRENT_TARGET    :
FS_FAILOVER_THRESHOLD         : 0
FS_FAILOVER_OBSERVER_PRESENT  :
FS_FAILOVER_OBSERVER_HOST     :
CONTROLFILE_CONVERTED         : NO
PRIMARY_DB_UNIQUE_NAME        :
SUPPLEMENTAL_LOG_DATA_PL      : NO
MIN_REQUIRED_CAPTURE_CHANGE#  :
[Row:1]--------------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL> 
SQL> exec print_table('select * from dba_users where rownum<=2');
USERNAME                      : SYS
USER_ID                       : 0
PASSWORD                      :
ACCOUNT_STATUS                : OPEN
LOCK_DATE                     :
EXPIRY_DATE                   :
DEFAULT_TABLESPACE            : SYSTEM
TEMPORARY_TABLESPACE          : TEMP
CREATED                       : 2013-08-24 11:37:40
PROFILE                       : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP   : SYS_GROUP
EXTERNAL_NAME                 :
PASSWORD_VERSIONS             : 10G 11G
EDITIONS_ENABLED              : N
AUTHENTICATION_TYPE           : PASSWORD
[Row:1]--------------------------------------------------------------------------------------------
USERNAME                      : SYSTEM
USER_ID                       : 5
PASSWORD                      :
ACCOUNT_STATUS                : EXPIRED
LOCK_DATE                     :
EXPIRY_DATE                   : 2020-12-07 02:00:02
DEFAULT_TABLESPACE            : SYSTEM
TEMPORARY_TABLESPACE          : TEMP
CREATED                       : 2013-08-24 11:37:40
PROFILE                       : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP   : SYS_GROUP
EXTERNAL_NAME                 :
PASSWORD_VERSIONS             : 10G 11G
EDITIONS_ENABLED              : N
AUTHENTICATION_TYPE           : PASSWORD
[Row:2]--------------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL> 
SQL> exec print_table(Q'[ select * from dba_users where USERNAME='SYSTEM'  ]');
USERNAME                      : SYSTEM
USER_ID                       : 5
PASSWORD                      :
ACCOUNT_STATUS                : EXPIRED
LOCK_DATE                     :
EXPIRY_DATE                   : 2020-12-07 02:00:02
DEFAULT_TABLESPACE            : SYSTEM
TEMPORARY_TABLESPACE          : TEMP
CREATED                       : 2013-08-24 11:37:40
PROFILE                       : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP   : SYS_GROUP
EXTERNAL_NAME                 :
PASSWORD_VERSIONS             : 10G 11G
EDITIONS_ENABLED              : N
AUTHENTICATION_TYPE           : PASSWORD
[Row:1]--------------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL> 
SQL> exec print_table( 'select * from dba_users where USERNAME=''SYSTEM'' ');
USERNAME                      : SYSTEM
USER_ID                       : 5
PASSWORD                      :
ACCOUNT_STATUS                : EXPIRED
LOCK_DATE                     :
EXPIRY_DATE                   : 2020-12-07 02:00:02
DEFAULT_TABLESPACE            : SYSTEM
TEMPORARY_TABLESPACE          : TEMP
CREATED                       : 2013-08-24 11:37:40
PROFILE                       : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP   : SYS_GROUP
EXTERNAL_NAME                 :
PASSWORD_VERSIONS             : 10G 11G
EDITIONS_ENABLED              : N
AUTHENTICATION_TYPE           : PASSWORD
[Row:1]--------------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL>