[整理]Oracle数据库的绑定变量特性及应用

-------------

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

在开发一个数据库系统前,有谁对Oracle 系统了解很多,尤其是它的特性,好象很少吧;对初学者来讲,这更是不可能的事情;仅仅简单掌握了SQL的写法,就开始了数据库的开发,其结果只能是开发一个没有效率,也没有可扩展的系统; 因此,我写这个主题也是希望让大家更多地掌握Oracle数据库的特性,从而在架构一个新系统时,能考虑系统的可扩展,可伸缩性,也兼顾系统的效率和稳定;

使用绑定变量是Oracle数据库的特性之一;于是大家要问,为什么使用,怎样使用,它的使用限制条件是什么?我会按照这样的想法去解答大家的疑问,我也会以举例子的方式来回答这些问题;

1. 为什么使用绑定变量?

这是解决Oracle应用程序可伸缩性的一个关键环节;而Oracle的共享池就决定了开发人员必须使用绑定变量;如果想要Oracle 运行减慢,甚至完全终止,那就可以不用绑定变量;

这里举例说明上述问题;

为了查询一个员工代号是123,你可以这样查询: select * from emp where empno=’123’; 你也可以这样查询:

select * from emp where empno=:empno;

象我们往常一样,你查询员工’123’一次以后,有可能再也不用;接着你有可能查询员工’456’,然后查询’789’等等;如果查询使用象第一个查询语句,你每次查询都是一个新的查询(我们叫它硬编码的查询方法);因此,Oracle每次必须分析,解析,安全检查, 优化等等;

第二个查询语句提供了绑定变量:empno,它的值在查询执行时提供,查询经过一次编译后,查询方案存储在共享池中,可以用来检索和重用;在性能和伸缩性方面,这两者的差异是巨大的,甚至是惊人的;通俗点讲,就不是一个级别;

第一个查询使用的频率越高,所消耗的系统硬件资源越大,从而降低了用户的使用数量;它也会把优化好的其它查询语句从共享池中踢出;就象一个老鼠坏了一锅汤似的,系统的整体性能降低; 而执行绑定变量,提交相同对象的完全相同的查询的用户(这句话,大家听起来比较难理解,随后我会给出详细的解释),一次性使用就可重复使用,其效率不言耳语; 打个形象的比喻来说,第一个查询就象一次性使用的筷子,而第二个查询象是铁筷子,只要洗干净,张三李四都能用,合理有效地使用了资源;

下面举例子去详细论证上述的问题,不使用绑定变量为生病状况:

这是一个未使用的绑定变量(吃药前): set echo on;(把执行结果显示出来) alter system flush shared_pool;

这条语句是清空共项池,每次都必须使用,确保共享池是空的,以提高执行效率; set timing on(打开记时器.)

-------------

-------------

declare

type rc is ref cursor; l_rc rc;

l_dummy all_objects.object_name%type; l_start number default dbms_utility.get_time; begin

for i in 1 .. 1000 loop

open l_rc for

'select object_name from all_objects

where object_id = ' || i; fetch l_rc into l_dummy; close l_rc; end loop;

dbms_output.put_line

( round( (dbms_utility.get_time-l_start)/100, 2 ) || ' seconds...' ); end; /

PL/SQL 过程已成功完成。

执行时间: 已用时间: 00: 00: 07.03

这是一个使用的绑定变量(吃药后): set echo on

alter system flush shared_pool; declare

type rc is ref cursor; l_rc rc;

l_dummy all_objects.object_name%type; l_start number default dbms_utility.get_time; begin

for i in 1 .. 1000 loop

open l_rc for

'select object_name from all_objects where object_id = :x' using i;

fetch l_rc into l_dummy; close l_rc; end loop;

dbms_output.put_line

( round( (dbms_utility.get_time-l_start)/100, 2 ) || ' seconds...' ); end; -------------

-------------

PL/SQL 过程已成功完成。

执行时间: 已用时间: 00: 00: 00.75

大家自己比较结果,相差就是一个数量级;使用绑定变量不仅仅是运行快,而且允许多个用户同时使用;

上述绑定变量的另一种写法供大家参考;

set echo on

alter system flush shared_pool;

declare

type rc is ref cursor; l_rc rc;

l_dummy all_objects.object_name%type; l_start number default dbms_utility.get_time; begin

for i in 1 .. 1000 loop

open l_rc for

select object_name from all_objects where object_id = I; fetch l_rc into l_dummy; close l_rc; end loop;

dbms_output.put_line

( round( (dbms_utility.get_time-l_start)/100, 2 ) || ' seconds...' ); end;

上述的环境是在数据哭Oracle 8.1.7, DB OS: Windows Server 2003, 1G Memory, P4 3.4GHZ CPU; 电脑配置不同,执行的结果是有差异的;

2. 怎样使用绑定变量? 下面举例说明:

2.1.让Oracle自己绑定变量(也叫静态绑定变量)

set serverout on; set timing on; declare

l_sql varchar2(2000); l_count number;

l_param1 varchar2(100); l_param2 varchar2(100); begin

l_param1:='a'; -------------

-------------

l_param2:='b';

select count(*) into l_count from table1 where col_1=l_param1 and col_2=l_param2;

dbms_output.put_line(l_count); end; /

在上面的情况，Oracle会自己绑定变量，即，如果参数保存在一个数组中，select语句放在一个循环中， select 语句只会编译一次。

2.2 .动态绑定变量

set serverout on; set timing on; declare

l_sql varchar2(2000); l_count number;

l_param1 varchar2(100); l_param2 varchar2(100); begin

l_param1:='a'; l_param2:='b';

l_sql:='select count(*) into :x from table1 where col_1=:y and col_2=:z '; Execute Immediate l_sql into l_count using l_param1,l_param2; dbms_output.put_line(l_count); end; /

2.3. dbms_output的绑定变量使用

Set echo on;

Set serveroutput on; Set timming on; declare

cursor_id integer; i number;

xSql Varchar2(200); xOut varchar2(200);

l_start number default dbms_utility.get_time; xRow integer; Begin

cursor_id:=DBMS_Sql.open_cursor; For i in 1..1000 Loop

DBMS_Sql.parse(cursor_id,'insert into t

values(: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);

--insert into t values('test'||to_char(i),i,Sysdate);

--xSql:='insert into t values(:username,:user_id,Sysdate)'; --execute immediate xSql using 'test'||to_char(i),i;

-------------