为什么我们不能在动态 SQL 语句中使用强引用游标? [英] Why can't we use strong ref cursor with dynamic SQL Statement?

问题描述

我正在尝试使用带有动态 sql 语句的强 ref cur 但它给出了一个错误，但是当我使用弱游标时它可以工作，请解释是什么原因，请向我转发 oracle 服务器架构师的任何链接，其中包含有关如何在 Oracle 服务器中完成编译和解析的问题.这是错误和代码.

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.

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;

推荐答案

这是一个带有强类型引用游标的过程:

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>

下一条语句失败，因为 EMP 记录的签名与 DEPT 表的签名不匹配.

This next statement fails because the signature of the EMP record doesn't match that of DEPT table.

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>

但是如果我们更改投影以匹配 DEPT 表，那么我们又成功了:

But if we change the projection to match the DEPT table then we have success again:

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>

那么，为什么我们不能在动态 SQL 中使用强类型引用游标呢?

So, why can't we use a strongly-typed ref-cursor with dynamic 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>

因为编译器无法解析动态SQL语句中的字符串.因此它不能断言查询投影中的列在数量和数据类型上与引用游标的签名相匹配.因此，它无法验证引用游标变量和查询之间的契约.当我们考虑到可以从 USER_TAB_COLUMNS 上的查询组装动态 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 语句中使用强引用游标?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！