在sqlplus下格式化輸出查詢結果集

rongshiyuan發表於2012-06-12
SQL
在sqlplus下經常會遇到這種問題：查詢結果中包含有許多列，在一行內不能完全顯示
sqlplus就自動換行，導致輸出非常混亂，可讀性很差，Tom寫了個以列形式顯示每行
的程式，我稍稍改動了一下，增加了顯示每行行號。

該程式中使用了呼叫者許可權屬性，即呼叫者呼叫該過程檢視的表是呼叫者自己的表，而
不是過程定義者所屬的表

注意：最好把輸出快取設定得大一些，免得報錯
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/17252115/viewspace-732530/，如需轉載，請註明出處，否則將追究法律責任。

相關文章