SQL*Plus 执行脚本时传递参数(@script_name var1,var2)

 在使用sqlplus执行sql脚本时，经常碰到向脚本传递参数的情形。类似于shell脚本的参数传递，我们同样可以向sql脚本传递参数，其方法是脚本后面直接跟多个连续的参数并以空格分开。本文描述该内容并给出示例。

1、SQLPlus 的帮助信息

[sql] view plain copy print ?

  下面的帮助信息是关于sqlplus调用带参脚本的说明
  sqlplus -H
  
  <start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]
  
      Runs the specified SQL*Plus script from a web server (URL) or the
      local file system (filename.ext) with specified parameters that
      will be assigned to substitution variables in the script.

2、shell 提示符下sqlplus调用带参脚本

[sql] view plain copy print ?

1. SQL> select * from v$version where rownum<2;  
2.   
3. BANNER  
4. --------------------------------------------------------------------------------  
5. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
6.   
7. SQL> insert into emp(empno,ename,job) select 8888,'Bob Cheng','DBA' from dual;  
8.   
9. SQL> commit;  
10.   
11. [oracle@linux1 ~]$ more test.sql   
12. set linesize 160  
13. select empno,ename,job from &1 where upper(ename)=upper('&2');  
14. exit;  
15.   
16. [oracle@linux1 ~]$ sqlplus scott/tiger@rac11g @test.sql emp scott  
17.   
18. old   1: select empno,ename,job from &1 where upper(ename)=upper('&2')  
19. new   1: select empno,ename,job from emp where upper(ename)=upper('scott')  
20.   
21.      EMPNO ENAME      JOB  
22. ---------- ---------- ---------   
23.       7788 SCOTT      ANALYST  
24.   
25. --注意，对于含有空格的调用需要使用单引号或双引号，如下示例  
26. [oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp 'bob cheng'    
27.   
28. old   1: select empno,ename,job from &1 where upper(ename)=upper('&2')  
29. new   1: select empno,ename,job from emp where upper(ename)=upper('bob cheng')  
30.   
31.      EMPNO ENAME      JOB  
32. ---------- ---------- ---------   
33.       8888 Bob Cheng  DBA  
34.   
35. [oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp "bob cheng"        

[sql] view plain copy print ?

1. SQL> select * from v$version where rownum<2;  
2.   
3. BANNER  
4. --------------------------------------------------------------------------------  
5. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
6.   
7. SQL> insert into emp(empno,ename,job) select 8888,'Bob Cheng','DBA' from dual;  
8.   
9. SQL> commit;  
10.   
11. [oracle@linux1 ~]$ more test.sql   
12. set linesize 160  
13. select empno,ename,job from &1 where upper(ename)=upper('&2');  
14. exit;  
15.   
16. [oracle@linux1 ~]$ sqlplus scott/tiger@rac11g @test.sql emp scott  
17.   
18. old   1: select empno,ename,job from &1 where upper(ename)=upper('&2')  
19. new   1: select empno,ename,job from emp where upper(ename)=upper('scott')  
20.   
21.      EMPNO ENAME      JOB  
22. ---------- ---------- ---------   
23.       7788 SCOTT      ANALYST  
24.   
25. --注意，对于含有空格的调用需要使用单引号或双引号，如下示例  
26. [oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp 'bob cheng'    
27.   
28. old   1: select empno,ename,job from &1 where upper(ename)=upper('&2')  
29. new   1: select empno,ename,job from emp where upper(ename)=upper('bob cheng')  
30.   
31.      EMPNO ENAME      JOB  
32. ---------- ---------- ---------   
33.       8888 Bob Cheng  DBA  
34.   
35. [oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp "bob cheng"        

SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> insert into emp(empno,ename,job) select 8888,'Bob Cheng','DBA' from dual;

SQL> commit;

[oracle@linux1 ~]$ more test.sql 
set linesize 160
select empno,ename,job from &1 where upper(ename)=upper('&2');
exit;

[oracle@linux1 ~]$ sqlplus scott/tiger@rac11g @test.sql emp scott

old   1: select empno,ename,job from &1 where upper(ename)=upper('&2')
new   1: select empno,ename,job from emp where upper(ename)=upper('scott')

     EMPNO ENAME      JOB
---------- ---------- ---------
      7788 SCOTT      ANALYST

--注意，对于含有空格的调用需要使用单引号或双引号，如下示例
[oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp 'bob cheng'  

old   1: select empno,ename,job from &1 where upper(ename)=upper('&2')
new   1: select empno,ename,job from emp where upper(ename)=upper('bob cheng')

     EMPNO ENAME      JOB
---------- ---------- ---------
      8888 Bob Cheng  DBA

[oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp "bob cheng"      

3、SQL提示符下调用带参脚本

[sql] view plain copy print ?

1. SQL> @test.sql emp scott  
2. SQL> set linesize 160  
3. SQL> select empno,ename,job from &1 where upper(ename)=upper('&2');  
4.   
5.      EMPNO ENAME      JOB  
6. ---------- ---------- ---------   
7.       7788 SCOTT      ANALYST  
8.   
9. SQL> exit;  -->由于脚本中有exit，因此脚本执行后会退出，如果是在SQL提示符下而不需要退出应注释该行  

[sql] view plain copy print ?

1. SQL> @test.sql emp scott  
2. SQL> set linesize 160  
3. SQL> select empno,ename,job from &1 where upper(ename)=upper('&2');  
4.   
5.      EMPNO ENAME      JOB  
6. ---------- ---------- ---------   
7.       7788 SCOTT      ANALYST  
8.   
9. SQL> exit;  -->由于脚本中有exit，因此脚本执行后会退出，如果是在SQL提示符下而不需要退出应注释该行  

SQL> @test.sql emp scott
SQL> set linesize 160
SQL> select empno,ename,job from &1 where upper(ename)=upper('&2');

     EMPNO ENAME      JOB
---------- ---------- ---------
      7788 SCOTT      ANALYST

SQL> exit;  -->由于脚本中有exit，因此脚本执行后会退出，如果是在SQL提示符下而不需要退出应注释该行

4、调用plsql时的带参脚本

[sql] view plain copy print ?

1. [oracle@linux1 ~]$ more test2.sql  
2. set serveroutput on;  
3. declare  
4.     v_sal  number;  
5.     v_emp  number:=&1;  
6. begin  
7.     select sal into v_sal from emp where empno=&1;  
8.     dbms_output.put_line('The sal is :'||to_char(v_sal));  
9. end;  
10. /  
11. exit;   
12.   
13. [oracle@linux1 ~]$ sqlplus scott/tiger @test2.sql 7788  
14.   
15. old   3:     v_emp  number:=&1;  
16. new   3:     v_emp  number:=7788;  
17. old   5:     select sal into v_sal from emp where empno=&1;  
18. new   5:     select sal into v_sal from emp where empno=7788;  
19. The sal is :3000  
20.   
21. PL/SQL procedure successfully completed.  

[sql] view plain copy print ?

1. [oracle@linux1 ~]$ more test2.sql  
2. set serveroutput on;  
3. declare  
4.     v_sal  number;  
5.     v_emp  number:=&1;  
6. begin  
7.     select sal into v_sal from emp where empno=&1;  
8.     dbms_output.put_line('The sal is :'||to_char(v_sal));  
9. end;  
10. /  
11. exit;   
12.   
13. [oracle@linux1 ~]$ sqlplus scott/tiger @test2.sql 7788  
14.   
15. old   3:     v_emp  number:=&1;  
16. new   3:     v_emp  number:=7788;  
17. old   5:     select sal into v_sal from emp where empno=&1;  
18. new   5:     select sal into v_sal from emp where empno=7788;  
19. The sal is :3000  
20.   
21. PL/SQL procedure successfully completed.  

[oracle@linux1 ~]$ more test2.sql
set serveroutput on;
declare
    v_sal  number;
    v_emp  number:=&1;
begin
    select sal into v_sal from emp where empno=&1;
    dbms_output.put_line('The sal is :'||to_char(v_sal));
end;
/
exit; 

[oracle@linux1 ~]$ sqlplus scott/tiger @test2.sql 7788

old   3:     v_emp  number:=&1;
new   3:     v_emp  number:=7788;
old   5:     select sal into v_sal from emp where empno=&1;
new   5:     select sal into v_sal from emp where empno=7788;
The sal is :3000

PL/SQL procedure successfully completed.

5、使用new_value子句

[sql] view plain copy print ?

1. SQL> col ename new_value v_name   -->new_value的实质是将得到的结果赋值给v_name以便后续调用  
2. SQL> select ename from emp where empno=7788;  
3.   
4. ENAME  
5. ----------   
6. SCOTT  
7.   
8. SQL> select ename,job,sal from emp where ename='&v_name';  
9. old   1: select ename,job,sal from emp where ename='&v_name'  
10. new   1: select ename,job,sal from emp where ename='SCOTT'  
11.   
12. ENAME      JOB              SAL  
13. ---------- --------- ----------  
14. SCOTT      ANALYST         3000  
15.   
16. --交互式方式使用new_value                      --Author : Robinson Cheng  
17. --下面的脚本是从awrrpti.sql摘取部分片段来展示     --Blog : http://blog.csdn.net/robinson_0612  
18. [oracle@linux1 ~]$ more temp.sql  
19. prompt  
20. prompt Specify the Report Type  
21. prompt ~~~~~~~~~~~~~~~~~~~~~~~  
22. prompt Would you like an HTML report, or a plain text report?  
23. prompt Enter 'html' for an HTML report, or 'text' for plain text  
24. prompt  Defaults to 'html'  
25.   
26. column report_type new_value report_type;    -->这个地方定义的new_value是关键  
27. set heading off;  
28. select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual;  
29. set heading on;  
30. prompt "Variable report_type value is :&report_type"  
31.   
32. SQL> @temp.sql  -->执行时出现交互提示  
33.   
34. Specify the Report Type  
35. ~~~~~~~~~~~~~~~~~~~~~~~  
36. Would you like an HTML report, or a plain text report?  
37. Enter 'html' for an HTML report, or 'text' for plain text  
38. Defaults to 'html'  
39. Enter value for report_type: html  
40. old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual  
41. new   1: select 'Type Specified: ',lower(nvl('html','html')) report_type from dual  
42.   
43. Type Specified:  html  
44.   
45. "Variable report_type value is :html"   -->变量的值传递给了report_type  

[sql] view plain copy print ?

1. SQL> col ename new_value v_name   -->new_value的实质是将得到的结果赋值给v_name以便后续调用  
2. SQL> select ename from emp where empno=7788;  
3.   
4. ENAME  
5. ----------   
6. SCOTT  
7.   
8. SQL> select ename,job,sal from emp where ename='&v_name';  
9. old   1: select ename,job,sal from emp where ename='&v_name'  
10. new   1: select ename,job,sal from emp where ename='SCOTT'  
11.   
12. ENAME      JOB              SAL  
13. ---------- --------- ----------  
14. SCOTT      ANALYST         3000  
15.   
16. --交互式方式使用new_value                      --Author : Robinson Cheng  
17. --下面的脚本是从awrrpti.sql摘取部分片段来展示     --Blog : http://blog.csdn.net/robinson_0612  
18. [oracle@linux1 ~]$ more temp.sql  
19. prompt  
20. prompt Specify the Report Type  
21. prompt ~~~~~~~~~~~~~~~~~~~~~~~  
22. prompt Would you like an HTML report, or a plain text report?  
23. prompt Enter 'html' for an HTML report, or 'text' for plain text  
24. prompt  Defaults to 'html'  
25.   
26. column report_type new_value report_type;    -->这个地方定义的new_value是关键  
27. set heading off;  
28. select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual;  
29. set heading on;  
30. prompt "Variable report_type value is :&report_type"  
31.   
32. SQL> @temp.sql  -->执行时出现交互提示  
33.   
34. Specify the Report Type  
35. ~~~~~~~~~~~~~~~~~~~~~~~  
36. Would you like an HTML report, or a plain text report?  
37. Enter 'html' for an HTML report, or 'text' for plain text  
38. Defaults to 'html'  
39. Enter value for report_type: html  
40. old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual  
41. new   1: select 'Type Specified: ',lower(nvl('html','html')) report_type from dual  
42.   
43. Type Specified:  html  
44.   
45. "Variable report_type value is :html"   -->变量的值传递给了report_type  

SQL> col ename new_value v_name   -->new_value的实质是将得到的结果赋值给v_name以便后续调用
SQL> select ename from emp where empno=7788;

ENAME
----------
SCOTT

SQL> select ename,job,sal from emp where ename='&v_name';
old   1: select ename,job,sal from emp where ename='&v_name'
new   1: select ename,job,sal from emp where ename='SCOTT'

ENAME      JOB              SAL
---------- --------- ----------
SCOTT      ANALYST         3000

--交互式方式使用new_value                      --Author : Robinson Cheng
--下面的脚本是从awrrpti.sql摘取部分片段来展示     --Blog : http://blog.csdn.net/robinson_0612
[oracle@linux1 ~]$ more temp.sql
prompt
prompt Specify the Report Type
prompt ~~~~~~~~~~~~~~~~~~~~~~~
prompt Would you like an HTML report, or a plain text report?
prompt Enter 'html' for an HTML report, or 'text' for plain text
prompt  Defaults to 'html'

column report_type new_value report_type;    -->这个地方定义的new_value是关键
set heading off;
select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual;
set heading on;
prompt "Variable report_type value is :&report_type"

SQL> @temp.sql  -->执行时出现交互提示

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new   1: select 'Type Specified: ',lower(nvl('html','html')) report_type from dual

Type Specified:  html

"Variable report_type value is :html"   -->变量的值传递给了report_type

6、new_value与&符号的综合运用

[sql] view plain copy print ?

1. --下面的这个脚本来自Tom大师，其中包含了new_value以及替代变量的使用  
2. --通过该例子来体会这两种方式   
3. [oracle@linux1 ~]$ more mystat.sql  
4. set echo off  
5. set verify off  
6. column value new_val V     -->此处使用了new_value方式来传递变量  
7. define S="&1"              -->此处定义了替代变量来接受参数  
8. set autotrace off  
9. select a.name, b.value  
10. from v$statname a, v$mystat b  
11. where a.statistic# = b.statistic#  
12. and lower(a.name) like '%' || lower('&S')||'%'  
13. /  
14. set echo on  
15.   
16. --下面是第二个脚本   
17. --在第二个脚本中引用到了前一个脚本定义的变量，如&S，&V。实际上&S是一个全局变量  
18. [oracle@linux1 ~]$ more mystat2.sql  
19. set echo off  
20. set verify off  
21. select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff  
22. from v$statname a, v$mystat b  
23. where a.statistic# = b.statistic#  
24. and lower(a.name) like '%' || lower('&S')||'%'  
25. /  
26. set echo on  
27.   
28. SQL> @mystat  
29. SQL> set echo off  
30. Enter value for 1: parse time elapsed  
31.   
32. NAME                                                        VALUE  
33. ------------------------------------------------------ ----------  
34. parse time elapsed                                             83  
35.   
36. SQL> update emp set sal=sal+200;  
37.   
38. 15 rows updated.  
39.   
40. SQL> @mystat2  
41. SQL> set echo off linesize 160  
42.   
43. NAME                                                            V DIFF  
44. ------------------------------------------------------ ---------- ----------------  
45. parse time elapsed                                            117               34  

[sql] view plain copy print ?

1. --下面的这个脚本来自Tom大师，其中包含了new_value以及替代变量的使用  
2. --通过该例子来体会这两种方式   
3. [oracle@linux1 ~]$ more mystat.sql  
4. set echo off  
5. set verify off  
6. column value new_val V     -->此处使用了new_value方式来传递变量  
7. define S="&1"              -->此处定义了替代变量来接受参数  
8. set autotrace off  
9. select a.name, b.value  
10. from v$statname a, v$mystat b  
11. where a.statistic# = b.statistic#  
12. and lower(a.name) like '%' || lower('&S')||'%'  
13. /  
14. set echo on  
15.   
16. --下面是第二个脚本   
17. --在第二个脚本中引用到了前一个脚本定义的变量，如&S，&V。实际上&S是一个全局变量  
18. [oracle@linux1 ~]$ more mystat2.sql  
19. set echo off  
20. set verify off  
21. select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff  
22. from v$statname a, v$mystat b  
23. where a.statistic# = b.statistic#  
24. and lower(a.name) like '%' || lower('&S')||'%'  
25. /  
26. set echo on  
27.   
28. SQL> @mystat  
29. SQL> set echo off  
30. Enter value for 1: parse time elapsed  
31.   
32. NAME                                                        VALUE  
33. ------------------------------------------------------ ----------  
34. parse time elapsed                                             83  
35.   
36. SQL> update emp set sal=sal+200;  
37.   
38. 15 rows updated.  
39.   
40. SQL> @mystat2  
41. SQL> set echo off linesize 160  
42.   
43. NAME                                                            V DIFF  
44. ------------------------------------------------------ ---------- ----------------  
45. parse time elapsed                                            117               34  

--下面的这个脚本来自Tom大师，其中包含了new_value以及替代变量的使用
--通过该例子来体会这两种方式
[oracle@linux1 ~]$ more mystat.sql
set echo off
set verify off
column value new_val V     -->此处使用了new_value方式来传递变量
define S="&1"              -->此处定义了替代变量来接受参数
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

--下面是第二个脚本
--在第二个脚本中引用到了前一个脚本定义的变量，如&S，&V。实际上&S是一个全局变量
[oracle@linux1 ~]$ more mystat2.sql
set echo off
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

SQL> @mystat
SQL> set echo off
Enter value for 1: parse time elapsed

NAME                                                        VALUE
------------------------------------------------------ ----------
parse time elapsed                                             83

SQL> update emp set sal=sal+200;

15 rows updated.

SQL> @mystat2
SQL> set echo off linesize 160

NAME                                                            V DIFF
------------------------------------------------------ ---------- ----------------
parse time elapsed                                            117               34

7、小结
a、关于SQLPlus执行脚本时的参数传递其实质是使用了替代变量&，可以参考:替代变量与SQL*Plus环境设置
b、new_value子句可以在脚本中传递变量，

通常的使用方法为:

    column column_name new_value var_name

 

    new_value是将所获得的列值赋予到变量名，然后该变量名可以参与后续处理

c、多个参数传递时，中间使用空格隔开

d、对于参数中本身含有空格的情形应使用引号将其括起来