dbms_sql与execute_immediate区别与使用

 

区别:

如何使用动态SQL

实现动态SQL有两种方式:

DBMS_SQL和本地动态SQL(EXECUTE IMMEIDATE)主要从以下方面考虑使用哪种方式:

1. 是否知道涉及的列数和类型DBMS_SQL包括了一个可以“描述”结果集的存储过程(DBMS_SQL.DESCRIBE_COLUMNS),而本地动态SQL没有。

2. 是否知道可能涉及的绑定变量数和类型DBMS_SQL答应过程化的绑定语句的输入,而本地动态SQL需要在编译时确定。

3. 是否使用“数组化”操作(Array Processing)DBMS_SQL答应,而本地动态SQL基本不可以,但可以用其他方式实现(对查询可用FETCH BULK COLLECT INTO,对INSERT等,可用一个BEGIN … END块中加循环实现)。

4. 是否在同一个会话中多次执行同一语句DBMS_SQL可以分析一次执行多次,而本地动态SQL会在每次执行时进行软分析。5. 是否需要用REF CURSOR返回结果集仅本地动态SQL可用REF CURSOR返回结果集。

如何使用动态SQL

DBMS_SQL

1. 调用OPEN_CURSOR获得一个游标句柄;

2. 调用PARSE分析语句。一个游标句柄可以用于多条不同的已分析语句,但一个时间点仅一条有效;

3. 调用BIND_VARIABLE或BIND_ARRAY来提供语句的任何输入;

4. 若是一个查询(SELECT语句),调用DIFINE_COLUMN或DEFINE_ARRAY来告知Oracle如何返回结果;

注:DIFINE_COLUMN(CURSOR_NAME,第几列,变量[,大小]);第几列是从1开始,变量类型如果是字符类型则还需要指定字符类型大小

5. 调用EXECUTE执行语句;

6. 若是一个查询,调用FETCH_ROWS来读取数据。可以使用COLUMN_VALUE从SELECT列表根据位置获得这些值;

7. 否则,若是一个PL/SQL块或带有RETURN子句的DML语句,可以调用VARIABLE_VALUE从块中根据变量名获得OUT值;

8. 调用CLOSE_CURSOR.注重这里对任何异常都应该处理,以关闭游标,防止泄露资源。

本地动态SQL

EXECUTE IMMEDIATE ‘语句’

[INTO {变量1, 变量2, … 变量N 记录体}]

[USING [IN OUT IN OUT] 绑定变量1, … 绑定变量N]

[{RETURNING RETURN} INTO 输出1 [, …, 输出N]…];

注重本地动态SQL仅支持弱类型REF CURSOR,即对于REF CURSOR,不支持BULK COLLECT.

最后说明

动态SQL的负面:破坏了依靠链、代码更脆弱、很难调优。

(转) http://space.itpub.net/trackback.php?code=YAqD0FYq10O5x3CWkb1UlC4DA8kEU1UJ8UcImmaBzg

DBMS_SQL实例:

通常运用DBMS_SQL包一般分为几步:
1. open cursor: 打开cursor
2. parse cursor:解析你要执行的SQL语句
3. bind variable:如果要执行的SQL语句中包含变量,在此就需要绑定变量
4. execute:执行SQL语句
5. close cursor:在执行后关闭此cursor.
如果你还需要返回执行SQL的结果集,还需要使用define_column,define_array等方法。

下面根据不同情况进行详细展示:
在做展示之前,先准备一些基础数据
create table demo (a number,b number,c number);
begin
for i in 1 .. 15 loop
insert into demo
values
(round(dbms_random.value, 2) * 100,
round(dbms_random.value, 2) * 100,
round(dbms_random.value, 2) * 100);
end loop;
commit;
end;

基础数据完成之后,下面开始对一些具体情况进行分析:
1.执行一般的select语句
首先先介绍最常用情况:
create or replace procedure define_column(no in number) is
cursor_name integer := dbms_sql.open_cursor; --在初始化参数时,就可以打开cursor;
row_process integer;
v_b number;
begin
--解析要执行的SQL.
dbms_sql.parse(cursor_name,
'select * from demo where a= :no',
dbms_sql.native);
--如果要执行的SQL中不需要参数,则可以省略掉bind_variable--
dbms_sql.bind_variable(cursor_name, 'no', no);
/*如果需要返回查询语句的结果,则必须在exec之前使用define_column函数定义返回字段;define_column函数的第一个参数是最初定义的cursor name,第二个参数是指需要返回的字段在查询结果中处于第几列,在此例中返回的字段是查询结果中的第二列,即b列;第三个参数就是接收返回结果需要的变量*/
dbms_sql.define_column(cursor_name, 2, v_b);
--必须定义一个参数接收exec的结果
row_process := dbms_sql.execute(cursor_name);
loop
if dbms_sql.fetch_rows(cursor_name) > 0 then
--将前面定义的字段返回给变量v_b--
dbms_sql.column_value(cursor_name, 2, v_b);
dbms_output.put_line('B is ' || v_b);
else
exit;
end if;
end loop;
--数据处理完成后记得要将cursor关闭
dbms_sql.close_cursor(cursor_name);
exception
when others then
dbms_sql.close_cursor(cursor_name);
end;

2.使用define_array方法得到查询结果
前面已经分析了如何使用define_column方法得到查询结果,但有时我们想要一次得到多行查询结果,此时我们就需要使用define_array方法,此方法常用于DML操作,稍后会有例子对此介绍,现在先来看一下如果使用define_array.

create or replace procedure define_array is
c NUMBER;
d NUMBER;
/*DBMS_SQL.NUMBER_TABLE类型实际就是type NUMBER_TABLE is table of number index by binary_integer;*/
n_tab DBMS_SQL.NUMBER_TABLE;
n_tab1 DBMS_SQL.NUMBER_TABLE;
indx NUMBER := 1;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c,
'select * from demo where rownum<13 order by 1',
DBMS_SQL.NATIVE);
/*在此需要特别介绍一下define_array函数的第一个参数是已经打开的cursor名称, 第二个参数是指需要返回的字段在查询结果中处于第几列,第三个参数就是接收返回结果需要的变量,与define_column不同的是此变量是table,而不是普通的字段类型;第四个参数表示一次可以返回的行数;第五个参数是指n_tab的index从哪个数值开始,此数值是递增的.在此例中index是从1开始的,一次得到9行结果集,则有n_tab(1)到n_tab(9),如果循环再得到新的结果集,则index继续增长n_tab(10)....*/
DBMS_SQL.DEFINE_ARRAY(c, 1, n_tab, 9, indx);
DBMS_SQL.DEFINE_ARRAY(c, 2, n_tab1, 9, indx);
d := DBMS_SQL.EXECUTE(c);
loop
d := DBMS_SQL.FETCH_ROWS(c);
dbms_output.put_line('fetch rows is ' || d);
EXIT WHEN d < 9;
DBMS_SQL.COLUMN_VALUE(c, 1, n_tab);
DBMS_SQL.COLUMN_VALUE(c, 2, n_tab1);
for i in 1 .. d loop
dbms_output.put_line(n_tab(i) || ',' || n_tab1(i));
end loop;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(c) THEN
DBMS_SQL.CLOSE_CURSOR(c);
END IF;
END;

3.使用variable_value显示DML后的返回结果(单条记录)
以上我们介绍了如何使用DBMS_SQL包来处理数据查询,如果我们把查询语句更换成DML语句,则可以完成各种DML操作。
在PL/SQL中我们可以使用returning方法返回DML操作结果,在DBMS_SQL包中可不可以实现呢?答案当然是可以,用variable_value方法就可以实现。下面就分别用两个例子来展示如何实现,一个是返回单条记录,另一个是返回多条记录。

(1)返回单条记录
procedure single_insert(c1 in number, c2 in number, r out number) is

cursor_name number := dbms_sql.open_cursor;
n number;
begin
dbms_sql.parse(cursor_name,
'insert into demo values (:a,:b) returning :a*:b into :r',
dbms_sql.native);
dbms_sql.bind_variable(cursor_name, 'a', c1);
dbms_sql.bind_variable(cursor_name, 'b', c2);
dbms_sql.bind_variable(cursor_name, 'r', r);
n := dbms_sql.execute(cursor_name);
--使用variable_value函数得到DML操作returning的结果集
dbms_sql.variable_value(cursor_name, 'r', r);
dbms_output.put_line(r);
dbms_sql.close_cursor(cursor_name);
exception
when others then
dbms_sql.close_cursor(cursor_name);
end;
(2)返回多条记录
结合define_array使用,可以更好的完成DML操作。

create or replace package DBMS_SQL_DEMO as

procedure multi_insert;

end;
/

create or replace package body DBMS_SQL_DEMO as

procedure multi_insert_priv(c1 in dbms_sql.Number_Table,
c2 in dbms_sql.Number_Table,
r out dbms_sql.Number_Table) is

cursor_name number := dbms_sql.open_cursor;
n number;
begin
dbms_sql.parse(cursor_name,
'insert into demo values (:a,:b) returning :a*:b into :r',
dbms_sql.native);
--使用bind_array函数将number_table类型的变量赋值给绑定变量
dbms_sql.bind_array(cursor_name, 'a', c1);
dbms_sql.bind_array(cursor_name, 'b', c2);
dbms_sql.bind_array(cursor_name, 'r', r);
n := dbms_sql.execute(cursor_name);
--使用variable_value函数将returning的结果集赋值给number_table类型的变量
dbms_sql.variable_value(cursor_name, 'r', r);
dbms_sql.close_cursor(cursor_name);
exception
when others then
dbms_sql.close_cursor(cursor_name);
end;

procedure multi_insert is
c1 dbms_sql.Number_Table;
c2 dbms_sql.Number_Table;
cursor_name number := dbms_sql.open_cursor;
n number;
r dbms_sql.Number_Table;
indx number := 1;
d number;
begin
dbms_sql.parse(cursor_name, 'select * from demo', dbms_sql.native);
dbms_sql.define_array(cursor_name, 1, c1, 5, indx);
dbms_sql.define_array(cursor_name, 2, c2, 5, indx);
n := dbms_sql.execute(cursor_name);
loop
d := dbms_sql.fetch_rows(cursor_name);
exit when d = 0;
dbms_sql.column_value(cursor_name, 1, c1);
dbms_sql.column_value(cursor_name, 2, c2);
multi_insert_priv(c1, c2, r);
for i in 1 .. r.count loop
dbms_output.put_line(r(i));
end loop;
end loop;
exception
when others then
dbms_sql.close_cursor(cursor_name);
end;

end;
/

希望以上的内容能够给你工作带来帮助。

  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值