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

《Oracle数据库的绑定变量特性及应用讲课讲稿.doc》由会员分享，可在线阅读，更多相关《Oracle数据库的绑定变量特性及应用讲课讲稿.doc（26页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

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

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

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

5、myall_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

6、:00: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;

7、dbms_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_startnumberdefa

8、ultdbms_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.);2. 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_

11、sqlintol_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;Foriin

12、1.1000LoopDBMS_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

13、(:username,: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;3. 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_quat

16、esthenthenl_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里

17、的语句,所有Sql的执行结果都写在这里;建立临时表;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

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

19、forever,level;这里的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-P

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

21、IntegerTryxConnStr=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)CMD

22、01.CommandType=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.A

24、ppSettings(DBCONN_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.

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

26、meterDimxSqlAsStringDimxSql01AsStringDimxRSAsADODB.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=0ThenMsgBoxD

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

28、ramInput,30,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_

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

30、Values(:username,: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_sq

31、l);stmt.setString(1,v_id);/为绑定变量赋值stmt.executeQuery();在Java中，结合使用setXXX系列方法，可以为不同数据类型的绑定变量进行赋值，从而大大优化了SQL语句的性能。4.4C#同VB.NET,这里不在赘述;5. 绑定变量使用限制条件是什么?为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过

32、的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路径.ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.可惜的是ORACLE只对简单的表提供高速缓冲(cachebuffering),这个功能并不适用于多表连接查询(这句并不完全可信,有兴趣的可以自己琢磨).数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须

33、完全相同(包括空格,换行等).共享的语句必须满足三个条件:A.字符级的比较:当前被执行的语句和共享池中的语句必须完全相同.例如:SELECT*FROMEMP;和下列每一个都不同SELECT*fromEMP;Select*FromEmp;SELECT*FROMEMP;B.两个语句所指的对象必须完全相同:例如:用户对象名如何访问Jacksal_limitprivatesynonymWork_citypublicsynonymPlant_detailpublicsynonymJillsal_limitprivatesynonymWork_citypublicsynonymPlant_detailta

34、bleowner考虑一下下列SQL语句能否在这两个用户之间共享.SQL能否共享原因selectmax(sal_cap)fromsal_limit;不能每个用户都有一个privatesynonym-sal_limit,它们是不同的对象selectcount(*)fromwork_citywheresdesclikeNEW%;能两个用户访问相同的对象publicsynonym-work_cityselecta.sdesc,b.locationfromwork_citya,plant_detailbwherea.city_id=b.city_id不能用户jack通过privatesynonym访问p

35、lant_detail而jill是表的所有者,对象不同.C.两个SQL语句中必须使用相同的名字的绑定变量(bindvariables)例如：第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)a.selectpin,namefrompeoplewherepin=:blk1.pin;selectpin,namefrompeoplewherepin=:blk1.pin;b.selectpin,namefrompeoplewherepin=:blk1.ot_ind;6. selectpin,namefrompeoplewherepin=:blk1.ov_ind;总结不使用绑定变量是坐着等死,使用绑定变量不一定不会死;没有任何的良药会包治百病,所以在如何合理有效地使用绑定变量仍就需要大家去摸索;-