schema的常用脚本:print
添加时间: 2008-4-24 20:16:20 作者: Oracle指导 阅读次数:7 来源: http://www.d9soft.com
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
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( '-----------------' ); --注意如果输出的行比较多的话,要加大dbms_output.enable(值)
end loop;
exception
when others then
dbms_sql.close_cursor( l_theCursor );
RAISE;
end;
/
grant execute on print_table to public;
--该脚本是实现横向改成纵向显示
例如:
一行记录显示如下:
ADMIN_MEMBER_ID : dealexpress
VIEW_NAME : Deal Express
BUSINESS_TYPE : 5
FIRST_NAME : Tim
LAST_NAME : Horton
JOB_TITLE :
PROVINCE : Wisconsin
COUNTRY : US
PHONE_COUNTRY : 1920
PHONE_AREA : 623
PHONE_NUMBER : 9528
FAX_COUNTRY : 1920
FAX_AREA : 623
FAX_NUMBER : 9528
MOBILE_NO :
ZIP : 53925
ADDRESS : 215 Robbins Rd
CITY : Columbus
上一篇文章: CCNA中文笔记-Wide Area Networking Protocols 下一篇文章: OracleERP系统北京康宁光缆应用案例