PL/SQL动态SQL
原文地址:http://ajita.iteye.com/blog/1487663
依据:
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;
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;
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;
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;