PL/SQL执行动态SQL（二）

PL/SQL动态SQL

依据：
DBMS_SQL包：这个包提供了一种使用动态sql来访问数据库的方法。

示例
1.执行插入删除等DML语句

declare
  v_cursorid number;
  v_insertRecords varchar2(500);
  v_numrows integer;
begin
  v_cursorid := dbms_sql.open_cursor;
  v_insertRecords := 'delete from bizbusinessmenuinfo where menubizname=:bizname';
  dbms_sql.parse(v_cursorid,v_insertrecords,dbms_sql.native);
  dbms_sql.bind_variable(v_cursorid,':bizname','1');
  v_numrows := dbms_sql.execute(v_cursorId);
  dbms_output.put_line(v_numrows);
  exception
    when others then raise;
  dbms_sql.close_cursor(v_cursorid);
  commit;
end;

2.执行动态DDL

declare
  v_cursorid number;
  v_createTableString varchar2(500);
  v_numrows integer;
begin
  v_cursorid := dbms_sql.open_cursor;
  v_createTableString := 'create table bizbusinessbaseinfo(bizname varchar2(64),bizstatus char(1))';
  dbms_sql.parse(v_cursorid,v_createTableString,dbms_sql.native);
  v_numrows := dbms_sql.execute(v_cursorid);
  exception
    when others then
    if sqlcode!=-955 then raise;
    else
       dbms_output.put_line('talbe already exists');
    end if;
  dbms_sql.close_cursor(v_cursorId);
end;

3.动态查询query

declare
  v_cursorid number;
  v_selectrecords varchar2(500);
  v_numrows integer;
  v_bizname varchar2(64);
  v_bizstatus char(1);
begin
  v_cursorid := dbms_sql.open_cursor;
  v_selectrecords := 'select bizname,bizstatus from bizbusinessbaseinfo where bizname like :bizname';
  dbms_sql.parse(v_cursorid,v_selectrecords,dbms_sql.native);
  --dbms_sql.define_column绑定变量的时候，变量如果是字符串，需要执行长度，否则会出现错误： 有太多的 'DEFINE_COLUMN' 说明与此次调用相匹配 
  dbms_sql.define_column(v_cursorid,1,v_bizname,64);
  dbms_sql.define_column(v_cursorid,2,v_bizstatus,1);  
  dbms_sql.bind_variable(v_cursorid,':bizname','1%');
  v_numrows := dbms_sql.execute(v_cursorid);
  loop
    if dbms_sql.fetch_rows(v_cursorid)=0 then exit;
    end if;
    dbms_sql.column_value(v_cursorid,1,v_bizname);
    dbms_sql.column_value(v_cursorid,2,v_bizstatus);
    dbms_output.put_line(v_bizname||'  '||v_bizstatus);
  end loop;

  dbms_sql.close_cursor(v_cursorid);
end;

4.动态查询的dbms_sql.last_row_count和dbms_sql.last_row_id
这两个字段可以反映出当前检索的进度。在上面的查询中加入对这两个字段的输出，示例如下：

declare
  v_cursorid number;
  v_selectrecords varchar2(500);
  v_numrows integer;
  v_bizname varchar2(64);
  v_bizstatus char(1);

v_myrowid rowid;
v_totrow integer;
begin
  v_cursorid := dbms_sql.open_cursor;
  v_selectrecords := 'select bizname,bizstatus from bizbusinessbaseinfo where bizname like :bizname';
  dbms_sql.parse(v_cursorid,v_selectrecords,dbms_sql.native);
  --dbms_sql.define_column绑定变量的时候，变量如果是字符串，需要执行长度，否则会出现错误： 有太多的 'DEFINE_COLUMN' 说明与此次调用相匹配 
  dbms_sql.define_column(v_cursorid,1,v_bizname,64);
  dbms_sql.define_column(v_cursorid,2,v_bizstatus,1);  
  dbms_sql.bind_variable(v_cursorid,':bizname','1%');
  v_numrows := dbms_sql.execute(v_cursorid);

  v_numrows := dbms_sql.execute(v_cursorid);

  loop
    if dbms_sql.fetch_rows(v_cursorid)=0 then exit;
    end if;

    v_totrow := dbms_sql.last_row_count;
    v_myrowid := dbms_sql.last_row_id;
    dbms_output.put_line('the last row count is:'||v_totrow||
    '  the last rowid is:'||v_myrowid);

    dbms_sql.column_value(v_cursorid,1,v_bizname);
    dbms_sql.column_value(v_cursorid,2,v_bizstatus);
    dbms_output.put_line(v_bizname||'  '||v_bizstatus);
  end loop;
dbms_sql.close_cursor(v_cursorid);
end;