也谈Oracle 数据库的绑定变量特性.docx
《也谈Oracle 数据库的绑定变量特性.docx》由会员分享，可在线阅读，更多相关《也谈Oracle 数据库的绑定变量特性.docx（14页珍藏版）》请在冰豆网上搜索。
也谈Oracle数据库的绑定变量特性
也谈Oracle数据库的绑定变量特性及其使用
关键词:
绑定变量（bindingvariable）,共项池（sharedbufferpool）,SGA（systemglobalarea）;
在开发一个数据库系统前,有谁对Oracle系统了解很多,尤其是它的特性,好象很少吧;对初学者来讲,这更是不可能的事情;仅仅简单掌握了SQL的写法,就开始了数据库的开发,其结果只能是开发一个没有效率,也没有可扩展的系统;
因此,我写这个主题也是希望让大家更多地掌握Oracle数据库的特性,从而在架构一个新系统时,能考虑系统的可扩展,可伸缩性,也兼顾系统的效率和稳定;
使用绑定变量是Oracle数据库的特性之一;于是大家要问,为什么使用,怎样使用,它的使用限制条件是什么?
我会按照这样的想法去解答大家的疑问,我也会以举例子的方式来回答这些问题;
1.为什么使用绑定变量?
这是解决Oracle应用程序可伸缩性的一个关键环节;而Oracle的共项池就决定了开发人员必须使用绑定变量;如果想要Oracle运行减慢,甚至完全终止,那就可以不用绑定变量;
这里举例说明上述问题;
为了查询一个员工代号是123,你可以这样查询:
select*fromempwhereempno=’123’;
你也可以这样查询:
select*fromempwhereempno=:
empno;
象我们往常一样,你查询员工’123’一次以后,有可能再也不用;接着你有可能查询员工’456’,然后查询’789’等等;如果查询使用象第一个查询语句,你每次查询都是一个新的查询（我们叫它硬编码的查询方法）;因此,Oracle每次必须分析,解析,安全检查,优化等等;
第二个查询语句提供了绑定变量:
empno,它的值在查询执行时提供,查询经过一次编译后,查询方案存储在共享池中,可以用来检索和重用;在性能和伸缩性方面,这两者的差异是巨大的,甚至是惊人的;通俗点讲,就不是一个级别;
第一个查询使用的频率越高,所消耗的系统硬件资源越大,从而降低了用户的使用数量;它也会把优化好的其它查询语句从共项池中踢出;就象一个老鼠坏了一锅汤似的,系统的整体性能降低;而执行绑定变量,提交相同对象的完全相同的查询的用户（这句话,大家听起来比较难理解,随后我会给出详细的解释）,一次性使用就可重复使用,其效率不言耳语;打个形象的比喻来说,第一个查询就象一次性使用的筷子,而第二个查询象是铁筷子,只要洗干净,张三李四都能用,合理有效地使用了资源;
下面举例子去详细论证上述的问题,不使用绑定变量为生病状况:
这是一个未使用的绑定变量（吃药前）:
setechoon;
altersystemflushshared_pool;
这条语句是清空共项池,每次都必须使用,确保共项池是空的,以提高执行效率;
settimingon（打开记时器.）
declare
typercisrefcursor;
l_rcrc;
l_dummyall_objects.object_name%type;
l_startnumberdefaultdbms_utility.get_time;
begin
foriin1..1000
loop
openl_rcfor
'selectobject_name
fromall_objects
whereobject_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
这是一个使用的绑定变量（吃药后）:
setechoon
altersystemflushshared_pool;
declare
typercisrefcursor;
l_rcrc;
l_dummyall_objects.object_name%type;
l_startnumberdefaultdbms_utility.get_time;
begin
foriin1..1000
loop
openl_rcfor
'selectobject_name
fromall_objects
whereobject_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
大家自己比较这中结果,相差就是一个数量级;使用绑定变量不仅仅是运行快,而且允许多个用户同时使用;
上述绑定变量的另一种写法供大家参考;
setechoon
altersystemflushshared_pool;
declare
typercisrefcursor;
l_rcrc;
l_dummyall_objects.object_name%type;
l_startnumberdefaultdbms_utility.get_time;
begin
foriin1..1000
loop
openl_rcfor
selectobject_name
fromall_objects
whereobject_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;
declare
l_sqlvarchar2（2000）;
l_countnumber;
l_param1varchar2（100）;
l_param2varchar2（100）;
begin
l_param1:
='a';
l_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;
declare
l_sqlvarchar2（2000）;
l_countnumber;
l_param1varchar2（100）;
l_param2varchar2（100）;
begin
l_param1:
='a';
l_param2:
='b';
l_sql:
='selectcount（*）into:
xfromtable1wherecol_1=:
yandcol_2=:
z';
ExecuteImmediatel_sqlintol_countusingl_param1,l_param2;
dbms_output.put_line（l_count）;
end;
/
2.3.dbms_output的绑定变量使用
Setechoon;
Setserveroutputon;
Settimmingon;
declare
cursor_idinteger;
inumber;
xSqlVarchar2（200）;
xOutvarchar2（200）;
l_startnumberdefaultdbms_utility.get_time;
xRowinteger;
Begin
cursor_id:
=DBMS_Sql.open_cursor;
Foriin1..1000Loop
DBMS_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;
这里推荐使用静态绑定变量,有兴趣的话可以自己比较;
3.绑定变量在应用开发环境下的使用;
3.1在VB.NetorASP.NETandVB中的的使用
建议用OracleclientDB的连接方法,OleDB不支持;下面是使用OracleClient连接的使用例子,这个代码执行只需要2秒不到;
Begin
Dimcn01AsNewOracleConnection
DimCMD01AsNewOracleCommand
DimCmdAsNewOleDbCommand
DimiAsInteger
Try
xConnStr=System.Configuration.ConfigurationSettings.AppSettings（"DBCONN_SFCFA"）
'cn01.ConnectionString（）
cn01.ConnectionString=xConnStr
cn01.Open（）
TextBox1.Text=Now
Application.DoEvents（）
xSql="insertintotvalues（:
username,:
userid,sysdate）"
Fori=1To1000
CMD01=NewOracleClient.OracleCommand（xSql,cn01）
CMD01.CommandType=CommandType.Text
CMD01.Parameters.Add（"username","test"+CStr（i））
CMD01.Parameters.Add（"userid",i）
CMD01.ExecuteNonQuery（）
CMD01.Parameters.Clear（）
Nexti
TextBox2.Text=Now
CatchexAsOleDbException
MsgBox（ex.Message）
CatchexAsException
MsgBox（ex.HelpLink+ex.Message）
EndTry
End.
OleDB（VB,ASP等）不支持绑定变量,或者我没有找到更好的方法去实现它;它有变量的概念但不支持绑定;网络上,有很多帖子说;他实现了绑定变量用VBorASP;我按照他们的方法去试,发现他们与单纯传参数没有什么区别,请看下面的内容;
OleDB（这个执行需要5秒;）:
DimxConnStr,xSqlAsString
DimCnAsNewOleDbConnection
Dimcn01AsNewOracleConnection
DimCMD01AsNewOracleCommand
DimCmdAsNewOleDbCommand
DimiAsInteger
Try
xConnStr=System.Configuration.ConfigurationSettings.AppSettings（"DBCONN_SFCFA"）
'Cn.ConnectionString（）
Cn.ConnectionString=xConnStr
Cn.Open（）
TextBox1.Text=Now
Application.DoEvents（）
xSql="insertintotvalues（?
?
sysdate）"
Fori=1To1000
Cmd=NewOleDbCommand（xSql,Cn）
Cmd.CommandType=CommandType.Text
Cmd.Parameters.Add（"username","test"+CStr（i））
Cmd.Parameters.Add（"userid",i）
Cmd.ExecuteNonQuery（）
Cmd.Parameters.Clear（）
Nexti
TextBox2.Text=Now
CatchexAsOleDbException
MsgBox（ex.Message）
CatchexAsException
MsgBox（ex.HelpLink+ex.Message）
EndTry
VBorASP（耗时也是5秒左右…）:
PrivateSubCommand1_Click（）
DimsConnAsString
DimBVCS_CNAsADODB.Connection
'DimBVCSasADODB.
DimxCMDAsADODB.Command
DimxPreAsADODB.Parameter
DimxSqlAsString
DimxSql01AsString
DimxRSAsADODB.Recordset
OnErrorGoTo1
SetDBConnection=True
SetBVCS_CN=NewADODB.Connection
'BVCS_CN.Provider="MSDAORA"
'sConn="DATASOURCE="&ServerName&";"
sConn="Provider=MSDAORA.1;Password=sfcfa;UserID=sfcfa;DataSource=cim;"
WithBVCS_CN
.OpensConn
EndWith
IfBVCS_CN.State=0Then
MsgBox"DBConnectioniserror"
ExitSub
EndIf
Text1.Text=Now
DoEvents
SetxCMD=NewADODB.Command
DimxTestAsString
SetxPre=NewADODB.Parameter
'BVCS_CN
Fori=1To1000
WithxCMD
.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
.Execute
EndWith
xCMD.Parameters.Delete1
xCMD.Parameters.Delete0
Nexti
SetxCMD=Nothing
Text2.Text=Now
ExitSub
1:
SetxCMD=Nothing
MsgBoxError$
ForEachobjErrInBVCS_CN.Errors
MsgBoxobjErr.Description
Next
BVCS_CN.Errors.Clear
ExitSub
ResumeNext
'MsgBoxErr.Description+CStr（Err.HelpContext）,vbCritical,Err.Number
EndSub
3.2在Delphi中的使用情况;
这里特殊说明,BorlandDelphi4.0以上的版本已经开始完全支持绑定变量的概念,因此,它执行数据库的查询效率要好于其他开发工具;执行的结果不到2秒;
procedureTForm1.Button1Click（Sender:
TObject）;
Var
i:
Integer;
begin
edit1.text:
=DatetimeToStr（now）;
Fori:
=1to1000do
//Begin
WithQuery1do
Begin
close;
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.绑定变量使用限制条件是什么?
为了不重复解析相同的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_limitprivatesynonym
Work_citypublicsynonym
Plant_detailpublicsynonym
Jillsal_limitprivatesynonym
Work_citypublicsynonym
Plant_detailtableowner
考虑一下下列SQL语句能否在这两个用户之间共享.
SQL
能否共享
原因
selectmax（sal_cap）fromsal_limit;
不能
每个用户都有一个privatesynonym-sal_limit,它们是不同的对象
selectcount（*0fromwork_citywheresdesclike'NEW%';
能
两个用户访问相同的对象publicsynonym-work_city
selecta.sdesc,b.locationfromwork_citya,plant_deta
升级会员 