动态sql（dbms_sql包的使用）

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

   a)打开一个游标

   b)分析要执行的语句

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

    d)执行语句

    e)关闭游标

select语句的执行如下：
set serveroutput  on;
declare
v_cursorid  number;
v_selectrecords  varchar2( 500);
v_numrows  integer;
v_mynum  integer;
v_mytext  varchar( 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) = 0  then  exit;
end  if;
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_cursorid  number;
v_createTableString  varchar2( 500);
v_numrows  integer;
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 others  then
if sqlcode !=- 955  then raise;
else
dbms_output.put_line( ' talbe already exists ');
end  if;
dbms_sql.close_cursor(v_cursorId);
end;
/
insert语句的例子如下
set serveroutput  on;
declare
v_cursorid  number;
v_insertRecords  varchar2( 500);
v_numrows  integer;
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 others  then raise;
dbms_sql.close_cursor(v_cursorid);
commit;
end;
/

3、执行pl/sql匿名块

a)打开一个游标

   b)分析要执行的语句

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

    d)执行语句

  5)取回记录

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

    e)关闭游标

 

执行匿名块
set serveroutput  on;
declare
v_cursorid  number;
v_matchrecord  varchar2( 500);
v_numrows  integer;
v_mynum  integer;
v_mytext  varchar2( 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 serveroutput  on;
declare
v_cursorid  number;
v_matchrecord  varchar2( 500);
v_numrows  integer;
v_mynum  integer;
v_mytext  varchar2( 50);

v_myrowid rowid;
v_totrow  integer;
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) = 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_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;
/