动态光标

我对语句使用游标：

SELECT NAME FROM STUDENT WHERE ROLL = 1;

我使用过：

CURSOR C IS SELECT NAME FROM STUDENT WHERE ROLL = roll;
--roll is a variable I receive via a procedure, and the procedure works fine for the received parameter.

执行此操作后，我能够检索卷= 1的所有记录。

现在，我需要检索组的记录（可能通过游标），就像：

SELECT NAME FROM STUDENT WHERE ROLL IN (2, 4, 6);

但是 IN 子句中的值仅在运行时已知。我应该怎么做？也就是说，有什么方法可以为游标的 WHERE 子句分配参数？

我尝试在游标声明中使用数组，但弹出一个错误，告诉如下：不能使用标准类型。

我使用过：

CURSOR C IS SELECT NAME FROM STUDENT WHERE ROLL IN (rolls);
--rolls is an array initialized with the required roll numbers.

首先，我假设过程的参数实际上与STUDENT表中列的名称不匹配。如果您实际编码了您发布的语句，则roll将被解析为列的名称，而不是参数或局部变量，因此此语句将返回STUDENT表中ROLL列NOT NULL的每一行。

CURSOR C 
    IS SELECT NAME 
         FROM STUDENT 
        WHERE ROLL = roll;

其次，虽然可以按照 Soni 的建议使用动态 @Gaurav，但这样做会生成一堆不可共享的 SQL 语句。这将淹没共享池，可能会使缓存中的其他语句老化，并且每次都使用大量 CPU 硬解析语句。 Oracle 构建的前提是，您将解析一次 SQL 语句，通常使用绑定变量，然后使用绑定变量的不同值多次执行该语句。 Oracle 只能完成一次解析查询、生成查询计划、将查询放入共享池等过程，然后在再次执行查询时重用所有这些过程。如果您生成一堆永远不会再次使用的 SQL 语句，因为您使用的动态 SQL 没有绑定变量，Oracle 最终将花费大量时间来缓存永远不会再次执行的 SQL 语句，将再次使用的有用缓存语句推出共享池，这意味着您下次遇到这些查询时将不得不重新解析这些查询。

此外，您已经向SQL注入攻击敞开了大门。攻击者可利用该过程从任何表中读取任何数据或执行存储过程所有者有权访问的任何函数。这将是一个重大的安全漏洞，即使您的应用程序不是特别注重安全。

您最好使用集合。这可以防止 SQL 注入攻击，并生成单个可共享的 SQL 语句，因此您不必执行持续的硬解析。

SQL> create type empno_tbl is table of number;
  2  /
Type created.
SQL> create or replace procedure get_emps( p_empno_arr in empno_tbl )
  2  is
  3  begin
  4    for e in (select *
  5                from emp
  6               where empno in (select column_value
  7                                 from table( p_empno_arr )))
  8    loop
  9      dbms_output.put_line( e.ename );
 10    end loop;
 11  end;
 12  /
Procedure created.
SQL> set serveroutput on;
SQL> begin
  2    get_emps( empno_tbl( 7369,7499,7934 ));
  3  end;
  4  /
SMITH
ALLEN
MILLER
PL/SQL procedure successfully completed.

create or replace procedure dynamic_cur(p_empno VARCHAR2) IS
cur     sys_refcursor;
v_ename emp.ename%type;
 begin
  open cur for 'select ename from emp where empno in (' || p_empno || ')';
  loop
   fetch cur into v_ename;
   exit when cur%notfound;
    dbms_output.put_line(v_ename);
  end loop;
  close cur;
end dynamic_cur;

创建的过程

运行procedure dynamic_cur

declare
v_empno   varchar2(200) := '7499,7521,7566';
begin
  dynamic_cur(v_empno);
end;

输出

ALLEN
WARD
JONES

注意：正如XQbert所提到的，dynamic cursor会导致SQL injection，但是如果您没有处理任何关键需求，则不涉及安全性，则可以使用它。

也许您可以将滚动作为一组带引号分隔的值传递。例如"1"、"2"等如果将此值传递到 varchar 输入变量中的过程中，则可用于根据表匹配获取多行。

因此光标从"学生"中选择"姓名"（滚动）;

将被评估为从学生中选择姓名（"1"，"2"）;

希望对你有帮助

相关内容

最新更新

热门标签：