简单的 Oracle 变量 SQL 赋值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8039907/
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
Simple Oracle variable SQL Assignment
提问by m.edmondson
Despite having spent an hour researching I can't seem to figure out how to correctly define a variable and then use it in your SQL.
尽管花了一个小时研究我似乎无法弄清楚如何正确定义变量然后在您的 SQL 中使用它。
This is what I have so far produced:
这是我迄今为止制作的：
DECLARE startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy');
DECLARE startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy');
of which I get the reply:
其中我得到答复：
ORA-06550: line 1, column 63: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
begin function package pragma procedure subtype type use form current cursor
Details: DECLARE startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy'); Error at line 1 ORA-06550: line 1, column 63: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
begin function package pragma procedure subtype type use form current cursor
ORA-06550：第 1 行，第 63 列：PLS-00103：在预期以下情况之一时遇到符号“文件结束”：
begin 函数包 pragma procedure subtype type use form current cursor
详细信息：DECLARE startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy'); 第 1 行 ORA-06550 错误：第 1 行，第 63 列：PLS-00103：在预期以下情况之一时遇到符号“文件结束”：
begin 函数包 pragma procedure subtype type use form current cursor
I'd love to find out how to do such a simple task!
我很想知道如何完成如此简单的任务！
回答by Xavi López
Your variable declaration is correct.
您的变量声明是正确的。
The DECLAREkeyword is used to define variables scoped in a PL/SQL block (whose body is delimited by BEGINand END;). How do you want to use this variable?
该DECLARE关键字用于定义范围在 PL/SQL 块（其主体由BEGIN和分隔END;）中的变量。你想如何使用这个变量？
The following PL/SQL works fine for me:
以下 PL/SQL 对我来说很好用：
DECLARE
startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy');
reccount INTEGER;
BEGIN
SELECT count(*) INTO reccount
FROM my_table tab
WHERE tab.somedate < startDate;
dbms_output.put_line(reccount);
END;
You can also use the DEFINEstatement to use simple string substitution variables. They are suitable for a client like SQL/PLUS or TOAD.
您还可以使用该DEFINE语句来使用简单的字符串替换变量。它们适用于 SQL/PLUS 或 TOAD 等客户端。
DEFINE start_date = "to_date('03/11/2011', 'dd/mm/yyyy')"
SELECT COUNT(*) from my_table tab where tab.some_date < &start_date;
回答by Allan
To accomplish what you're attempting in Toad, you don't need to declare the variable at all. Simply include your variable prefaced with a colon and Toad will prompt you for the variable's value when you execute the query. For example:
要完成您在 Toad 中的尝试，您根本不需要声明变量。只需包含以冒号开头的变量，Toad 就会在您执行查询时提示您输入变量的值。例如：
select * from all_tables where owner = :this_is_a_variable;
If this doesn't work initially, right-click anywhere in the editor and make sure "Prompt for Substitution Variables" is checked.
如果这最初不起作用，请右键单击编辑器中的任意位置并确保选中“提示替换变量”。
If you really want to do it similarly to the way SQL Server handles variables (or you want to be able to do the same thing in SQL*Plus), you can write it as follows:
如果你真的想像 SQL Server 处理变量的方式那样做（或者你想在 SQL*Plus 中做同样的事情），你可以这样写：
var this_is_a_variable varchar2(30);
exec :this_is_a_variable := 'YOUR_SCHEMA_NAME';
print this_is_a_variable;
select * from all_tables where owner = :this_is_a_variable;
However, to make this work in Toad, you'll need to run it through "Execute as script", rather than the typical "Execute statement" command.
但是，要在 Toad 中执行此操作，您需要通过“作为脚本执行”而不是典型的“执行语句”命令来运行它。
回答by Gabriele D'Antona
Take in mind that Oracle's PL/SQL is notSQL.
请记住，Oracle 的 PL/SQL不是SQL。
PL/SQL is a procedural language. SQL is not procedural, but you can define "variables" the user can enter via the "&var" syntax (see http://www.orafaq.com/node/515).
PL/SQL 是一种过程语言。SQL 不是程序性的，但您可以定义用户可以通过“&var”语法输入的“变量”（请参阅http://www.orafaq.com/node/515）。
回答by Gordon Linoff
This is an old post, but in case anyone stumbles on this (as I just did), you can handle this with a CTE:
这是一篇旧帖子，但万一有人偶然发现（就像我刚做的那样），您可以使用 CTE 来处理：
with params as (
select date '2011-11-03' as startdate
from dual
)
select . . .
from params cross join
. . .
Almost the same syntax works in SQL Server (minus the date-specific stuff and from dual).
几乎相同的语法在 SQL Server 中工作（减去date-特定的东西和from dual）。
回答by Slim Aloui
Can you please try this :
你能试试这个吗：
DEF startDate = to_date('03/11/2011', 'dd/mm/yyyy');
Select &startDate from dual;
