现在的位置: 首页 > 数据库 > 正文

Oracle动态SQL和绑定变量

2016年04月16日 数据库 ⁄ 共 3268字 暂无评论 ⁄ 阅读 1,644 次
文章目录

【动态SQL定义】

动态SQL是指在PL/SQL块中,可以根据不同参数拼接不同的SQL字符串,即执行前不能确定该SQL是什么(如表名、字段名或条件值未知)。

【动态SQL与静态SQL区别】

1)静态SQL是确定的,在执行前已经完成编译(随PL/SQL块一起完成了编译),执行时数据库直接执行编译好的SQL;而动态SQL是不确定的,是在程序运行时才编译并执行(不随PL/SQL块编译时编译)。

2)静态SQL一次编译,多次调用,使用相同的执行计划。动态SQL每次运行均要先对其编译,即多次调用则需要多次编译。

3)静态SQL使用相同的执行计划,对于确定的任务而言,静态SQL更具有高效性,但缺乏灵活性;动态SQL使用了不同的执行计划,效率不如静态SQL,但能够解决复杂的问题。

4)动态SQL容易产生SQL注入,为数据库安全带来隐患。

【动态SQL用途】

1)处理PL/SQL块中不能处理的DDL或DCL语句;

2)处理PL/SQL块中带参数具有不确定性的SQL(如表名、字段名或条件值作为变量);

3)PL/SQL中静态SQL出现性能瓶颈;

【动态SQL语法】

execute immediate 'sql';

--区别于静态SQL变量赋值的INTO位置,select col into var from table;

execute immediate 'sql_select' into var_1, var_2;

execute immediate 'sql' using bind_var_1,bind_var_2;

execute immediate 'sql_select' into var_1, var_2 using bind_var_1,bind_var_2;

-- returning子句在insert之后返回新加的值,update之后返回修改后的值,delete返回删除前的值

execute immediate 'sql_dml' returning into var_1;

--批量动态SQL,即在动态SQL中使用BULK子句,或使用游标变量时在fetch中使用BULK,或在FORALL语句中使用BULK子句来实现

execute immediate 'sql' bulk collect into var_array;

--动态REF游标,不同于静态游标声明方式

open cursor_name for ‘sql’ using bind_var_1;

【动态SQL举例】

--注:变量不能放在引号内,否则会解析成文本而不是变量,但若在DML语句的绑定变量,则需要在引号内,拼SQL时一定要注意关键字后的空格,如truncate table之后是有一个空格的

--注:INTO的位置,不像静态SQL一样在字段名后,而是在execute immediate时才加,且关键字在using之前;另外,注意区分有returning子句的,returning子句在SQL文本和execute immediate子句中均有出现,但此时INTO的位置在using之后。

--注:动态SQL内的结尾不加分号“;”,但PL/SQL的语句结尾加“;”,且冒号后的占位符是字母或数字完全没有影响,using的变量值可以是绑定变量、或变量或常量均可。如:占位符:percent对应绑定变量&1,虽然名字完全不同,但也不影响,另外,占位符:2对应了变量v_eno,也是完全不同。即,按顺序占位,与占位符名无关。按顺序绑定变量,与绑定变量名无关。给占位符或绑定变量命名只是为了程序的可读性。

【绑定变量】

Oracle会自动把循环中带参数SQL语句转换为采用绑定变量方式执行,以减少硬解析和latch竞争。所以,很多时候我们并不需要专门去注意在PL/SQL块中使用绑定变量,Oracle会自动帮我们完成这个动作。至于SQLPLUS中使用绑定变量,其实我们日常也较少使用SQLPLUSE,故也无需专门注意该问题。

/*sqlplus中使用绑定变量*/

variable x number(4);

exec :x := 1;

select * from emp t where empno = :x;

--注:必须先使用关键字variable声明变量,再使用exec给绑定变量赋值(变量前带冒号),最后使用绑定变量。

绑定变量效率高的原因

Oracle中,对于一个提交的sql语句,存在两种可选的解析过程,一种叫做硬解析,一种叫做软解析。当一个sql语句提交后,oracle会首先检查一下共享缓冲池(shared pool)里有没有与之完全相同的语句,如果有的话只须执行软分析即可,否则就得进行硬分析。

硬解析需要经解析,制定执行路径、优化访问计划等许多的步骤。硬解释不仅仅耗费大量的cpu,更重要的是会占据重要闩(latch)资源,严重的影响系统规模扩大(即限制了系统的并发行),而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为闩是为了顺序访问以及修改一些内存区域而设置的,这些内存区域是不能被同时修改。

若Oracle在shared pool中查找相同SQL语句的过程中,SQL语句使用了绑定变量(bind variable),那么就是比较SQL语句的静态部分,前面我们已经知道,静态部分是有限的,很容易就能够缓存在内存里,从而找到相同的SQL语句的概率很高。如果没有使用绑定变量,则就是比较SQL语句的静态部分和动态部分,而动态部分的变化是无限的,因此这样的SQL语句很难被缓存在shared pool里。毕竟内存是有限的,不可能把所有的动态部分都缓存在sharedpool里,即便能够缓存,管理这样一个无限大的shared pool也是不可能完成的任务。不使用绑定变量导致的直接结果就是,找到相同的SQL语句的概率很低,导致必须完整的解析SQL语句,也就导致消耗更多的资源。

绑定变量的使用环境 

由于在OLTP中,SQL语句大多是比较简单或者操作的结果集都很小。当一个表上创建了索引,那么这种极小结果集的操作使用索引最合适,并且几乎所有的SQL的执行计划的索引都会被选择,因为这种情况下,索引可能只需要扫描几个数据块就可以定位到数据,而全表扫描将会相当耗资源。因此,这种情况下,即使每个用户的谓词条件不一样,执行计划也是一样的,就是都用索引来访问数据,基本不会出现全表扫描的情况。在这种执行计划几乎唯一的情况下,使用绑定变量来代替谓词常量是合适的。

注意,在OLTP中使用绑定变量一定要注意,变量的类型要与表字段类型一样,否则若造成了隐式转换,索引将失效而采用全表扫描,此时会造成系统性能的极大下降。

在OLAP系统中,SQL的操作就复杂很多,OLAP数据库上大多数时候运行的一些报表SQL,这些SQL经常会用到聚合查询(如:groupby),而且结果集也是非常庞大,在这种情况下,索引并不是必然的选择,甚至有时候全表扫描的性能会更优于索引,即使相同的SQL,如果谓词不同,执行计划都可能不同。让Oracle对每条SQL做硬分析,确切的知道谓词条件的值,这对执行计划的选择至关重要,这样做的原因是为了得到一个最优的执行计划。在OLAP系统中,系统的资源基本上是用于做大的SQL查询,和查询比起来SQL解析消耗的资源显得微不足道,SQL硬分析的代价是可以忽略的。因此让Oracle确切地知道谓词的数值至关重要,它直接决定了SQL执行计划的选择,所以在OLAP系统完全没有必要绑定变量,那样很可能带来负面影响,比如导致SQL选择错误的执行,这个代价有时是灾难性的。

 

【文章参考】

http://blog.csdn.net/leshami/article/details/6118010

http://czmmiao.iteye.com/blog/1489625

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?