ITPub博客

首页 > 数据库 > 数据库开发技术 > 在sqlplus下格式化输出查询结果集

在sqlplus下格式化输出查询结果集

原创 数据库开发技术 作者:yaanzy 时间:2005-03-30 10:12:39 0 删除 编辑
在sqlplus下经常会遇到这种问题:查询结果中包含有许多列,在一行内不能完全显示
sqlplus就自动换行,导致输出非常混乱,可读性很差,Tom写了个以列形式显示每行
的程序,我稍稍改动了一下,增加了显示每行行号。[@more@]

该程序中使用了调用者权限属性,即调用者调用该过程查看的表是调用者自己的表,而
不是过程定义者所属的表

注意:最好把输出缓存设置得大一些,免得报错
     ORA-20000: ORU-10027: buffer overflow,limit of 2000 bytes

set serveroutput on size 10000000

CREATE OR REPLACE PROCEDURE print_table( p_query in varchar2 )
AUTHID CURRENT_USER
/*    Author : Tom Kyte
      Last update: yaanzy
*/
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;
    l_rowCnt        number:=1;
BEGIN
    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( '[Row:'||l_rowCnt||']---------------------------------------------------' );
        l_rowCnt := l_rowCnt + 1;
    end loop;
   
EXCEPTION
    when others then
        dbms_sql.close_cursor( l_theCursor );
        RAISE;
       
END print_table;


下面是一个实际输出的例子:

SQL> exec print_table('select * from user_objects where rownum <=6');
OBJECT_NAME                   : AAA
SUBOBJECT_NAME                :
OBJECT_ID                     : 36130
DATA_OBJECT_ID                : 36130
OBJECT_TYPE                   : TABLE
CREATED                       : 2005-03-30 09:23:27
LAST_DDL_TIME                 : 2005-03-30 09:23:27
TIMESTAMP                     : 2005-03-30:09:23:27
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
[Row:1]---------------------------------------------------
OBJECT_NAME                   : CP
SUBOBJECT_NAME                :
OBJECT_ID                     : 35821
DATA_OBJECT_ID                : 35821
OBJECT_TYPE                   : TABLE
CREATED                       : 2005-03-09 17:30:45
LAST_DDL_TIME                 : 2005-03-09 17:30:45
TIMESTAMP                     : 2005-03-09:17:30:45
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
[Row:2]---------------------------------------------------
OBJECT_NAME                   : INDEX_ORDER_USER_3
SUBOBJECT_NAME                :
OBJECT_ID                     : 35636
DATA_OBJECT_ID                : 35636
OBJECT_TYPE                   : INDEX
CREATED                       : 2005-02-23 12:06:43
LAST_DDL_TIME                 : 2005-02-23 12:06:43
TIMESTAMP                     : 2005-02-23:12:06:43
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
[Row:3]---------------------------------------------------
OBJECT_NAME                   : INDEX_ORDER_USER_5
SUBOBJECT_NAME                :
OBJECT_ID                     : 35637
DATA_OBJECT_ID                : 35637
OBJECT_TYPE                   : INDEX
CREATED                       : 2005-02-23 12:06:50
LAST_DDL_TIME                 : 2005-02-23 12:06:50
TIMESTAMP                     : 2005-02-23:12:06:50
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
[Row:4]---------------------------------------------------
OBJECT_NAME                   : IND_MPN_SEG
SUBOBJECT_NAME                :
OBJECT_ID                     : 35960
DATA_OBJECT_ID                : 35960
OBJECT_TYPE                   : INDEX
CREATED                       : 2005-03-14 11:17:50
LAST_DDL_TIME                 : 2005-03-14 11:17:50
TIMESTAMP                     : 2005-03-14:11:17:50
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
[Row:5]---------------------------------------------------
OBJECT_NAME                   : IND_SEND_DONE_LOG_20050216
SUBOBJECT_NAME                :
OBJECT_ID                     : 35483
DATA_OBJECT_ID                : 35483
OBJECT_TYPE                   : INDEX
CREATED                       : 2005-02-17 09:41:03
LAST_DDL_TIME                 : 2005-02-17 09:41:03
TIMESTAMP                     : 2005-02-17:09:41:03
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
[Row:6]---------------------------------------------------

PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/3898/viewspace-794395/,如需转载,请注明出处,否则将追究法律责任。

下一篇: rman技术速查手册
请登录后发表评论 登录
全部评论
  • 博文量
    108
  • 访问量
    759589