oracle替代变量(Substitution Variable )的用法zz

一、定义替代变量

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

十一、将参数值传递给SQL*Plus替代变量

可以将参数传递到SQL*Plus脚本中执行

sqlplushr/*********************************\"DeHaan\"

或者

SQL> @myscript.sql employees \"De Haan\"

可以在myscript.sql中用“&1”和“&2”传递参数“employee”和“\"De Haan\"”，如下所示

set verify off

select employee_id from &1 where last_name = '&2';

“set verify off”可阻止SQL*Plus返回替代变量在执行SQL前后的状态。例如：

SQL> @xxx Hello!

old 1: select '&1' greeting from dual

new 1: select 'Hello!' greeting from dual

HI

------

Hello!

SQL> set verify off

SQL> @xxx Greetings!

HI

----------

Greetings!

十二、传递操作系统参数给SQL*Plus替代变量

可以将操作系统参数传递到SQL*Plus脚本中执行

在UNIX系统下：

sqlplushr/************************$USER

在Windows系统下：

sqlplushr/************************%USERNAME%

分别将系统参数USER和USERNAME传递给SQL*Plus脚本使用，在SQL*Plus脚本用“&1”来调用。

十三、将命令行的值传递给存储过程

如果我们已经建立如下的存储过程

create or replace procedure myproc (p1 in number) as

begin

dbms_output.put_line('The number is '||p1);

end;

我们想用SQL*Plus脚本调用它，则编写一个脚本myscript.sql

begin

myproc(&1);

end;

/

然后我们执行

sqlplushr/**************************

就将“88”通过“&1”传递给“p1”，如果存储过程的参数有“OUT”型的，就不能这样直接的在myscript.sql脚本中调用，需要将myscript.sql脚本中增加绑定变量

variable mybindvar number

begin

:mybindvar := &1;

myproc(:mybindvar);

end;

/

这样即使“p1”为“out”型也可以传递了。

十四、实现脚本中的参数可选以及设定默认值

如果命令行传递参数给脚本，则我们使用该参数，否则提示用户输入

-- Name: myscript.sql

prompt Enter a value for PAGESIZE

set termout off

define mypar = &1

set termout on

prompt Setting PAGESIZE to &mypar

set pagesize &mypar

select last_name from employees where rownum < 20;

exit

当我们运行脚本而且没有指定参数的话，数据库会提示输入参数，假设我们输入“12”

%sqlplushr/************************

SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 5 15:19:40 2003

...

Enter a value for PAGESIZE

12

Setting PAGESIZE to 12

LAST_NAME

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

King

Kochhar

De Haan

...

当我们运行脚本的时候指定参数为“8”，运行情况如下

%sqlplushr/*************************

SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 5 15:20:38 2003

...

Enter a value for PAGESIZE

Setting PAGESIZE to 8

LAST_NAME

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

King

Kochhar

De Haan

...

十五、传输值到iSQL*Plus网页界面

可以通过URL的方式将参数传递到iSQL*Plus网页界面

在10g中格式如下

http://machine/isqlplus/dynamic?script=http://machine/mys.sql&myv=emp&v2=dept

在9i中格式如下

http://machine/isqlplus?script=http://machine/mys.sql&myv=emp&v2=dept

就相当于定义变量myv和v2，同时将它们赋值“emp”和“dept”，这样运行mys.sql脚本就可以调用这两个变量。

十六、在iSQL*Plus网页中提示用户输入参数

我们先在Oracle的HTTP服务器编写脚本employee_name.sql

-- Name: employee_name.sql

set verify off

set pagesize 200

set feedback off

prompt Employee Details for Employee(s) with Last Name like &last_name%

select *

from employees

where upper(last_name) like upper('&last_name%')

/

在Oracle的HTTP服务器建立HTML文件

iSQL*Plus Dynamic Report - Query by Last Name

其中用Oracle的HTTP服务器的主机名、域名和端口来代替http://machine，例如http://machine.oracle.com:7777/

INPUT TYPE需要与替代变量类型、名称、大小保持一致

十七、传递SQL*Plus的返回状态

EXIT myv

不需要在替代变量myv之前添加“&”就可以实现返回状态的功能

对于数字绑定变量需要添加前缀“：”

EXIT :mybv

十八、提示输入用户名和数据库实例

在10g的glogin.sql或者login.sql有这样一段话

set sqlprompt \"_user'@'_connect_identifier:SQL> \"

提示用户名和数据库实例

set termout off

define myv = 'Not connected'

column myc new_value myv

select user||'@'||global_name myc from global_name;

set sqlprompt '&myv:SQL> '

set termout on