Oracle 游标&子程序&触发器

╰+哭是因爲堅強的太久メ 2023-10-16 09:07 51阅读 0赞

#### 文章目录 ####

*  一、游标
 *   *  1.隐式游标
     *  2.显示游标
     *  3.REF游标
 *  二、子程序
 *   *  1.存储过程
     *   *  1.1 语法结构
         *  1.2 案例讲解
     *  2.存储函数
     *   *  2.1 语法结构
         *  2.2 案例讲解
     *  3.程序包
 *  三、触发器
 *   *  1.触发器的基本讲解
     *  2.触发器的类型
     *   *  2.1 语句级触发器
         *  2.2 行级触发器
         *  2.3 限制行级触发器

## 一、游标 ##

游标的作用：处理多行数据，类似与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;

[image.png]: https://img-blog.csdnimg.cn/img_convert/4660acfe8b7c648fd377dbc6020494c4.png