接收sqlplus的值_sql*plus参数传递

1. SET DEFINE

变量替换标志，默认为'&'，可以用set define命令修改成其他字符，如'*'等

SQL> show define

define "&" (hex 26)

SQL> select object_name,&col2

2 from dba_objects

3 where rownum<2;

Enter value for col2: object_id

old 1: select object_name,&col2

new 1: select object_name,object_id

OBJECT_NAME OBJECT_ID

--------------- ---------

ACCESS$     89

SQL> set define '*'

SQL> list

1 select object_name,&col2

2 from dba_objects

3* where rownum<2

SQL> /

SP2-0552: Bind variable "COL2" not declared.

注：'&' 已经不是替换变量的标志,所以出错

SQL> c/&/*

1* select object_name,*col2

SQL> list

1 select object_name,*col2

2 from dba_objects

3* where rownum<2

SQL> /

Enter value for col2: object_id

old 1: select object_name,*col2

new 1: select object_name,object_id

OBJECT_NAME OBJECT_ID

--------------- ---------

ACCESS   $ 89

注：由于现在的变量替换标志是 '*'， 将 '&' 改成 '*' 后，语句顺利执行

2. SET ESCAPE

替换标志要作为普通字符出现在SQL中时，前面要有ESCAPE字符作引导。

ESCAPSE默认字符为反斜杠''

SQL> select '&a' from dual;

Enter value for a: bb

old 1: select '&a' from dual

new 1: select 'bb' from dual

'B

--

bb

SQL> select '&a' from dual;

'&

--

&a

SQL> set escape '@'

SQL> select '@&a' from dual;

'&

--

&a

SQL> select '&a' from dual;

Enter value for a:

注：此时 ''已不是escape字符

3. SET VERIFY

VERIFY参数控制变量输入值后，是否显示old/new确认行

SQL> show verify

verify ON

SQL> select '&a' from dual;

Enter value for a: bb

old 1: select '&a' from dual

new 1: select 'bb' from dual

'B

--

bb

SQL> set verify off

SQL> /

Enter value for a: bb

'B

--

bb

4. SET CONCAT

作为变量和其他字符连接的标志

SQL> show concat

concat "." (hex 2e)

SQL> select &aa.'bb' from dual;

Enter value for aa: 'cc'

old 1: select &aa.'bb' from dual

new 1: select 'cc''bb' from dual

注：'.'将&aa变量值替换后跟后面值连接，并且'.'字符本身自动消失

SQL> select &aa^'bb' from dual;

Enter value for aa: 'cc'

old 1: select &aa^'bb' from dual

new 1: select 'cc'^'bb' from dual

select 'cc'^'bb' from dual

注：'^'作为普通字符，变量&aa用值替换后'^'仍出现在sql中

5. 允许用户交互式输入参数值，用 ACCEPT...PROMPT...

PROMPT:屏幕上显示的提示信息

ACCEPT:接受参数值的变量

PROMPT3.SQL的脚本内容：

GLVT12$ type prompt3.sql

column owner format a15

column object_name format a20

column object_id format 999,999

break on owner

clear screen

prompt Please specify the owner of objects you want to view

accept obj_owner char prompt 'OBJECT OWNER:'

select owner,object_name,object_id

from dba_objects

where owner='&obj_owner'

and rownum<5

/

执行过程：

SQL> start prompt3.sql

Please specify the owner of objects you want to view

OBJECT OWNER:SYS --用户输入

old 3: where owner='&obj_owner'

new 3: where owner='SYS'

执行结果：