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

问题描述

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

1:num number:='&&num';
2:变量num1个数；

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

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

1:变量num1号；
2: var num1 number;

解决方案

您似乎对 Oracle 中的 绑定变量 和 SQL 中的替换变量 之间的区别有些困惑*加上.

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

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

<前>SQL> 定义子变量=XSQL> select * from dual where dummy = &subvar;旧 1: select * from dual where dummy = &subvar新 1: select * from dual where dummy = Xselect * from dual where dummy = X*第 1 行的错误:ORA-00904: "X": 无效标识符

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

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

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

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

<前>SQL> select * from dual where dummy = '&subvar';旧 1: select * from dual where dummy = '&subvar'新 1: select * from dual where dummy = 'X'D——X

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

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

<前>一个----------12345

那我们就可以了

<前>SQL> 定义子变量=5SQL> select * from test where a = &subvar;旧 1: select * from test where a = &subvar新 1:从测试中选择 *，其中 a = 5一个----------5

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

<前>SQL> 变量 bindvar varchar2(1);SQL> exec :bindvar := 'X';PL/SQL 过程成功完成.

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

<前>SQL> select * from dual where dummy = :bindvar;D——XSQL> select * from dual where dummy = ':bindvar';未选择任何行

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

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

<前>SQL> 变量绑定变量号；SQL> exec :bindvar := 'X';开始 :bindvar := 'X';结尾;*第 1 行的错误:ORA-06502:PL/SQL:数字或值错误:字符到数字的转换错误ORA-06512:在第 1 行

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

<小时>

最后，variable num1 number 和 var num1 number 都是同一个意思.它们都定义了一个 number 类型的绑定变量 num1.var 只是 variable 的缩写.

What is the difference between these two variable declarations?

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

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: variable num1 number;
2: var num1 number;

解决方案

You appear to have some confusion about the differences between bind variables in Oracle and substitution variables in 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.

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

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.

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.

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

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.

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

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

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.

---

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.

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