将参数绑定到Oracle Dynamic SQL

问题描述

我有一个存储过程，可以接受多个参数(即pName，pHeight，pTeam)

I have a stored procedure that accepts multiple parameters (i.e. pName, pHeight, pTeam)

我已经建立了这样的查询:

I have the query built up like this:

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;

如果我执行传递所有参数的过程，则它将正常运行.

If I execute the procedure passing all parameters, it runs properly.

但是，如果我仅传递了一个或两个参数，那么该过程将出错:

But if I only passed one or two of the parameters, then the procedure errors out:

ORA-01006: bind variable does not exist

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

How do I selectively bind the variable with the parameters based on where the parameter value was used? For example, if only pName was passed, then I would only execute the query:

OPEN TestCursor FOR SQLQuery USING pName;

或者如果同时传递了pName和pTeam，则:

Or if both pName and pTeam was passed, then:

OPEN TestCursor FOR SQLQuery USING pName, pTeam;

希望有人可以提出更多解决方案.谢谢.

Hope someone can shed more ways to resolve this. Thanks.

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

I could actually use the following:

-根据传递的参数构建查询. 如果pName不为空 SQLQuery:= SQLQuery || '和名称类似''|| pName || ''''; END IF;

-- 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;

但这很容易受到SQL注入的攻击...

But this would be VERY vulnerable to SQL Injection...

推荐答案

这不是很优雅，但这意味着您始终可以提供所有三个绑定变量，即使其中一些为空.仅在需要时添加额外的WHERE子句.

This is not hugely elegant but it would mean that you could always supply all three bind variables even if some of them are null. You only add the extra WHERE clauses if needed.

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

(I've tried to format the dynamic SQL to make it more readable, you could just supply it as one long string).

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;

我不在具有数据库访问权限的工作站前，因此我无法测试该功能，但应该关闭该功能(请原谅任何语法错误，这已经很长了！)

I'm not in front of a workstation with DB access so I can't test the function but it should be close (please forgive any syntax errors, it's been a long day!)

希望有帮助...

这篇关于将参数绑定到Oracle Dynamic SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！