开发者

Ref cursor with Execute immediate

I want to get the results in ref_cursor, but I am not able to do that.

Please suggest me how to get the results in ref_cursor using Execute immediate

CREATE OR REPLACE PROCEDURE TEST_PROC_QT ( p_name IN VARCHAR2,
                                           p_result_set OUT sys_refcursor ) IS

  v_sql VARCHAR2(4000);

开发者_StackOverflow社区BEGIN

   v_sql := '';
   v_sql := 'SELECT * FROM USERS WHERE 1=1 ';

   IF p_name is not null THEN
     v_sql := v_sql || ' AND login_id = :v_name';
   ELSE
     v_sql := v_sql || ' AND ((1=1) or :v_name is null)';
   END IF;

   Dbms_output.put_line(v_sql);

   EXECUTE IMMEDIATE v_sql

   --OPEN p_result_set for v_sql
   --INTO p_result_set using p_name;  

END;


Just:

OPEN p_result_set for v_sql using p_name;  


  1. You need to create a type to keep the data you select is going to return,for example. Imagine this is yours users table user(id number, name varchar2), your type should look like:

    create type users_type as object (id number, name varchar2);
    
  2. Create a type table to keep object to created above.

    create type users_table_object as table of users_type;
    
  3. create the procedure:

    create the procedure test(
    cv_1  OUT SYS_REFCURSOR
    ....
    )
    as
    users_table_object;
    v_query VARCHAR2(2000);  
    
    begin
    v_query := N'select users_type(id, name) from users ';
    
    execute immediate
    bulk collect into users_table_object;
    
    open cv_1 for table(users_table_object)     
    
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜