一、使用dbms_sql执行查询

利用dbms_sql执行select语句，其顺序为 open cursor-->parse-->define column-->execute-->fetch rows-->close cursor;

1、创建班组表结构，如下图所示：

proteamid：主键ID、proteamname：班组名称，jctype：机车类型，workflag：工作标识

2、编写存储过程，使用dbms_sql从dict_proteam中查询数据信息，并输出结果：

create or replace procedure pro_dict_proteam is

/**

利用dbms_sql执行select语句，其顺序为

open cursor-->parse-->define column-->execute

-->fetch rows-->close cursor;

**/

--定义变量

v_cursor number;--游标ID

v_sql varchar2(500);--用于存放sql语句

v_proteam_id number;--班组ID

v_proteam_name varchar2(50);--班组名称

v_count number;--在这里没有实际的含义，只是存放函数返回值

--v_jctype varchar(20):=',SS3B,';

v_workflag number:=1;--查询条件字段

begin

--:v_workflag是占位符

--select语句中的第一列是proteamid，第二列是proteamname

v_sql:='select proteamid,proteamname from dict_proteam where workflag=:v_workflag';

v_cursor:=dbms_sql.open_cursor;--打开游标

dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);--解析动态sql语句

--绑定输入参数，v_workflag的值传给:v_workflag

dbms_sql.bind_variable(v_cursor,':v_workflag',v_workflag);

--定义列，v_proteam_id对应select语句中的第一列

dbms_sql.define_column(v_cursor,1,v_proteam_id);

--定义列，v_proteam_name对应select语句中的第二列，长度为50

dbms_sql.define_column(v_cursor,2,v_proteam_name,50);

--执行动态sql语句

v_count:=dbms_sql.execute(v_cursor);

dbms_output.put_line('v_count='||v_count);

loop

--fetch_rows在结果集中移动游标，如果未抵达末尾，返回1

--从游标中把数据检索到缓存区(buffer)中，缓冲区的值只能被函数

--column_value()所读取

exit when dbms_sql.fetch_rows(v_cursor)<=0;

--把缓冲区的列的值读入到相应变量中

--将当前行的查询结果写入上面定义的列中

--第一列的值被读入v_proteam_id中

dbms_sql.column_value(v_cursor,1,v_proteam_id);

--第二列的值被读入v_proteam_name中

dbms_sql.column_value(v_cursor,2,v_proteam_name);

--打印变量的值

dbms_output.put_line(v_proteam_id||' '||v_proteam_name);

end loop;

dbms_sql.close_cursor(v_cursor);--关闭游标

end;3、执行存储过程

begin

-- Call the procedure

pro_dict_proteam;

end;4、测试输出结果

v_count=0

1 电器上车组

2 电机上车组

3 台车上车组

4 受电弓组

5 制动组

6 行安设备组

8 仪表组

9 充电组

10 探伤组二、使用dbms_sql执行DML语句

insert、update其顺序为：open cursor-->parse-->bind variable-->execute-->close cursor;

delete其顺序为：open cursor-->parse-->execute-->close cursor;

1、创建测试表结构：

create table TB_TEST2

(

ID NUMBER not null,

NAME VARCHAR2(100),

SEX CHAR(5)

)2、创建存储过程，使用dbms_sql往tb_test2中插入数据

create or replace procedure pro_tb_test2

/**

利用dbms_sql执行DML语句

insert、update其顺序为

open cursor-->parse-->bind variable-->execute-->close cursor;

delete其顺序为

open cursor-->parse-->execute-->close cursor;

**/

is

v_cursor number;

v_id number;

v_name varchar2(100);

v_sex char(5);

v_sql varchar2(100);

v_count number;

begin

v_id:=1;

v_name:='tom';

v_sex:='男';

v_sql:='insert into tb_test2(id,name,sex) values(:v_id,:v_name,:v_sex)';

v_cursor:=dbms_sql.open_cursor;

dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);

dbms_sql.bind_variable(v_cursor,':v_id',v_id);

dbms_sql.bind_variable(v_cursor,':v_name',v_name);

dbms_sql.bind_variable(v_cursor,':v_sex',v_sex);

v_count:=dbms_sql.execute(v_cursor);

dbms_sql.close_cursor(v_cursor);

dbms_output.put_line('数据插入成功!'||v_count);

commit;

Exception

when others then

dbms_output.put_line('出现异常信息！');

end;3、测试存储过程

begin

-- Call the procedure

pro_tb_test2;

end;4、结果输出：数据插入成功!1

5、查询tb_test2表中数据信息：

id  name  sex

1   tom   男

总结：

DBMS_SQL包提供一个接口，用于执行动态SQL(包括DDL 和DML)。  DBMS_SQL定义了一个实体叫游标ID，游标ID 是一个PL/SQL整型数，通过游标ID，可以对游标进行操作。 DBMS_SQL包和本地动态SQL在功能上有许多重叠的地方，但是有的功能只能通过本地动态SQL实现，而有些功能只能通过DBMS_SQL实现。