• 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

  • 相关阅读:
    faster with MyISAM tables than with InnoDB or NDB tables
    w-BIG TABLE 1-toSMALLtable @-toMEMORY
    Indexing and Hashing
    MEMORY Storage Engine MEMORY Tables TEMPORARY TABLE max_heap_table_size
    controlling the variance of request response times and not just worrying about maximizing queries per second
    Variance
    Population Mean
    12.162s 1805.867s
    situations where MyISAM will be faster than InnoDB
    1920.154s 0.309s 30817
  • 原文地址:https://www.cnblogs.com/kerrycode/p/9581464.html
Copyright © 2020-2023  润新知