一.存储过程

1.存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据
库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

其实存储过程就相当于自定义函数一样，其在navicat里面的翻译就是函数。

创建存储过程：

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	--SQL语句
END;

调用存储过程：

CALL 名称([参数]);

查看存储过程：

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='XXX';   #查询指定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程;                                          #查询某个存储过程的定义

删除存储过程：

DROP PROCEDURE [IF EXISTS] 存储过程名称;

注意：在命令行中，执行创建存储过程的SQL时需要通过关键字delimiter指定SQL语句的结束符。

例如：

delimiter $

该命令把$作为结束符。

2.变量

变量分为：

  • 系统变量
  • 用户自定义变量
  • 局部变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)，服务器默认是会话变量。

查看系统变量：

SHOW [SESSION|GLOBAL] VARIABLES;                          -- 查看所有系统变量
SHOW [SESSION|GLOBAL] VARIABLES LIKE '……';                -- 可以通过like模糊匹配方式查找变量
SELECT @@[SESSION|GLOBAL].系统变量名;                      -- 查看指定变量的值

设置系统变量：

SET [SESSION|GLOBAL] 系统变量名=值;
SET @@[SESSION|GLOBAL] 系统变量名=值;

mysql服务重新启动之后，所设当的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置。

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接（会话）。

赋值：

SET @变量名=值;
SET @变量名:=值;

SET方式的赋值可以联合赋值

SELECT @变量名:=值;
SELECT 字段名 INTO @变量名 FROM 表名;                        #把select语句的输出结果赋给变量

使用：

SELECT @变量名;

用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量
的范围是在其内声明的BEGIN ... END块。

声明：

DECLARE 变量名 变量类型 [DEFAULT 默认值];

局部变量不需要@，声明后通过set直接赋值即可，在声明时可以用default提供默认值。

3.IF条件判断

IF 条件1 THEN
	……
ELSEIF 条件2 THEN
	……
ELSE
	默认执行语句
END IF;

4.参数

在这里插入图片描述

用法：

CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT 参数名 参数类型])
BEGIN
	--SQL语句
END;

5.流程控制case

用法1：

CASE 参数
	WHEN 条件1 THEN 
		执行语句1
	……
	ELSE 
		默认执行语句
END CASE;

用法2：

CASE
	WHEN 条件1 THEN 
		执行语句1
	……
	ELSE 
		默认执行语句
END CASE;

6.循环结构

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句

WHILE 条件 DO
	执行语句……
END WHILE;

repeat是有条件的循环控制语句，当满足条件的时候退出循环

#先执行一次逻辑，然后判定条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT 
	执行语句
	UNTIL 条件
END REPEAT;

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用:

  • LEAVE：配合循环使用，退出循环。
  • ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

两个语句搭配标签使用。

创建LOOP：

标签名:LOOP
	执行逻辑……
END LOOP 标签名;

两个补充：

LEAVE 标签名;                                  -- 退出标记的循环体
ITERATE 标签名;                                -- 直接进入下一次循环

7.游标

游标(CURSOR)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括 游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下

声明游标：

DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标：

OPEN 游标名称;

获取游标记录：

FETCH 游标名称 INTO 变量;

关闭游标：

CLOSE 游标名称;

8.条件处理程序

条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤：

DECLARE handler_action HANDLER FOR [(SQLSTATE '状态码')|(condition_value)] [执行语句];

handler_action可以取：

  • CONTINUE：继续执行当前程序
  • EXIT：终止执行当前程序

condition_value可以取：

  • SQLWARING：所有以01开头的SQLSTATE代码的简写
  • NOT FOUND：所有以02开头的SQLSTATE代码的简写
  • SQLEXCEPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

理解：

  • DECLARE handler_action HANDLER FOR [(SQLSTATE '状态码')|(condition_value)]声明一个条件处理程序，当抛出’状态码’的异常时进行条件处理：继续或终止执行当前程序
  • SQLSTATE '状态码'：根据状态码详细抛出异常
  • condition_value：异常用三个关键字进行划分，三选一
  • 后面可以补充执行语句表示处理异常后要执行的操作

9.案例练习（游标+条件处理）

-- 游标
-- 根据传入的参数uage，来查询用户表 tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），
-- 并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    declare u_cursor cursor for select name,profession from tb_user where age <= uage;
    declare exit handler for SQLSTATE '02000' close u_cursor;

    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );

    open u_cursor;
    while true do
        fetch u_cursor into uname,upro;
        insert into tb_user_pro values (null, uname, upro);
    end while;
    close u_cursor;

end;

10.存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。

语法：

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型 [返回参数特性]
BEGIN
	-- SQL语句
	RETURN……;
END

参数特性（三选一）：

  • DETERMINISTIC：相同的输入参数总是产生相同的结果
  • NOT SQL：不包含SQL语句
  • READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

二.触发器

1.触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

理解：把触发器作为一个SQL语句集合，但是在 insert/update/delete 之前或之后可以通过某一条件使其自动执行。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句
级触发。

创建触发器：

CREATE TRIGGER 触发器名字
	BEFORE|AFTER INSERT|UPDATE|DELETE 
ON 表名 FOR EACH ROW                                        -- 行级触发器
BEGIN
	执行语句	
END;

查看触发器：

SHOW TRIGGERS;

删除触发器：

DROP TRIGGER [数据库名] 触发器名;                     -- 如果没有指定数据库，则默认当前数据库

2.案例练习

准备日志表：

-- 触发器
-- 需求: 通过触发器记录 user 表的数据变更日志(user_logs) , 包含增加, 修改 , 删除 ;

-- 准备工作 : 日志表 user_logs
create table user_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

插入触发器：

-- 插入数据触发器
create trigger tb_user_insert_trigger
    after insert on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'insert', now(), new.id, concat('插入的数据内容为: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;

更新触发器：

-- 修改数据触发器
create trigger tb_user_update_trigger
    after update on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'update', now(), new.id,
        concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,
            ' | 更新之后的数据: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;

删除触发器：

-- 删除数据触发器
create trigger tb_user_delete_trigger
    after delete on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'delete', now(), old.id,
        concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
end;
# 数据库 # mysql # oracle
Logo
腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

cover

动物识别系统 python实现+UI

avatar 腾讯云开发者社区

Linux下安装MySQL8.0(超详细)

Linux下安装MySQL8.0(超详细)

avatar 腾讯云开发者社区
cover

安卓手机如何连接苹果电脑

avatar 腾讯云开发者社区