dbms_sql

DECLARE
  p_no        NUMBER := 50;
  cursor_name INTEGER := dbms_sql.open_cursor; --在初始化参数时,就可以打开cursor; 
  row_process INTEGER;
  v_b         NUMBER;
BEGIN
  --解析要执行的SQL. 
  dbms_sql.parse(cursor_name,
                 'select * from test 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 test 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;