Oracle替代变量(Substitution Variable )的用法

Oracle替代变量(Substitution Variable )的用法

一、定义替代变量

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