Oracle 游标&子程序&触发器

简介: 游标的作用:处理多行数据,类似与java中的集合

@[TOC]

一、游标

游标的作用:处理多行数据,类似与java中的集合

1.隐式游标

一般是配合显示游标去使用的,不需要显示声明,打开,关闭,系统自定维护,名称为:sql

常用属性:

  • sql%found:语句影响了一行或者多行时为true
  • %NOTFOUND:语句没有任何影响的时候为true
  • %ROWCOUNT:语句影响的行数
  • %ISOPEN:游标是否打开,始终为false

案例:

begin
           update t_student set age=20 ;

           if sql%found then
               dbms_output.put_line('修改成功,共修改了   ' ||  sql%rowcount  || '   条记录');
             else
                 dbms_output.put_line('没有这个学生');
             end if;

            -- commit ;-- 提交应该要放在隐式游标后面
        end ;

2.显示游标

  显式游标在PL/SQL块的声明部分定义查询,该查询可以返回多行,处理多行数据

image.png

实现步骤:

  1. 声明一个游标
  2. 打开游标
  3. 循环提取数据
  4. 关闭游标

案例:

a) 无参数 :查询所有学生信息,并显示出学生姓名,性别,年龄

-- 步骤:1.声明一个游标  2.打开游标  3.循环提取数据 4.关闭游标
-- 查询所有的学生信息。并且显示学生的姓名,年龄和性别
declare
   v_row t_student%rowtype;
   -- 1.游标的声明
   cursor mycursor is select * from t_student ;
begin
    -- 2.打开游标
    open mycursor;

    -- 3.循环提取数据
    loop
        fetch mycursor into v_row;
        -- 找到出口
        exit when mycursor%notfound;
        dbms_output.put_line(v_row.name||'-'||v_row.gender||'-'||v_row.age);
    end loop;
    -- 4.关闭游标
    close mycursor;
end;

b) 有参数:

declare
          v_sex varchar2(4) :='&请输入性别' ;
           v_row t_student%rowtype ;
           cursor mycursor(p_sex varchar2) is select * from t_student where sex=p_sex ; -- 注:参数的类型不要指定长度大小
        begin
           open mycursor(v_sex) ;-- 2、打开游标
           loop
               fetch mycursor into v_row;
                  exit when mycursor%notfound;
               dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age);

           end loop; 
           close mycursor;--  4、 关闭游标
        end ;

c ) 循环游标. 简化 游标 for:不需要打开游标 也不需要关闭游标

declare
          v_sex varchar2(4) :='&请输入性别' ;
           cursor mycursor(p_sex varchar2) is select * from t_student where sex=p_sex ; -- 注:参数的类型不要指定长度大小
        begin

          for v_row   in  mycursor(v_sex)    loop
               dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age);  
           end loop; 

        end ;

d) 使用显式游标更新行:

允许使用游标删除或更新活动集中的行,声明游标时必须使用 select ... for update 语句。

declare
          v_sex varchar2(4) :='&请输入性别' ;
           v_row t_student%rowtype ;
           cursor mycursor(p_sex varchar2) is select * from t_student where sex=p_sex  for update; -- 注:参数的类型不要指定长度大小
        begin
           open mycursor(v_sex) ;-- 2、打开游标
           loop
               fetch mycursor into v_row;
                  exit when mycursor%notfound;
              -- dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age);

               update t_student set age = age +10 where current of mycursor;

           end loop; 
           --commit ;
           close mycursor;--  4、 关闭游标
        end ;

3.REF游标

  处理运行时动态执行的 SQL 查询,特点:

优点:

  1. 动态SQL语句
  2. 在存储过程中可以当参数

缺点:

  1. 不能使用循环游标for
  2. 不能使用游标更新行

使用步骤:

  1. 定义一个ref的类型
  2. 声明游标
  3. 打开游标
  4. 提取数据
  5. 关闭游标

案例讲解

declare
         v_sex varchar2(4) ;
       --type mytype is ref cursor return t_student%rowtype; -- 强类型的 ref 游标类型
         type mytype is ref cursor  ;   --  1)弱类型的 ref 游标类型
         mycursor mytype;   --  2) 声明游标
         v_sql varchar2(100) ;
         v_row t_student%rowtype ;
     begin
         v_sql :=' select * from t_student ' ;

        -- open mycursor for select * from t_student; 
         open mycursor for v_sql ;
         loop
             fetch mycursor into v_row ;
             exit when mycursor%notfound ;
             dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age);
         end loop;
         close mycursor ;  

     end ;

可以使用sys_refcursor类型

declare
         v_stuname t_student.stuname%type :='&请输入名字' ;
           v_sex varchar2(3) :='&请输入性别' ;

         mycursor  sys_refcursor ;   --  2) 声明游标
         v_sql varchar2(100) ;
         v_row t_student%rowtype ;
     begin

          v_sql :='select * from t_student  where  1=1 ';

             if  v_stuname is not null then 
                 v_sql :=v_sql  || '  and stuname like  ''%'  || v_stuname || '%'' ' ;
              end if;  

            if  v_sex is not null then
                   v_sql :=v_sql || '  and sex = '''  || v_sex || ''' ' ;
            end if;

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


        -- open mycursor for select * from t_student; 
         open mycursor for v_sql ;
         loop
             fetch mycursor into v_row ;
             exit when mycursor%notfound ;
             dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age);
         end loop;
         close mycursor ;  

     end ;

游标的小结:

  • 游标用于处理查询结果集中的数据
  • 游标类型有:隐式游标、显式游标和 REF游标
  • 隐式游标由 PL/SQL 自动定义、打开和关闭
  • 显式游标用于处理返回多行的查询
  • 显式游标可以删除和更新活动集中的行
  • 要处理结果集中所有记录时,可使用循环游标

二、子程序

什么是子程序:命名的 PL/SQL 块,编译并存储在数据库中

1.存储过程

1.1 语法结构

CREATE [OR REPLACE] PROCEDURE 
   <procedure name> [(<parameter list>)]
IS|AS 
   <local variable declaration>
BEGIN
   <executable statements>
[EXCEPTION
   <exception handlers>]
END;

1.2 案例讲解

无参数案例:写一个存储过程 ,往学生表中模拟 10 00 条数据(插入1000 条数据 )

create or replace procedure protest01
     is
        -- 声明变量  
     begin
         for  i  in 1..100 loop
             insert into t_student(id,stuname,sex,age) values(seq_t_student.nextval  , '小李' || i  , '男' , i );
         end loop;
         commit ;
     end ;

调用存储过程:

declare
        begin
           -- protest01();
            protest01;   --  当没有参数里,括号可省略不写
        end;

有参数的案例:

create or replace procedure protest02(
    p_name varchar2,
    p_sex  varchar2, 
    p_age number
    )
     is
        -- 声明变量  
     begin

         dbms_output.put_line(p_name || ',' || p_sex || ',' || p_age );

     end ;

调用处理

declare
           v_name varchar2(10) :='&请输入名字' ;
           v_sex varchar2(4) :='&请输入性别';
           v_age number(3) :='&请输入年龄'; 
        begin
            protest02(v_name,  v_sex , v_age);   --  当没有参数里,括号可省略不写
        end;

参数的三种类型

  • in 输入
  • out 输出
  • in out 输入输出

案例讲解

create or replace procedure protest03(
    p_name in varchar2, 
    p_sex in out  varchar2, 
    p_age in out number)
     is
        -- 声明变量  
     begin

         dbms_output.put_line(p_name || ',' || p_sex || ',' || p_age );
         p_sex :='我是P_sex';  
     end ;

调用过程

     declare
           v_name varchar2(10) :='&请输入名字' ;
           v_sex varchar2(50) :='&请输入性别';
           v_age number(3) :='&请输入年龄'; 
        begin
            protest03(v_name,  v_sex , v_age);   --  当没有参数里,括号可省略不写
             dbms_output.put_line(v_sex);
        end;

案例: 请根据性别或名字查询相关记录,并把结果 返回来 打印了出来 提示用 sys_refcursor

create or replace procedure protest04(
        p_name varchar2,
        p_sex varchar2, 
        myresult out sys_refcursor)
          is
             v_sql  varchar2(100) ;
          begin
             v_sql :='select * from t_student where 1=1 ';
             if p_name is not null then
                v_sql :=v_sql  ||  '  and  stuname like  ''%'  || p_name  ||   '%'' ' ;
             end if;
             if p_sex is not null then
                v_sql := v_sql || '  and  sex = ''' || p_sex || '''  ';
             end if;
             dbms_output.put_line('v_sql='  || v_sql);

             open myresult for v_sql ;

          end;

调用

-- 执行测试
          declare
             v_name varchar2(20) :='&请输入名字';
             v_sex varchar2(4) :='&请输入性别' ;
             mycursor sys_refcursor ;
             v_row t_student%rowtype;
          begin
             --  protest04(v_name, v_sex , mycursor);  
    -- 1) 位置传递
                --2 )名称传递
                 --protest04( p_sex =>v_sex , p_name => v_name , myresult => mycursor);
                 --3) 混合使用  : 先用位置传递,如果后面有用了名称传递,后面就不能用位置传递
                     protest04(v_name , myresult=>mycursor ,p_sex => v_sex,);
               loop
                   fetch mycursor into  v_row ;
                    exit when mycursor%notfound ;
                    dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age );
               end loop;

               close mycursor ;   
          end;

2.存储函数

  类似于java中方法,有返回值可以返回值的命名的 PL/SQL 子程序。

2.1 语法结构

CREATE [OR REPLACE] FUNCTION 
  <function name> [(param1,param2)]
RETURN <datatype>  IS|AS 
  [local declarations]
BEGIN
  Executable Statements;
  RETURN result;
EXCEPTION
  Exception handlers;
END;

2.2 案例讲解

无参案例:写一个函数 ,获取学生名称

create or replace function funtest02
   return varchar2
   is
      v_name varchar2(20) ;
   begin
         select stuname into v_name from t_student where id=201;
         return v_name;
   end ;

如何调用呢

  • a)用PL/SQL块调用函数
  • b)用select语句调用: 在函数中不能有增加删除修改的语句,只能是查询的语句

有参案例:

create or replace function  funtest03(p_name in varchar2, p_sex out varchar2, p_age in out number)
     return varchar2
     is
        -- 声明变量  
     begin

         dbms_output.put_line(p_name || ',' || p_sex || ',' || p_age );
         p_sex :='我是函数的p_sex' ;
            return '成功';
     end ;

调用

   -- 调用 
     declare
           v_name varchar2(10) :='&请输入名字' ;
           v_sex varchar2(20) :='&请输入性别';
           v_age number(3) :='&请输入年龄'; 
           v_result varchar2(30) ;
        begin
            v_result := funtest03(v_name, v_sex , p_age=>v_age);   
              dbms_output.put_line('v_result=' || v_result || ', v_sex=' || v_sex );
        end;

3.程序包

程序包:作用就是管理我们的存储过程和方法

-- 程序包:作用就是管理我们的存储过程和方法
-- 规范和主体两部分组成
-- 创建一个规范
create or replace package pak01
is
   procedure myprocdure01(p_name varchar2);
   function myfun01 return number;
end pak01;


-- 创建规范对应的主体:主体中的方法如果在规范中声明了。那么外包可以访问。如果没有那么就只能被内部调用
create or replace package body pak01
is

    -- myprodure01 存储过程
    procedure myprocdure01(p_name varchar2)
    as
    begin
        dbms_output.put_line('p_name = '||p_name);
    end;
    -- myfun01 方法
    function myfun01 return number
    is
    begin
         dbms_output.put_line('方法执行了.... ');
         return 666;
    end;
end pak01;


create or replace package body pak01
is

    -- myprodure01 存储过程
    procedure myprocdure01(p_name varchar2)
    as
    begin
        dbms_output.put_line('p_name = '||p_name);
    end;
    -- myfun01 方法
    function myfun01 return number
    is
    begin
         dbms_output.put_line('方法执行了.... ');
         return 666;
    end;

    function myfun02 return number
    is

    begin
       dbms_output.put_line('方法执行了..222.. ');
       return 999;
    end;
end pak01;

-- 调用package中的过程和方法 package.
begin
   pak01.myprocdure01('李四');
end;

select pak01.myfun02 from dual;

三、触发器

1.触发器的基本讲解

当特定事件出现时自动执行的存储过程

语法结构

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]] 
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
declare
begin
end;

案例:对学生表进行增加删除修改后打印一句 操作成功

create or replace trigger trigger01
after insert or update or delete on t_student
declare

begin
   dbms_output.put_line('操作成功');
end ;

2.触发器的类型

2.1 语句级触发器

关注的是执行了这条语句

案例:创建一个对学生表的增删改的审计触发器

准备表

CREATE TABLE t_audit_table
(
  stablename varchar2(30),
  nins number,--记录添加次数
  nupd number,--记录修改次数
  ndel number,--记录删除次数
  startdate date,
  enddate date
)

实现:

create or replace trigger trigger02
    after insert or delete or update on t_student
    declare
       v_count number(3);
    begin
        -- 先判断t_student在这个日志表中是否有这条记录,如果没有,要先插入数据
        select count(*) into v_count from t_audit_table where stablename='t_student';
        if v_count<=0 then
             insert into t_audit_table(stablename,nins,nupd,ndel) values('t_student', 0,0 ,0);
        end if;

        if inserting then
            update t_audit_table set nins=nins+1 where stablename='t_student';
        end if;
        if updating then
             update t_audit_table set nupd=nupd+1 where stablename='t_student';
        end if;
        if deleting then
            update t_audit_table set ndel=ndel+1 where stablename='t_student';
        end if;

2.2 行级触发器

影响的行数:影响了多少行数据。那么这个触发器就会触发多少次

create or replace trigger trigger02
    after insert or delete or update on t_student
    FOR EACH ROW
    declare
       v_count number(3);
    begin
        -- 先判断t_student在这个日志表中是否有这条记录,如果没有,要先插入数据
        select count(*) into v_count from t_audit_table where stablename='t_student';
        if v_count<=0 then
             insert into t_audit_table(stablename,nins,nupd,ndel) values('t_student', 0,0 ,0);
        end if;

        if inserting then
            update t_audit_table set nins=nins+1 where stablename='t_student';
        end if;
        if updating then
             update t_audit_table set nupd=nupd+1 where stablename='t_student';
        end if;
        if deleting then
            update t_audit_table set ndel=ndel+1 where stablename='t_student';
        end if;

2.3 限制行级触发器

  对部分数据做特定的处理,比如:不能删除管理员

create or replace trigger trigger03
   before  delete on t_student
    for each row
    when(old.stuname='小李6')  
  declare
  begin
         dbms_output.put_line('班长不能被删除');

        RAISE_APPLICATION_ERROR(-20001, '班长不能被删除');
  end;
相关文章
|
1月前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL隐式游标:数据的“自动导游”与“轻松之旅”
【4月更文挑战第19天】Oracle PL/SQL中的隐式游标是自动管理的数据导航工具,简化编程工作,尤其适用于简单查询和DML操作。它自动处理数据访问,提供高效、简洁的代码,但不适用于复杂场景。显式游标在需要精细控制时更有优势。了解并适时使用隐式游标,能提升数据处理效率,让开发更加轻松。
|
1月前
|
SQL 存储 Oracle
Oracle系列十四:触发器
Oracle系列十四:触发器
|
8天前
|
存储 SQL 关系型数据库
MySQL存储过程_触发器_游标——Baidu Comate
MySQL存储过程_触发器_游标——Baidu Comate
12 0
|
1月前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标属性:数据的“导航仪”与“仪表盘”
【4月更文挑战第19天】Oracle PL/SQL游标属性如同车辆的导航仪和仪表盘,提供丰富信息和控制。 `%FOUND`和`%NOTFOUND`指示数据读取状态,`%ROWCOUNT`记录处理行数,`%ISOPEN`显示游标状态。还有`%BULK_ROWCOUNT`和`%BULK_EXCEPTIONS`增强处理灵活性。通过实例展示了如何在数据处理中利用这些属性监控和控制流程,提高效率和准确性。掌握游标属性是提升数据处理能力的关键。
|
1月前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL中FOR语句循环游标的奇幻之旅
【4月更文挑战第19天】在Oracle PL/SQL中,FOR语句与游标结合,提供了一种简化数据遍历的高效方法。传统游标处理涉及多个步骤,而FOR循环游标自动处理细节,使代码更简洁、易读。通过示例展示了如何使用FOR循环游标遍历员工表并打印姓名和薪资,对比传统方式,FOR语句不仅简化代码,还因内部优化提升了执行效率。推荐开发者利用这一功能提高工作效率。
|
1月前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL显式游标:数据的“私人导游”与“定制之旅”
【4月更文挑战第19天】Oracle PL/SQL中的显式游标提供灵活精确的数据访问,与隐式游标不同,需手动定义、打开、获取和关闭。通过DECLARE定义游标及SQL查询,OPEN启动查询,FETCH逐行获取数据,CLOSE释放资源。显式游标适用于复杂数据处理,但应注意SQL效率、游标管理及异常处理。它是数据海洋的私人导游,助力实现业务逻辑和数据探险。
|
1月前
|
缓存 Oracle 关系型数据库
Oracle中的触发器与序列:自增列的魔法组合
【4月更文挑战第19天】Oracle数据库中,通过触发器和序列的组合可实现自增列功能。序列充当自动计数器,提供递增数值,而触发器则在插入新记录时自动分配序列值。创建序列如`CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE`,然后创建触发器`TRIGGER trg_employees_before_insert`在`BEFORE INSERT`时将序列的下一个值赋予`employee_id`。这种方式使得在插入记录时无需手动设置ID,提高了效率。但使用时需注意序列状态、并发性和性能优化。
|
1月前
|
SQL 存储 Oracle
Oracle系列十三:游标
Oracle系列十三:游标
|
1月前
|
存储 SQL 关系型数据库
触发器和游标区别
触发器(Trigger)和游标(Cursor)是关系型数据库中常见的两个概念,它们在功能和使用上有一些区别。下面是触发器和游标的主要区别:
194 1
|
1月前
|
存储 SQL Oracle

热门文章

最新文章

推荐镜像

更多