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>

转载于:https://www.cnblogs.com/liaomin416100569/p/9331273.html