当前位置:Gxlcms > 数据库问题 > oracle中dbms_sql的使用

oracle中dbms_sql的使用

时间:2021-07-01 10:21:17 帮助过:51人阅读

利用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实现。

oracle中dbms_sql的使用

标签:

人气教程排行