在Oracle中行转列函数PRINT_TABLE的用法
Tags： OraclePRINT_TABLE行转列
👉 本文共约246个字，系统预计阅读时间或需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(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
