Oracle12C--替代变量（六）

知识点的梳理：

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,varchar或date型数据；
   
3. format格式：指定格式化模型，例如A10或9.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') ;