oracle 为什么我们不能在动态 SQL 语句中使用强引用游标？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2783461/
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
Why can't we use strong ref cursor with dynamic SQL Statement?
提问by Vineet
I am trying to use a strong ref cur with dynamic sql statment but it is giving out an error,but when i use weak cursor it works,Please explain what is the reason and please forward me any link of oracle server architect containing matter about how compilation and parsing is done in Oracle server. THIS is the error along with code.
我正在尝试使用带有动态 sql 语句的强 ref cur，但它给出了一个错误，但是当我使用弱游标时，它可以工作，请解释原因，并请向我转发 oracle 服务器架构师的任何链接，其中包含有关如何操作的问题编译和解析在 Oracle 服务器中完成。这是错误和代码。
ERROR at line 6:
ORA-06550: line 6, column 7:
PLS-00455: cursor 'EMP_REF_CUR' cannot be used in dynamic SQL OPEN statement
ORA-06550: line 6, column 2:
PL/SQL: Statement ignored
declare
type ref_cur_type IS REF CURSOR RETURN employees%ROWTYPE; --Creating a strong REF cursor,employees is a table
emp_ref_cur ref_cur_type;
emp_rec employees%ROWTYPE;
BEGIN
OPEN emp_ref_cur FOR 'SELECT * FROM employees';
LOOP
FETCH emp_ref_cur INTO emp_rec;
EXIT WHEN emp_ref_cur%NOTFOUND;
END lOOP;
END;
回答by APC
Here is a procedure with a strongly-typed ref cursor:
这是一个带有强类型引用游标的过程：
SQL> create or replace procedure p1 is
2 type dept_rc is ref cursor return dept%rowtype;
3 my_ref_cursor dept_rc;
4 begin
5 open my_ref_cursor for
6 select * from dept;
7 end;
8 /
Procedure created.
SQL>
This next statement fails because the signature of the EMP record doesn't match that of DEPT table.
下一条语句失败，因为 EMP 记录的签名与 DEPT 表的签名不匹配。
SQL> create or replace procedure p1 is
2 type dept_rc is ref cursor return dept%rowtype;
3 my_ref_cursor dept_rc;
4 begin
5 open my_ref_cursor for
6 select * from emp;
7 end;
8 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: SQL Statement ignored
6/9 PLS-00382: expression is of wrong type
SQL>
But if we change the projection to match the DEPT table then we have success again:
但是，如果我们更改投影以匹配 DEPT 表，那么我们又成功了：
SQL> create or replace procedure p1 is
2 type dept_rc is ref cursor return dept%rowtype;
3 my_ref_cursor dept_rc;
4 begin
5 open my_ref_cursor for
6 select deptno, ename, job from emp;
7 end;
8 /
Procedure created.
SQL>
So, why can't we use a strongly-typed ref-cursor with dynamic SQL?
那么，为什么我们不能在动态 SQL 中使用强类型引用游标呢？
SQL> create or replace procedure p1 is
2 type dept_rc is ref cursor return dept%rowtype;
3 my_ref_cursor dept_rc;
4 begin
5 open my_ref_cursor for
6 'select * from dept';
7 end;
8 /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: Statement ignored
5/10 PLS-00455: cursor 'MY_REF_CURSOR' cannot be used in dynamic SQL
OPEN statement
SQL>
Because the compiler cannot parse the string in the dynamic SQL statement. So it cannot assert that the columns in the query's projection match in number and datatype the signature of the ref cursor. Consequently it cannot validate the contract between the ref cursor variable and the query. It is even easier to understand why this cannot be allowed when we consider that the dynamic SQL statement could be assembled from a query on USER_TAB_COLUMNS.
因为编译器无法解析动态 SQL 语句中的字符串。因此它不能断言查询投影中的列在数量和数据类型上与引用游标的签名相匹配。因此，它无法验证引用游标变量和查询之间的契约。当我们考虑到可以从 USER_TAB_COLUMNS 上的查询组装动态 SQL 语句时，更容易理解为什么不允许这样做。
回答by user2001990
Another possibility is to declare and define a Record Type object to be a container for your query results. This could be useful if the query is a JOIN query, returning columns from several joined tables.
另一种可能性是声明和定义一个记录类型对象作为查询结果的容器。如果查询是 JOIN 查询，从多个连接表返回列，这可能很有用。
SQL> create or replace procedure p1 is
/* Declare you destination data structure row container */
TYPE TestRecTyp IS RECORD (
deptno varchar(50),
ename varchar(50),
job varchar(50)
);
/* Define an instance of the record type */
testrec TestRecTyp;
type dept_rc is ref cursor; /*return dept%rowtype;*/
my_ref_cursor dept_rc;
begin
open my_ref_cursor for 'select deptno,ename,job from emp';
LOOP
FETCH my_ref_cursor INTO testrec;
EXIT WHEN my_ref_cursor%NOTFOUND;
/* Do some operations with testrec*/
END LOOP;
end;
NOTE: You could use the above technique on a dynamically constructed SQL query statement by substituting 'select deptno,ename,job from emp' with a variable such as v_sql and update this variable with the SQL statement within the body of the procedure.
注意：您可以在动态构造的 SQL 查询语句上使用上述技术，方法是将“select deptno,ename,job from emp”替换为诸如 v_sql 之类的变量，并使用过程主体内的 SQL 语句更新此变量。
回答by Ramakant Mishra
You are not allowed to open a strongly typed ref cursor for a dynamic sql, as the oracle engine cannot check whether the structure of the dynamic sql is matching with that of the ref cursor's return type.
不允许为动态 sql 打开强类型 ref 游标，因为 oracle 引擎无法检查动态 sql 的结构是否与 ref 游标的返回类型匹配。
