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