oracle print_table procedure

sqlplus类似mysql \G的用法。

1、cat print_table.sql
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;
/

2、实例

set serveroutput on size 99999

exec print_table ('select * from V$database');

相关日志