在 Oracle 中定义局部变量的最简单方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11298666/
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
What is the simplest way to define a local variable in Oracle?
提问by user595234
In the SQL Server, I can define local variables like this.
在 SQL Server 中，我可以像这样定义局部变量。
declare @id number := 1000
select * from tbl_A where id = @id;
select * from tbl_B where id = @id;
It is very convenient.
I tried to do same thing in PL/SQLbut it doesn't work.
非常方便。我试图做同样的事情，PL/SQL但它不起作用。
DECLARE id number;
select 1000 into id from dual;
Do you know how to do something similar? The simplest method is my objective.
你知道如何做类似的事情吗？最简单的方法是我的目标。
回答by Justin Cave
If you want to define a local variable in PL/SQL, you need a complete PL/SQL block
如果要在 PL/SQL 中定义局部变量，则需要一个完整的 PL/SQL 块
DECLARE
id NUMBER;
BEGIN
SELECT 1000
INTO id
FROM dual;
END;
or just
要不就
DECLARE
id NUMBER := 1000;
BEGIN
<<do something that uses the local variable>>
END;
If you want to declare a variable in SQL*Plus
如果要在 SQL*Plus 中声明变量
SQL> variable id number
SQL> begin
select 1000 into :id from dual;
end;
/
SQL> print id
ID
----------
1000
SQL> SELECT * FROM tbl_a WHERE id = :id
回答by Slim Aloui
I tried this on Oracle and it worked :
我在 Oracle 上试过这个，它奏效了：
> DEF x = TOTO
> SELECT '&x' FROM dual;
The result will be : TOTO
结果将是：TOTO
Edit: Note that the variable will keep the value after execution. UNDEFINE x to clear variable
编辑：请注意，该变量将在执行后保留该值。UNDEFINE x 清除变量
回答by jeromerg
An alternative to DECLARE Block is to use a WITH Clause:
DECLARE 块的替代方法是使用 WITH 子句：
WITH my_params AS (
SELECT 123 AS min_id FROM DUAL
)
SELECT *
FROM some_table
WHERE id > (SELECT min_id FROM my_params)
It is more portable as many vendors support the WITH clause and you can change seamless from parameter to dynamic value. For example:
它更易于移植，因为许多供应商都支持 WITH 子句，并且您可以从参数无缝更改为动态值。例如：
WITH my_params AS (
SELECT min(id) AS min_id FROM some_id_table
)
SELECT *
FROM some_table
WHERE id > (SELECT min_id FROM my_params)
回答by yong321
(Just stumbled across this thread.) Beginning with SQL*Plus 12.2, you can declare and assign a value at the same time:
（刚刚偶然发现了这个线程。）从 SQL*Plus 12.2 开始，您可以同时声明和分配一个值：
SQL> var id number = 1000
SQL> 变量 ID 号 = 1000
SQL> select * from tbl_A where id = :id;
SQL> select * from tbl_A where id = :id;
Oracle calls it input binding.
Oracle 称之为输入绑定。
If you must do it in PL/SQL, the answer was given by others.
如果一定要在PL/SQL中做，答案是别人给的。
回答by Aniket Thakur
General syntax to declare variable in PL/SQL is
在 PL/SQL 中声明变量的一般语法是
var_nm datatype [NOT NULL := var_value ];
- var_nn is the name of the variable.
- datatype is a valid PL/SQL datatype.
- NOT NULL is an optional specification on the variable which this variable cannot be assigned null value.
- var_value or DEFAULT value is also an optional specification, where you can initialize a variable with some specific value.
- Each variable declaration is a separate statement and must be terminated by a semicolon.
var_nm 数据类型 [NOT NULL := var_value ];
- var_nn 是变量的名称。
- 数据类型是有效的 PL/SQL 数据类型。
- NOT NULL 是对该变量的可选规范，该变量不能被赋予空值。
- var_value 或 DEFAULT 值也是一个可选规范，您可以在其中使用某些特定值初始化变量。
- 每个变量声明都是一个单独的语句，必须以分号结尾。
We can assign value to variables in one of the following two ways -
我们可以通过以下两种方式之一为变量赋值 -
- direct assignment (Eg.
var_nm:= var_value;) - Using
select from(Eg.SELECT col_nm INTO var_nm FROM tbl_nm [WHERE clause];)
- 直接赋值（例如
var_nm:= var_value;） - 使用
select from（例如SELECT col_nm INTO var_nm FROM tbl_nm [WHERE clause];）
In you case as Justin Cave has already mentioned it can be
在你的情况下，正如贾斯汀凯夫已经提到的那样，它可以是
DECLARE
id number;
BEGIN
SELECT 1000 into id from dual;
dbms_output.put_line('id : '|| id );
END;
/
OR
或者
DECLARE
id number := 1000;
BEGIN
dbms_output.put_line('id : '|| id );
END;
/
NOTE: '/' i.e Back slash after END keyword indicates to execute the above PL/SQL Block.
注意：'/' 即 END 关键字后的反斜杠表示执行上述 PL/SQL 块。
