专栏首页乐沙弥的世界SQL*Plus 执行脚本时传递参数(@script_name var1,var2)

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

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

1、SQLPlus 的帮助信息

  下面的帮助信息是关于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> 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> @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时的带参脚本

[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> 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与&符号的综合运用

--下面的这个脚本来自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子句可以在脚本中传递变量,可以参考:SQL 基础--> NEW_VALUE 的使用 c、多个参数传递时,中间使用空格隔开 d、对于参数中本身含有空格的情形应使用引号将其括起来

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 今天我就说三句话

    腾讯NEXT学位
  • 白底黑字or黑底白字,眼睛更喜欢哪一个?

    腾讯大讲堂
  • 【系统设置】CentOS 修改机器名

    ken.io
  • 穿越十年后看互联网+:家电行业的金矿在哪里?

    现在市场上炒得火热的智能家居未来出路在何方?做智能家居的创业者应该注意哪些机会?传统家电厂商又到底如何借助互联网进行转型?本文以智能空调为例,用故事的形式,提前...

    华章科技
  • SQL中GROUP BY用法示例

    GROUP BY我们可以先从字面上来理解,GROUP表示分组,BY后面写字段名,就表示根据哪个字段进行分组,如果有用Excel比较多的话,GROUP BY比较类...

    Awesome_Tang
  • 你可以从面试中学到什么?

    讲一下我对面试的一些。。。“偏见”,哈哈,熟悉我的同学们一定要批判的读接下来的内容哈。

    web前端教室
  • 「我真的没有改需求」

    非著名程序员
  • 复杂业务下向Mysql导入30万条数据代码优化的踩坑记录

    从毕业到现在第一次接触到超过30万条数据导入MySQL的场景(有点low),就是在顺丰公司接入我司EMM产品时需要将AD中的员工数据导入MySQL中,因此楼主负...

    haifeiWu
  • 知识体系解决迷茫的你

    最近在星球里群里都有小伙伴说道自己对未来的路比较迷茫,一旦闲下来就不知道自己改干啥,今天我这篇文章就是让你觉得一天给你 25 个小时你都不够用,觉得睡觉都是浪费...

    桃翁
  • 这是对付产品经理的一副毒药,程序员慎入

    程序员和产品经理的日常就像是一对天生的冤家,为了需求的实现,几乎天天在争吵。这不,就在昨天各大技术和产品群里一个程序员暴打产品经理的视频火了,被广泛传播。

    非著名程序员

扫码关注云+社区

领取腾讯云代金券