oracle 在 .sql 脚本中定义和使用变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15691582/
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
Defining and Using Variables in .sql scripts
提问by user1888243
I'm trying to define variables in a .sql file so that I can use them in my sql statments. But I'm so confused about how these variables are defined, and I can't find even one good online resource that has explained all this in clear manner. I follow the recommendations in different sources and I keep getting compile errors in 'Oracle SQL Developer'. Here are my 2 problems:
我正在尝试在 .sql 文件中定义变量，以便我可以在我的 sql 语句中使用它们。但是我对如何定义这些变量感到很困惑，我什至找不到一个很好的在线资源来清楚地解释所有这些。我遵循不同来源中的建议，并且在“Oracle SQL Developer”中不断出现编译错误。这是我的两个问题：
Problem 1:It seems that you can define variables in the following 2 ways. What is the difference between these 2 ways, and can I use both?
问题一：好像可以通过以下2种方式来定义变量。这两种方式有什么区别，我可以同时使用吗？
define first_name = Joe;
select * from customer where name = '&firstname';
or
或者
variable first_name CHAR;
exec :first_name:= 'Joe';
select * from customer where name = :firstname;
Problem 2:In the first method (meaning using define command), can I define a variable of type number and if so, how can I do it?
问题2：在第一种方法中（意思是使用define命令），我可以定义一个number类型的变量吗？如果可以，我该怎么做？
回答by Ed Gibbs
You can define NUMBERvariables no problem:
你可以定义NUMBER变量没问题：
SQL> VARIABLE myNum NUMBER;
SQL> EXEC :myNum := 123.456;
PL/SQL procedure successfully completed.
SQL> print myNum;
MYNUM
----------
123.456
Lots of other types are supported as well. Here's the USAGEhelp text from the VARIABLE command:
还支持许多其他类型。这是USAGE来自 VARIABLE 命令的帮助文本：
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
If you type VARIABLE(or just VAR) without anything else, SQL*Plus will list all your variables with their values.
如果您键入VARIABLE（或只键入VAR）而没有其他任何内容，SQL*Plus 将列出您的所有变量及其值。
Addendum: contrast the two variable assignment styles in the original question.
附录：对比原始问题中的两种变量赋值风格。
When you do this...
当你这样做...
define first_name = Joe
select * from customer where name = '&first_name';
... It's more like a #definein C/C++. You can't treat it like a variable; it's just text that gets pasted every time SQL*Plus sees &first_name.
...它更像是#defineC/C++ 中的一个。你不能把它当作一个变量；它只是每次 SQL*Plus 看到时粘贴的文本&first_name。
When you do this...
当你这样做...
variable first_name CHAR;
exec :first_name:= 'Joe';
select * from customer where name = :first_name;
You're creating a real variable that you can manipulate.
您正在创建一个可以操作的真实变量。
Note that if you use CHARin the definition (without a size), the first assignment will determine its size and you can't make it longer after that. If you define it as CHAR(50)it'll always be 50 characters long and padded with spaces. That can get confusing so I'd recommend VARCHAR2in most cases for strings.
请注意，如果您CHAR在定义中使用（没有大小），则第一个分配将确定其大小，之后不能再延长。如果您将其定义为CHAR(50)始终为 50 个字符长并用空格填充。这可能会让人感到困惑，所以我建议VARCHAR2在大多数情况下使用字符串。
回答by Younes
- Answer to problem 1:The first type of variables is called Substitution Variables which only works in SQL*Plus and SQL Developer. So when you supply a substituation vaiable in any SQL statement, SQL*Plus replaces the variable with it's value. It has nothing to do with Oracle server or performance. The example you gave will be translated to the follwing BEFORE sending it to the Oracle database server:
- 问题 1 的答案：第一种类型的变量称为替换变量，它仅适用于 SQL*Plus 和 SQL Developer。因此，当您在任何 SQL 语句中提供替代变量时，SQL*Plus 会用它的值替换该变量。它与 Oracle 服务器或性能无关。您提供的示例将在将其发送到 Oracle 数据库服务器之前转换为以下内容：
select * from customer where name = 'Joe';
The second part is called bind variables which is not exclusive to SQL*Plus or SQL Developer as you can use it, for example, in a Java application (or other languages) connecting to Oracle. Bind variables provide better performance when you run the same statement many times as you always submit the statement as it is (without rewriting). Then the variables get evaluated at the database level. For example, let's say you've changed the value of "first_name" to "Mark":
第二部分称为绑定变量，它不是 SQL*Plus 或 SQL Developer 独有的，因为您可以在例如连接到 Oracle 的 Java 应用程序（或其他语言）中使用它。当您多次运行相同的语句时，绑定变量可提供更好的性能，因为您总是按原样提交语句（无需重写）。然后在数据库级别评估变量。例如，假设您已将“first_name”的值更改为“Mark”：
exec :first_name:= 'Mark';
The same statement with the bind variable is submitted to Oracle database server. The database uses cached area to find that the same statement was run perviously and uses it again. Then the database uses the variables values. This means the database will not need to re-parse and re-calculate the best execution plan for the same statement. But this is not the case with the first type(Substitution Variables).
与绑定变量相同的语句被提交到 Oracle 数据库服务器。数据库使用缓存区来查找之前运行过的相同语句并再次使用它。然后数据库使用变量值。这意味着数据库将不需要重新解析和重新计算同一语句的最佳执行计划。但第一种类型（替代变量）并非如此。
- Answer to problem 2:No you can't, because as I said above, all what SQL*Plus or SQL Developer do is just rewriting the statement replacing the variable name with it's value. It doesn't know anything about it's type. Only text replacement is done here.
- 问题 2 的答案：不，你不能，因为正如我上面所说的，SQL*Plus 或 SQL Developer 所做的只是重写语句，用它的值替换变量名。它对它的类型一无所知。这里只进行文本替换。
You can find more details here: http://www.oracle-base.com/articles/misc/literals-substitution-variables-and-bind-variables.php
您可以在此处找到更多详细信息：http: //www.oracle-base.com/articles/misc/literals-substitution-variables-and-bind-variables.php
回答by MikeTWebb
In Oracle, you can define variables as follows:
在 Oracle 中，您可以按如下方式定义变量：
CREATE OR REPLACE FUNCTION MyFunction()
AS
my_number NUMBER (20, 0);
first_name VARCHAR2(256);
BEGIN
first_name := 'Joe';
select * from customer where name = first_name;
END
Is that what you're looking for?
这就是你要找的吗？
