动态sql（dbms_sql包的使用）

原文地址：http://blog.csdn.net/skyspark/article/details/1711980

 

1、执行诸如DDL和DDL语句（select除外)

   a)打开一个游标

   b)分析要执行的语句

   c)绑定可能需要的任何输入变量

    d)执行语句

    e)关闭游标

select语句的执行如下：
set serveroutputon;
declare
v_cursoridnumber;
v_selectrecordsvarchar2(500);
v_numrowsinteger;
v_mynuminteger;
v_mytextvarchar(50);
begin
v_cursorid := dbms_sql.open_cursor;
v_selectrecords :='select * from mytable';
dbms_sql.parse(v_cursorid,v_selectrecords,dbms_sql.native);
dbms_sql.define_column(v_cursorid,1,v_mynum);
dbms_sql.define_column(v_cursorid,2,v_mytext,50);
v_numrows := dbms_sql.execute(v_cursorid);
loop
if dbms_sql.fetch_rows(v_cursorid)=0then exit;
endif;
dbms_sql.column_value(v_cursorid,1,v_mynum);
dbms_sql.column_value(v_cursorid,2,v_mytext);
dbms_output.put_line(v_mynum||' '||v_mytext);
end loop;

dbms_sql.close_cursor(v_cursorid);
end;
/

2、select查询的动态sql实现

   a)打开一个游标

   b)分析要执行的语句

   c)绑定可能需要的任何输入变量（如果需要）

  4)定义输出变量

    d)执行语句

  5)取回记录

6)将取回的记录结果存储在pl/sql变量中

    e)关闭游标

create语句的例子如下
declare
v_cursoridnumber;
v_createTableStringvarchar2(500);
v_numrowsinteger;
begin
v_cursorid := dbms_sql.open_cursor;
v_createTableString :='create table mytable(myrow integer,mydesc varchar2(50)) tablespace tabs';
dbms_sql.parse(v_cursorid,v_createTableString,dbms_sql.native);
v_numrows := dbms_sql.execute(v_cursorid);
exception
when othersthen
if sqlcode!=-955then raise;
else
dbms_output.put_line('talbe already exists');
endif;
dbms_sql.close_cursor(v_cursorId);
end;
/
insert语句的例子如下
set serveroutputon;
declare
v_cursoridnumber;
v_insertRecordsvarchar2(500);
v_numrowsinteger;
begin
v_cursorid := dbms_sql.open_cursor;
v_insertRecords :='insert into mytable values(:mynum,:mytext)';
dbms_sql.parse(v_cursorid,v_insertrecords,dbms_sql.native);
dbms_sql.bind_variable(v_cursorid,':mynum',1);
dbms_sql.bind_variable(v_cursorid,':mytext','one');
v_numrows := dbms_sql.execute(v_cursorId);
dbms_output.put_line(v_numrows);

--di 2 tiao ji lu
dbms_sql.bind_variable(v_cursorid,':mynum',2);
dbms_sql.bind_variable(v_cursorid,':mytext','two');
v_numrows := dbms_sql.execute(v_cursorId);
dbms_output.put_line(v_numrows||'2');
exception
when othersthen raise;
dbms_sql.close_cursor(v_cursorid);
commit;
end;
/

3、执行pl/sql匿名块

a)打开一个游标

   b)分析要执行的语句

   c)绑定可能需要的任何输入变量（如果需要）

    d)执行语句

  5)取回记录

6)将取回的记录结果存储在pl/sql变量中

    e)关闭游标

执行匿名块
set serveroutputon;
declare
v_cursoridnumber;
v_matchrecordvarchar2(500);
v_numrowsinteger;
v_mynuminteger;
v_mytextvarchar2(50);
begin
v_cursorid := dbms_sql.open_cursor;
v_matchrecord :='begin
                  select myrow,mydesc into :myrow ,:mytext from mytable
                  where myrow = 2;
                  end;';
dbms_sql.parse(v_cursorid,v_matchrecord,dbms_sql.native);
dbms_sql.bind_variable(v_cursorid,':myrow',v_mynum);
dbms_sql.bind_variable(v_cursorid,':mytext',v_mytext,50);
v_numrows := dbms_sql.execute(v_cursorid);
dbms_sql.variable_value(v_cursorid,':myrow',v_mynum);
dbms_sql.variable_value(v_cursorid,':mytext',v_mytext);

dbms_output.put_line(v_mynum||' '||v_mytext);

dbms_sql.close_cursor(v_cursorid);
end;
/

检查取回记录的进度
set serveroutputon;
declare
v_cursoridnumber;
v_matchrecordvarchar2(500);
v_numrowsinteger;
v_mynuminteger;
v_mytextvarchar2(50);

v_myrowid rowid;
v_totrowinteger;
begin
v_cursorid := dbms_sql.open_cursor;
v_matchrecord :='select * from mytable for update';
dbms_sql.parse(v_cursorid,v_matchrecord,dbms_sql.native);

dbms_sql.define_column(v_cursorid,1,v_mynum);
dbms_sql.define_column(v_cursorid,2,v_mytext,50);

v_numrows := dbms_sql.execute(v_cursorid);

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

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_mynum);
dbms_sql.column_value(v_cursorid,2,v_mytext);
dbms_output.put_line(v_mynum||' '||v_mytext);
end loop;



dbms_sql.close_cursor(v_cursorid);
end;
/