在Oracle中行转列函数PRINT_TABLE的用法
Tags： OraclePRINT_TABLE行转列
👉 本文共约250个字，系统预计阅读时间或需1分钟。
简介
在sqlplus，如果列比较多，往往会显示不够清晰，这时如果能把查询语句行转列就明了多了，可以用print_table存储过程实现。
存储过程PRINT_TABLE源码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 | 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用法
1 2 3 4 5 6 7 8 | 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'' '); |
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
含单引号时，可以使用Q语法，或者多一层引号也可以。
有关Oracle的Q语法请参考：https://www.xmmup.com/zaioraclezhongshenmeshiquoteq-qyufa.html
PRINT_TABLE示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 | 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> |
