PLSQL之DBMS_SQL程序包使用2

测试表

CREATE TABLE demo(
  a NUMBER,
  b NUMBER,
  c NUMBER
);

测试数据

BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO demo(a,b,c) VALUES(
      ROUND(dbms_random.value,2)*100,
      ROUND(dbms_random.value,2)*100,
      ROUND(dbms_random.value,2)*100
    );
  END LOOP;
END;

执行一般的select语句

DECLARE
  v_a NUMBER;
  v_b NUMBER;
  v_cursor NUMBER;                                    --定义光标
  v_string VARCHAR2(200);                             --字符串变量
  v_row NUMBER;                                       --行数
BEGIN
  v_a := 56;
  v_cursor := dbms_sql.open_cursor;                   --为处理打开游标
  v_string := 'select * from demo where a = :p_a';
  dbms_sql.parse(v_cursor,v_string,dbms_sql.native);  --分析语句
  dbms_sql.bind_variable(v_cursor,'p_a',v_a);         --绑定变量
  dbms_sql.define_column(v_cursor,2,v_b);             --定义返回字段
  v_row := dbms_sql.execute(v_cursor);                --执行动态SQL
  LOOP
    IF dbms_sql.fetch_rows(v_cursor) > 0 THEN
      dbms_sql.column_value(v_cursor,2,v_b);
      dbms_output.put_line(' B is ' || v_b);
    ELSE
      EXIT;
    END IF;
  END LOOP;
  dbms_sql.close_cursor(v_cursor);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_sql.close_cursor(v_cursor);
END;

使用define_array方法得到查询结果

DECLARE
  a_table dbms_sql.Number_Table;
  b_table dbms_sql.Number_Table;
  indx NUMBER := 1;
  v_cursor NUMBER;                                             --定义光标
  v_string VARCHAR2(200);                                      --字符串变量
  v_row NUMBER;                                                --行数
BEGIN
  v_cursor := dbms_sql.open_cursor;                            --为处理打开光标
  v_string := 'select * from demo where rownum < 13 order by 1';
  dbms_sql.parse(v_cursor,v_string,dbms_sql.native);  --分析语句
  --table_variable
  --Local variable that has been declared as <datatype>.
  --cnt
  --Number of rows that must be fetched.
  --lower_bnd
  --Results are copied into the collection, starting at this lower bound index.
  dbms_sql.define_array(v_cursor,1,a_table,9,indx);
  dbms_sql.define_array(v_cursor,2,b_table,9,indx);
  v_row := dbms_sql.execute(v_cursor);                         --执行动态SQL
  LOOP
    v_row := dbms_sql.fetch_rows(v_cursor);
    dbms_output.put_line('fetch rows is ' || v_row);
    dbms_sql.column_value(v_cursor,1,a_table);
    dbms_sql.column_value(v_cursor,2,b_table);
    EXIT WHEN v_row < 9;
  END LOOP;
  --输出
  --dbms_output.put_line('a_table.COUNT' || a_table.COUNT);
  FOR i IN 1..a_table.COUNT LOOP
    dbms_output.put_line('A is ' || a_table(i) || ',B is ' || b_table(i));
  END LOOP;
  dbms_sql.close_cursor(v_cursor);                             --关闭光标
  EXCEPTION
    WHEN OTHERS THEN
      IF dbms_sql.is_open(v_cursor) THEN
        dbms_sql.close_cursor(v_cursor);
      END IF;
END;

使用variable_value显示DML后的返回结果（单条记录）

DECLARE
  v_a NUMBER;
  v_b NUMBER;
  v_r NUMBER;
  v_cursor NUMBER;
  v_string VARCHAR2(200);
  v_row NUMBER;
BEGIN
  v_a := 9;
  v_b := 8;
  v_cursor := dbms_sql.open_cursor;                                        --为处理打开光标
  v_string := 'insert into demo(a,b) values(:a,:b) returning :a*:b into :r';
  dbms_sql.parse(v_cursor,v_string,dbms_sql.native);           --分析语句
  dbms_sql.bind_variable(v_cursor,'a',v_a);                    --绑定变量
  dbms_sql.bind_variable(v_cursor,'b',v_b);                    --绑定变量
  dbms_sql.bind_variable(v_cursor,'r',v_r);                    --绑定变量
  v_row := dbms_sql.execute(v_cursor);                         --执行动态SQL
  --使用variable_value函数得到DML操作returning的结果集
  dbms_sql.variable_value(v_cursor,'r',v_r);
  dbms_output.put_line(v_r);
  dbms_sql.close_cursor(v_cursor);
  EXCEPTION
    WHEN OTHERS THEN
      IF dbms_sql.is_open(v_cursor) THEN
        dbms_sql.close_cursor(v_cursor);
      END IF;
END;

使用variable_value显示DML后的返回结果（多条记录）

CREATE OR REPLACE PACKAGE pkg_dbms_sql_demo AS
  PROCEDURE multi_insert;
END pkg_dbms_sql_demo;

CREATE OR REPLACE PACKAGE BODY pkg_dbms_sql_demo AS
  PROCEDURE multi_insert_priv(
    a_table IN dbms_sql.Number_Table,
    b_table IN dbms_sql.Number_Table,
    r_table OUT dbms_sql.Number_Table) 
  IS
    v_cursor NUMBER;
    v_string VARCHAR2(200);
    v_row NUMBER;
  BEGIN
    v_cursor := dbms_sql.open_cursor;                              --为处理打开光标
    v_string := 'insert into demo(a,b) values(:a,:b) returning :a*:b into :r';
    dbms_sql.parse(v_cursor,v_string,dbms_sql.native);             --分析语句
    dbms_sql.bind_array(v_cursor,'a',a_table);                  --绑定变量
    dbms_sql.bind_array(v_cursor,'b',b_table);                  --绑定变量
    dbms_sql.bind_array(v_cursor,'r',r_table);                  --绑定变量
    v_row := dbms_sql.execute(v_cursor);                           --执行动态SQL
    --variable_value函数将returning的结果赋值给number_table类型变量
    dbms_sql.variable_value(v_cursor,'r',r_table);
    dbms_sql.close_cursor(v_cursor);
    EXCEPTION
      WHEN OTHERS THEN
        IF dbms_sql.is_open(v_cursor) THEN
          dbms_sql.close_cursor(v_cursor);
        END IF;
  END;
  
  PROCEDURE multi_insert IS
    a_table dbms_sql.Number_Table;
    b_table dbms_sql.Number_Table;
    r_table dbms_sql.Number_Table;
    v_cursor NUMBER;
    v_string VARCHAR2(200);
    v_result NUMBER;
    v_row NUMBER;
    indx NUMBER := 1;
  BEGIN
    v_cursor := dbms_sql.open_cursor;                                    --为处理打开光标
    v_string := 'select * from demo';
    dbms_sql.parse(v_cursor,v_string,dbms_sql.native);                   --分析语句
    dbms_sql.define_array(v_cursor,1,a_table,5,indx);
    dbms_sql.define_array(v_cursor,2,b_table,5,indx);
    v_result := dbms_sql.execute(v_cursor);                              --执行动态SQL
    
    LOOP
      v_row := dbms_sql.fetch_rows(v_cursor);
      dbms_output.put_line('受影响行数：' || v_row);
      dbms_sql.column_value(v_cursor,1,a_table);
      dbms_sql.column_value(v_cursor,2,b_table);
      multi_insert_priv(a_table,b_table,r_table);
      EXIT WHEN v_row < 5;
    END LOOP;
    --输出
    dbms_output.put_line(r_table.COUNT);
    FOR i IN 1.. r_table.COUNT LOOP
      dbms_output.put_line('r_table(' || i ')=' || r_table(i));
    END LOOP;
    EXCEPTION
      WHEN OTHERS THEN
        IF dbms_sql.is_open(v_cursor) THEN
          dbms_sql.close_cursor(v_cursor);
        END IF;
  END;
END pkg_dbms_sql_demo;