这段代码同样是执行了1000条insert语句,但是每一条语句都是不同的,因此ORACLE会把每条语句硬解析一次,其效率就比前面那段就低得多了。如果要提高效率,不妨使用绑定变量将循环中的语句改为
sqlstr:='insert into 测试表 (:i,:i+1,:i*1,:i*2,:i-1) ';
execute immediate sqlstr using i,i,i,i,i;
这样执行的效率就高得多了。
我曾试着使用绑定变量来代替表名、过程名、字段名等,结果是语句错误,结论就是绑定变量不能当作嵌入的字符串来使用,只能当作语句中的变量来用。
从效率来看,由于oracle10G放弃了RBO,全面引入CBO,因此,在10G中使用绑定变量效率的提升比9i中更为明显。
最后,前面说到绑定变量是在通常情况下能提升效率,那哪些是不通常的情况呢?
答案是:在字段(包括字段集)建有索引,且字段(集)的集的势非常大(也就是有个值在字段中出现的比例特别的大)的情况下,使用绑定变量可能会导致查询计划错误,因而会使查询效率非常低。这种情况最好不要使用绑定变量。
EXECUTE IMMEDIATE代替了以前Oracle8i中DBMS_SQL package包.它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前,EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能,较之以前它相当容易编码.尽管DBMS_SQL仍然可用,但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。
使用技巧
1. EXECUTE IMMEDIATE将不会提交一个DML事务执行,应该显式提交
如果通过EXECUTE IMMEDIATE处理DML命令,那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分. 如果通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据
2. 不支持返回多行的查询,这种交互将用临时表来存储记录(参照例子如下)或者用REF cursors.
3. 当执行SQL语句时,不要用分号,当执行PL/SQL块时,在其尾部用分号.
4. 在Oracle手册中,未详细覆盖这些功能。下面的例子展示了所有用到Execute immediate的可能方面.希望能给你带来方便.
5. 对于Forms开发者,当在PL/SQL 8.0.6.3.版本中,Forms 6i不能使用此功能.
EXECUTE IMMEDIATE用法例子
1. 在PL/SQL运行DDL语句
begin
execute immediate 'set role all';
end;
2. 给动态语句传值(USING 子句)
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
3. 从动态语句检索值(INTO子句)
declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;
4. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;
if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;
5. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量
declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;
6. 传递并检索值.INTO子句用在USING子句前
declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;
7. 多行查询选项.对此选项用insert语句填充临时表,用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.
declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' ||
' select empno, ename from emp ' ||
' where sal > :1'
using l_sal;
commit;
end;
对于处理动态语句,EXECUTE IMMEDIATE比以前可能用到的更容易并且更高效.当意图执行动态语句时,适当地处理异常更加重要.应该关注于捕获所有可能的异常.
分享到:
相关推荐
Oracle中EXECUTE IMMEDIATE用法
execute immediate 用法研究 为初学者打造。
用execute immediate 执行备份恢复SQLSVR数据库
EXECUTE IMMEDIATE将不会提交一个DML事务执行,应该显式提交 如果通过EXECUTE IMMEDIATE处理DML命令, 那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分. 如果通过EXECUTE IMMEDIATE处理DDL命令,
NULL 博文链接:https://wushuangyan26.iteye.com/blog/1306094
oracle动态sql之EXECUTE IMMEDIATE.docx
在Oracle中若删除一个不存在的表,如 “DROP TABLE tableName”,则会提示: ORA-00942:表或视图不存在 若在程序中执行该语句则会报异常,这就需要...EXECUTE IMMEDIATE 'DROP TABLE tableName'; END IF; END; 在Oracle中
OCILIB是一个便携式Oracle驱动程序,可提供非常快速和可靠地访问Oracle数据库。它提供了一个丰富,功能齐全,并易于使用的API 。OCILIB
整理的Oracle数据库游标使用大全 ...较好的PL/SQL程序设计是在PL/SQL块中使用象DBMS_SQL这样的内建包或执行EXECUTE IMMEDIATE命令建立动态SQL来执行DDL命令,PL/SQL编译器保证对象引用以及用户的权限。。。。。。
execute immediate v_sql into v_count; p_OutRecordCount := v_count; ----执行分页查询 v_heiRownum := p_PageNo * p_PageSize; v_lowRownum := v_heiRownum - p_PageSize + 1; v_sql := 'SELECT * FROM ...
如果需要在pl/sql 程序中使用动态sql,建议使用execute immediate 对于非常大的表,考虑使用表和索引的分区 如果需要在创建索引的时候减少所需时间,可以在会话集设置比较大的sort_area_size 考虑更多的使用decode...
存储过程.txt EXECUTE IMMEDIATE.txt
execute immediate 'create table newTypes(id integer primary key,name nvarchar2(200))'; --create table ... PL/SQL中不能直接使用数据定义语言,而动态sql中允许 sql_str:='select id, name from producttype...
ORACLE 培训 动态SQL编写 EXECUTE IMMEDIATE用法 DBMS_SQL用法
execute immediate str_sql into tabtcn; --动态执行DDL语句
动态PL/SQL用法例子 begin execute immediate 'create table test_qiu(id number)'; end;
在ORACLE数据库杀掉会话进程有三种方式: 1:ALTER SYSTEM KILL SESSION 关于KILL SESSION Clause ,官方文档描述alter system kill session实际上...Specify IMMEDIATE to instruct Oracle Database to roll back on
8.6.1 EXECUTE IMMEDIATE 立即执行语句...................... 278 8.6.2 PREPARE 准备语句 ................................... 278 8.6.3 EXCUTE 执行语句 .................................... 279...
linux oracle 12c 自动启动.docx Oracle12c2 开机自动启动 触发器 SQL> create or replace trigger open_pbds ... 4 execute immediate 'ALTER PLUGGABLE DATABASE ALL OPEN'; 5 end open_pdbs; 6 /