1、执行诸如DDL和DDL语句(select除外)
a)打开一个游标
b)分析要执行的语句
c)绑定可能需要的任何输入变量
d)执行语句
e)关闭游标
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
select语句的执行如下:
set serveroutput
on;
declare
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_cursorid
number;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_selectrecords
varchar2(
500);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_numrows
integer;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_mynum
integer;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_mytext
varchar(
50);
begin
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_cursorid :
= dbms_sql.open_cursor;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_selectrecords :
=
'
select * from mytable
';
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.parse(v_cursorid,v_selectrecords,dbms_sql.native);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.define_column(v_cursorid,
1,v_mynum);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.define_column(v_cursorid,
2,v_mytext,
50);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_numrows :
= dbms_sql.
execute(v_cursorid);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
loop
if dbms_sql.fetch_rows(v_cursorid)
=
0
then
exit;
end
if;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.column_value(v_cursorid,
1,v_mynum);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.column_value(v_cursorid,
2,v_mytext);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_output.put_line(v_mynum
||
'
'
||v_mytext);
end loop;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.close_cursor(v_cursorid);
end;
/
2、select查询的动态sql实现
a)打开一个游标
b)分析要执行的语句
c)绑定可能需要的任何输入变量(如果需要)
4)定义输出变量
d)执行语句
5)取回记录
6)将取回的记录结果存储在pl/sql变量中
e)关闭游标
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
create语句的例子如下
declare
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_cursorid
number;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_createTableString
varchar2(
500);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_numrows
integer;
begin
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_cursorid :
= dbms_sql.open_cursor;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_createTableString :
=
'
create table mytable(myrow integer,mydesc varchar2(50)) tablespace tabs
';
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.parse(v_cursorid,v_createTableString,dbms_sql.native);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_numrows :
= dbms_sql.
execute(v_cursorid);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
exception
when others
then
if sqlcode
!=-
955
then raise;
else
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_output.put_line(
'
talbe already exists
');
end
if;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.close_cursor(v_cursorId);
end;
/
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
insert语句的例子如下
set serveroutput
on;
declare
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_cursorid
number;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_insertRecords
varchar2(
500);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_numrows
integer;
begin
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_cursorid :
= dbms_sql.open_cursor;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_insertRecords :
=
'
insert into mytable values(:mynum,:mytext)
';
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.parse(v_cursorid,v_insertrecords,dbms_sql.native);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.bind_variable(v_cursorid,
'
:mynum
',
1);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.bind_variable(v_cursorid,
'
:mytext
',
'
one
');
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_numrows :
= dbms_sql.
execute(v_cursorId);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_output.put_line(v_numrows);
--
di 2 tiao ji lu
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.bind_variable(v_cursorid,
'
:mynum
',
2);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.bind_variable(v_cursorid,
'
:mytext
',
'
two
');
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_numrows :
= dbms_sql.
execute(v_cursorId);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_output.put_line(v_numrows
||
'
2
');
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
exception
when others
then raise;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.close_cursor(v_cursorid);
commit;
end;
/
3、执行pl/sql匿名块
a)打开一个游标
b)分析要执行的语句
c)绑定可能需要的任何输入变量(如果需要)
d)执行语句
5)取回记录
6)将取回的记录结果存储在pl/sql变量中
e)关闭游标
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
执行匿名块
set serveroutput
on;
declare
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_cursorid
number;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_matchrecord
varchar2(
500);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_numrows
integer;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_mynum
integer;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_mytext
varchar2(
50);
begin
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_cursorid :
= dbms_sql.open_cursor;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_matchrecord :
=
'
begin
select myrow,mydesc into :myrow ,:mytext from mytable
where myrow = 2;
end;
';
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.parse(v_cursorid,v_matchrecord,dbms_sql.native);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.bind_variable(v_cursorid,
'
:myrow
',v_mynum);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.bind_variable(v_cursorid,
'
:mytext
',v_mytext,
50);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_numrows :
= dbms_sql.
execute(v_cursorid);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.variable_value(v_cursorid,
'
:myrow
',v_mynum);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.variable_value(v_cursorid,
'
:mytext
',v_mytext);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_output.put_line(v_mynum
||
'
'
||v_mytext);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.close_cursor(v_cursorid);
end;
/
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
检查取回记录的进度
set serveroutput
on;
declare
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_cursorid
number;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_matchrecord
varchar2(
500);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_numrows
integer;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_mynum
integer;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_mytext
varchar2(
50);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_myrowid rowid;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_totrow
integer;
begin
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_cursorid :
= dbms_sql.open_cursor;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_matchrecord :
=
'
select * from mytable for update
';
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.parse(v_cursorid,v_matchrecord,dbms_sql.native);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.define_column(v_cursorid,
1,v_mynum);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.define_column(v_cursorid,
2,v_mytext,
50);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_numrows :
= dbms_sql.
execute(v_cursorid);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
loop
if dbms_sql.fetch_rows(v_cursorid)
=
0
then
exit;
end
if;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_totrow :
= dbms_sql.last_row_count;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
v_myrowid :
= dbms_sql.last_row_id;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_output.put_line(
'
the last row count is:
'
||v_totrow
||
'
the last rowid is:
'
||v_myrowid);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.column_value(v_cursorid,
1,v_mynum);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.column_value(v_cursorid,
2,v_mytext);
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_output.put_line(v_mynum
||
'
'
||v_mytext);
end loop;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
dbms_sql.close_cursor(v_cursorid);
end;
/