将参数绑定到 Oracle 动态 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11003733/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Binding Parameters to Oracle Dynamic SQL
提问by Batuta
I have a stored procedure that accepts multiple parameters (i.e. pName, pHeight, pTeam)
我有一个接受多个参数的存储过程（即 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
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:
如何根据使用参数值的位置有选择地将变量与参数绑定？例如，如果只传递了 pName，那么我只会执行查询：
OPEN TestCursor FOR SQLQuery USING pName;
Or if both pName and pTeam was passed, then:
或者，如果 pName 和 pTeam 都通过了，则：
OPEN TestCursor FOR SQLQuery USING pName, pTeam;
Hope someone can shed more ways to resolve this. Thanks.
希望有人可以摆脱更多方法来解决这个问题。谢谢。
Edit: I could actually use the following:
编辑：我实际上可以使用以下内容：
-- Build the query based on the parameters passed. IF pName IS NOT NULL SQLQuery := SQLQuery || 'AND Name LIKE ''' || pName || ''' '; END IF;
-- 根据传递的参数构建查询。如果 pName 不是 NULL SQLQuery := SQLQuery || 'AND 名字 LIKE ''' || 名称 || ''' '; 万一;
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;
But this would be VERY vulnerable to SQL Injection...
但这很容易受到 SQL 注入的影响......
采纳答案by Ollie
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 WHEREclauses if needed.
这不是非常优雅，但这意味着您始终可以提供所有三个绑定变量，即使其中一些为空。WHERE如果需要，您只需添加额外的子句。
(I've tried to format the dynamic SQL to make it more readable, you could just supply it as one long string).
（我尝试格式化动态 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;
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!)
我不在具有数据库访问权限的工作站前，所以我无法测试该功能，但它应该很接近（请原谅任何语法错误，这是漫长的一天！）
Hope it helps...
希望能帮助到你...
回答by Luke Woodward
You can use the DBMS_SQLpackage. This provides an alternative way to run dynamic SQL. It is perhaps a little more cumbersome to use, but it can be more flexible, especially with varying numbers of bind parameters.
您可以使用DBMS_SQL包。这提供了一种运行动态 SQL 的替代方法。使用起来可能有点麻烦，但它可以更灵活，尤其是在绑定参数数量不同的情况下。
Here's how you could use it (warning: I haven't tested this):
这是您可以使用它的方法（警告：我还没有测试过）：
FUNCTION player_search (
pName IN VARCHAR2,
pHeight IN NUMBER,
pTeam IN VARCHAR2
) RETURN SYS_REFCURSOR
IS
cursor_name INTEGER;
ignore INTEGER;
id_var MyTable.ID%TYPE;
name_var MyTable.Name%TYPE;
height_var MyTable.Height%TYPE;
team_var MyTable.Team%TYPE;
BEGIN
-- Put together SQLQuery here...
-- Open the cursor and parse the query
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, SQLQuery, DBMS_SQL.NATIVE);
-- Define the columns that the query returns.
-- (The last number for columns 2 and 4 is the size of the
-- VARCHAR2 columns. Feel free to change them.)
DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, id_var);
DBMS_SQL.DEFINE_COLUMN(cursor_name, 2, name_var, 30);
DBMS_SQL.DEFINE_COLUMN(cursor_name, 3, height_var);
DBMS_SQL.DEFINE_COLUMN(cursor_name, 4, team_var, 30);
-- Add bind variables depending on whether they were added to
-- the query.
IF pName IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(cursor_name, ':pName', pName);
END IF;
IF pHeight > 0 THEN
DBMS_SQL.BIND_VARIABLE(cursor_name, ':pHeight', pHeight);
END IF;
IF pTeam IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(cursor_name, ':pTeam', pTeam);
END IF;
-- Run the query.
-- (The return value of DBMS_SQL.EXECUTE for SELECT queries is undefined,
-- so we must ignore it.)
ignore := DBMS_SQL.EXECUTE(cursor_name);
-- Convert the DBMS_SQL cursor into a PL/SQL REF CURSOR.
RETURN DBMS_SQL.TO_REFCURSOR(cursor_name);
EXCEPTION
WHEN OTHERS THEN
-- Ensure that the cursor is closed.
IF DBMS_SQL.IS_OPEN(cursor_name) THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END IF;
RAISE;
END;
(Note: DBMS_SQL.TO_REFCURSORis new in Oracle 11g.)
（注意：DBMS_SQL.TO_REFCURSOR是 Oracle 11g 中的新增功能。）
回答by Donogst
The approach I use is to include in the dynamic SQL an ELSE case the states the reverse of the IF. Your code tests that pName is not null, so I would add a clause to the generated query testing that pName IS Null. That way you can pass the same parameters every time without affecting the results of the query.
我使用的方法是在动态 SQL 中包含一个 ELSE 情况，说明 IF 的相反情况。您的代码测试 pName 不为空，因此我将在生成的查询中添加一个子句来测试 pName 为空。这样您每次都可以传递相同的参数，而不会影响查询的结果。
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 ';
ELSE
SQLQuery := SQLQuery || 'AND :pName IS NULL';
END IF;
IF pHeight IS > 0
SQLQuery := SQLQuery || 'AND Height = :pHeight ';
ELSE
SQLQuery := SQLQuery || 'AND :pHeight <=0 ';
END IF;
IF pTeam IS NOT NULL
SQLQuery := SQLQuery || 'AND Team LIKE :pTeam ';
ELSE
SQLQuery := SQLQuery || 'AND :pTeam IS NULL';
END IF;
OPEN TestCursor FOR SQLQuery USING pName, pHeight, pTeam;
回答by archimede
How about
怎么样
SQLQuery := 'SELECT ID, Name, Height, Team FROM MyTable WHERE ID IS NOT NULL ';
SQLQuery := SQLQuery || 'AND Name LIKE :pName ';
SQLQuery := SQLQuery || 'AND Team LIKE :pTeam ';
SQLQuery := SQLQuery || 'AND (Height = :pHeight OR :pHeight = 0)';
OPEN TestCursor FOR SQLQuery USING nvl(pName, '%'), nvl(pTeam, '%'), nvl(pHeight, 0), nvl(pHeight, 0);
?
?
