plsql中的存储过程execute immediate相关问题

Question

create or replace procedure SP_GRN_PROCESS
(kssj in date,
jssj in date,
typename in varchar2,
result_process out sys_refcursor
) is
v_sql varchar2(2000);
begin

v_sql :='select a.doc_subject,
       a.fd_number,
       b.fd_fact_node_name,
       to_char(b.fd_start_date,''yyyy-mm-dd hh24:mi:ss'') as fd_start_date,
       to_char(b.fd_finish_date,''yyyy-mm-dd hh24:mi:ss'') as fd_finish_date,
       b.fd_target_name
from km_review_main  a,lbpm_history_node  b,km_review_template  c
where a.fd_id = b.fd_process_id 
and a.fd_template_id = c.fd_id
and (to_char(b.fd_start_date,''yyyy-mm-dd'') = to_char( '''||kssj||''',''yyyy-mm-dd'') or '''||kssj||''' is null) 
and (to_char(b.fd_finish_date,''yyyy-mm-dd'') = to_char( '''||jssj||''',''yyyy-mm-dd'') or '''||jssj||''' is null) 
and'|| (' (c.fd_name LIKE ''%' || replace(typename,',','%'' OR c.fd_name LIKE ''%')  ||  '%'')')||
' order by a.doc_subject,fd_start_date asc';

open result_process for  execute immediate v_sql;

end SP_GRN_PROCESS;
这个 execute immediate v_sql执行的是没有问题，我debug有看了一下v_sql中的值，并拿出来执行，没问题。但我想要这个存储过程的结果集，所以加了游标，现在不知道要怎么把execute immediate v_sql放到游标里，或者有什么方法可以看到execute immediate v_sql执行后的结果集？求大神帮忙

Answer 1

可参考一下我的范例
先建立PROCEDURE XXb

CREATE OR REPLACE PROCEDURE XXb( p_rc OUT SYS_REFCURSOR )
AS
BEGIN
  OPEN p_rc 
   FOR select id,eq from xx2
where wpsid = 'PR12100';
END;

前台 call

DECLARE
  l_cursor SYS_REFCURSOR;
  l_id  varchar(100);
  l_seq varchar(100);
begin
  XXb( l_cursor );
LOOP 
    FETCH l_cursor
    INTO  l_id, l_seq;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_id || ' | ' || l_seq );
  END LOOP;
  CLOSE l_cursor;
end;
/

在我的计算机测试OK
你试一下