oracle - 将参数绑定(bind)到 Oracle 动态 SQL

Question

我有一个接受多个参数的存储过程(即 pName、pHeight、pTeam)

我有这样的查询:

SQLQuery VARCHAR2(6000);
TestCursor T_CURSOR;

SQLQuery := 'SELECT ID, Name, Height, Team FROM MyTable WHERE ID IS NOT NULL ';


-- Build the query based on the parameters passed.
IF pName IS NOT NULL
  SQLQuery := SQLQuery || 'AND Name LIKE :pName ';
END IF;

IF pHeight IS > 0
  SQLQuery := SQLQuery || 'AND Height = :pHeight ';
END IF;

IF pTeam IS NOT NULL
  SQLQuery := SQLQuery || 'AND Team LIKE :pTeam ';
END IF;


OPEN TestCursor FOR SQLQuery USING pName, pHeight, pTeam;

如果我执行传递所有参数的过程，它会正常运行。

但是如果我只传递了一个或两个参数，那么程序就会出错:

ORA-01006: bind variable does not exist

如何根据使用参数值的位置有选择地将变量与参数绑定(bind)？例如，如果只传递了 pName，那么我将只执行查询:

OPEN TestCursor FOR SQLQuery USING pName;

或者如果 pName 和 pTeam 都通过了，那么:

OPEN TestCursor FOR SQLQuery USING pName, pTeam;

希望有人可以提出更多解决此问题的方法。谢谢。

编辑:
我实际上可以使用以下内容:

-- 根据传递的参数构建查询。
如果 pName 不为空
SQLQuery := SQLQuery || '和名字一样''' ||姓名 || '''';
万一;

IF pHeight IS > 0
  SQLQuery := SQLQuery || 'AND Height = pHeight ';
END IF;

IF pTeam IS NOT NULL
  SQLQuery := SQLQuery || 'AND Team LIKE ''' || pTeam || ''' ';
END IF;


OPEN TestCursor FOR SQLQuery;

但这很容易受到 SQL 注入(inject)的影响......

Answer 1

这不是非常优雅，但这意味着您始终可以提供所有三个绑定(bind)变量，即使其中一些为空。您只需添加额外的 WHERE如果需要的话。

(我尝试格式化动态 SQL 以使其更具可读性，您可以将其作为一个长字符串提供)。

FUNCTION myFunc (
   pName   IN VARCHAR2,
   pHeight IN VARCHAR2,
   pTeam   IN VARCHAR2
)
   RETURN T_CURSOR
IS
   -- Local Variables
   SQLQuery   VARCHAR2(6000);
   TestCursor T_CURSOR;
BEGIN
   -- Build SQL query
   SQLQuery := 'WITH t_binds '||
                ' AS (SELECT :v_name AS bv_name, '||
                           ' :v_height AS bv_height, '||
                           ' :v_team AS bv_team '||
                      ' FROM dual) '||
               ' SELECT id, '||
                      ' name, '||
                      ' height, '||
                      ' team '||
                 ' FROM MyTable, '||
                      ' t_binds '||
                ' WHERE id IS NOT NULL';

   -- Build the query WHERE clause based on the parameters passed.
   IF pName IS NOT NULL
   THEN
     SQLQuery := SQLQuery || ' AND Name LIKE bv_name ';
   END IF;

   IF pHeight > 0
   THEN
     SQLQuery := SQLQuery || ' AND Height = bv_height ';
   END IF;

   IF pTeam IS NOT NULL
   THEN
     SQLQuery := SQLQuery || ' AND Team LIKE bv_team ';
   END IF;

   OPEN TestCursor 
    FOR SQLQuery 
  USING pName, 
        pHeight, 
        pTeam;

   -- Return the cursor
   RETURN TestCursor;
END myFunc;

我不在具有数据库访问权限的工作站前，所以我无法测试该功能，但它应该很接近(请原谅任何语法错误，这是漫长的一天!)

希望能帮助到你...