清茶书香

一杯清茶,一本书籍,一个下午。


  • 首页

  • 归档

  • 分类

  • 关于

  • 搜索
Redis JPA Solr SpringData SpringMVC localRepository local Mapper 事务 Mybatis JDBC AOP DI IOC 常用函数 触发器 存储过程 Promise Gateway SpringCloud vue-cli axios es6 webpack npm vue 个性化 zsh 终端 caffeine jvm缓存 guava cache validation Mapping MapStruct comment 小程序 建站 WeHalo config logback plugins database idea maven spring https http nginx password RabbitMQ 秒杀系统 Windows MySQL 数据备份 halo SpringBoot shell Linux ip Optional Stream Lambda k8s Docker 列编辑 vim MacOS 图片合成 Java 远程联调 nps 内网穿透

MySQL存储过程和触发器的语法

发表于 2019-10-24 | 分类于 MySQL | 0 | 阅读次数 192

MySQL的存储过程

一、创建存储过程

存储过程就是具有名字的一段代码,用来完成一个特定的功能,

实质就是数据库 SQL 语言层面的代码封装与重用。

优点(为什么要用存储过程?):

  ①将重复性很高的一些操作,封装到一个存储过程中,简化了对这些SQL的调用

  ②批量处理:SQL+循环,减少流量,也就是“跑批”

  ③统一接口,确保数据的安全

CREATE
    [DEFINER = { user | CURRENT_USER }] 
 PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter: 
    [ IN | OUT | INOUT ] param_name type  #参数名 参数类型( IN | OUT | INOUT )
    
    #IN 	输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
	#OUT 	输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
	#INOUT	输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
	# 注:INOUT 尽量少用

characteristic: 
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
  Valid SQL routine statement

[begin_label:] BEGIN
  [statement_list]
    ……
END [end_label]

(一)存储过程中的关键语法

声明语句的结束符,可以自定义

DELIMITER $$ #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
或
DELIMITER //

声明存储过程

CREATE PROCEDURE demo_in_parameter(IN p_in int) #带参的存储过程(也可以不带参)

存储过程的开始和结束符号

BEGIN .... END    

定义变量

DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];

其中,datatype 为 MySQL 的数据类型,如: int, float, date,varchar(length),例如:

DECLARE l_int int unsigned default 4000000; #int类型、无符号类型、默认4000000
DECLARE l_int int unsigned default 4000000;  
DECLARE l_numeric number(8,2) DEFAULT 9.95;  
DECLARE l_date date DEFAULT '1999-12-31';  
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

变量赋值

SET @p_in=1  

调用存储过程

call sp_name[(传参)];  

(二)存储过程的控制语句

条件语句

(1)if-then-else语句:

if var=0 then #var代表一个变量
	#执行体,比如:
	insert into tableName values(17); #tableName里只有一个整型字段filedName
end if;  
if parameter=0 then 
	#执行体,比如:
  	update tableName set filedName=filedName+1;  
else 
	#执行体,比如:
  	update tableName set filedName=filedName+2;  
end if;  

(2)case语句:

case
    when var=0 then #var代表一个变量
        insert into tableName values(30);
    when var>0 then
    	insert into tableName values(20);
    when var<0 then
    	insert into tableName values(10);
    else
    	insert into tableName values(0);
end case
循环语句

(1)while ···· end while

类似C、C++、Java等语言中的while语句

while 条件 do
    -- 循环体
end while

示例:

declare var int;  
set var=0;  
while var<6 do  
	insert into tableName values(var);  
set var=var+1;  
end while;  

(2)repeat···· end repea

它在执行操作后检查结果,而 while 则是执行前进行检查。类似C、C++、Java等语言中的do ... while语句

repeat
    -- 循环体
until 循环条件  
end repeat;

示例:

declare v int; 
set v=0;  
repeat  
	insert into tableName values(v);  
set v=v+1;  
until v>=5
end repeat;

(3) loop ·····endloop

loop 循环和 repeat 循环相似, leave 语句的意义是离开循环。

declare v int;  
set v=0;  
LOOP_LABLE: loop
	insert into tableName values(v);
set v=v+1;
if v >=5 then 
	leave LOOP_LABLE; 
end if;  
end loop;

(4)ITERATE迭代

ITERATE 通过引用复合语句的标号,来从新开始复合语句:

declare v int;  
set v=0;  
LOOP_LABLE: loop  
	if v=3 then   
    	set v=v+1;  
    	ITERATE LOOP_LABLE;
    end if;  
    	insert into t values(v);  
    	set v=v+1;  
    if v>=5 then 
    	leave LOOP_LABLE;  
    end if;
end loop;  

(三)存储过程的示例

delimiter $$  #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE pre_name VARCHAR(255);#定义一个临时的字符类型的name
DECLARE ageVal INT;#定义一个临时的整型的age
DECLARE i INT;#定义一个整型的i
DECLARE pre_gender VARCHAR(255);#定义一个临时的字符类型的gender
DECLARE pre_nick_name VARCHAR(255);#定义一个临时的字符类型的nick_name

SET ageVal=100;#给ageVal变量赋值
SET i=1;#给i赋值初始值

WHILE i < 100 DO
		#循环产生一个随机的“姓名”的字符串,并赋值给pre_name
		SET pre_name=concat(substring('赵钱孙李龚',floor(1+5*rand()),1),substring('明国华建',floor(1+4*rand()),1),substring('明国华建',floor(1+4*rand()),1));
		#循环产生一个随机的“性别”的字符串,并赋值给pre_gender
		SET pre_gender=SUBSTRING('男女',floor(1+2*rand()),1);
		#循环产生一个随机的“昵称”的字符串,并赋值给pre_nick_name
		SET pre_nick_name=concat(substring('赵钱孙李',floor(1+4*rand()),1),substring('明国华建',floor(1+4*rand()),1),substring('明国华一',floor(1+4*rand()),1));
		INSERT INTO student(sname,age,gender,nick_name)VALUES(pre_name,(ageVal+i)%30,pre_gender,pre_nick_name);
SET i=i+1;
END WHILE;
END $$ #这里的$$相当于平时的SQL语句结束时的;   代表着语句真正结束

delimiter;  #将语句的结束符号恢复为分号
call proc_batch_insert(); #调用存储过程

解析:默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名前面加数据库名做前缀。 在定义过程时,使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$,使得过程体中使用的分号被直接传递到服务器,而不会被客户端(如mysql)解释。

存储过程体

  • 存储过程体包含了在过程调用时必须执行的语句,例如:dml、ddl语句,if-then-else和while-do语句、声明变量的declare语句等

  • 过程体格式:以begin开始,以end结束(可嵌套)

    label1: BEGIN #“label1”为语句贴上标签
      label2: BEGIN
        label3: BEGIN
          statements; 
        END label3;
      END label2;
    END label1
    

**注意:**每个嵌套块及其中的每条语句,必须以分号结束,表示过程体结束的begin-end块(又叫做复合语句compound statement),则不需要分号。

**标签有两个作用: **

  • 1、增强代码的可读性
  • 2、在某些语句(例如:leave和iterate语句),需要用到标签

二、修改存储过程

ALTER PROCEDURE 数据库.存储过程名;

三、删除存储过程

DROP PROCEDURE 数据库.存储过程名;

四、查询存储过程

SHOW CREATE PROCEDURE 数据库.存储过程名; -- 查看存储过程详细情况
select name from mysql.proc where db='数据库名'; -- 查询某个数据库中的存储过程的名字
select routine_name from information_schema.routines where routine_schema='数据库名';
-- 查询某个数据库中的routine_name
show procedure status where db='数据库名'; -- 查看某个存储过程的状态

MySQL中的触发器

!!尽量少使用触发器,不建议使用。

触发器是针对每一行的;对增删改非常频繁的表上切记不要使用触发器,因为它会非常消耗资源。

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
  [trigger_order]
trigger_body

trigger_time: { BEFORE | AFTER } #触发时机:触发操作前、触发操作后

trigger_event: { INSERT | UPDATE | DELETE } #触发事件:增加、更新、删除

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

示例:通过触发器实现级联更新、删除

#级联更新的触发器
DROP TRIGGER IF EXISTS `ins_goods`;
create trigger ins_goods
after insert on goods for each row 
begin
	  insert into shop_goods ( cId, gId)
		values( new.cId, new.gId);
end;
#级联删除的触发器
DROP TRIGGER IF EXISTS `del_goods`;
create trigger del_goods
after delete on goods for each row 
begin
	  delete from shop_goods where cId = old.cId;
end;
Bennett wechat
欢迎收藏我的微信小程序,方便查看更新的文章。
  • 本文作者: Bennett
  • 本文链接: https://hibennett.cn/archives/storedprocedures-triggers
  • 版权声明: 本博客所有文章除特别声明外,均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处!
# MySQL # 存储过程 # 触发器
MySQL存储函数-基本常用函数
安装jar包到本地maven仓库
  • 文章目录
  • 站点概览
Bennett

Bennett

60 日志
28 分类
74 标签
RSS
Github E-mail Gitee QQ
Creative Commons
Links
  • MacWk
  • 知了
0%
© 2020 — 2023 hibennett.cn版权所有
由 Halo 强力驱动
|
主题 - NexT.Pisces v5.1.4

浙公网安备 33010802011246号

    |    浙ICP备2020040857号-1