oracle中有 lake运算符,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脚本中执行

sqlplus

hr/my_password @myscript.sql employees "De Haan"

或者

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系统下：

sqlplus

hr/my_password @myscript.sql $USER

在Windows系统下：

sqlplus

hr/my_password @myscript.sql %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;

/

然后我们执行

sqlplus

hr/my_password @myscript.sql 88

就将“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”

% sqlplus

hr/my_password @myscript.sql

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”，运行情况如下

% sqlplus

hr/my_password @myscript.sql 8

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

iSQL*Plus Dynamic Report - Query

by Last Name

value="http://machine/employee_name.sql">

Enter last name of employee:

size="20">

其中用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