sql 触发器实例和简单的存蓄过程

最新推荐文章于 2022-07-04 23:32:17 发布
最新推荐文章于 2022-07-04 23:32:17 发布
阅读量237 收藏
点赞数
分类专栏： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44178762/article/details/106481519
版权

存蓄过程  求父类下所有对象的id集合

CREATE DEFINER=`jadmin`@`%` FUNCTION `getChild`(rootId VARCHAR(36)) RETURNS varchar(10000) CHARSET utf8
BEGIN
    DECLARE ptemp VARCHAR(10000);
    DECLARE ctemp VARCHAR(10000);
    SET ptemp = '0';
    SET ctemp = rootId;
    WHILE ctemp IS NOT NULL DO
      SET ptemp = CONCAT(ptemp, ',', ctemp);
      SELECT GROUP_CONCAT(dept_id)
      INTO ctemp
      FROM sys_dept
      WHERE FIND_IN_SET(parent_id, ctemp) > 0;
    END WHILE;
    RETURN ptemp;
  END

 

触发器

 

BEGIN
    DECLARE bexist INT;
    SET bexist = (SELECT COUNT(*) FROM `fume_online_time` WHERE id=NEW.site_id);
    IF bexist = 0 THEN
        INSERT INTO `fume_online_time` VALUES(NEW.site_id,0,0);
    END IF;
    IF NEW.JHA > 0 THEN
        UPDATE `fume_online_time` SET jha_time=(jha_time+1) WHERE id = NEW.site_id;
    END IF;
    IF NEW.FJA > 0 THEN
        UPDATE `fume_online_time` SET fja_time=(fja_time+1) WHERE id = NEW.site_id;
    END IF;
END

 

 

 

 

例子：

当前有三张表A、B、C其中A和B是一对多关系，B和C是一对多关系，现在需要将B中A表的主键存到C中；
常规思路就是将B中查询出来然后通过一个update语句来更新C表就可以了，但是B表中有2000多条数据，
难道要执行2000多次？显然是不现实的；最终找到写一个存储过程然后通过循环来更新C表，
然而存储过程中的写法用的就是游标的形式。

【简介】

    游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

    游标充当指针的作用。

    尽管游标能遍历结果中的所有行，但他一次只指向一行。

    游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

【用法】

    一、声明一个游标: declare 游标名称 CURSOR for table;(这里的table可以是你查询出来的任意集合)
    二、打开定义的游标:open 游标名称;
    三、获得下一行数据:FETCH  游标名称 into testrangeid,versionid;
    四、需要执行的语句(增删改查):这里视具体情况而定
    五、释放游标:CLOSE 游标名称;
  注:mysql存储过程每一句后面必须用;结尾，使用的临时字段需要在定义游标之前进行声明。

【实例】

复制代码

-  
BEGIN  
  
--定义变量  
declare testrangeid BIGINT;  
declare versionid BIGINT;   
declare done int;  
--创建游标，并存储数据  
declare cur_test CURSOR for   
   select id as testrangeid,version_id as versionid from tp_testrange;  
--游标中的内容执行完后将done设置为1  
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;   
--打开游标  
open cur_test;  
--执行循环  
  posLoop:LOOP  
--判断是否结束循环  
        IF done=1 THEN    
      LEAVE posLoop;  
    END IF;   
--取游标中的值  
    FETCH  cur_test into testrangeid,versionid;  
--执行更新操作  
    update tp_data_execute set version_id=versionid where testrange_id = testrangeid;  
  END LOOP posLoop;  
--释放游标  
CLOSE cur_test;  
  
END  
-

复制代码

例子2：

我们现在要用存储过程做一个功能，统计iphone的总库存是多少，并把总数输出到控制台。

复制代码

--在windows系统中写存储过程时，如果需要使用declare声明变量，需要添加这个关键字，否则会报错。  
delimiter //  
drop procedure if exists StatisticStore;  
CREATE PROCEDURE StatisticStore()  
BEGIN  
    --创建接收游标数据的变量  
    declare c int;  
    declare n varchar(20);  
    --创建总数变量  
    declare total int default 0;  
    --创建结束标志变量  
    declare done int default false;  
    --创建游标  
    declare cur cursor for select name,count from store where name = 'iphone';  
    --指定游标循环结束时的返回值  
    declare continue HANDLER for not found set done = true;  
    --设置初始值  
    set total = 0;  
    --打开游标  
    open cur;  
    --开始循环游标里的数据  
    read_loop:loop  
    --根据游标当前指向的一条数据  
    fetch cur into n,c;  
    --判断游标的循环是否结束  
    if done then  
        leave read_loop;    --跳出游标循环  
    end if;  
    --获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作，  
    set total = total + c;  
    --结束游标循环  
    end loop;  
    --关闭游标  
    close cur;  
  
    --输出结果  
    select total;  
END;  
--调用存储过程  
call StatisticStore();

复制代码

fetch是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标溢出。
使用loop循环游标时，他本身是不会监控是否到最后一条数据了，像下面代码这种写法，就会造成死循环；

read_loop:loop  
fetch cur into n,c;  
set total = total+c;  
end loop;

在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，所以在上面使用下面的代码指定了当引发not found错误时定义一个continue 的事件，指定这个事件发生时修改done变量的值。

declare continue HANDLER for not found set done = true;

所以在循环时加上了下面这句代码：

--判断游标的循环是否结束  
if done then  
    leave read_loop;    --跳出游标循环  
end if;

如果done的值是true，就结束循环。继续执行下面的代码

使用方式

游标有三种使用方式：
第一种就是上面的实现，使用loop循环；
第二种方式如下，使用while循环：

复制代码

drop procedure if exists StatisticStore1;  
CREATE PROCEDURE StatisticStore1()  
BEGIN  
    declare c int;  
    declare n varchar(20);  
    declare total int default 0;  
    declare done int default false;  
    declare cur cursor for select name,count from store where name = 'iphone';  
    declare continue HANDLER for not found set done = true;  
    set total = 0;  
    open cur;  
    fetch cur into n,c;  
    while(not done) do  
        set total = total + c;  
        fetch cur into n,c;  
    end while;  
      
    close cur;  
    select total;  
END;  
  
call StatisticStore1();

 

第三种方式是使用repeat执行：

 

drop procedure if exists StatisticStore2;  
CREATE PROCEDURE StatisticStore2()  
BEGIN  
    declare c int;  
    declare n varchar(20);  
    declare total int default 0;  
    declare done int default false;  
    declare cur cursor for select name,count from store where name = 'iphone';  
    declare continue HANDLER for not found set done = true;  
    set total = 0;  
    open cur;  
    repeat  
    fetch cur into n,c;  
    if not done then  
        set total = total + c;  
    end if;  
    until done end repeat;  
    close cur;  
    select total;  
END;  
  
call StatisticStore2();

 

游标嵌套

在mysql中，每个begin end 块都是一个独立的scope区域，由于MySql中同一个error的事件只能定义一次，如果多定义的话在编译时会提示Duplicate handler declared in the same block。

 

drop procedure if exists StatisticStore3;  
CREATE PROCEDURE StatisticStore3()  
BEGIN  
    declare _n varchar(20);  
    declare done int default false;  
    declare cur cursor for select name from store group by name;  
    declare continue HANDLER for not found set done = true;  
    open cur;  
    read_loop:loop  
    fetch cur into _n;  
    if done then  
        leave read_loop;  
    end if;  
    begin  
        declare c int;  
        declare n varchar(20);  
        declare total int default 0;  
        declare done int default false;  
        declare cur cursor for select name,count from store where name = 'iphone';  
        declare continue HANDLER for not found set done = true;  
        set total = 0;  
        open cur;  
        iphone_loop:loop  
        fetch cur into n,c;  
        if done then  
            leave iphone_loop;  
        end if;  
        set total = total + c;  
        end loop;  
        close cur;  
        select _n,n,total;  
    end;  
    begin  
            declare c int;  
            declare n varchar(20);  
            declare total int default 0;  
            declare done int default false;  
            declare cur cursor for select name,count from store where name = 'android';  
            declare continue HANDLER for not found set done = true;  
            set total = 0;  
            open cur;  
            android_loop:loop  
            fetch cur into n,c;  
            if done then  
                leave android_loop;  
            end if;  
            set total = total + c;  
            end loop;  
            close cur;  
        select _n,n,total;  
    end;  
    begin  
      
    end;  
    end loop;  
    close cur;  
END;  
  
call StatisticStore3();

复制代码

上面就是实现一个嵌套循环，当然这个例子比较牵强。凑合看看就行。

动态SQL

Mysql 支持动态SQL的功能

set @sqlStr='select * from table where condition1 = ?';  
prepare s1 for @sqlStr;  
--如果有多个参数用逗号分隔  
execute s1 using @condition1;  
--手工释放，或者是 connection 关闭时， server 自动回收  
deallocate prepare s1;
qi_gaolei
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL Server：触发器和存储过程的使用
Atidote的博客
09-01 682
SQL Server：触发器和存储过程的使用 创建数据库表 create table t_users ( id int(8) not null AUTO_INCREMENT primary key, --主键ID user_id varchar(10), --用户编号 user_name varchar(25), --用户姓名 user_age int(4), --用户年龄 user_sex varchar(2), --用户性别 user_phone varchar(11), --手机号码 create_t
SQL触发器--插入时判断数据是否已存在
weixin_30511107的博客
09-24 1686
判断插入的人员身份证号是否已存在，如存在则取消插入，并给出提示。--身份证号唯一，但并非主键，主键是ZA0100create TRIGGER flx_isexistON FD01for INSERTASbegindeclare @id varchar(20)declare @key varchar(64)select @id=aa0177,@key=ZA0100 from inserted ...
SQL 判断表、存储过程、数据库、视图、触发器是否存在
silentloser的博客
03-07 780
–判断表是否存在 if OBJECT_ID(N’表名’,N’U’) is not null print ‘表存在！’ else print ‘表不存在！’ –判断表是否存在 if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N’表名’) and xtype=‘U’) print ‘表存在’ else print ‘表不存在’ –判断数据库是否存在 if exists(select * from master…sysdatabases wh
SQL编程：存储过程、触发器、函数（实例基于MySQL5.7.12）
doctorvian的博客
04-05 1273
SQL编程基础 编程环境：  即存储过程、触发器和函数中进行SQL编程，所以有些语法并不能应用于普通的SQL应用场景，如命令行直接SQL查询。 常量声明： 变量声明： （1）全局变量 声明：set @变量名 = 值； 读取：select @变量名 赋值： set @变量名=表达式； select @变量名:=表达式；-- 会输出结果集 select 表达式 into @变量名;–不输出结果集 ...
mysql触发器检查记录是否存在_SQL触发器--插入时判断数据是否已存在
weixin_28909303的博客
01-27 3123
判断插入的人员身份证号是否已存在，如存在则取消插入，并给出提示。--身份证号唯一，但并非主键，主键是ZA0100create TRIGGER flx_isexistON FD01for INSERTASbegindeclare @id varchar(20)declare @key varchar(64)select @id=aa0177,@key=ZA0100 from insertedIF (...
SQL触发器实例讲解
12-15
SQL触发器实例1 定义： 何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。 常见的触发器有三种：分别应用于Insert , Update , Delete ...
SQL Server：触发器实例详解
09-09
SQL Server中的触发器是数据库对象的一种，主要用于在特定的数据操作（如INSERT、UPDATE、DELETE）发生时自动执行一些额外的任务，以确保数据的完整性和一致性。触发器不能像普通存储过程那样被显式调用，而是隐式地...
SQL Server 触发器实例详解
12-15
Microsoft SQL Server:trade_mark: ...触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server 2005中触发
SQL触发器实例讲解.pdf
10-04
SQL 触发器实例讲解 SQL 触发器是一种特殊的存储过程，用于在数据库中执行某些操作时自动触发某些事件。触发器可以应用于 Insert、Update、Delete 事件等。 什么是触发器？ 触发器是一个特殊的存储过程，在 SQL ...
sql server调用存储过程
10-08
sql server调用存储过程,sql server,存储,过程,存储过程,java,jsp,EL
SQL Server 2008 R2数据库存储过程和触发器
GIS热爱者
04-19 7711
内容：创建并执行存储过程、修改存储过程、删除存储过程、创建和执行触发器、修改触发器、删除触发器、习题、总结 意义：1.掌握存储过程的概念、功能等2.掌握用户自定义存储过程的创建、修改、删除和执行3.掌握触发器的概念和功能4.掌握触发器的创建、修改、删除及其使用方法
SQL:存储过程和触发器～笔记
零小唬的博客
07-04 177
存储过程和触发器
【T-SQL】存储过程和触发器使用实例
Lee的博客
12-16 4612
一、本文列举了一些T-SQL存储过程和触发的例题以及解答 背景知识： 存储过程：封装了可重用的代码块和模块，通过接收参数然后返回结果集来执行。存储过程经过数据库编译后存储在服务器端。 触发器：由SQL自动执行，当发生特定操作（update、delete、insert）时自动执行事前编辑好的代码块。原理是使用两个临时表inserted、deleted进行操作。 二、名称解释（不区分大小写） student学生表： 包含属性列：sno学号、sname学生姓名、age年龄、sex性别、dno学院编号、bi
SQL高级--储存过程和触发器
越来越好ing的博客
12-04 378
储存过程 什么是存储过程 存储过程：是一组SQL命令集合，经过预编译存放在系统中 在日常的数据库操作中，会有大量的T-SQL批处理、游标，如果每次都要去重写或者打开脚本来执行会相当麻烦，数据库可以将我们编写的T-SQL批处理保存在一个处理过程中，就像java程序里的方法，可以重复的被调用，这个被保存的处理过程在数据库中叫存储过程，和程序中的方法一样，同样可以根据需要设置参数、和返回值。 存储过...
sql的存储过程与触发器的区别，以及各自的优缺点！
LQzhang_11的博客
08-08 4799
首先说存储过程吧： 存储过程： 是一组SQL语句集，从开头到结尾包含这许多的SQL语句，然后中间对于有需要的业务逻辑处理，进行逻辑控制。解决一些复杂的业务时比较实用。 比如说我在service层处理业务逻辑的虎，可能会涉及到多次数据库的连接。这就相当的耗费资源，但如果你用存储过程的话，就只有一次，从响应来说，时间上很有优势，而且性能也会提高，也就是说存储过程可以给我们带来运行效率提高的好处。 ...
SQL存储过程+触发器
hytfreshman的博客
05-01 255
SQL存储过程，触发器 适合小白研究学习
批量操作的触发器sql server2008
weixin_42386538的博客
06-06 2983
有三种建立触发器的方式写法，我们来看一下哪种正确1.第一种方式create trigger T on stucou for insert,update,delete as begin update course set willnum=willnum+(select count(couno)from inserted) where couno=(select couno from inserted...
SQL存储过程使用介绍
热门推荐
EnskDeCode
04-25 15万+
在数据库编程过程中经常会用到存储过程 , 相比 SQL 语句 , 存储过程更方便 , 快速 , 安全 ; 先将存储过程的相关介绍和使用方法总结如下 ;1. 存储过程的概念存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次编译 , 用户通过指定存储过程的名字并给出参数 (如果该存储
SQLSERVER实用教程- 存储过程和触发器.pdf
最新发布
04-17
本资源是一份名为《SQLSERVER实用教程- 存储过程和触发器.pdf》的详细教程，主要讲解了SQL Server中的存储过程和触发器概念。存储过程是SQL Server 2008的核心组件，它允许在服务器端执行T-SQL语句，具有显著的优势...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值