简介
在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> 