• oracle中print_table存储过程介绍


    一直以来,觉得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

  • 相关阅读:
    超酷的元素周期表
    TestLink在线Excel用例转换xml
    我也学习JAVA多线程-join
    request.getSession(true/false)的区别
    nginx location配置详细解释
    RestTemplate--解决中文乱码
    扇贝-每日一句
    Hexo博客系列(三)-将Hexo v3.x个人博客发布到GitLab Pages
    C程序的内存分区(节选自黑马训练营day1)
    CodeBlocks更换界面主题界面、汉化及去掉注释及字符串的下划线(汉化包的链接来自本站的BeatificDevin大神)
  • 原文地址:https://www.cnblogs.com/kerrycode/p/9581464.html
Copyright © 2020-2023  润新知