默认的Select 语句的显示结果是把表中的每一条记录显示成一行,如果表的列数很多会带来一个问题(求其是在SQL*PLUS中),那就是要么列名显示不全,或者会折成很多行来显示,造成阅读上的不便。 如果我们只是很关心这种多列表中的特定一行或几行数据,如果可以把显示结果“转置”过来,每一行显示一个列的数据,这样会大大有助于阅读,分析数据。
多说无益,还是上例子说明。 如下,
默认的显示
SQL> select * from t where rownum = 1;
N1 N2 N3 N4 N5
---------- ---------- ---------- ---------- ----------
10 20 30 200 30
====》
转换后的显示
N1 : 10
N2 : 20
N3 : 30
N4 : 200
N5 : 30
-----------------
Tom 很早就给出了这样一个解决方法,参见这里
具体代码如下:
1: create or replace procedure print_table( p_query in varchar2 )
2: AUTHID CURRENT_USER
3: is
4: l_theCursor integer default dbms_sql.open_cursor;
5: l_columnValue varchar2(4000);
6: l_status integer;
7: l_descTbl dbms_sql.desc_tab;
8: l_colCnt number;
9: begin
10: execute immediate
11: 'alter session set
12: nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
13:
14: dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
15: dbms_sql.describe_columns
16: ( l_theCursor, l_colCnt, l_descTbl );
17:
18: for i in 1 .. l_colCnt loop
19: dbms_sql.define_column
20: (l_theCursor, i, l_columnValue, 4000);
21: end loop;
22:
23: l_status := dbms_sql.execute(l_theCursor);
24:
25: while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
26: for i in 1 .. l_colCnt loop
27: dbms_sql.column_value
28: ( l_theCursor, i, l_columnValue );
29: dbms_output.put_line
30: ( rpad( l_descTbl(i).col_name, 30 )
31: || ': ' ||
32: l_columnValue );
33: end loop;
34: dbms_output.put_line( '-----------------' );
35: end loop;
36: execute immediate
37: 'alter session set nls_date_format=''dd-MON-rr'' ';
38: exception
39: when others then
40: execute immediate
41: 'alter session set nls_date_format=''dd-MON-rr'' ';
42: raise;
43: end;
44: /
45:
用法很简单,
SQL> set serveroutput on
SQL> exec print_table('select * from t where rownum <= 2');
N1 : 10
N2 : 20
N3 : 30
N4 : 200
N5 : 30
-----------------
N1 : 10
N2 : 1
N3 : 11
N4 : 10
N5 : 11
-----------------
PL/SQL procedure successfully completed.
SQL>