PL/SQL执行动态SQL（二）

PL/SQL动态SQL

原文地址：http://ajita.iteye.com/blog/1487663

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

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

Sql代码

1. declare
2. v_cursorid number;
3. v_insertRecords varchar2(500);
4. v_numrows integer;
5. begin
6. v_cursorid := dbms_sql.open_cursor;
7. v_insertRecords := 'delete from bizbusinessmenuinfo where menubizname=:bizname';
8. dbms_sql.parse(v_cursorid,v_insertrecords,dbms_sql.native);
9. dbms_sql.bind_variable(v_cursorid,':bizname','1');
10. v_numrows := dbms_sql.execute(v_cursorId);
11. dbms_output.put_line(v_numrows);
12. exception
13. when others then raise;
14. dbms_sql.close_cursor(v_cursorid);
15. commit;
16. 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

Sql代码

1. declare
2. v_cursorid number;
3. v_createTableString varchar2(500);
4. v_numrows integer;
5. begin
6. v_cursorid := dbms_sql.open_cursor;
7. v_createTableString := 'create table bizbusinessbaseinfo(bizname varchar2(64),bizstatus char(1))';
8. dbms_sql.parse(v_cursorid,v_createTableString,dbms_sql.native);
9. v_numrows := dbms_sql.execute(v_cursorid);
10. exception
11. when others then
12. if sqlcode!=-955 then raise;
13. else
14. dbms_output.put_line('talbe already exists');
15. end if;
16. dbms_sql.close_cursor(v_cursorId);
17. 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

Sql代码

1. declare
2. v_cursorid number;
3. v_selectrecords varchar2(500);
4. v_numrows integer;
5. v_bizname varchar2(64);
6. v_bizstatus char(1);
7. begin
8. v_cursorid := dbms_sql.open_cursor;
9. v_selectrecords := 'select bizname,bizstatus from bizbusinessbaseinfo where bizname like :bizname';
10. dbms_sql.parse(v_cursorid,v_selectrecords,dbms_sql.native);
11. --dbms_sql.define_column绑定变量的时候，变量如果是字符串，需要执行长度，否则会出现错误： 有太多的 'DEFINE_COLUMN' 说明与此次调用相匹配
12. dbms_sql.define_column(v_cursorid,1,v_bizname,64);
13. dbms_sql.define_column(v_cursorid,2,v_bizstatus,1);
14. dbms_sql.bind_variable(v_cursorid,':bizname','1%');
15. v_numrows := dbms_sql.execute(v_cursorid);
16. loop
17. if dbms_sql.fetch_rows(v_cursorid)=0 then exit;
18. end if;
19. dbms_sql.column_value(v_cursorid,1,v_bizname);
20. dbms_sql.column_value(v_cursorid,2,v_bizstatus);
21. dbms_output.put_line(v_bizname||' '||v_bizstatus);
22. end loop;
24. dbms_sql.close_cursor(v_cursorid);
25. 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
这两个字段可以反映出当前检索的进度。在上面的查询中加入对这两个字段的输出，示例如下：

Sql代码

1. declare
2. v_cursorid number;
3. v_selectrecords varchar2(500);
4. v_numrows integer;
5. v_bizname varchar2(64);
6. v_bizstatus char(1);
8. v_myrowid rowid;
9. v_totrow integer;
10. begin
11. v_cursorid := dbms_sql.open_cursor;
12. v_selectrecords := 'select bizname,bizstatus from bizbusinessbaseinfo where bizname like :bizname';
13. dbms_sql.parse(v_cursorid,v_selectrecords,dbms_sql.native);
14. --dbms_sql.define_column绑定变量的时候，变量如果是字符串，需要执行长度，否则会出现错误： 有太多的 'DEFINE_COLUMN' 说明与此次调用相匹配
15. dbms_sql.define_column(v_cursorid,1,v_bizname,64);
16. dbms_sql.define_column(v_cursorid,2,v_bizstatus,1);
17. dbms_sql.bind_variable(v_cursorid,':bizname','1%');
18. v_numrows := dbms_sql.execute(v_cursorid);
20. v_numrows := dbms_sql.execute(v_cursorid);
22. loop
23. if dbms_sql.fetch_rows(v_cursorid)=0 then exit;
24. end if;
26. v_totrow := dbms_sql.last_row_count;
27. v_myrowid := dbms_sql.last_row_id;
28. dbms_output.put_line('the last row count is:'||v_totrow||
29. ' the last rowid is:'||v_myrowid);
31. dbms_sql.column_value(v_cursorid,1,v_bizname);
32. dbms_sql.column_value(v_cursorid,2,v_bizstatus);
33. dbms_output.put_line(v_bizname||' '||v_bizstatus);
34. end loop;
35. dbms_sql.close_cursor(v_cursorid);
36. end;