如何在oracle pl sql中使用sys_refcursor返回标头和数据

create table fetch_header_rows
(
company_name varchar2(500),
Company_id number,
ammount number(20,8),
data_commnets varchar2(500)
);

insert into fetch_header_rows values('company1' , 1, 128.80,'test data1');

insert into fetch_header_rows values('company2' , 2, 129.80,'test data1');

insert into fetch_header_rows values('company3' , 3, 128,'test data1');

insert into fetch_header_rows values('company4' , 4, 100.80,'test data1');


create or replace procedure SP_fetch_header_rows(data_fetch  out sys_refcursor )
as

begin

open data_fetch for 
select * from fetch_header_rows;

end;
复制

declare
  cur        sys_refcursor;
  cur_handle number;
  rec_tab    dbms_sql.desc_tab;
  col_cnt    integer;
begin
  SP_fetch_header_rows ( cur );
  cur_handle := dbms_sql.to_cursor_number ( cur ) ;
  
  dbms_sql.describe_columns ( cur_handle, col_cnt, rec_tab );
  
  for i in 1 .. rec_tab.count loop
    dbms_output.put_line ( rec_tab(i).col_name );
  end loop;
  
  dbms_sql.close_cursor ( cur_handle );
end;
/

COMPANY_NAME
COMPANY_ID
AMMOUNT
DATA_COMMNETS
复制