一、使用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实现。