备案控制台
探索云世界
开发者社区 数据库 文章 正文

Oracle 游标&子程序&触发器

2023-09-26 50
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 游标的作用：处理多行数据，类似与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;
文章标签：
SQL
存储
Perl
关系型数据库
Oracle
关键词：
oracle触发器
触发器oracle
Oracle数据库一键上云触发器
Oracle数据库一键上云游标
触发器游标
lx4hkk5yox6ya
目录
相关文章
机器学习Zero
|
1月前
|
SQL 存储 Oracle
Oracle系列十四：触发器
Oracle系列十四：触发器
机器学习Zero
18 0
1941623231718325
|
9天前
|
缓存 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，提高了效率。但使用时需注意序列状态、并发性和性能优化。
1941623231718325
14 0
1941623231718325
|
9天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
【4月更文挑战第19天】Oracle PL/SQL游标属性如同车辆的导航仪和仪表盘，提供丰富信息和控制。 `%FOUND`和`%NOTFOUND`指示数据读取状态，`%ROWCOUNT`记录处理行数，`%ISOPEN`显示游标状态。还有`%BULK_ROWCOUNT`和`%BULK_EXCEPTIONS`增强处理灵活性。通过实例展示了如何在数据处理中利用这些属性监控和控制流程，提高效率和准确性。掌握游标属性是提升数据处理能力的关键。
1941623231718325
30 9
机器学习Zero
|
1月前
|
SQL 存储 Oracle
Oracle系列十三：游标
Oracle系列十三：游标
机器学习Zero
14 0
疯狂的猿
|
3月前
|
存储 SQL 关系型数据库
触发器和游标区别
触发器（Trigger）和游标（Cursor）是关系型数据库中常见的两个概念，它们在功能和使用上有一些区别。下面是触发器和游标的主要区别：
疯狂的猿
187 1
游客gy27fhrpaouum
|
4月前
|
存储 SQL Oracle
Oracle存储过程（while循环、for循环、if判断、sql拼接、游标）
while循环、for循环、if判断、sql拼接、游标
游客gy27fhrpaouum
184 0
可口也可樂、
|
4月前
|
存储 SQL 关系型数据库
MySQL基础-变量/流程控制/游标/触发器
MySQL基础-变量/流程控制/游标/触发器
可口也可樂、
70 1
瑶大头*^_^*
|
6月前
|
SQL 存储 Oracle
Oracle触发器----你会教对象触发嘛
Oracle触发器----你会教对象触发嘛
瑶大头*^_^*
36 0
RedEric
|
10月前
|
SQL 存储 Oracle
oracle触发器和webservice数据推送
oracle触发器和webservice数据推送
RedEric
88 0
杰克.陈
|
SQL Oracle 关系型数据库
Oracle基础 游标
原文:Oracle基础 游标 一、游标　　 　　游标用来处理从数据库中检索的多行记录（使用SELECT语句）。利用游标，程序可以逐个地处理和遍历一次检索返回的整个记录集。      为了处理SQL语句，Oracle将在内存中分配一个区域，这就是上下文区。
杰克.陈
1184 0

热门文章

最新文章

  • 1
    【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
  • 2
    【Oracle】玩转Oracle数据库（七）：RMAN恢复管理器
  • 3
    【Oracle】玩转Oracle数据库（二）：体系结构、存储结构与各类参数
  • 4
    Oracle的PL/SQL游标属性：数据的“导航仪”与“仪表盘”
  • 5
    oracle 访问控制
  • 6
    Oracle的三重奏：密码文件、警告文件与跟踪文件
  • 7
    Oracle 12c的自动数据优化（ADO）与热图：数据管理的“瘦身”与“透视”艺术
  • 8
    Oracle的PL/SQL表达式：数据的魔法公式
  • 9
    Oracle日志文件：数据王国的“记事本”
  • 10
    Oracle数据区：数据王国的黄金地段
  • 1
    服务器Centos7 静默安装Oracle Database 12.2
    86
  • 2
    SpringBoot整合Mybatis连接Oracle数据库
    65
  • 3
    避坑，Oracle中rownum与order by的执行顺序
    27
  • 4
    避坑，Oracle中left join 与 (+) 的区别
    25
  • 5
    Oracle临时表详解
    18
  • 6
    Oracle insert数据时字符串中有‘单引号问题
    32
  • 7
    【Bug记录】Oracle中小数点前面的0不显示的问题
    43
  • 8
    Oracle中merge Into的用法
    18
  • 9
    Flink CDC产品常见问题之使用cdc-Oracle连接器报错如何解决
    53
  • 10
    Oracle系列十八：Oracle RAC
    55

    • 相关课程

    更多
  • 数据库及SQL/MySQL基础

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
  • SQLServer数据库的创建
  • SQL的增删改查及函数应用

    • 推荐镜像

    更多
  • oracle
  • postgresql
  • mysql
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）