Oracle之DBMS_SQL包用法详解

admin

通常运用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等方法，具体可以看如下流程图：
Sql代码  [url=][/url]

1. --  The flow of procedure calls will typically look like this:  
   
2. --  
   
3. --                      -----------  
   
4. --                    | open_cursor |  
   
5. --                      -----------  
   
6. --                           |  
   
7. --                           |  
   
8. --                           v  
   
9. --                         -----  
   
10. --          ------------>| parse |  
    
11. --         |               -----  
    
12. --         |                 |  
    
13. --         |                 |---------  
    
14. --         |                 v         |  
    
15. --         |           --------------  |  
    
16. --         |-------->| bind_variable | |  
    
17. --         |     ^     -------------   |  
    
18. --         |     |           |         |  
    
19. --         |      -----------|         |  
    
20. --         |                 |<--------  
    
21. --         |                 v  
    
22. --         |               query?---------- yes ---------  
    
23. --         |                 |                           |  
    
24. --         |                no                           |  
    
25. --         |                 |                           |  
    
26. --         |                 v                           v  
    
27. --         |              -------                  -------------  
    
28. --         |----------->| execute |            ->| define_column |  
    
29. --         |              -------             |    -------------  
    
30. --         |                 |------------    |          |  
    
31. --         |                 |            |    ----------|  
    
32. --         |                 v            |              v  
    
33. --         |           --------------     |           -------  
    
34. --         |       ->| variable_value |   |  ------>| execute |  
    
35. --         |      |    --------------     | |         -------  
    
36. --         |      |          |            | |            |  
    
37. --         |       ----------|            | |            |  
    
38. --         |                 |            | |            v  
    
39. --         |                 |            | |        ----------  
    
40. --         |                 |<-----------  |----->| fetch_rows |  
    
41. --         |                 |              |        ----------  
    
42. --         |                 |              |            |  
    
43. --         |                 |              |            v  
    
44. --         |                 |              |    --------------------  
    
45. --         |                 |              |  | column_value         |  
    
46. --         |                 |              |  | variable_value       |  
    
47. --         |                 |              |    ---------------------  
    
48. --         |                 |              |            |  
    
49. --         |                 |<--------------------------  
    
50. --         |                 |  
    
51. --          -----------------|  
    
52. --                           |  
    
53. --                           v  
    
54. --                      ------------  
    
55. --                    | close_cursor |  
    
56. --                      ------------  
    
57. --  

複製代碼

• 
  

下面根据不同情况用例子详细展示：

在做展示之前，先准备一些基础数据
Sql代码  [url=][/url]

• 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代码  [url=][/url]

• 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_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）返回单条记录
Sql代码  [url=][/url]

• 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代码  [url=][/url]

• 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;