27
oracle中print_table存储过程介绍
source link: http://www.cnblogs.com/kerrycode/p/9581464.html?amp%3Butm_medium=referral
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
一直以来,觉得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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK