sql*plus参数传递

关于sql*plus里面参数传递的设置和命令[@more@]

SQL> column object_name format a15
SQL> column object_id format 999,999
SQL> column object_type format a15

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'

执行结果：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/207/viewspace-816407/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/207/viewspace-816407/