Oracle之DBMS_SQL包用法详解
通常运用DBMS_SQL包一般分为如下几步：1. open cursor：打开cursor2. parse cursor：解析你要执行的SQL语句
3. bind variable：如果要执行的SQL语句中包含变量，在此就需要绑定变量
4. execute：执行SQL语句
5. close cursor：在执行后关闭此cursor.
如果你还需要返回执行SQL的结果集，还需要使用define_column,define_array等方法，具体可以看如下流程图：
Sql代码http://www.iot.idv.tw/images/icon_star.pnghttp://www.iot.idv.tw/images/spinner.gif
--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 |
-- ------------
--
[*]
下面根据不同情况用例子详细展示：在做展示之前，先准备一些基础数据
Sql代码http://www.iot.idv.tw/images/icon_star.pnghttp://www.iot.idv.tw/images/spinner.gif
[*]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;
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语句首先先介绍最常用情况：
Sql代码http://www.iot.idv.tw/images/icon_star.pnghttp://www.iot.idv.tw/images/spinner.gif
[*]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;
[*]DECLARE
[*]c NUMBER;
[*]d NUMBER;
[*]n_tabdbms_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）返回单条记录
Sql代码http://www.iot.idv.tw/images/icon_star.pnghttp://www.iot.idv.tw/images/spinner.gif
[*]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操作。
Sql代码http://www.iot.idv.tw/images/icon_star.pnghttp://www.iot.idv.tw/images/spinner.gif
[*]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;
頁:
[1]