oracle中关于替代变量,accpt，绑定变量，字符变量

此文档介绍两个事情，一个是替代变量，另一个就是了解一下硬解析和软解析对于变量来说declare定义的好还是variable定义的好
在oracle中，对于一个提交的sql语句,存在两种可选的解析过程,一种叫做硬解析,一种叫做软解析.一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（sharedpool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。
而唯一使得oracle能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。
连接
前两天看到有人在pub上问在sqlplus中通过define和variable定义的变量的区别。其实define定义的我

理解不是变量而是字符常量，通过define定义之后，在通过&或者&&引用的时候不需要输入了，仅此而已。

oracle在执行的时候自动用值进行了替换；而variable定义的是绑定变量,上面已经提到。



绑定变量引用的时候用":",替代变量引用的时候用"&";
绑定变量初始化exec:num1:=2,替代变量默认类型为char

替换变量（仅用于SQL*Plus或者用于原理和SQL*Plus相同的开发工具）：
临时存储值
利用它可以达到创建通用脚本的目的
利用它可以达到和用户交互，故在SQL*Plus中又称交互式命令

替换变量的格式式在变量名称前加一个&，以便在运行SQL命令时提示用户输入替换数据，然后按输入数据运行SQL命令
语法：
（1）&：“&变量名”eg：&name;
生命周期：单次引用中，不需要声明，如果替换字符或日期类型，最好用单引号扩起
使用范围：where、orderby、列表达式、表名、整个SELECT语句中
www.2cto.com
（2）&&：“&&变量名”eg：&&name;
生命周期：整个会话（session连接）,不需要声明

（3）define：“define变量名=变量值”eg：DEFINEa=clark;
生命周期：整个会话，预先声明，使用时用&引用声明的变量
definevariable=用户创建的CHAR类型的值：define变量名=值；
define变量名：查看变量命令。
undefine变量名：清除变量
define：查看在当前会话中所有的替换变量和它们的值

（4）accept

生命周期：整个会话
预先声明，可以客户化提示信息，使用时用&引用声明的变量。
定义：
accept变量名namenumber/char/dateprompt'提示信息内容'即：ACC[EPT]variable
[NUM[BER]|CHAR|DATE][FOR[MAT]format][DEF[AULT]default][PROMPT
text|NOPR[OMPT]][HIDE]
解释：
PROMPT命令：用于输出提示用户的信息，以便使用户了解脚本文件的功能和运行情况
PAUSE命令：用于暂停脚本文件的运行
HIDE选项：用于隐藏用户的输入，使别人不可见，安全
这条命令的意思是：当plsql程序段执行到变量name的时候，此时需要用户的交互才能继续执行下去，plsql程序段会显示“提示信息内容”让用户输入相关信息（如果指定hide选项，那么在接下去用户输入的东西将被用星号显示出来增加安全，有点像输入密码），用户输入的内容被接收到并且把它付给name，关于在“提示信息内容”下用户输入的内容的类型，plsql程序段开发人员来通过number/char/date指定，变量name得到正确的值以后，继续执行相关下面的程序！
例：acceptacharprompt'请输入员工的雇佣时间(yyyy-mm-dd):'hide
例：acceptacharprompt'inputa:'hide
www.2cto.com
setverifyon/off;#verify：是否给出原值及新值提示。

具体请参看下面的例子：
plsql程序1：
[sql]
declare
v_salnumber(6,2);
v_enameemp.ename%type:='&ename';
begin
selectsalintov_salfromemp
wherelower(ename)=lower(v_ename);
ifv_sal<2000then
updateempsetsal=v_sal+200
wherelower(ename)=lower(v_ename);
endif;
end;

/
plsql程序2：
[sql]
declare
v_salnumber(6,2);
v_enameemp.ename%type:='&&ename';
begin
selectsalintov_salfromemp
wherelower(ename)=lower(v_ename);
ifv_sal<2000then
updateempsetsal=v_sal+200
wherelower(ename)=lower(v_ename);
endif;www.2cto.com
end;

/
secureCRT的一个会话中先执行程序2，再次执行程序1，会发现直接PL/SQLproceduresuccessfullycompleted.
而不让我输入ename，将setverifyoff也不行
另一个打开会话将setverifyoff后，每次执行程序1都会让你输入ename。
这就是在前面一个会话执行程序2的时候已经将ename，保存为了会话的变量，而不是plsql程序的变量。


另外一个案例完整的accept例子


CREATETABLEEMP(EMPNONUMBER(4)NOTNULL,ENAMEVARCHAR2(10),JOBVARCHAR2(9),MGRNUMBER(4),HIREDATEDATE,SALNUMBER(7,2),COMMNUMBER(7,DEPTNONUMBER(2));

INSERTINTOEMPVALUES(7369,'SMITH','CLERK',7902,TO_DATE('23-12-2013','DD-MM-YYYY'),800,NULL,20);
INSERTINTOEMPVALUES(7499,'ALLEN','SALESMAN',7698,TO_DATE('20-02-1981',1600,300,30);
INSERTINTOEMPVALUES(7521,'WARD',TO_DATE('22-02-1981',1250,500,30);
INSERTINTOEMPVALUES(7566,'JONES','MANAGER',7839,TO_DATE('22-04-1981',2975,20);
INSERTINTOEMPVALUES(7654,'MARTIN',TO_DATE('28-09-1981',1400,30);
INSERTINTOEMPVALUES(7698,'BLAKE',TO_DATE('01-03-1981',2850,30);
INSERTINTOEMPVALUES(7782,'CLARK',TO_DATE('09-05-1981',2450,10);
INSERTINTOEMPVALUES(7788,'SCOTT','ANALYST',7566,TO_DATE('09-12-1982',3000,20);
INSERTINTOEMPVALUES(7839,'KING','PRESIDENT',TO_DATE('17-11-1981',5000,10);
INSERTINTOEMPVALUES(7844,'TURNER',TO_DATE('08-09-1981',1500,30);
INSERTINTOEMPVALUES(7876,'ADAMS',7788,TO_DATE('12-06-1983',1100,20);
INSERTINTOEMPVALUES(7900,'JAMES',TO_DATE('13-12-1981',950,30);
INSERTINTOEMPVALUES(7902,'FORD',20);
INSERTINTOEMPVALUES(7934,'MILLER',7782,TO_DATE('23-03-1982',1300,10);



promptCREATENEWEMPLOYEERECORD
prompt
promptEntertheemployee'sinformation:
prompt
acceptl_enamecharformata10prompt'名字:'
acceptl_empnonumberformat'9999'prompt'编号#:'
acceptl_salnumberformat'99999.99'prompt'Salary[1000]:'default'1000.00'
acceptl_commnumberformat'99999.99'prompt'Commission%[0]:'default'0'
acceptl_hireddateformat'mm/dd/yyyy'prompt'Hiredate(mm/dd/yyyy):'

promptListofavailablejobs:
selectdistinctjob
fromemp
orderbyjob
/
acceptl_jobcharformata9prompt'Job:'

promptListofmanagersandemployeenumbers:
selectempno,ename
fromemp
orderbyename
/
acceptl_mgrnumberformat'9999'prompt'Manager''sEmployee#:'

promptListofdepartmentnumbersandnames:
selectdeptno,dname
fromdept
orderbydeptno
/
acceptl_deptnumberformat'99'prompt'Department#:'

insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(&l_empno,'&l_ename','&l_job',&l_mgr,to_date('&l_hired','mm/dd/yyyy'),&l_sal,&l_comm,&l_dept)
/
select*fromempwhereempno=&l_empno
/

droptableemp;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!