使用绑定变量的一点总结!.docx

二、绑定变量的使用1、在SQLPlus中使用绑定变量SQL variable eno number; -使用variable定义变量SQL exec :eno:=7788;SQL select ename,job,sal from emp where empno=:eno; ENAME JOB SAL - - - SCOTT ANALYST 3000 2、PL/SQL块中使用绑定变量下面的pl/sql代码中，Oracle实现自动变量自动绑定，执行了30次的insert操作，但oracle认为每次执行的语句都是一样的/*/* Author: Robinson Cheng */* Blog: /robinson_0612 */* MSN: robinson_0612 */* QQ: 645746311 */*/SQL begin -执行pl/sql代码，向表t中插入30条记录 2 for i in 1.30 loop 3 insert into t values(i,i*2); 4 end loop; 5 commit; 6 end; 7 / PL/SQL procedure successfully completed.3、在存储过程或包中使用绑定变量-存储过程和保重，对参数的传递即是使用自动绑定变量来实现，因此编程人员无须操心绑定变量问题，如下例所示：SQL create or replace procedure ins_t(p_id in number,p_value in number) -创建一个过程用于向表t插入记录 2 as 3 begin 4 insert into t values(p_id,p_value); 5 commit; 6 end; 7 / Procedure created. 4、在动态SQL中是使用绑定变量-动态SQL中不能自动使用绑定变量，需要手动设定绑定变量SQL begin 2 for i in 1.30 loop 3 execute immediate insert into t values(:1,:2) using i,i+i-2; -动态SQL使用绑定变量，该语句将执行30次 4 end loop; 5 commit; 6 end; 7 / PL/SQL procedure successfully completed.ORACLE 绑定变量用法总结分类：Oracle Dev2009-11-06 21:3114726人阅读评论(4)收藏举报oraclesqlparsingstringinsertapplication之前对ORACLE中的变量一直没个太清楚的认识，比如说使用：、&、&、DEIFINE、VARIABLE等等。今天正好闲下来，上网搜了搜相关的文章，汇总了一下，贴在这里，方便学习。=在oracle中，对于一个提交的sql语句,存在两种可选的解析过程,一种叫做硬解析,一种叫做软解析.一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。而唯一使得oracle能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。1.sqlplus中如何使用绑定变量，可以通过variable来定义c-sharpview plaincopy1. SQLselect*fromttwhereid=1;2. 3. IDNAME4. -5. 1test6. 7. SQLselect*fromttwhereid=2;8. 9. IDNAME10. -11. 2test12. 13. SQLvariableinumber;14. SQLexec:i:=1;15. 16. PL/SQL过程已成功完成。17. 18. SQLselect*fromttwhereid=:i;19. 20. IDNAME21. -22. 1test23. 24. SQLexec:i:=2;25. 26. PL/SQL过程已成功完成。27. 28. SQLselect*fromttwhereid=:i;29. 30. IDNAME31. -32. 2test33. 34. SQLprinti;35. 36. I37. -38. 239. 40. SQLselectsql_text,parse_callsfromv$sqlwheresql_textlikeselect*fromt41. twhereid=%;42. 43. SQL_TEXTPARSE_CALLS44. -45. select*fromttwhereid=2146. select*fromttwhereid=1147. select*fromttwhereid=:i248. SQL从上面试验发现绑定变量i的使用使查询id=1和id=2的sqlselect *from tt where id=:i得以重复使用，从而避免了hard parse，这里的PARSE_CALLS2包括了一次soft parse2.前两天看到有人在pub上问在sqlplus中通过define和variable定义的变量的区别。其实define定义的我理解不是变量而是字符常量，通过define定义之后，在通过&或者&引用的时候不需要输入了，仅此而已。oracle在执行的时候自动用值进行了替换；而variable定义的是绑定变量,上面已经提到。c-sharpview plaincopy1. C:sqlplusxys/manager2. SQL*Plus:Release.0-Productionon星期二4月114:03:0020083. Copyright(c)1982,2007,Oracle.Allrightsreserved.4. 5. 连接到:6. OracleDatabase11gEnterpriseEditionRelease.0-Production7. WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions8. SQLdefine9. DEFINE_DATE=01-4月-08(CHAR)10. DEFINE_CONNECT_IDENTIFIER=db11(CHAR)11. DEFINE_USER=XYS(CHAR)12. DEFINE_PRIVILEGE=(CHAR)13. DEFINE_SQLPLUS_RELEASE=1101000600(CHAR)14. DEFINE_EDITOR=Notepad(CHAR)15. DEFINE_O_VERSION=OracleDatabase11gEnterpriseEditionRelease6. 6.0-Production17. WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions(18. CHAR)19. DEFINE_O_RELEASE=1101000600(CHAR)20. SQLselect*fromtt;21. IDNAME22. -23. 1a24. 2a25. 3abc26. SQLdefinea27. SP2-0135:符号a未定义28. SQLdefinea=129. SQLdefine30. DEFINE_DATE=01-4月-08(CHAR)31. DEFINE_CONNECT_IDENTIFIER=db11(CHAR)32. DEFINE_USER=XYS(CHAR)33. DEFINE_PRIVILEGE=(CHAR)34. DEFINE_SQLPLUS_RELEASE=1101000600(CHAR)35. DEFINE_EDITOR=Notepad(CHAR)36. DEFINE_O_VERSION=OracleDatabase11gEnterpriseEditionRelease7. 6.0-Production38. WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions(CHAR)39. DEFINE_O_RELEASE=1101000600(CHAR)40. DEFINEA=1(CHAR)41. -通过上面显示define定义的应该是字符（串）常量。42. SQLselect*fromttwhereid=&a;43. 原值1:select*fromttwhereid=&a44. 新值1:select*fromttwhereid=145. IDNAME46. -47. 1a48. SQLselect*fromttwhereid=&a;49. 原值1:select*fromttwhereid=&a50. 新值1:select*fromttwhereid=151. IDNAME52. -53. 1a54. SQLdefineb=a;55. SQLdefine56. DEFINE_DATE=01-4月-08(CHAR)57. DEFINE_CONNECT_IDENTIFIER=db11(CHAR)58. DEFINE_USER=XYS(CHAR)59. DEFINE_PRIVILEGE=(CHAR)60. DEFINE_SQLPLUS_RELEASE=1101000600(CHAR)61. DEFINE_EDITOR=Notepad(CHAR)62. DEFINE_O_VERSION=OracleDatabase11gEnterpriseEditionRelease3. 6.0-Production64. WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions(65. CHAR)66. DEFINE_O_RELEASE=1101000600(CHAR)67. DEFINEA=1(CHAR)68. DEFINEB=a(CHAR)69. 70. -如果是字符类型那么在引用时别忘了加上单引号，另外通过define定义之后在引用时不需要输入了。71. SQLselect*fromttwherename=&b;72. 原值1:select*fromttwherename=&b73. 新值1:select*fromttwherename=a74. select*fromttwherename=a75. *76. 第1行出现错误:77. ORA-00904:A:标识符无效78. 79. SQLselect*fromttwherename=&b;80. 原值1:select*fromttwherename=&b81. 新值1:select*fromttwherename=a82. IDNAME83. -84. 1a85. 2a86. SQLselect*fromttwherename=&b;87. 原值1:select*fromttwherename=&b88. 新值1:select*fromttwherename=a89. IDNAME90. -91. 1a92. 2a93. -执行sql时进行了替换94. SQLselectsql_textfromv$sqlwheresql_textlikeselect*fromttwherename95. =%;96. SQL_TEXT97. -98. select*fromttwherename=199. select*fromttwherename=a100. SQL3.oracle在解析sql时会把plsql中定义的变量转为为绑定变量c-sharpview plaincopy1. SQLcreatetablett(idint,namevarchar2(10);2. 3. 表已创建。4. 5. SQLaltersessionsetsql_trace=true;6. 7. 会话已更改。8. 9. SQLdeclare10. 2begin11. 3foriin1.100loop12. 4insertintottvalues(i,test);13. 5endloop;14. 6commit;15. 7end;16. 8/17. 18. PL/SQL过程已成功完成。19. 20. SQLaltersessionsetsql_trace=false;21. -tracefile:22. =23. PARSINGINCURSOR#3len=90dep=0uid=31oct=47lid=31tim=7109565004hv=96225923924. 25. ad=668ec52826. declare27. begin28. foriin1.100loop29. insertintottvalues(i,test);30. endloop;31. commit;32. end;33. ENDOFSTMT34. PARSE#3:c=15625,e=5678,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,tim=710956499635. =36. PARSINGINCURSOR#5len=34dep=1uid=31oct=2lid=31tim=7109565520hv=129922687637. 38. ad=6686993439. INSERTINTOTTVALUES(:B1,test)40. ENDOFSTMT41. PARSE#5:c=0,e=226,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=710956551342. =另外从hard parse的数据量上其实也可以大致猜测oracle会把plsql中定义的变量转换为绑定变量处理c-sharpview plaincopy1. SQLconnect/assysdba2. 已连接。3. SQLshutdownimmediate4. 数据库已经关闭。5. 已经卸载数据库。6. ORACLE例程已经关闭。7. SQLstartup8. ORACLE例程已经启动。9. 10. TotalSystemGlobalArea167772160bytes11. FixedSize1247900bytes12. VariableSize67110244bytes13. DatabaseBuffers96468992bytes14. RedoBuffers2945024bytes15. 数据库装载完毕。16. 数据库已经打开。17. SQLconnectxys/manager18. 已连接。19. SQLdroptablett;20. 21. 表已删除。22. 23. SQLcreatetablett(idint,namevarchar2(10);24. 25. 表已创建。26. SQLcolnameformata3027. SQLselecta.*,28. 2fromv$sesstata,v$statnameb29. 3wherea.statistic#=b.statistic#30. 4anda.sid=(selectdistinctsidfromv$mystat)31. 5like%parse%;32. 33. SIDSTATISTIC#VALUENAME34. -35. 15932839parsetimecpu36. 15932974parsetimeelapsed37. 159330339parsecount(total)38. 159331165parsecount(hard)39. 1593320parsecount(failures)40. 41. SQLdeclare42. 2begin43. 3foriin1.100loop44. 4insertintottvalues(i,test);45. 5endloop;46. 6commit;47. 7end;48. 8/49. 50. PL/SQL过程已成功完成。51. 52. SQLselecta.*,53. 2fromv$sesstata,v$statnameb54. 3wherea.statistic#=b.statistic#55. 4anda.sid=(selectdistinctsidfromv$mystat)56. 5like%parse%57. 6/58. 59. SIDSTATISTIC#VALUENAME60. -61. 15932839parsetimecpu62. 15932974parsetimeelapsed63. 159330345parsecount(total)64. 159331167parsecount(hard)65. 1593320parsecount(failures)66. 67. SQL这里发现hard parse只增加了2，如果没有使用绑定变量的话，相信hard parse会更多4.过程中的参数会自动转化为绑定变量c-sharpview plaincopy1. SQLedit2. 已写入fileafiedt.buf3. 4. 1createorreplaceprocedureproc_test(p_idint,p_namevarchar2)5. 2is6. 3begin7. 4insertintottvalues(p_id,p_name);8. 5commit;9. 6*end;10. SQL/11. 12. 过程已创建。13. 14. SQLaltersessionsetsql_trace=true;15. 16. 会话已更改。17. 18. SQLexecproc_test(200,test);19. 20. PL/SQL过程已成功完成。21. 22. SQLaltersessionsetsql_trace=false;23. 24. 会话已更改。25. -tracefile：26. altersessionsetsql_trace=true27. ENDOFSTMT28. EXEC#3:c=0,e=749,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=739390848729. =30. PARSINGINCURSOR#1len=35dep=0uid=31oct=47lid=31tim=7403000735hv=52648477631. 32. ad=6687b0b833. BEGINproc_test(200,test);END;34. ENDOFSTMT35. PARSE#1:c=0,e=2584,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=740300072736. =37. PARSINGINCURSOR#6len=33dep=1uid=31oct=2lid=31tim=7403001293hv=287474822938. 39. ad=668e9cd840. INSERTINTOTTVALUES(:B2,:B1)41. ENDOFSTMT42. PARSE#6:c=0,e=246,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=740300128643. =另外也可以直观的观察：c-sharpview plaincopy1. SQLexecproc_test(200,test);2. 3. PL/SQL过程已成功完成。4. 5. SQLselectsql_textfromv$sqlwheresql_textlike%proc_test%;6. 7. SQL_TEXT8. -9. BEGINproc_test(200,test);END;10. 11. SQL在sqlplus里执行过程不能观察出来下面在plsql developer执行一次过程之后再来看执行的情况c-sharpview plaincopy1. SQLselectsql_textfromv$sqlwheresql_textlike%proc_test%;2. 3. SQL_TEXT4. -5. begin-Calltheprocedureproc_test(p_id=:p_id,p_name=:p_name);end;6. 7. SQL很显然oracle在执行过程时把参数转化为绑定变量了，其实从plsql developer中执行过程时的语法就能看出来：c-sharpview plaincopy1. begin2. -Calltheprocedure3. proc_test(p_id=:p_id,4. p_name=:p_name);5. end;在输入参数列表框上面的执行语法就是这样的。5.在动态sql中使用绑定变量，动态sql中使用绑定变量非常明显也容易理解，下面给出2个简单的例子c-sharpview plaincopy1. SQLsetserveroutputon2. SQLdeclare3. 2v_stringvarchar2(100);4. 3v_idtt.id%type;5. 4v_%type;6. 5begin7. 6v_string:=select*fromttwhereid=:v_id;8. 7executeimmediatev_stringintov_id,v_nameusing&a;9. 8dbms_output.put_line(v_id|v_name);10. 9end;11. 10/12. 输入a的值:113. 原值7:executeimmediatev_stringintov_id,v_nameusing&a;14. 新值7:executeimmediatev_stringintov_id,v_nameusing1;15. 1test16. 17. PL/SQL过程已成功完成。18. 19. SQLdeclare20. 2v_stringvarchar2(100);21. 3v_idtt.id%type;22. 4v_%type;23. 5begin24. 6v_string:=insertintottvalues(:id,:name);25. 7executeimmediatev_stringusing&id,&name;26. 8end;27. 9/28. 输入id的值:100029. 输入name的值:test30. 原值7:executeimmediatev_stringusing&id,&name;31. 新值7:executeimmediatev_stringusing1000,test;32. 33. PL/SQL过程已成功完成。34. 35. SQLselect*fromttwhereid=1000;36. 37. IDNAME38. -39. 1000test40. 41. SQL=下面加上一些其他变量的使用方法=eg001（&替换变量）c-sharpview plaincopy1. SQLselectxh,xmfromsystem.xswherezym=&zym;2. 输入zym的值:计算机3. 原值1:selectxh,xmfromsystem.xswherezym=&zym4. 新值1:selectxh,xmfromsystem.xswherezym=计算机5. 6. XHXM7. -8. 061101王林9. 061102程明10. 061103王燕11. 061104韦严平12. 0