Oracle数据库的绑定变量特性及应用讲课讲稿

1、Good is good, but better carries it.精益求精，善益求善。Oracle数据库的绑定变量特性及应用-Oracle数据库的绑定变量特性及应用在开发一个数据库系统前,有谁对Oracle系统了解很多,尤其是它的特性,好象很少吧;对初学者来讲,这更是不可能的事情;仅仅简单掌握了SQL的写法,就开始了数据库的开发,其结果只能是开发一个没有效率,也没有可扩展的系统;因此,我写这个主题也是希望让大家更多地掌握Oracle数据库的特性,从而在架构一个新系统时,能考虑系统的可扩展,可伸缩性,也兼顾系统的效率和稳定;使用绑定变量是Oracle数据库的特性之一;于是大家要问,为什么使

2、用,怎样使用,它的使用限制条件是什么?我会按照这样的想法去解答大家的疑问,我也会以举例子的方式来回答这些问题;为什么使用绑定变量?这是解决Oracle应用程序可伸缩性的一个关键环节;而Oracle的共享池就决定了开发人员必须使用绑定变量;如果想要Oracle运行减慢,甚至完全终止,那就可以不用绑定变量;这里举例说明上述问题;为了查询一个员工代号是123,你可以这样查询:select*fromempwhereempno=123;你也可以这样查询:select*fromempwhereempno=:empno;象我们往常一样,你查询员工123一次以后,有可能再也不用;接着你有可能查询员工456,然

3、后查询789等等;如果查询使用象第一个查询语句,你每次查询都是一个新的查询(我们叫它硬编码的查询方法);因此,Oracle每次必须分析,解析,安全检查,优化等等;第二个查询语句提供了绑定变量:empno,它的值在查询执行时提供,查询经过一次编译后,查询方案存储在共享池中,可以用来检索和重用;在性能和伸缩性方面,这两者的差异是巨大的,甚至是惊人的;通俗点讲,就不是一个级别;第一个查询使用的频率越高,所消耗的系统硬件资源越大,从而降低了用户的使用数量;它也会把优化好的其它查询语句从共享池中踢出;就象一个老鼠坏了一锅汤似的,系统的整体性能降低;而执行绑定变量,提交相同对象的完全相同的查询的用户(这句

4、话,大家听起来比较难理解,随后我会给出详细的解释),一次性使用就可重复使用,其效率不言耳语;打个形象的比喻来说,第一个查询就象一次性使用的筷子,而第二个查询象是铁筷子,只要洗干净,张三李四都能用,合理有效地使用了资源;下面举例子去详细论证上述的问题,不使用绑定变量为生病状况:这是一个未使用的绑定变量(吃药前):setechoon;(把执行结果显示出来)altersystemflushshared_pool;这条语句是清空共项池,每次都必须使用,确保共享池是空的,以提高执行效率;settimingon(打开记时器.)declaretypercisrefcursor;l_rcrc;l_dummya

5、ll_objects.object_name%type;l_startnumberdefaultdbms_utility.get_time;beginforiin1.1000loopopenl_rcforselectobject_namefromall_objectswhereobject_id=|i;fetchl_rcintol_dummy;closel_rc;endloop;dbms_output.put_line(round(dbms_utility.get_time-l_start)/100,2)|seconds.);end;/PL/SQL过程已成功完成。执行时间:已用时间:00:00

6、:07.03这是一个使用的绑定变量(吃药后):setechoonaltersystemflushshared_pool;declaretypercisrefcursor;l_rcrc;l_dummyall_objects.object_name%type;l_startnumberdefaultdbms_utility.get_time;beginforiin1.1000loopopenl_rcforselectobject_namefromall_objectswhereobject_id=:xusingi;fetchl_rcintol_dummy;closel_rc;endloop;dbm

7、s_output.put_line(round(dbms_utility.get_time-l_start)/100,2)|seconds.);end;PL/SQL过程已成功完成。执行时间:已用时间:00:00:00.75大家自己比较结果,相差就是一个数量级;使用绑定变量不仅仅是运行快,而且允许多个用户同时使用;上述绑定变量的另一种写法供大家参考;setechoonaltersystemflushshared_pool;declaretypercisrefcursor;l_rcrc;l_dummyall_objects.object_name%type;l_startnumberdefault

8、dbms_utility.get_time;beginforiin1.1000loopopenl_rcforselectobject_namefromall_objectswhereobject_id=I;fetchl_rcintol_dummy;closel_rc;endloop;dbms_output.put_line(round(dbms_utility.get_time-l_start)/100,2)|seconds.);end;上述的环境是在数据哭Oracle8.1.7,DBOS:WindowsServer2003,1GMemory,P43.4GHZCPU;电脑配置不同,执行的结果是

9、有差异的;怎样使用绑定变量?下面举例说明:2.1.让Oracle自己绑定变量(也叫静态绑定变量)setserverouton;settimingon;declarel_sqlvarchar2(2000);l_countnumber;l_param1varchar2(100);l_param2varchar2(100);beginl_param1:=a;l_param2:=b;selectcount(*)intol_countfromtable1wherecol_1=l_param1andcol_2=l_param2;dbms_output.put_line(l_count);end;/在上面的

10、情况，Oracle会自己绑定变量，即，如果参数保存在一个数组中，select语句放在一个循环中，select语句只会编译一次。2.2.动态绑定变量setserverouton;settimingon;declarel_sqlvarchar2(2000);l_countnumber;l_param1varchar2(100);l_param2varchar2(100);beginl_param1:=a;l_param2:=b;l_sql:=selectcount(*)into:xfromtable1wherecol_1=:yandcol_2=:z;ExecuteImmediatel_sqlint

11、ol_countusingl_param1,l_param2;dbms_output.put_line(l_count);end;/2.3.dbms_output的绑定变量使用Setechoon;Setserveroutputon;Settimmingon;declarecursor_idinteger;inumber;xSqlVarchar2(200);xOutvarchar2(200);l_startnumberdefaultdbms_utility.get_time;xRowinteger;Begincursor_id:=DBMS_Sql.open_cursor;Foriin1.1000

12、LoopDBMS_Sql.parse(cursor_id,insertintotvalues(:username,:user_id,Sysdate),DBMS_SQL.V7);DBMS_Sql.bind_variable(cursor_id,username,test|to_char(i);DBMS_Sql.bind_variable(cursor_id,user_id,i);xRow:=DBMS_Sql.execute(cursor_id);-insertintotvalues(test|to_char(i),i,Sysdate);-xSql:=insertintotvalues(:user

13、name,:user_id,Sysdate);-executeimmediatexSqlusingtest|to_char(i),i;Endloop;DBMS_sql.close_cursor(cursor_id);dbms_output.put_line(round(dbms_utility.get_time-l_start)/100,2)|seconds.);-xOut:=to_char(round(dbms_utility.get_time-l_start)/100,2)|seconds.;-xOut:=seconds.;-returnxout;end;这里强烈推荐使用静态绑定变量,有兴

14、趣的话可以自己比较;我怎样知道正在使用绑定变量的方法;下面举例说明;创建一个Table;Createtablet(xxint);执行下面的语句;BeginForIin1.100loopExecuteimmediateinsertintotvalues(|t|);Endloop;end;现在准备好了脚本,开始创建一个字符串中删除常数的一个函数,它采用的是SQL语句为:insertintotvalues(hello,55);insertintotvalues(world,56);将其转换为insertintotvalues(#,);所有相同的语句很显然是可见的(使用绑定变量);上述两个独特的插入语

15、句经过转换后变成同样的语句;完成的转换函数为:Createorreplacefunctionremove_constants(p_queryinvarchar2)returnvarchar2asl_querylong;l_charvarchar2(1);l_in_quatesbooleandefaultfalse;beginforiin1.length(p_query)loopl_char:=substr(p_query,i,1);ifl_char=andl_in_quatesthenl_in_quates:=False;elsifl_char=andnotl_in_quatesthenthe

16、nl_in_quates:=true;l_query=:l_query|#;endififnotl_in_quatesthenl_query=:l_query|l_char;endif;endloop;l_query:=tranlate(l_query,0123456789,);foriin1.8loopl_query:=replace(l_query,lpad(,10-i,),);l_query:=replace(l_query,lpad(,10-i,),);endloop;returnupper(l_query);end;/接着我们建立一个临时表去保存V$SQLAREA里的语句,所有Sql

17、的执行结果都写在这里;建立临时表;createglobaltemporarytablesql_area_tmponcommitpreserverowsasselectsql_text,sql_textsql_text_wo_constantsfromv$sqlareawhere1=0;保存数据到临时表上;insertintosql_area_tmp(sql_text)selectsql_textfromv$sqlarea;对临时表中的数据进行更新;删除掉常数;Updatesql_area_tmpsetSQL_TEXT_WO_CONSTANTS=remove_constants(sql_text

18、);现在我们要找到哪个糟糕的查询selectSQL_TEXT_WO_CONSTANTS,count(*)fromsql_area_tmpgroupbySQL_TEXT_WO_CONSTANTShavingcount(*)10orderby2;SQL_TEXT_WO_CONSTANTScount(*)-INSERTINTOTVALUES()100另外,设定如下参数Altersessionsetsql_trace=true;Altersessionsettimed_statictics=True;Altersessionsetevents10046tracenamecontextforever,l

19、evel;这里的N表示的是1,4,8,12,详细内容请参考相关文档Altersessionsetevents10046tracenamecontextoff;可以用TKPROF工具查看绑顶变量执行的结果,如例子:declarel_numbernumber;l_textvarchar2(5);beginforiin1.1000loopl_number:=i;l_text:=test|to_char(i);insertintotvalues(i,l_text);endloop;commit;end;callcountcpuelapseddiskquerycurrentrows-Parse20.00

20、0.100000Execute10090.090.210410351009Fetch00.000.000000-total10110.090.310410351009绑定变量在应用开发环境下的使用;4.1在VB.NetorASP.NETandVB中的的使用建议用OracleclientDB的连接方法,OleDB不支持;下面是使用OracleClient连接的使用例子(这个代码执行只需要2秒不到);BeginDimcn01AsNewOracleConnectionDimCMD01AsNewOracleCommandDimCmdAsNewOleDbCommandDimiAsIntegerTryxC

21、onnStr=System.Configuration.ConfigurationSettings.AppSettings(DBCONN_SFCFA)cn01.ConnectionString()cn01.ConnectionString=xConnStrcn01.Open()TextBox1.Text=NowApplication.DoEvents()xSql=insertintotvalues(:username,:userid,sysdate)Fori=1To1000CMD01=NewOracleClient.OracleCommand(xSql,cn01)CMD01.CommandTy

22、pe=CommandType.TextCMD01.Parameters.Add(username,test+CStr(i)CMD01.Parameters.Add(userid,i)CMD01.ExecuteNonQuery()CMD01.Parameters.Clear()NextiTextBox2.Text=NowCatchexAsOleDbExceptionMsgBox(ex.Message)CatchexAsExceptionMsgBox(ex.HelpLink+ex.Message)EndTryEnd.OleDB(VB,ASP等)不支持绑定变量,或者我没有找到更好的方法去实现它;它有

23、变量的概念但不支持绑定;网络上,有很多帖子说;他实现了绑定变量用VBorASP;我按照他们的方法去试,发现他们与单纯传参数没有什么区别,请看下面的内容;OleDB(这个执行需要5秒;):DimxConnStr,xSqlAsStringDimCnAsNewOleDbConnectionDimcn01AsNewOracleConnectionDimCMD01AsNewOracleCommandDimCmdAsNewOleDbCommandDimiAsIntegerTryxConnStr=System.Configuration.ConfigurationSettings.AppSettings(D

24、BCONN_SFCFA)Cn.ConnectionString()Cn.ConnectionString=xConnStrCn.Open()TextBox1.Text=NowApplication.DoEvents()xSql=insertintotvalues(?,?,sysdate)Fori=1To1000Cmd=NewOleDbCommand(xSql,Cn)Cmd.CommandType=CommandType.TextCmd.Parameters.Add(username,test+CStr(i)Cmd.Parameters.Add(userid,i)Cmd.ExecuteNonQu

25、ery()Cmd.Parameters.Clear()NextiTextBox2.Text=NowCatchexAsOleDbExceptionMsgBox(ex.Message)CatchexAsExceptionMsgBox(ex.HelpLink+ex.Message)EndTryVBorASP(耗时也是5秒左右):PrivateSubCommand1_Click()DimsConnAsStringDimBVCS_CNAsADODB.ConnectionDimBVCSasADODB.DimxCMDAsADODB.CommandDimxPreAsADODB.ParameterDimxSql

26、AsStringDimxSql01AsStringDimxRSAsADODB.RecordsetOnErrorGoTo1SetDBConnection=TrueSetBVCS_CN=NewADODB.ConnectionBVCS_CN.Provider=MSDAORAsConn=DATASOURCE=&ServerName&;sConn=Provider=MSDAORA.1;Password=sfcfa;UserID=sfcfa;DataSource=cim;WithBVCS_CN.OpensConnEndWithIfBVCS_CN.State=0ThenMsgBoxDBConnectioni

27、serrorExitSubEndIfText1.Text=NowDoEventsSetxCMD=NewADODB.CommandDimxTestAsStringSetxPre=NewADODB.ParameterBVCS_CNFori=1To1000WithxCMD.ActiveConnection=BVCS_CN.CommandText=InsertintoTT(username,userid)values(?,?).CommandType=adCmdText.Parameters.Append.CreateParameter(username,adBSTR,adParamInput,30,

28、test+CStr(i).Parameters.Append.CreateParameter(userid,adInteger,adParamInput,4,i).Prepared=True.ExecuteEndWithxCMD.Parameters.Delete1xCMD.Parameters.Delete0NextiSetxCMD=NothingText2.Text=NowExitSub1:SetxCMD=NothingMsgBoxError$ForEachobjErrInBVCS_CN.ErrorsMsgBoxobjErr.DescriptionNextBVCS_CN.Errors.Cl

29、earExitSubResumeNextEndSub4.2在Delphi中的使用情况;这里特殊说明,BorlandDelphi4.0以上的版本已经开始完全支持绑定变量的概念,因此,它执行数据库的查询效率要好于其他开发工具;执行的结果不到2秒;procedureTForm1.Button1Click(Sender:TObject);Vari:Integer;beginedit1.text:=DatetimeToStr(now);Fori:=1to1000do/BeginWithQuery1doBeginclose;Sql.clear;Sql.add(InsertintotValues(:user

30、name,:user_id,sysdate);ParamByName(username).AsString:=test;ParamByName(user_id).AsInteger:=i;execSql;End;/end;/edit2.text:=DateToStr(now);edit2.text:=DatetimetoStr(now);end;4.3.在Java中的使用绑定变量Stringv_id=xxxxx;Stringv_sql=selectnamefromtable_awhereid=?;/嵌入绑定变量stmt=con.prepareStatement(v_sql);stmt.setS

31、tring(1,v_id);/为绑定变量赋值stmt.executeQuery();在Java中，结合使用setXXX系列方法，可以为不同数据类型的绑定变量进行赋值，从而大大优化了SQL语句的性能。4.4C#同VB.NET,这里不在赘述;绑定变量使用限制条件是什么?为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路径.ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.可惜的是ORACLE只对简单的表提供高速缓冲(cachebuffering),这个功能并不适用于多表连接查询(这句并不完全可信,有兴趣的可以自己琢磨).数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.当你向OR