oracle 是否可以在 pl sql 中参数化查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/449922/
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
Is it possible to parameterize a query inside pl sql?
提问by Serhat Ozgel
The stored procedures being written here currently concats the parameters to the queries:
此处编写的存储过程当前将参数连接到查询：
SELECT *
FROM Names
WHERE Name = ' || prmName || '
ORDER BY ' || prmSortField
Is it possible to parameterize this query inside the stored procedure? Possibly like:
是否可以在存储过程中参数化此查询？可能喜欢：
query = 'select * From Names Where Name = @name Order By ' || prmSortField
call(query, prmName)
Note:
In case you wonder why we do so, there are two common parameters for our sp's: sortFieldIndexand sortDirection. Since we cannot directly parameterize these, the query is dynamically generated. But other parameters make the queries open for injection. So I am looking a way to parameterize some of the parameters.
注意：
如果您想知道我们为什么这样做，我们的 sp 有两个通用参数：sortFieldIndex和sortDirection。由于我们不能直接参数化这些，查询是动态生成的。但是其他参数使查询开放以供注入。所以我正在寻找一种方法来参数化一些参数。
回答by cletus
Absolutely. Use cursors.
绝对地。使用游标。
DECLARE
CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
SELECT * FROM employees WHERE job_id = job AND salary > max_wage;
BEGIN
FOR person IN c1('CLERK', 3000)
LOOP
-- process data record
DBMS_OUTPUT.PUT_LINE('Name = ' || person.last_name || ', salary = ' ||
person.salary || ', Job Id = ' || person.job_id );
END LOOP;
END;
回答by Tony Andrews
For a dynamic query with bind values, do this:
对于具有绑定值的动态查询，请执行以下操作：
procedure p (prmName varchar2, prmSortField varchar2)
is
query varchar2(100);
rc sys_refcursor;
names_rec names%rowtype;
begin
query = 'select * From Names Where Name = :name Order By ' || prmSortField
open rc for query using prmName;
loop
fetch rc into names_rec;
exit when rc%notfound;
-- process this row
end loop;
close rc;
end;
回答by Stew S
For a more elaborate procedure that supports optional parameter values (but uses sys context), check out the following post on Asktom.com
有关支持可选参数值（但使用 sys 上下文）的更详细的过程，请查看 Asktom.com 上的以下帖子
