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

问题描述

我正在尝试使用带有动态sql语句的强引用库，但它给出了一个错误，但是当我使用弱游标时，它可以工作，请解释原因，请 转发给我任何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中使用强类型的ref-cursor?

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语句中的字符串.因此，它不能断言查询的投影中的列在数量和数据类型上都与引用游标的签名匹配.因此，它无法验证ref游标变量和查询之间的约定.当我们认为可以通过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屋！