Oracle替代变量(SubstitutionVariable)的用法

一、定义替代变量1.通过DEFINE设置精确值define myv =

一、定义替代变量

1.通过DEFINE设置精确值

define myv = 'King';

变量myv的值为King。

2.通过ACCEPT定义一个变量，同时提示你输入它的值

accept myv char prompt 'Enter a last name: '

变量myv的值需要用户输入。
3.通过&&定义一个变量，同时要求你输入它的值

select first_name from employees where last_name = '&&myuser';

变量myuser的值需要用户输入。
4.通过COLUMN NEW_VALUE定义

column last_name new_value mynv
select last_name from employees where employee_id = 100;
变量mynv的值就是字段last_name存储的记录。

二、使用替代变量

如果替代变量已经定义或者赋值，那么就可以在其之前加“&”来调用它

select employee_id from employees where last_name = '&myv';
三、查询已定义的变量

使用define命令，后面不需要添加任何参数，就可以查询到所有已经定义过的替代变量，以及它的值和类型。

define
之后就会得出

DEFINE MYV = "King" (CHAR)
...
四、在数据中插入字符“&”

有时候我们需要在数据中插入字符“&”，而不需要它作为替代变量的调用符号，，我们可以通过以下方法来实现：

1.set define off关闭替代变量功能；

2.set escape \设置转义字符“\”，这样使用“\&”就表示字符“&”。

五、在spool脚本中添加当前时间
可以通过为sysdate定义替代变量的方法在spool脚本中添加时间
column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD') dcol from dual;

spool &mydate.report.txt
-- my report goes here
select last_name from employees;
spool off
这样mydate变量就把系统时间传递到spool脚本里了，其中noprint保证select语句不会将mydate的值输出，执行下面的 select语句不会有任何结果返回的，“&mydate.report.txt”中第一个“.”只是替代变量的结束符不能当做字符，如果 “mydate”的值为“20100124”的话“&mydate.report.txt”在spool脚本中输出地字符就为 “20100124report.txt”。
六、可以通过“.”使用已经定义的替代变量
define mycity = Melbourne
spool &mycity.Australia.txt
之后输出就变为“MelbourneAustralia.txt”。
七、在替代变量的值后面添加字符“.”
define mycity = Melbourne
spool &mycity..log
之后输出为“Melbourne.log”。
八、在TTITLE, BTITLE, REPHEADER 和 REPFOOTER之中使用定值的替代变量
define dept = '60'
ttitle left 'Salaries for department &dept'
select last_name, salary from employees where department_id = &dept;
九、在TTITLE, BTITLE, REPHEADER 和 REPFOOTER之中使用变值的替代变量
column department_id new_value dv noprint
ttitle left 'Members of department ' dv
break on department_id skip page
select department_id, last_name from employees order by department_id, last_name;
不是在替代变量之前添加“&”，而是将它放在字符串的外面，这样就可以实现不同值的输出。
在BTITLE和REPFOOTER需要用“COLUMN OLD_VALUE”代替“COLUMN NEW_VALUE”。
十、在SQL*Plus命令中使用绑定变量
因为像SPOOL, SET 和 TTITLE这些SQL*Plus命令，是在SQL*Plus程序中执行，而不是传送到数据中再执行的，所以它们无法识别绑定变量。
-- Set a bind variable to a text string
variable mybindvar varchar2(20)
begin
:mybindvar := 'myspoolfilename';
end;

-- Transfer the value from the bind variable to the substitution variable
column mc new_value mysubvar noprint
select :mybindvar mc from dual;

-- Use the substitution variable
spool &mysubvar..txt
select * from employees;
spool off