oracle 绑定变量和我使用 && 输入的变量有什么区别？

What is the difference between bind variables and the variable which I input using &&?

提问by Tarun

What is the difference between these two variable declarations?

这两个变量声明有什么区别？

1: num number:='&&num';
2: variable num1 number;

1：num number:='&&num';
2：variable num1 number;

Since in both cases I can reference numby using &numor &&numin other files also, and in the case of bind variables :num1.

因为在这两种情况下，我也可以num通过 using&num或&&num在其他文件中引用，并且在绑定变量的情况下:num1。

Moreover I have one more confusion: whether any of the below statements differ somehow, are they both valid and do they mean the same thing?

此外，我还有一个困惑：以下陈述中是否有任何不同之处，它们都有效并且它们的意思相同吗？

1: variable num1 number;
2: var num1 number;

1：variable num1 number;
2：var num1 number;

回答by Luke Woodward

You appear to have some confusion about the differences between bind variablesin Oracle and substitution variablesin SQL*Plus.

您似乎对Oracle 中的绑定变量和SQL*Plus 中的替换变量之间的差异有些困惑。

Let's start with substitution variables. Substitution variables are unique to SQL*Plus and are not part of the database. They won't work if you try to use them with JDBC, for example.

让我们从替换变量开始。替换变量是 SQL*Plus 独有的，不是数据库的一部分。例如，如果您尝试将它们与 JDBC 一起使用，它们将不起作用。

Substitution variables can only hold a piece of text. If SQL*Plus encounters a substitution variable in a line of input, it will replace the variable with its text contents:

替换变量只能保存一段文本。如果 SQL*Plus 在输入行中遇到替换变量，它将用其文本内容替换该变量：

SQL> define subvar=X
SQL> select * from dual where dummy = &subvar;
old   1: select * from dual where dummy = &subvar
new   1: select * from dual where dummy = X
select * from dual where dummy = X
                                 *
ERROR at line 1:
ORA-00904: "X": invalid identifier

Note that SQL*Plus replaced our substitution variable with its text value with no regard for whether it gave us valid SQL. In the example above, we omitted the single quotes around &subvarand it gave us invalid SQL, so we got an error.

请注意，SQL*Plus 用其文本值替换了我们的替换变量，而不管它是否为我们提供了有效的 SQL。在上面的例子中，我们省略了周围的单引号&subvar，它给了我们无效的 SQL，所以我们得到了一个错误。

The lines beginning oldand newshow us the line we entered before and after SQL*Plus applied the substitution variables. The newline is the line the database tried to run.

行开始old并new显示我们在 SQL*Plus 应用替换变量之前和之后输入的行。该new行是数据库尝试运行的行。

You can enable or disable the display of the oldand newlines using SET VERIFY ONand SET VERIFY OFF. You can also turn the replacement of substitution variables on or off by using SET DEFINE ONand SET DEFINE OFF.

您可以使用and启用或禁用old和new行的显示。您还可以使用和打开或关闭替换变量的替换。SET VERIFY ONSET VERIFY OFFSET DEFINE ONSET DEFINE OFF

If we want to run the above query using the substitution variable, we must put quotes around it:

如果我们想使用替换变量运行上面的查询，我们必须在它周围加上引号：

SQL> select * from dual where dummy = '&subvar';
old   1: select * from dual where dummy = '&subvar'
new   1: select * from dual where dummy = 'X'

D
-
X

If &subvarhappens to contain a string that was a valid number (e.g. 5), then we can get away without using the quotes, but that's only because taking out the text &subvarand replacing it with the text 5happens to give us valid SQL.

如果&subvar碰巧包含一个有效数字的字符串（例如5），那么我们可以不使用引号而逃脱，但这只是因为取出文本&subvar并用文本替换它5恰好为我们提供了有效的 SQL。

For example, suppose we have a table called testwith the following data in it:

例如，假设我们有一个表，test其中包含以下数据：

         A
----------
         1
         2
         3
         4
         5

Then we can do

然后我们可以做

SQL> define subvar=5
SQL> select * from test where a = &subvar;
old   1: select * from test where a = &subvar
new   1: select * from test where a = 5

         A
----------
         5

Bind variables, on the other hand, have types. They are not simple text values. Their values are sent to the database, and the database can also set their values.

另一方面，绑定变量具有类型。它们不是简单的文本值。它们的值被发送到数据库，数据库也可以设置它们的值。

SQL> variable bindvar varchar2(1);
SQL> exec :bindvar := 'X';

PL/SQL procedure successfully completed.

You don't put quotes around a bind variable when you want to use it:

当你想使用绑定变量时，你不要在它周围加上引号：

SQL> select * from dual where dummy = :bindvar;

D
-
X

SQL> select * from dual where dummy = ':bindvar';

no rows selected

In the second example above, we got no rows returned because the DUALtable has no rows with the DUMMYcolumn containing the text :bindvar.

在上面的第二个示例中，我们没有返回任何行，因为DUAL表中没有DUMMY包含 text 列的行:bindvar。

You'll get an error if you attempt to assign a value of the wrong type to a bind variable:

如果您尝试将错误类型的值分配给绑定变量，则会出现错误：

SQL> variable bindvar number;
SQL> exec :bindvar := 'X';
BEGIN :bindvar := 'X'; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1

Bind variables are a standard part of the database, and you can use them with JDBC or whichever method of connecting to the database you choose.

绑定变量是数据库的标准部分，您可以将它们与 JDBC 或您选择的任何连接到数据库的方法一起使用。

---

---

Finally, variable num1 numberand var num1 numberboth mean the same thing. They both define a bind variable num1of type number. varis just an abbreviation for variable.

最后，variable num1 number并且var num1 number都意味着同样的事情。它们都定义了一个num1类型为 的绑定变量number。 var只是 的缩写variable。