Oracle数据库绑定变量特性及应用

Oracle数据库绑定变量特性及应用在开发一个数据库系统前,有谁对Oracle系统了解很多，尤其是它的特性,好象很少吧；对初学者来讲，这更是不可能的事情;仅仅简单掌握了SQL的写法，就开始了数据库的开发，其结果只能是开发一个没有效率，也没有可扩展的系统；因此,我写这个主 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 也是希望让大家更多地掌握Oracle数据库的特性，从而在架构一个新系统时,能考虑系统的可扩展，可伸缩性,也兼顾系统的效率和稳定；使用绑定变量是Oracle数据库的特性之一;于是大家要问，为什么使用，怎样使用，它的使用限制条件是什么?我会按照这样的想法去解答大家的疑问,我也会以举例子的方式来回答这些问题；1.为什么使用绑定变量？这是解决Oracle应用程序可伸缩性的一个关键环节;而Oracle的共享池就决定了开发人员必须使用绑定变量;如果想要Oracle运行减慢,甚至完全终止，那就可以不用绑定变量;这里举例说明上述问题；为了查询一个员工代号是123,你可以这样查询：select*fromempwhereempno二'123';你也可以这样查询：select*fromempwhereempno=:empno;象我们往常一样，你查询员工'123'一次以后，有可能再也不用;接着你有可能查询员工'456'，然后查询'789'等等;如果查询使用象第一个查询语句,你每次查询都是一个新的查询（我们叫它硬编码的查询 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 ）;因此,Oracle每次必须 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 ，解析，安全检查，优化等等；第二个查询语句提供了绑定变量:empno,它的值在查询执行时提供，查询经过一次编译后，查询方案存储在共享池中，可以用来检索和重用;在性能和伸缩性方面，这两者的差异是巨大的，甚至是惊人的;通俗点讲，就不是一个级别；第一个查询使用的频率越高，所消耗的系统硬件资源越大，从而降低了用户的使用数量；它也会把优化好的其它查询语句从共享池中踢出;就象一个老鼠坏了一锅汤似的，系统的整体性能降低；而执行绑定变量，提交相同对象的完全相同的查询的用户（这句话，大家听起来比较难理解,随后我会给出详细的解释），一次性使用就可重复使用，其效率不言耳语；打个形象的比喻来说，第一个查询就象一次性使用的筷子，而第二个查询象是铁筷子，只要洗干净，张三李四都能用，合理有效地使用了资源；下面举例子去详细论证上述的问题，不使用绑定变量为生病状况：这是一个未使用的绑定变量（吃药前）：setechoon;（把执行结果显示出来）altersystemflushshared_pool;这条语句是清空共项池，每次都必须使用，确保共享池是空的，以提高执行效率；以下是引用片段：settimingon（打开记时器.）declaretypercisrefcursor;l_rcrc;l_dummyall_objects.object_name%type;l_startnumberdefaultdbms_utility.get_time;beginforiin1..1000loopopenl_rcfor'selectobject_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:07.03这是一个使用的绑定变量(吃药后)：setechoonaltersystemflushshared_pool;declaretypercisrefcursor;l_rcrc;l_dummyall_objects.object_name%type;l_startnumberdefaultdbms_utility.get_time;beginforiin1..1000loopopenl_rcfor'selectobject_namefromall_objectswhereobject_id=:x'usingi;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:00.75大家自己比较结果，相差就是一个数量级;使用绑定变量不仅仅是运行快，而且允许多个用户同时使用；上述绑定变量的另一种写法供大家参考；以下是引用片段：setechoonaltersystemflushshared_pool;declaretypercisrefcursor;l_rcrc;l_dummyall_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;上述的环境是在数据哭Oracle8.1.7,DBOS:WindowsServer2003,1GMemory,P43.4GHZCPU;电脑配置不同，执行的结果是有差异的；2■怎样使用绑定变量？下面举例说明：2・1■让Oracle自己绑定变量（也叫静态绑定变量）以下是引用片段：setserverouton;settimingon;declarel_sqlvarchar2(2000);l_countnumber;l_param1varchar2(100);l_param2varchar2(100);beginI_param1:='a';I_param2:='b';selectcount(*)intol_countfromtable1wherecol_1=l_param1andcol_2=l_param2;dbms_output.put_line(l_count);end;/在上面的情况，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_sqlintol_countusingI_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..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(:username,:user_id,Sysdate)';--executeimmediatexSqlusing'test'||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;这里强烈推荐使用静态绑定变量,有兴趣的话可以自己比较；我怎样知道正在使用绑定变量的方法;下面举例说明；创建一个Table;Createtablet(xxint);执行下面的语句；以下是引用片段：BeginForIin1..100loopExecuteimmediate'insertintotvalues('||tEndloop;end;现在准备好了脚本，开始创建一个字符串中删除常数的一个函数，它采用的是SQL语句为：insertintotvalues('hello',55);insertintotvalues('world',56);将其转换为insertintotvalues('#',@);所有相同的语句很显然是可见的(使用绑定变量);上述两个独特的插入语句经过转换后变成同样的语句；完成的转换函数为：以下是引用片段：Createorreplacefunctionremove_constants(p_queryinvarchar2)returnvarchar2asl_querylong;I_charvarchar2(1);l_in_quatesbooleandefaultfalse;beginforiin1..length(p_query)loopl_char:=substr(p_query,i,1);ifl_char='H'andl_in_quatesthenl_in_quates:=False;elsifl_char=''Handnotl_in_quatesthenthenl_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;/接着我们建立一个临时 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 去保存V$SQLAREA里的语句，所有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(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;Altersessionsetevents'10046tracenamecontextforever,level';这里的'N'表示的是1,4,8,12,详细内容请参考相关文档Altersessionsetevents'10046tracenamecontextoff';可以用TKPROF工具查看绑顶变量执行的结果，如例子：以下是引用片段：declarel_numbernumber;l_textvarchar2(5);beginforiin1..1000loopI_number:=i;l_text:='test'||to_char(i);insertintotvalues(i,l_text);endloop;commit;end;callcountcpuelapseddiskquerycurrentrowsParse20.000.100000Execute10090.090.210410351009Fetch00.000.000000total10110.090.310410351009绑定变量在应用开发环境下的使用；4.1在VB.NetorASP・NETandVB中的的使用建议用OracleclientDB的连接方法,OleDB不支持;下面是使用OracleClient连接的使用例子(这个代码执行只需要2秒不到)；以下是引用片段：BeginDimcn01AsNewOracleConnectionDimCMD01AsNewOracleCommandDimCmdAsNewOleDbCommandDimiAsIntegerTryxConnStr=System.Configuration.ConfigurationSettings.AppSettings("DBCONN_SFCFA"'cn01.ConnectionString()cnO1.ConnectionString=xConnStrcn01.Open()TextBox1.Text=NowApplication.DoEvents()xSql="insertintotvalues(:username,:userid,sysdate)"Fori=1To1000CMD01=NewOracleClient.OracleCommand(xSql,cn01)CMD01.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等)不支持绑定变量，或者我没有找到更好的方法去实现它;它有变量的概念但不支持绑定;网络上，有很多帖子说;他实现了绑定变量用VBorASP;我按照他们的方法去试，发现他们与单纯传参数没有什么区别，请看下面的内容；以下是引用片段：OleDB(这个执行需要5秒)：DimxConnStr,xSqlAsStringDimCnAsNewOleDbConnectionDimcn01AsNewOracleConnectionDimCMD01AsNewOracleCommandDimCmdAsNewOleDbCommandDimiAsIntegerTryxConnStr=System.Configuration.ConfigurationSettings.AppSettings("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.ExecuteNonQuery()Cmd.Parameters.Clear()NextiTextBox2.Text=NowCatchexAsOleDbExceptionMsgBox(ex.Message)CatchexAsExceptionMsgBox(ex.HelpLink+ex.Message)EndTryVBorASP(耗时也是5秒左右…)：PrivateSubCommand1_Click()DimsConnAsStringDimBVCS_CNAsADODB.Connection'DimBVCSasADODB.DimxCMDAsADODB.CommandDimxPreAsADODB.ParameterDimxSqlAsStringDimxSqlO1AsStringDimxRSAsADODB.RecordsetOnErrorGoTo1SetDBConnection=TrueSetBVCS_CN=NewADODB.Connection'BVCS_CN.Provider="MSDAORA"'sConn="DATASOURCE="&ServerName&";"sConn="Provider=MSDAORA.1;Password=sfcfa;UserID=sfcfa;DataSource=cim・";WithBVCS_CN.OpensConnEndWithIfBVCS_CN.State=0ThenMsgBox"DBConnectioniserror"ExitSubEndIfTextLText=NowDoEventsSetxCMD=NewADODB.CommandDimxTestAsStringSetxPre=NewADODB.Parameter'BVCS_CNFori=1To1000WithxCMD.ActiveConnection=BVCS_CN.CommandText="InsertintoTT(username,userid)values(?,?)".CommandType=adCmdText.Parameters.Append.CreateParameter("username",adBSTR,adParamInput,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_CN.Errors.ClearExitSubResumeNextEndSub4.2在Delphi中的使用情况;这里特殊说明，BorlandDelphi4.0以上的版本已经开始完全支持绑定变量的概念，因此,它执行数据库的查询效率要好于其他开发工具;执行的结果不到2秒；以下是引用片段：procedureTForml.Button1Click(Sender:TObject);Vari:Integer;beginedit1.text:=DatetimeToStr(now);Fori:=1to1000do//BeginWithQuery1doBeginclose;Sql.clear;Sql.add('InsertintotValues(: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_sql);stmt.setString(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中为这个区域设置合适的参数,当这个内存区域越大，就可以保留更多的语句,当然被共享的可能性也就越大了.当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.这里需要注明的是,ORACLE对两者采取的是一种严格匹配，要达成共享,SQL语句必须完全相同(包括空格，换行等).共享的语句必须满足三个条件：A.字符级的比较：当前被执行的语句和共享池中的语句必须完全相同.例如：SELECT*FROMEMP;和下列每一个都不同SELECT*fromEMP;Select*FromEmp;SELECT*FROMEMP;B.两个语句所指的对象必须完全相同:例如：用户对象名Jacksal_limitWork_cityPlant_detailJillsal_limitWork_cityPlantdetail如何访问privatesynonympublicsynonympublicsynonymprivatesynonympublicsynonymtableowner能否共享不能考虑一下下列SQL语句能否在这两个用户之间共享.SQL原因selectmax(sal_cap)fromsal_limit;每个用户都有一个privatesynonym-sal_limit,它们是不同的对象selectcount（*Ofromwork_citywheresdesclike'NEW%';能两个用户访问相同的对象publicsynonym-work_cityselecta.sdesc,b.locationfromwork_citya,plant_detailbwherea.city_id=b.city_id；不能用户jack通过privatesynonym访问plant_detail而jill是表的所有者，对象不同.C.两个SQL语句中必须使用相同的名字的绑定变量（bindvariables）例如：第一组的两个SQL语句是相同的（可以共享），而第二组中的两个语句是不同的（即使在运行时，赋于不同的绑定变量相同的值）a.以下是引用片段：selectpin,namefrompeoplewherepin=:blk1.pin;selectpin,namefrompeoplewherepin=:blk1.pin;b.以下是引用片段：selectpin,namefrompeoplewherepin=:blk1.ot_ind;selectpin,namefrompeoplewherepin=:blk1.ov_ind;6. 总结 初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf 不使用绑定变量是做着等死，使用绑定变量不一定不会死;没有任何的良药会包治百病,所以在如何合理有效地使用绑定变量仍就需要大家去摸索;