Oracle之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 等方法 ,具体可以看如下流程图:
-- The flow of procedure calls will typically look like this:
--
-- -----------
-- | open_cursor |
-- -----------
-- |
-- |
-- v
-- -----
-- ------------>| parse |
-- | -----
-- | |
-- | |---------
-- | v |
-- | -------------- |
-- |-------->| bind_variable | |
-- | ^ ------------- |
-- | | | |
-- | -----------| |
-- | |<--------
-- | v
-- | query?---------- yes ---------
-- | | |
-- | no |
-- | | |
-- | v v
-- | ------- -------------
-- |----------->| execute | ->| define_column |
-- | ------- | -------------
-- | |------------ | |
-- | | | ----------|
-- | v | v
-- | -------------- | -------
-- | ->| variable_value | | ------>| execute |
-- | | -------------- | | -------
-- | | | | | |
-- | ----------| | | |
-- | | | | v
-- | | | | ----------
-- | |<----------- |----->| fetch_rows |
-- | | | ----------
-- | | | |
-- | | | v
-- | | | --------------------
-- | | | | column_value |
-- | | | | variable_value |
-- | | | ---------------------
-- | | | |
-- | |<--------------------------
-- | |
-- -----------------|
-- |
-- v
-- ------------
-- | close_cursor |
-- ------------
--
下面根据不同情况 用例子 详细展示:
在做展示之前,先准备一些基础数据
create table cux_demo (a number,b number,c number);
begin
for i in 1 .. 15 loop
insert into cux_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 语句
首先先介绍最常用情况:
DECLARE
p_no NUMBER := 80;
cursor_name INTEGER := dbms_sql.open_cursor; --在初始化参数时,就可以打开cursor;
row_process INTEGER;
v_b NUMBER;
BEGIN
--解析要执行的SQL.
dbms_sql.parse(cursor_name,
'select * from cux_demo where a= :no',
dbms_sql.native);
--如果要执行的SQL中不需要参数,则可以省略掉bind_variable--
dbms_sql.bind_variable(cursor_name,
'no',
p_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.
DECLARE
c NUMBER;
d NUMBER;
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 cux_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)返回单条记录
DECLARE
c1 NUMBER := 100;
c2 NUMBER := 100;
r NUMBER;
cursor_name NUMBER := dbms_sql.open_cursor;
n NUMBER;
BEGIN
dbms_sql.parse(cursor_name,
'insert into cux_demo(a,b) 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 操作。
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 cux_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 cux_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;