如何在 Oracle SQL Developer 中使用变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5653423/
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
How do I use variables in Oracle SQL Developer?
提问by Nathan
Below is an example of using variables in SQL Server 2000.
下面是在 SQL Server 2000 中使用变量的示例。
DECLARE @EmpIDVar INT
SET @EmpIDVar = 1234
SELECT *
FROM Employees
WHERE EmployeeID = @EmpIDVar
I want to do the exact same thing in Oracle using SQL Developer without additional complexity. It seems like a very simple thing to do, but I can't find a simple solution. How can I do it?
我想在没有额外复杂性的情况下使用 SQL Developer 在 Oracle 中做完全相同的事情。这似乎是一件非常简单的事情，但我找不到简单的解决方案。我该怎么做？
回答by Omphaloskopie
I am using the SQL-Developer in Version 3.2. The other stuff didn't work for me, but this did:
我在 3.2 版中使用 SQL-Developer。其他东西对我不起作用，但这样做了：
define value1 = 'sysdate'
SELECT &&value1 from dual;
Also it's the slickest way presented here, yet.
这也是这里介绍的最巧妙的方式。
(If you omit the "define"-part you'll be prompted for that value)
（如果您省略“定义”部分，系统将提示您输入该值）
回答by gavenkoa
There are two types of variable in SQL-plus: substitution and bind.
SQL-plus 中有两种类型的变量：替换和绑定。
This is substitution(substitution variables can replace SQL*Plus command options or other hard-coded text):
这是替换（替换变量可以替换 SQL*Plus 命令选项或其他硬编码文本）：
define a = 1;
select &a from dual;
undefine a;
This is bind(bind variables store data values for SQL and PL/SQL statements executed in the RDBMS; they can hold single values or complete result sets):
这是绑定（绑定变量存储 RDBMS 中执行的 SQL 和 PL/SQL 语句的数据值；它们可以保存单个值或完整的结果集）：
var x number;
exec :x := 10;
select :x from dual;
exec select count(*) into :x from dual;
exec print x;
SQL Developer supports substitution variables, but when you execute a query with bind :varsyntax you are prompted for the binding (in a dialog box).
SQL Developer 支持替换变量，但是当您使用绑定:var语法执行查询时，系统会提示您进行绑定（在对话框中）。
Reference:
参考：
- http://www.oracle.com/technetwork/testcontent/sub-var-087723.htmlSQL*Plus Substitution Variables, Christopher Jones, 2004
- http://www.oracle.com/technetwork/testcontent/sub-var-087723.htmlSQL*Plus 替换变量，Christopher Jones，2004
UPDATEsubstitutionvariables are a bit tricky to use, look:
UPDATE替换变量使用起来有点棘手，请看：
define phone = '+38097666666';
select &phone from dual; -- plus is stripped as it is a number
select '&phone' from dual; -- plus is preserved as it is a string
回答by Justin Cave
In SQL*Plus, you can do something very similar
在 SQL*Plus 中，您可以执行非常相似的操作
SQL> variable v_emp_id number;
SQL> select 1234 into :v_emp_id from dual;
1234
----------
1234
SQL> select *
2 from emp
3 where empno = :v_emp_id;
no rows selected
In SQL Developer, if you run a statement that has any number of bind variables (prefixed with a colon), you'll be prompted to enter values. As Alex points out, you can also do something similar using the "Run Script" function (F5) with the alternate EXEC syntax Alex suggests does.
在 SQL Developer 中，如果您运行的语句具有任意数量的绑定变量（以冒号为前缀），系统将提示您输入值。正如亚历克斯指出的那样，您还可以使用“运行脚本”功能 (F5) 和亚历克斯建议的替代 EXEC 语法来执行类似的操作。
variable v_count number;
variable v_emp_id number;
exec :v_emp_id := 1234;
exec select count(1) into :v_count from emp;
select *
from emp
where empno = :v_emp_id
exec print :v_count;
回答by Chandu
Simple answer NO.
简单的回答 NO。
However you can achieve something similar by running the following version using bind variables:
但是，您可以通过使用绑定变量运行以下版本来实现类似的效果：
SELECT * FROM Employees WHERE EmployeeID = :EmpIDVar
Once you run the query above in SQL Developer you will be prompted to enter value for the bind variable EmployeeID.
在 SQL Developer 中运行上述查询后，系统将提示您输入绑定变量 EmployeeID 的值。
回答by zpontikas
Ok I know this a bit of a hack but this is a way to use a variable in a simple query, not a script:
好的，我知道这有点小技巧，但这是一种在简单查询中使用变量的方法，而不是脚本：
WITH
emplVar AS
(SELECT 1234 AS id FROM dual)
SELECT
*
FROM
employees,
emplVar
WHERE
EmployId=emplVar.id;
You get to run it everywhere.
你可以到处运行它。
回答by Baodad
You can read up elsewhere on substitution variables; they're quite handy in SQL Developer. But I have fits trying to use bind variables in SQL Developer. This is what I do:
您可以在其他地方阅读有关替换变量的内容；它们在 SQL Developer 中非常方便。但是我很适合尝试在 SQL Developer 中使用绑定变量。这就是我所做的：
SET SERVEROUTPUT ON
declare
v_testnum number;
v_teststring varchar2(1000);
begin
v_testnum := 2;
DBMS_OUTPUT.put_line('v_testnum is now ' || v_testnum);
SELECT 36,'hello world'
INTO v_testnum, v_teststring
from dual;
DBMS_OUTPUT.put_line('v_testnum is now ' || v_testnum);
DBMS_OUTPUT.put_line('v_teststring is ' || v_teststring);
end;
SET SERVEROUTPUT ONmakes it so text can be printed to the script output console.
SET SERVEROUTPUT ON使文本可以打印到脚本输出控制台。
I believe what we're doing here is officially called PL/SQL. We have left the pure SQL land and are using a different engine in Oracle. You see the SELECTabove? In PL/SQL you always have to SELECT ... INTOeither variable or a refcursor. You can't just SELECTand return a result set in PL/SQL.
我相信我们在这里所做的正式称为 PL/SQL。我们已经离开了纯 SQL 领域，并在 Oracle 中使用了不同的引擎。看到SELECT上面的了吗？在 PL/SQL 中，你总是需要SELECT ... INTO变量或引用。您不能只SELECT在 PL/SQL 中返回结果集。
回答by Ivan Gerasimenko
Use the next query:
使用下一个查询：
DECLARE
EmpIDVar INT;
BEGIN
EmpIDVar := 1234;
SELECT *
FROM Employees
WHERE EmployeeID = EmpIDVar;
END;
回答by Peter
I think that the Easiest way in your case is :
我认为最简单的方法是：
DEFINE EmpIDVar = 1234;
SELECT *
FROM Employees
WHERE EmployeeID = &EmpIDVar
For the string values it will be like :
对于字符串值，它将类似于：
DEFINE EmpIDVar = '1234';
SELECT *
FROM Employees
WHERE EmployeeID = '&EmpIDVar'
回答by Jeen Jose Akkanath
Try this it will work, it's better create a procedure, if procedure is not possible you can use this script.
试试这个它会工作，最好创建一个程序，如果程序不可行，您可以使用此脚本。
with param AS(
SELECT 1234 empid
FROM dual)
SELECT *
FROM Employees, param
WHERE EmployeeID = param.empid;
END;
回答by Md. Kamruzzaman
In sql developer define properties by default "ON". If it is "OFF" any case, use below steps.
在 sql developer 中，默认情况下定义属性为“ON”。如果它在任何情况下都是“OFF”，请使用以下步骤。
set define on;
define batchNo='123';
update TABLE_NAME SET IND1 = 'Y', IND2 = 'Y' WHERE BATCH_NO = '&batchNo';
set define on;
define batchNo='123';
update TABLE_NAME SET IND1 = 'Y', IND2 = 'Y' WHERE BATCH_NO = '&batchNo';
