在Oracle数据库里SQL*Plus变量的使用

SQL*Plus中变量的使用

SQL*Plus中的变量在整个启动期间一直有效，变量可以用在SQL语句、PL/SQL块以及文本文件中。

1.用户自定义的变量

两种类型：

1. 自定义变量
	&变量名				//每次遇到&变量，都要输入变量值
	例1.在select语句中使用两个变量
	select ename , sal from &table_name where ename='&'
	&&变量名				//第一次遇到需要输入，以后不再需要
2. 需要事先定义，且需要提供初值
	define 变量名 = 变量值

3. 删除变量
	undefine

例1.在select语句中使用两个变量

select ename , sal from &table_name where ename='&name'

例2. 使用变量在SQL*Plus中创建2个用户，初始化密码均为123456，并给用户赋予create session权限，并验证所有用户是否可以正常登陆使用。

create user &user identified by 123456 ；

例3. 显示当前已经使用define命令定义的变量与其变量值,使用define自定义两个变量，将‘SCOTT’变量值赋给name变量，将’tiger’变量值赋给psw变量，再次查看当前已经自定义的变量内容

define
define name = SCOTT
define psw = tiger

例4. 使用name变量和psw变量登陆oracle数据库b

conn &name/&psw

例5. 删除已经自定义变量name和psw

undefine name
undefine psw
//undefine命令用于取消一个变量的定义

2. 参数变量

参数变量不需要事前定义，可以直接使用。参数变量一般使用在脚本类的文本文件中，使用@、get将其调入缓冲区使用

格式：

@ 文件名 参数1 参数2 参数3 ...		//对应&1、&2、&3

例6. 在当前目录下创建脚本文件，名为test.sql，内容为：

select ename , job from emp where deptno = &1 and sal >&2;

例7. 在scott用户下，调用并执行test脚本，参数1设置为20，参数2为2000，查看最后执行结果。

@ test

3. 与变量有关的交互式命令

SQL*Plus还提供了几条交互式命令，主要有：

prompt			//用来在屏幕上显示指定的字符串，字符串不需要单引号限定
	prompt I‘m a programmer

accept			//用于接收用户的键盘输入，并把输入数据存放到指定的变量中
	accept 变量名 变量类型 prompt 提示信息 选项			//变量类型：数字型、字符型、日期型
											//选项包括：hide、default
pause			//使当前的执行暂时停止，在用户输入回车键后继续。

例8. 从键盘输入一个数字型数据到变量d，在输入之前显示指定的提示信息，并为变量指定默认值为 0 ，这样如果在输入数据时直接回车，那么变量的值就是默认值。

accept d number prompt pls_input _value_of_d: default 0 //prompt后面的字符串之间尽量不能有空格
//查看此变量d的值：define

例9. 交互式命令的用法,将下列文本写入当前目录下，名为：test.sql,试执行该脚本文件。

prompt 工资统计现在开始
accept dno number prompt 请输入部门号： default 0
pause 请输入回车键开始统计……
select ename ,sal from scott.emp where deptno = &dno

例10. 更新例9脚本功能，将输出结果生成一个报表文件，文件名为：output.lst

prompt 工资统计现在开始
accept dno number prompt 请输入部门号： default 0
pause 请输入回车键开始统计……
spool output
select ename ,sal from scott.emp where deptno = &dno
spool off