10替代变量
<h1>替代变量</h1>
<h2>替代变量的基本概念</h2>
<p><strong>替代变量：</strong>某些数据由用户输入，前面用&标记。
示例：</p>
<pre><code>select ename,job,sal,hiredate from emp where sal>&inputsal;
select empno,ename,job,hiredate,sal from emp where ename=&inputname;</code></pre>
<p><strong>问题：</strong>用户输入是不会考虑单引号与大小写问题的，优化：</p>
<pre><code>select empno,ename,job,hiredate,sal from emp where ename=upper('&inputname');
select empno,ename,job,hiredate,sal from emp where ename like upper('%&inputname%');
select empno,ename,job,hiredate,sal from emp where hiredate <to_date('&inputhiredate','yyyy-mm-dd');
select empno,ename,job,hiredate,sal from emp where hiredate <'&inputhiredate';
select ename,job,hiredate,sal from emp where job=upper('&inputjob') and sal>&inputsal;--输入两个值</code></pre>
<h2>替代变量的详细说明</h2>
<ul>
<li>输入两个值</li>
<li>在select、from、order by、group by 中使用替代变量</li>
</ul>
<pre><code>select &inputColumnName from emp where deptno=&inputDeptno ;
select * from &inputTabel ;
select * from emp where deptno=20 order by &inputOrderByColumn &inputtype ;
select &inputGroupByColumn,sum(sal),avg(sal) from emp group by &inputGroupByColumn ;--在SQLPlus中是要输入两次的，使用下一个方法就可以只输入一次
select &&inputGroupByColumn,sum(sal),avg(sal) from emp group by &inputGroupByColumn ;--这样就只输入一次，但是上一次输入会保存，要清除</code></pre>
<ul>
<li>清除前面存储的替代变量的值有两种方法
关闭SQLPlus窗口
调用UNDEFINE命令，`UNDEFINE inputGroupByColumn</li>
</ul>
<h2>定义替代变量</h2>
<ul>
<li>利用<code>DEFINE 替代变量名=替代变量值</code>定义一个变量</li>
<li>变量一经定义，一直保存到SESSION结束或者遇到UNDEFINE清除变量。</li>
</ul>
<pre><code>define inputdname='FORD' --定义替代变量
select * from emp where ename='&inputdname';--直接使用定义的替代变量，无须再手动输入
define inputdname --查询定义的替代变量值
undefine inputdname --清除定义的替代变量值</code></pre>
<h2>ACCEPT命令</h2>
<ul>
<li>用于指定输入变量值的提示语</li>
<li>accept的语句比较复杂，p237</li>
<li>相关内容要在文件中才能实现</li>
<li>使用accept命令定义替代变量不区分大小写</li>
</ul>
<p><strong>创建文件：</strong> 创建文件<code>vi /tmp/testaccept/mldn.sql</code>，内容如下</p>
<pre><code>accept inputename prompt '请输入要查询信息的雇员姓名:'
select * from emp where ename=upper('&inputenamd');</code></pre>
<p><strong>执行文件：</strong></p>
<pre><code>SQL> @/tmp/testaccept/mldn.sql
--输出内容如下，疑问是中文提示后要回车再输入内容
请输入要查询信息的雇员姓名:
Enter value for inputenamd: ford
old 1: select * from emp where ename=upper('&inputenamd')
new 1: select * from emp where ename=upper('ford')
--后输入查询的记录</code></pre>
<ul>
<li>跟上面方法一样的执行，即可看到效果，这是明文显示，下面看隐藏显示</li>
</ul>
<pre><code>accept inputGroupByColumn prompt '请输入分组的字段:'
select &&inputGroupByColumn,sum(sal),avg(sal) from emp group by &inputGroupByCol
umn ;</code></pre>
<ul>
<li>只是输入的时候看不到输入的值</li>
</ul>
<pre><code>accept inputGroupByColumn prompt '请输入分组的字段:' hide
select &&inputGroupByColumn,sum(sal),avg(sal) from emp group by &inputGroupByCol
umn ;</code></pre>
<ul>
<li>限定输入的值，指定最多只能输入10个字符</li>
</ul>
<pre><code>accept inputGroupByColumn prompt '请输入分组的字段:' format A10
select &&inputGroupByColumn,sum(sal),avg(sal) from emp group by &inputGroupByCol
umn ;</code></pre>
<ul>
<li>输入字符不符合，重新输入</li>
</ul>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/4748729e62892ab289b614d656ed6596?showdoc=.jpg" alt="" /></p>
<ul>
<li>指定输入的格式</li>
</ul>
<pre><code>accept inputDate date format 'yyyy-mm-dd' prompt '请输入符合格式的日期:'
select ename,job,hiredate,sal from emp where hiredate>to_date('&inputDate','yyyy
-mm-dd');</code></pre>
<ul>
<li>示例：</li>
</ul>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/47d6b02f421548bb4be5334f91699cc8?showdoc=.jpg" alt="" /></p>
<h2>小结</h2>
<ul>
<li>执行查询或者更新操作，想要让用户输入值，可以使用替代变量，用<code>&</code>标记.</li>
<li>使用<code>DEFINE</code>自定义替代变量的值，用<code>UNDEFINE</code>可以清除自定义的变量。</li>
<li>使用<code>ACCEPT</code>可以定义输入变量的提示信息。但是要使用执行脚本去实现。</li>
</ul>