Chinaunix首页 | 论坛 | 博客
  • 博客访问: 529012
  • 博文数量: 134
  • 博客积分: 7990
  • 博客等级: 少将
  • 技术积分: 1290
  • 用 户 组: 普通用户
  • 注册时间: 2007-10-29 11:43
文章分类

全部博文(134)

文章存档

2009年(7)

2008年(80)

2007年(47)

我的朋友

分类: Oracle

2008-01-17 14:35:15

 
 
 

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;
/

For example, here is a plsql snippet i have for 8.0.  It lets me execute something like:


scott@8.0> @printtbl8 'select * from emp where ename = "KING" '
EMPNO                         : 7839
ENAME                         : KING
JOB                           : PRESIDENT
MGR                           :
HIREDATE                      : 17-nov-2081 00:00:00
SAL                           : 5000
COMM                          :
DEPTNO                        : 10
-----------------

PL/SQL procedure successfully completed.

阅读(1738) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~