oracle 带变量的 PL/SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1767178/
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
PL/SQL Query with Variables
提问by Jim
I have a fairly complex query that will be referencing a single date as a start or stop date multiple times throughout. I'm going to have to run this query for 3 different fiscal years, and don't want to have to hunt down the date 17 times in order to change it throughout my query.
我有一个相当复杂的查询，它将在整个过程中多次引用单个日期作为开始或停止日期。我将不得不在 3 个不同的财政年度运行此查询，并且不想为了在整个查询中更改日期而不得不搜索日期 17 次。
Is there a way to set a variable at the beginning of my query and reference it throughout? I'm not looking to write a whole function, just reference a variable throughout my query.
有没有办法在我的查询开始时设置一个变量并在整个过程中引用它？我不想写一个完整的函数，只是在整个查询中引用一个变量。
回答by OMG Ponies
Yes, depends how you want to do it.
是的，取决于你想怎么做。
You could use an anonymous procedure IE:
您可以使用匿名程序 IE：
BEGIN
v_date DATE := TO_DATE(your_date, your_date_mask);
[your query referencing v_date where ever you need];
END;
Or if you run the query in SQLPlus, you use &to note variables (IE: &your_date), and will be prompted for the value when you run the script.
或者，如果您在 SQLPlus 中运行查询，则使用&注释变量（IE：&your_date），并且在运行脚本时会提示输入值。
回答by Dan
As OMG Ponies says, inside PL/SQL you can always refer to any PL/SQL variable (including parameters) right in the SQL as long as it's static SQL. Outside PL/SQL, or if your SQL is dynamic (because native dynamic SQL doesn't support reusable named parameters at least as of 10g) you can use the following trick. Add the following before the WHERE clause in your query:
正如 OMG Ponies 所说，在 PL/SQL 中，您始终可以在 SQL 中引用任何 PL/SQL 变量（包括参数），只要它是静态 SQL。在 PL/SQL 之外，或者如果您的 SQL 是动态的（因为本机动态 SQL 至少从 10g 开始不支持可重用的命名参数），您可以使用以下技巧。在查询中的 WHERE 子句之前添加以下内容：
CROSS JOIN (SELECT :dateparam Mydate FROM dual) Dateview
And everywhere you want to refer to that value in your main query, call it Dateview.MydateThen when you execute the query, you need only pass in the one bind parameter.
并且在主查询中您想在任何地方引用该值，调用它Dateview.Mydate然后当您执行查询时，您只需要传入一个绑定参数。
回答by David
You're not really saying how you reference this so I'll just show from SQL*Plus point of view.
您并没有真正说明您如何引用它，因此我将仅从 SQL*Plus 的角度进行展示。
Two ways
两种方式
Have it prompt you for the value. Since you use the same variable many times you'll want to use the && operator.
让它提示您输入值。由于您多次使用相同的变量，因此您需要使用 && 运算符。
SQL> SELECT &&var, &&var FROM Dual;
Enter value for var: 'PUMPKIN'
old 1: SELECT &&var, &&var FROM Dual
new 1: SELECT 'PUMPKIN', 'PUMPKIN' FROM Dual
'PUMPKI 'PUMPKI
------- -------
PUMPKIN PUMPKIN
Alternatively you could set it before you ran your SQL.
或者，您可以在运行 SQL 之前设置它。
SQL> VARIABLE new_var VARCHAR2(20);
SQL> EXECUTE :new_var := 'PUMPKIN PIE';
PL/SQL procedure successfully completed.
SQL> SELECT :new_var, :new_var FROM DUAL;
:NEW_VAR :NEW_VAR
-------------------------------- --------------------------------
PUMPKIN PIE PUMPKIN PIE
回答by Hernaldo Gonzalez
If you use Toad with second mouse button -> Execute as Script, so not prompt you for values:
如果您通过第二个鼠标按钮使用 Toad -> 作为脚本执行，则不会提示您输入值：
var myVar varchar2(20);
exec :req := 'x';
delete from MYTable where Field = :myVar;
var myVar varchar2(20);
执行 :req := 'x';
从 MYTable 中删除其中 Field = :myVar;
