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;
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);
Create a type table to keep object to created above.
create type users_table_object as table of users_type;
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)
精彩评论