PL/SQL中返回记录集的一个例子

CREATE   OR   REPLACE  package my_pack  as
TYPE p_cursor  IS  REF  CURSOR ;
end ;


CREATE   OR   REPLACE   procedure  server_check_report_pro(p_cursor out my_pack.p_cursor)
is
vlevel  number ;
vre  varchar2 ( 3000 );
cursor  c1  is
select   *   from  server_dbcheck_list;
begin
delete   from  server_check_report;
for  c  in  c1
loop
server_getlogbyip(c.ip,vre,vlevel);
insert   into  server_check_report (ip,check_item,check_order,error_level,detail) values
(c.ip, ' OS DB Log Check ' , 0 ,vlevel,substr(vre, 1 , 2999 ));
end  loop;
insert   into  server_check_report(ip,check_item,check_order,error_level,detail)
select  a.ip, ' Disk Spaces Check ' , 1 ,nvl(b.error_level, 0 ),nvl(b.remark, '' )
from  server_dbcheck_list a,server_logcheck_logs b
where  a.ip = b.ip( + )
and  error_level( + ) > 0
and  id( + ) = 4 ;
insert   into  server_check_report (ip,check_item,check_order,error_level,detail)
select  a.ip,a.tns_name || ' : ' || b.LOG_INFO check_item, 2 ,nvl(b.error_level, 0 ) error_level,decode(nvl(b.error_level, 0 ), 0 , '' , replace ( replace (b.value_list, ' #### ' , ' , ' ), ' " ' , '' )) remark
from  server_dbcheck_list a,server_dbcheck_logs b
where  a.ip = b.ip( + )
and  a.tns_name = b.tns_name( + )
and  error_level( + ) > 0
and  b.id( + ) = 1 ;
commit ;
open  p_cursor  for   ' select * from server_check_report order by ip,check_order ' ;
end ;
/

 如何取得紀錄集呢,打開SQLProgress,登陸進去,點菜單"advance"-->exec func/prod ...在彈出的框中輸入procedure的名字,和參數,直接點OK就可以看到結果.具體操作可以看看SQLProgress的幫助.