oracle中print_table存储过程实例介绍

数据库  /  管理员 发布于 6年前   403

定义

所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过
编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数
来调用并执行它，从而完成一个或一系列的数据库操作。

一直以来，觉得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       : 000000085FA35CA0SID       : 997SERIAL#      : 1AUDSID      : 0PADDR       : 000000085F6B7E70USER#       : 0USERNAME      :COMMAND      : 0OWNERID      : 2147483644TADDR       :LOCKWAIT      :STATUS      : ACTIVESERVER      : DEDICATEDSCHEMA#      : 0SCHEMANAME     : SYSOSUSER      : oraclePROCESS      : 5036MACHINE      : xxxxPORT       : 0TERMINAL      : UNKNOWNPROGRAM      : oracle@xxxxx (DBW0)TYPE       : BACKGROUNDSQL_ADDRESS     : 00SQL_HASH_VALUE    : 0SQL_ID      :SQL_CHILD_NUMBER    : 0PREV_SQL_ADDR     : 00PREV_HASH_VALUE    : 0PREV_SQL_ID     :PREV_CHILD_NUMBER    : 0PLSQL_ENTRY_OBJECT_ID   :PLSQL_ENTRY_SUBPROGRAM_ID  :PLSQL_OBJECT_ID    :PLSQL_SUBPROGRAM_ID   :MODULE      :MODULE_HASH     : 0ACTION      :ACTION_HASH     : 0CLIENT_INFO     :FIXED_TABLE_SEQUENCE   : 0ROW_WAIT_OBJ#     : -1ROW_WAIT_FILE#    : 0ROW_WAIT_BLOCK#    : 0ROW_WAIT_ROW#     : 0LOGON_TIME     : 04-jul-2018 21:15:52LAST_CALL_ET     : 5272838PDML_ENABLED     : NOFAILOVER_TYPE     : NONEFAILOVER_METHOD    : NONEFAILED_OVER     : NORESOURCE_CONSUMER_GROUP  :PDML_STATUS     : DISABLEDPDDL_STATUS     : DISABLEDPQ_STATUS      : DISABLEDCURRENT_QUEUE_DURATION  : 0CLIENT_IDENTIFIER    :BLOCKING_SESSION_STATUS  : NO HOLDERBLOCKING_INSTANCE    :BLOCKING_SESSION    :SEQ#       : 34697EVENT#      : 3EVENT       : rdbms ipc messageP1TEXT      : timeoutP1       : 300P1RAW       : 000000000000012CP2TEXT      :P2       : 0P2RAW       : 00P3TEXT      :P3       : 0P3RAW       : 00WAIT_CLASS_ID     : 2723168908WAIT_CLASS#     : 6WAIT_CLASS     : IdleWAIT_TIME      : 0SECONDS_IN_WAIT    : 107STATE       : WAITINGSERVICE_NAME     : SYS$BACKGROUNDSQL_TRACE      : DISABLEDSQL_TRACE_WAITS    : FALSESQL_TRACE_BINDS    : FALSEECID       :-----------------PL/SQL procedure successfully completed.SQL>

参考资料:

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1035431863958,%7Bprint_table%7D

总结

以上所述是小编给大家介绍的oracle中print_table存储过程介绍，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对站的支持！