oracle define,undefine,ACCEPT,以及变量的获取 命令
oracle的define命令的使用
查看define的帮助
SQL> ? define
DEFINE
------
Specifies a substitution variable and assigns a CHAR value to it, or
lists the value and variable type of a single variable or all variables.
DEF[INE] [variable] | [variable = text]
这里define定义的host变量 text被定义成char类型的字符串
定义变量A=2
SQL> def A=v$session
查看所有的变量
SQL> def
DEFINE _DATE = "02-9月 -15" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "TEST" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000200" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release
2.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1001000200" (CHAR)
DEFINE _RC = "1" (CHAR)
DEFINE A = "v$session" (CHAR)
DEFINE TNAME = "AA" (CHAR)
在sql中使用这个变量
select * from &A
&A被自动替换成 v$session
如果我们这样
select * from &B
那么因为B没有定义 dos要求我们输入B变量的值
SQL> select * from &B
2 /
Enter value for b: goods
old 1: select * from &B
new 1: select * from goods
select * from goods
*
ERROR at line 1:
ORA-00942: table or view does not exist
如果要删除变量
使用 undefine 变量名 缩写为：undef
我们可以使用确认的方式 define一个变量 并且可以指定类型
查看accept命令帮助：
SQL> ? acc
ACCEPT
------
Reads a line of input and stores it in a given substitution variable.
In iSQL*Plus, displays the Input Required screen for you to enter a
value for the substitution variable.
ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE]
[FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
---》测试一下
SQL> acc i num prompt 'input number: ';
input number: ee
SP2-0425: "ee" is not a valid NUMBER
input number: 6
SQL> def
DEFINE _DATE = "02-9月 -15" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "TEST" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000200" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.1.0.
2.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1001000200" (CHAR)
DEFINE _RC = "1" (CHAR)
DEFINE I = 6 (NUMBER)
SQL>
查看define的帮助
SQL> ? define
DEFINE
------
Specifies a substitution variable and assigns a CHAR value to it, or
lists the value and variable type of a single variable or all variables.
DEF[INE] [variable] | [variable = text]
这里define定义的host变量 text被定义成char类型的字符串
定义变量A=2
SQL> def A=v$session
查看所有的变量
SQL> def
DEFINE _DATE = "02-9月 -15" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "TEST" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000200" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release
2.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1001000200" (CHAR)
DEFINE _RC = "1" (CHAR)
DEFINE A = "v$session" (CHAR)
DEFINE TNAME = "AA" (CHAR)
在sql中使用这个变量
select * from &A
&A被自动替换成 v$session
如果我们这样
select * from &B
那么因为B没有定义 dos要求我们输入B变量的值
SQL> select * from &B
2 /
Enter value for b: goods
old 1: select * from &B
new 1: select * from goods
select * from goods
*
ERROR at line 1:
ORA-00942: table or view does not exist
如果要删除变量
使用 undefine 变量名 缩写为：undef
我们可以使用确认的方式 define一个变量 并且可以指定类型
查看accept命令帮助：
SQL> ? acc
ACCEPT
------
Reads a line of input and stores it in a given substitution variable.
In iSQL*Plus, displays the Input Required screen for you to enter a
value for the substitution variable.
ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE]
[FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
---》测试一下
SQL> acc i num prompt 'input number: ';
input number: ee
SP2-0425: "ee" is not a valid NUMBER
input number: 6
SQL> def
DEFINE _DATE = "02-9月 -15" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "TEST" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000200" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.1.0.
2.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1001000200" (CHAR)
DEFINE _RC = "1" (CHAR)
DEFINE I = 6 (NUMBER)
SQL>
