SQL 立即执行引用游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6457085/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Ref cursor with Execute immediate
提问by Naveen Chakravarthy
I want to get the results in ref_cursor, but I am not able to do that.
我想在 ref_cursor 中获得结果，但我无法做到这一点。
Please suggest me how to get the results in ref_cursor using Execute immediate
请建议我如何使用立即执行在 ref_cursor 中获得结果
CREATE OR REPLACE PROCEDURE TEST_PROC_QT ( p_name IN VARCHAR2,
p_result_set OUT sys_refcursor ) IS
v_sql VARCHAR2(4000);
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;
回答by Tony Andrews
Just:
只是：
OPEN p_result_set for v_sql using p_name;
回答by carlos da silva
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)
例如，您需要创建一个类型来保留您选择的数据将返回。想象一下这是你的 users 表
user(id number, name varchar2)，你的类型应该是这样的：create type users_type as object (id number, name varchar2);创建一个类型表以保留上面创建的对象。
create type users_table_object as table of users_type;创建程序：
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)
