Oracle12C--替代变量(六)

本文介绍了Oracle12C中替代变量的概念和用法,包括在SELECT、FROM、WHERE、ORDER BY和GROUP BY子句中的应用。通过示例展示了如何定义、使用和清除替代变量,以及如何使用ACCEPT指令提供更明确的用户输入提示。

知识点的梳理:

  1. accept命令只能在脚本文件中使用:需要使用"@"命令执行;
  2. accept替代变量不区分大小写;
  3. 替代变量需要用在查询或更新操作时,用户自行输入相应的数据,而这些数据前可以使用“&”标记;
  4. 使用define可以定义一个替代变量的内容,或者使用undefine清楚一个替代变量的内容;

替代变量基本概念

替代变量是指在进行查询或更新操作时,某些数据是由用户所输入的,而这些数据前可以使用"&"标记


入门示例

示例1:要求用户输入一个工资数据,而后查询比这个工资高的全部雇员姓名,基本工资,职位

SELECT ename,job,sal,hiredate 
FROM emp 
WHERE sal>&inputsal ;
效果:

示例2:查询一个雇员编号,姓名,职位,雇员日期,基本工资,查询

SELECT empno,ename,job,hiredate,sal FROM emp 
WHERE ename=&inputename ;

这次需要输入字符串,就有两个问题:
1.在根据姓名查询时,用户往往不会考虑单引号的问题;
2.用户在输入雇员姓名时,不会考虑大小写的问题

--修改sql语句,使用upper('&')解决单引号问题,upper函数自动变为大写:
SELECT empno,ename,job,hiredate,sal FROM emp WHERE ename=UPPER('&inputename') ;

示例3'%&%'实现模糊查询

SELECT empno,ename,job,hiredate,sal FROM emp 
WHERE ename LIKE '%&inputkeyword%' ;

示例4:使用to_date()函数将输入的字符串转换为date类型,日期格式要按照'yyyy-mm-dd'

SELECT empno,ename,job,hiredate,sal FROM emp WHERE hiredate<TO_DATE('&inputhiredate','yyyy-mm-dd') ;

示例5:输入多个查询数据

SELECT empno,ename,job,hiredate,sal FROM emp 
WHERE job=UPPER('&inputjob')
      AND sal>&inputsal ;

详细说明

替代变量可以出现在Sql语句的任何位置

select

select子句中出现的一般是要显示的数据列,此句出现的替代变量表示要查询的字段内容由用户所决定。

示例:在SELECT与where中设置了两个替代变量,主要功能是由用户输入要查询的列的名称,且由用户输入指定要查询的部门编号

SELECT &inputColumnName
FROM emp
WHERE deptno=&inputDeptno ;



from

示例:使用替代变量,来指定要查询的表名

SELECT * 
FROM &inputTableName ;



order by

示例:使用替代变量,来指定排序字段

SELECT empno,ename,job,hiredate,sal 
FROM emp
WHERE deptno=20
ORDER BY &inputOrderByColumn DESC ;

group by

示例:使用替代变量,来指定分组

SELECT &inputGroupByColumn ,SUM(sal) ,AVG(sal) 
FROM emp e
GROUP BY &inputGroupByColumn ;


在此操作中,select中只能出现分组字段和聚合函数,所以select里的替代变量要和groupby子句的替代变量输入一样的内容。这样输入两次不太方便,对sql语句做出如下改变

SELECT &&inputGroupByColumn ,SUM(sal) ,AVG(sal) 
FROM emp e
GROUP BY &inputGroupByColumn ;

替代变量失效的两种情况:
使用“
&&”就相当于在sqlplus中定义了一个变量,而这个变量失效的情况有以下两种情况:
1.一个sqlplus窗口关闭后;
2.调用了undefine命令;

取消inputGroupByColumn这个变量:执行完这句sql后,就可以再次使用"&&"sql语句,重新赋值了
UNDEFINE inputGroupByColumn
;


定义替代变量

define

用户可以使用该命令创建一个字符型的替代变量,这种方式定义的替代变量会一直保存到一个session的操作结束或是遇到undefine清楚变量

语法:

define 替代变量名称=;

示例1:定义一个替代变量inputdname

DEFINE inputdname='ACCOUNTING'

示例2:查询示例1的替代变量

DEFINE inputdname ;

示例3:使用define定义的替代变量,此时的程序不再提示用户输入变量,只要变量的名称和定义的名称相同,即可直接使用define定义的变量内容

SELECT * FROM dept WHERE dname='&inputdname' ;

undefine语法:

UNDEFINE 替代变量名称;

示例1:清除inputdname替代变量内容之后,在使用此替代变量的sql,就需要用户自己输入了

UNDEFINE inputdname ;

accept指令

作用:

1.上面两种方式创建的替代变量提示信息不够明确;

2.accept指令可以指定替代变量的提示信息;

语法:

accept替代变量名称 [数据名称] [format 格式] [prompt '提示信息'] [hide]

参数作用:

  1. 替代变量名称:存储值的变量名称,如果该变量不存在,则有sqlplus创建该变量,但是在定义该替代变量名称前不能加上'&';
  2. 数据类型:可以是number,varchardate型数据;
  3. format格式:指定格式化模型,例如A109.99;
  4. prompt提示信息:用户输入替代变量时的提示信息;
  5. hide:隐藏输入内容,例如输入密码;

示例1:定义脚本文件使用ACCEPT指令,要把这段代码以脚本形式存放。此示例将这段sql保存到名为demo1.sql的文件里

--脚本内容:
ACCEPT inputEname PROMPT '请输入要查询信息的雇员姓名:'
SELECT empno,ename,job,hiredate,sal FROM emp 
WHERE ename=UPPER('&inputename') ;


示例2:

--脚本内容:
ACCEPT inputGroupByColumn PROMPT '请输入要分组的字段:'
SELECT &&inputGroupByColumn ,SUM(sal) ,AVG(sal) 
FROM emp e
GROUP BY &inputGroupByColumn ;



示例3:hide,隐藏输入的内容

--脚本内容:
ACCEPT inputGroupByColumn PROMPT '请输入要分组的字段:' HIDE
SELECT &&inputGroupByColumn ,SUM(sal) ,AVG(sal) 
FROM emp e
GROUP BY &inputGroupByColumn ;


示例4:使用FORMAT限定输入的数据长度

--脚本内容:
ACCEPT inputGroupByColumn PROMPT '请输入要分组的字段:' FORMAT A10
SELECT &&inputGroupByColumn ,SUM(sal) ,AVG(sal) 
FROM emp e
GROUP BY &inputGroupByColumn ;


示例5:使用FORMAT格式化日期输入

--脚本内容:
ACCEPT inputDate DATE FORMAT 'YYYY-MM-DD' PROMPT '请输入要查询的雇佣日期:' 
SELECT empno , ename , job , hiredate
FROM emp e
WHERE hiredate=TO_DATE('&inputDate','YYYY-MM-DD') ;



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值