oracle中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 
 EXECUTE IMMEDIATE 'alter session set nls_date_format=''dd-mon-yyyy hh34: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; 
/