--tom写的print_table
--将一个查询的结果 记录by记录 地列出来


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

------解决方案--------------------

SQL code

create or replace package pkg_employees
 as
  Type cur_employees is ref cursor;
  procedure getemployees
  (parm_rec out cur_employees);
 end pkg_employees;



 create or replace package body pkg_employees
 as
  procedure getemployees
  (parm_rec out cur_employees)
  as
  begin
  open parm_rec
  for
  select employee_id,last_name,salary
  from employees
  where salary > 10000
  order by salary desc;
  end getemployees;
 end pkg_employees;

declare
  p_cursor pkg_employees.cur_employees;
  eid EMPLOYEES.EMPLOYEE_ID%type;
  elastname EMPLOYEES.LAST_NAME%type;
  esalary EMPLOYEES.SALARY%type;
begin
   pkg_employees.getemployees(p_cursor);
  loop
    fetch p_cursor into eid,elastname,esalary;
    exit when p_cursor%notfound;
    dbms_output.put_line(eid || '|' || elastname || '|' || esalary);
  end loop;
end;