oracle undefine的用法_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>