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 ;
/
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 ;
/
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 );
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 ;
/
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 ;
/