如何将动态光标添加到plsql函数中

Question

我想在plsql函数中添加一个动态光标。其中，clause_是函数的in参数。可能会有所不同。下面是我的代码。

FUNCTION Filter_Data_With_Security(
   where_clause_  IN VARCHAR2) RETURN VARCHAR2
IS
   CURSOR check_sequrity IS 
      SELECT 1
      FROM tab b
      WHERE where_clause_
AND b.col1 = 'C2';    
BEGIN
   OPEN check_sequrity;
   FETCH check_sequrity INTO temp_;
   IF (check_sequrity%FOUND) THEN
      CLOSE year_exists;
      exist_ := 'TRUE';
   ELSE
      CLOSE check_sequrity;
      exist_ := 'FALSE';
   END IF;
   RETURN exist_;
END Filter_Data_With_Security;

但这给了我一个错误，如下

PL/SQL:ORA-00920：第9109行出现无效关系运算符错误

请帮我解决这个

Answer 1

参见示例7.4此处的

FUNCTION Filter_Data_With_Security (where_clause_ IN VARCHAR2)
   RETURN VARCHAR2
IS
   TYPE EmpCurTyp IS REF CURSOR;
   check_sequrity   EmpCurTyp;
   v_stmt_str       VARCHAR2 (200);
   temp_            NUMBER;
   exists_          VARCHAR2 (20);
BEGIN
   -- Dynamic SQL statement with placeholder:
   v_stmt_str := 'SELECT 1 FROM tab b WHERE ' || where_clause_ || 'AND b.col1 = ''C2''';
   OPEN check_sequrity FOR v_stmt_str;
   FETCH check_sequrity INTO temp_;
   IF (check_sequrity%FOUND) THEN
      CLOSE year_exists;
      exist_ := 'TRUE';
   ELSE
      CLOSE check_sequrity;
      exist_ := 'FALSE';
   END IF;
   RETURN exist_;
END Filter_Data_With_Security;

动态SQL不是很安全；）。您应该检查可能的SQL注入。

Answer 2

只是一个想法。您也可以尝试此选项。基本上，您需要检查是否有从基本输入返回的记录。我认为我们不需要为此循环。希望下面的片段能有所帮助。

CREATE OR REPLACE FUNCTION Filter_Data_With_Security(
    where_clause_ IN VARCHAR2)
  RETURN VARCHAR2
IS
  lv_num DBMS_SQL.NUMBER_TABLE;
  exist_ VARCHAR2(100);
BEGIN
  lv_sql:='SELECT  1 FROM tab b WHERE '||where_clause_||' AND b.col1 = ''C2''';
  EXECUTE IMMEDIATE lv_sql BULK COLLECT INTO lv_num;
  IF lv_num.EXISTS(1) THEN
    exist_ := 'TRUE';
  ELSE
    exist_ := 'FALSE';
  END IF;
  RETURN exist_;
END Filter_Data_With_Security;