绑定变量和替换变量(我使用&&输入)之间有什么区别? [英] What is the difference between bind variables and substitution variables(which I input using &&)?

问题描述

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

What is the difference between these two variable declarations?

1: num number:='&& num';
2:可变num1号；

由于在两种情况下，我都可以通过在其他文件中使用& num 或&& num 来引用 num ，对于绑定变量:num1 .

Since in both cases I can reference num by using &num or &&num in other files also, and in the case of bind variables :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:可变num1号；
2: var num1数字；

推荐答案

对于Oracle中的绑定变量和SQL中的替代变量之间的差异，您似乎有些困惑*加.

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

让我们从替换变量开始.替代变量对于SQL * Plus是唯一的，并且不是数据库的一部分.例如，如果您尝试将它们与JDBC一起使用，它们将无法工作.

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在输入行中遇到替换变量，它将用其文本内容替换该变量:

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> 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

请注意，SQL * Plus将替换变量替换为其文本值，而不考虑它是否为我们提供了有效的SQL.在上面的示例中，我们省略了& subvar 周围的单引号，这给了我们无效的SQL，因此出现了一个错误.

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 &subvar and it gave us invalid SQL, so we got an error.

以 old 和 new 开头的行向我们显示了在SQL * Plus应用替换变量之前和之后输入的行. new 行是数据库尝试运行的行.

The lines beginning old and new show us the line we entered before and after SQL*Plus applied the substitution variables. The new line is the line the database tried to run.

您可以使用 SET VERIFY ON 和 SET VERIFY OFF 启用或禁用 old 和 new 行的显示.代码>.您还可以使用 SET DEFINE ON 和 SET DEFINE OFF 来打开或关闭替换变量的替换.

You can enable or disable the display of the old and new lines using SET VERIFY ON and SET VERIFY OFF. You can also turn the replacement of substitution variables on or off by using SET DEFINE ON and SET 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

如果& subvar 恰好包含一个有效数字的字符串(例如 5 )，那么我们可以不用引号了，但这仅仅是因为取出文本& subvar 并将其替换为文本 5 可以使我们获得有效的SQL.

If &subvar happens 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 &subvar and replacing it with the text 5 happens to give us valid SQL.

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

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

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

那我们就可以做

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

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

In the second example above, we got no rows returned because the DUAL table has no rows with the DUMMY column containing the 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

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

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.

最后，变量num1编号和 var num1编号都具有相同的含义.它们都定义了类型为 number 的绑定变量 num1 . var 只是 variable 的缩写.

Finally, variable num1 number and var num1 number both mean the same thing. They both define a bind variable num1 of type number. var is just an abbreviation for variable.

这篇关于绑定变量和替换变量(我使用&&输入)之间有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！