MySQL（视图、存储过程与函数、流程控制、触发器）

雷小道

已于 2023-06-19 20:26:40 修改

阅读量731

点赞数

文章标签： mysql 数据库 java

于 2023-06-19 17:22:31 首次发布

本文链接：https://blog.csdn.net/weixin_53120322/article/details/131225652

版权

第一章视图

1 什么是视图

为了提高复杂的SQL语句的复用性和表的操作的安全性，MySQL数据库管理系统提高了视图特性，所谓视图，本质上是一种虚拟表，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是视图并不在数据库以存储数据值的形式存在，行和列数据来自自定义视图的查询所引用的基本表。并且在具体引用视图时动态生成
视图事程序员只关心某些特定数据和他们所负责的特定任务，这样程序员只能看到视图中所定义的数据，而不是所引用表中的数据，从而提高数据库中数据的安全性

1.1 视图的特点

视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系
视图是由基本表（实表）产生的表(虚表)
视图的建立和删除不影响基本表
对视图内容的增删改直接影响基本表
当视图来自多个基本表时，不允许添加和删除数据

2. 创建视图

2.1 创建视图的语法形式

虽然视图可以被看成一种虚拟表，但是其物理上是不存在的，即MySQL并没有专门的位置为视图存储数据，根据视图的概念可以发现其数据来源于查询语句，创建视图的语法如下

CREATE [OR REPLACE] [ALGORITHM=[UNDEFINED][MERGE][TEMPTABLE]]
VIEW viewname[columnlist]
AS SELECT statement
[WITH [CASCADED][LOCAL]CHECK OPTION]

CRESTE：表示创建新的视图
REPLACE：表示替换已经创建的视图，
ALGORITHM：表示视图选择的算法；
1. UNDEFINED参数：表示MySQL将自动选择算法
2. MERGE参数：表示将使用视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分
3. TEMPTABLE参数：表示将视图的结构存入临时表，然后用临时表来执行语句
viewname：为视图的名称；columnlist：为属性列
SELECT statement：表示SELECT语句，
WITH [CASCADED][LOCAL]CHECK OPTION]：表示在视图更新时保证在视图的权限范围内
1. CASCADED参数：为默认值，表示更新视图时要满足所有相关视图和表条件
2. LOCAL参数：表示更新视图时满足该视图本身定义的条件即可
创建视图要求具有针对视图的CREATE VIEW权限，以及针对由SELECT语句选择的每一列上的某些权限，对于在SELECT语句中其它地方使用的列，必须具有SELECT权限，如果还有OR REPLACE子句，就必须在视图上具有DROP权限
注意：使用CREATE VIEW语句创建视图时，最好加上WITH CHECK OPTIO参数，而且最好加上CASCADED参数，这样从视图派生出新视图后，更新视图需要考虑其父视图的约束条件，这种方式比较严格，可以保证数据的安全性

创建视图时，需要有CREATE VIEW的权限，同时应该具有查询设计的列的SELECT权限，在MySQL数据库下面的表user中保存这些权限信息，可以使用SELECT语句查询

select Select_priv,Create_view_priv
from mysql.user
where user='root'

Select_priv属性：表示用户是否具有SELECT权限，Y表示拥有，N表示没有
Create_view_priv属性：表示用户是否具有CREATE VIEW权限，
mysql.user：表示MySQL数据库下面的表user
root：表示登录的用户名

2.2 在单表上创建视图

基础表emps数据
创建view_emps视图具体SQL语句如下

create view view_emps as
select employee_id,first_name,email,hire_date from emps

查看视图的结构

describe view_emps

在这里插入图片描述

2.3 在多表上创建视图

depts表数据
创建view_depts_emps视图

create ALGORITHM=MERGE VIEW 
view_depts_emps(firt_name,email,hire_date,department_name)
as select first_name,email,hire_date,depts.department_name
from emps,depts where emps.department_id=depts.department_id
with local check option

查看视图的结构

describe view_depts_emps

查询视图

SELECT * FROM 	view_depts_emps

在这里插入图片描述

3. 查看视图

3.1 使用DESCRIBE|DESC语句查看视图基本信息

DESCRIBE 视图的语法如下

DESCRIBE|DESC viewname

viewname：视图的名称

3.2 使用SHOW TABLES语句查看视图基本信息

执行SHOW TABLES语句时不仅会显示表的名字，同时会显示视图的名称,语句如下

use databasename（数据库名）;
show TABLES;

3.3 使用 SHOW TABLE STATUS语句查看视图基本信息

SHOW TABLE STATUS语句不仅会显示表的详细信息，同时也会显示视图的详细信息语法如下

show table atatus [from dbname] [like 'pattern']

dbname参数：用来设置数据库
关键字：show table status：表示将显示所设置的数据库里的表和视图的详细信息
LIKE参数：可以查看某一个具体表和视图的详细信息

use dbtest14
show table status from dbtest14

在这里插入图片描述

3.4 使用SHOW CREATE VIEW语句查看视图详细信息

如果想查看视图的定义信息，可以通过语句SHOW CREATE VIEW实现，语法如下

show create view view_emps（视图名）

在这里插入图片描述

3.5 在views表中查看视图详细信息

在MySQL中，所有视图的定义都保存在数据库information_schema的表views中，查询表views可以看到数据库中所有视图的纤细信息，语句如下

use information_schema
select * from information_schema.views where table_name='viewname'

查询view_emps表

select * from information_schema.views where table_name='view_emps'

在这里插入图片描述

4. 修改视图

修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化的时候，可以通过修改视图来保存与基本表的一致性，MySQL中通过CREATE OR REPLACE VIEW语句和ALTER语句来修改视图

4.1 使用CREATE OR REPLACE VIEW 语句修改视图

该语句的使用非常灵活，在视图已经存在的情况下，对视图进行修改，在视图不存在的时候可以创建视图，语法形式如下

CREATE OR REPLACE [ALGORITHM=[UNDEFINED][MERGE][TEMPTABLE]]
VIEW viewname[columnlist]
AS SELECT statement
[WITH [CASCADED][LOCAL]CHECK OPTION]

OR REPLACE表示：用来替换已经创建的视图

create view view_emps as
select employee_id,first_name,email,hire_date from emps
use dbtest14
# 替代：
create or replace view view_emps as
select first_name,email,hire_date from emps

4.2 使用ALTER语句修改语句

ALTER语句不仅可以修改表的定义、创建索引，还可以用来修改视图，ALTER语句修改视图的语法格式如下

ALTER ALGORITHM=[UNDEFINED][MERGE][TEMPTABLE]]
VIEW viewname[columnlist]
AS SELECT statement
[WITH [CASCADED][LOCAL]CHECK OPTION]
# 案例
alter view view_emps as 
select employee_id,first_name,email from emps

5. 更新视图

更新视图是指视图来进行增删改表中的数据，因为视图是一个虚拟表，其中没有数据，视图更新时都是转换到基本表来更新。更新视图时，只能更新权限范围内的数据，超出了范围就不能更新。

5.1 使用SQL语句更新视图

查询视图view_emps

select * from view_emps

在这里插入图片描述
2. 在视图view_emps中更新id为1000的记录，first_name=hr_lxd,email=Hello;具体语句如下

update view_emps
set first_name='hr_lxd',
email='Hello'
where employee_id=100

虽然UPDATE语句更新的是视图，但实际更新的是表emps,上面的UPDATE语句可以等价为

UPDATE emps set first_name='hr_lxd',
email='Hello'
where employee_id=100

5.2 更新基本表后视图自动更新

当在基本表中插入视图的数据的时候，已经创建的的视图会自动更新表中的数据

创建视图view_student并在student中插入一条数据

create view view_student
as select * from student

insert into student values(null,'小道','DEMO1')

查看视图，可以看到数据自动更新

select * from view_student

在这里插入图片描述

5.3 删除视图中的数据

使用DELEC语句删除view_student视图中的标号为5的记录，具体语句如下

delete from view_student where id=5

查询student表可以发现已经删除成功

select * from student

在这里插入图片描述

5.4 不能更新的视图

对视图的更新最后都是实现在基本表上，更新视图时，实际上更新的是基本表上的记录，但是，并不是所有的视图都是可以更新的，以下几种情况是不能更新视图的
1. 视图中包含sum()、count()、max()和min()等函数
2. 视图中包含union、union all、distinct、croup by和having等关键字
3. 常量视图
4. 视图的SELECT中包含子查询
5. 由不可更新的视图导航粗的视图
6. 创建视图时，ALGORITHM为TEMPTABLE类型
7. 视图对应的表存在没有默认值的列，而且该列没有包含在视图里

6. 删除视图

删除视图是指删除数据库中已经存在的视图，删除视图时，只能删除视图的定义，不会删除数据

6.1 删除视图的语法形式

drop view viewname

第二章存储过程与函数

存储过程和函数是在数据库定义的一些SQL语句的集合，直接调用这些存储过程和函数来执行已经定义好的SQL语句，使用存储过程和函数可以避免开发人员重复的编写相同的SQL语句，而且存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务端的数据传输

1. 创建和存储过程和函数

创建存储过程和函数是指将经常使用的一组SQL语句组合在一起，并将这些SQL语句当做一个整体存储在MySQL服务器中，存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是：CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值函数可以从语句外调用(通过引用函数名)也能返回标量值，存储过程也可以调用其它存储过程

1.1 创建存储过程

在MySQL中创建存储过程通过SQL语句CREATE PROCEDURE来实现，语法形式如下

CREATE PROCEDURE procedure_name([proc_param[,....]])
[characteristic....] routine_body

procedure_name：表示要创建存储过程的名称
proc_param参数：表示存储过程的参数
characteristic参数：表示存储过程的特性
routine_body参数：表示存储过程的SQL语句代码，
可以用BEGIN…END来表示SQL语句的开始和结束

proc_param中每个参数的语法形式如下

[IN|OUT|INOUT] param_naem type

IN：表示输入类型、OUT：表示输出类型、INOUT：表示输入/输出类型
param_name表示参数名
type：表示参数类型，可以使MySQL软件所支持的任意一个数据类型

参数charateristic指定存储过程的特性，有以下取值

LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL ,SQL是LANGUAGE的唯一值
[NOT]DETERMINISTIC：指明存储过程执行的结果是否正确，DETERMINISTIC表示结果是确定的，每次执行存储过程是，相同的输入会得到相同的输出，NOR DETERMINISTIC表示结果是不确定的，相同的结果可能得到不同的输出，如果没有指定任意一个值，默认是NOR DETERMINISTIC
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使
用SQL语句的限制。
1. CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
2. NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
3. READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
4. MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
5. 默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执
行当前存储过程
1. DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
2. INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
3. 默认是DEFINER
COMMENT’string’：注释信息，可以用来描述存储过程和函数
存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进
行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值

需要设置新的结束标记：ELIMITER 新的结束标记

代码示例

创建存储过程select_all_emp，查看emps表的所有数据

delimiter $
create procedure select_all_emp()
COMMENT '查看所有数据'
begin
select * from emps;
end;
$
delimiter ;

创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出

DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms FROM emps;
END //
DELIMITER ;

创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名。

DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
SELECT salary FROM emps WHERE ename = empname;
END //
DELIMITER ;

1.2 创建存储函数

在MySQL中，创建函数通过SQL语句CREATE FUNCTION来实现，其语法形式weiu

create function fun_name([func_param[,.....]])
[characteristic] routine_body

func_name参数：代表创建函数的名字
func_param参数：表示函数的参数
1. 语法形式为：param_name type
2. param_name表示参数名，type表示参数类型
characateristic参数：表示函数的特征
routine_body参数：表示函数的SQL语句代码，可以用begin…end来表示SQL语句的开始和结束

创建查询emps表中id为100员工的工资的函数

delimiter $$
create function func_employee()
returns int(6)
comment '查询某个员工的工资'
begin 
	return (select salary from emps where emps.employee_id=100);
end;
$$
deltmiter ;

2. 调用存储过程和函数

2.1 调用存储过程

MySQL中使用CALL语句来调用存储过程，调用存储过程后，数据库系统将执行存储过程中的语句，语法如下

call proc_name([parameter[,....]])

proc_name：存储过程的名称，parameter：存储过程的参数

定义一个存储过程，然后调用这个存储过程


create procedure proc_emps_id(in empid int,out sal int)
comment '查询某个员工的薪水'
begin 
select salary from emps where employee_id=empid;
end;
$$
delimiter ;
call proc_emps_id(100,@sal);
select @sal;

2.2 调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是 用户自己定义 的，而内部函数是MySQL的 开发者定义 的
定义一个存储寒暑假，然后调用这个存储函数，代码如下


delimiter $$
create function func_emps_sq(id int)
returns int
begin 
return (select salary from emps where employee_id=id);
end;
$$
delimiter ;
select func_emps_sq(100)

3 查看存储过程和函数

3.1 使用show status语句查看存储过程和函数的状态

show {procedure|function} status{LIKE 'patten'}

procedure：表示查询存储过程
funcion：表示查询存储参数
LIKE‘pattern’用来匹配存储过程和函数的名称

查询名为proc_emps_id的存储过程的状态

show procedure status like 'proc_emps_id'

在这里插入图片描述
执行结果显示了存储过程的创建时间、修改时间和字符集等信息

3.2 使用show create 语句查看存储过程和函数的定义

语句如下

show create {procedure|function} proc_name

3.3 从information_schema.routine表中查看存储过程和函数的信息

存储过程和函数的信息存储在information_schema数据库下的routines表中，可以通过查询该表的记录来查询存储过程和函数的信息,语句如下

select * from information_schema.Routines where ROUTINE_MAME='proc_name'

ROUTINE_NAME字段：存储的是存储过程和函数的名称

4 修改存储过程和函数

4.1 修改存储过程和函数的语法

MySQL中修改存储过程和函数的语句的语法形式如下

altat {[procedure|function} proc_name[characteristic...]
Characteristic
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
|SQL SCURITY{definder|invoker}
|comment 'sting'

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使
用SQL语句的限制。
1. CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
2. NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
3. READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
4. MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
5. 默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执
行当前存储过程
1. DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
2. INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
3. 默认是DEFINER

5. 删除存储过程和函数

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。

第三章变量、流标控制与游标

1. 变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。
在 MySQL 数据库中，变量分为系统变量以及用户自定义变量。

1.1 系统变量

1.1.1 系统变量的分类

变量由系统定义，不是用户定义，属于服务器层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件（例如my.ini等）中的参数值。大家可以通过网址查看MySQL文档的系统变量。
系统变量分为全局系统变量（需要添加 global 关键字）以及会话系统变量（需要添加 session 关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。如果不写，默认会话级别。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量。
每一个MySQL客户机成功连接MySQL服务器后，都会产生与之对应的会话。会话期间，MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制。如下图：
在这里插入图片描述

全局系统变量针对于所有会话（连接）有效，但不能跨重启
会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值。会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。

在MySQL中有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；有些系统变量作用域既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；有些系统变量的作用域只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID。

1.1.2 查看系统变量

查看所有或部分系统变量

#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
或
SHOW VARIABLES;
#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';
SHOW GLOBAL VARIABLES LIKE 'admin_%';

查看指定系统变量
- 作为 MySQL 编码规范，MySQL 中的系统变量以两个“@” 开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

#查看指定的系统变量的值
SELECT @@global.变量名;
#查看指定的会话变量的值
SELECT @@session.变量名;
#或者
SELECT @@变量名;

修改系统变量的值
- 有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、特征。具体方法：
- 方式1：修改MySQL 配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
- 方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;
#为某个会话变量赋值
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;
SELECT @@global.autocommit;
SET GLOBAL autocommit=0;
SELECT @@session.tx_isolation;
SET @@session.tx_isolation='read-uncommitted';
SET GLOBAL max_connections = 1000;
SELECT @@global.max_connections;

1.2 用户变量

1.2.1 用户变量的分类

用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以一个“@” 开头。根据作用范围不同，又分为会话用户变量和局部变量

会话用户变量：作用域和会话变量一样，只对 当前连接 会话有效
局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在 存储过程和函数 中使用。

1.2.2 会话用户变量

变量的定义

#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;
#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];

查看用户变量的值（查看、比较、运算等）

SELECT @用户变量
举例
SELECT @a;
SELECT @num := COUNT(*) FROM employees;
SELECT @num;
SELECT AVG(salary) INTO @avgsalary FROM employees;
SELECT @avgsalary;
SELECT @big; #查看某个未声明的变量时，将得到NULL值

1.2.3 局部变量

定义：可以使用 DECLARE 语句定义一个局部变量
作用域：仅仅在定义它的 BEGIN … END 中有效
位置：只能放在 BEGIN … END 中，而且只能放在第一句

BEGIN
#声明局部变量
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
#为局部变量赋值
SET 变量名1 = 值;
SELECT 值 INTO 变量名2 [FROM 子句];
#查看局部变量的值
SELECT 变量1,变量2,变量3;
END

定义变量：

DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL

.变量赋值

SET 变量名=值;
SET 变量名:=值;
# 或者
SELECT 字段名或表达式 INTO 变量名 FROM 表;

使用变量

SELECT 局部变量名;

举例1：声明两个变量，求和并打印（分别使用会话用户变量、局部变量的方式实现）

#方式1：使用用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
#方式2：使用局部变量
DELIMITER //
CREATE PROCEDURE add_value()
BEGIN
#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 3;
DECLARE SUM INT;
SET SUM = m+n;
SELECT SUM;
END //
DELIMITER ;

举例2：创建存储过程“different_salary”查询某员工和他领导的薪资差距，并用IN参数emp_id接收员工id，用OUT参数dif_salary输出薪资差距结果。

1.2.4 对比会话变量与局部变量

在这里插入图片描述

2. 定义条件与处理程序

定义条件 是事先定义程序执行过程中可能遇到的问题， 处理程序 定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能
力，避免程序异常停止运行。
说明：定义条件和处理程序在存储过程、存储函数中都是支持的

2.1 定义条件

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。
定义条件使用DECLARE语句，语法格式如下：

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

错误码的说明：

MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误
- MySQL_error_code是数值类型错误代码。
- sqlstate_value是长度为5的字符串类型错误代码。
例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000’是sqlstate_value。
例如，在ERROR 1142（42000）中，1142是MySQL_error_code，'42000’是sqlstate_value

举例：：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。

#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

2.2 处理程序

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时，使用DECLARE语句的语法如下：

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。
- CONTINUE ：表示遇到错误不处理，继续执行。
- EXIT ：表示遇到错误马上退出。
- UNDO ：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
错误类型（即条件）可以有如下取值：
- SQLSTATE ‘字符串错误码’ ：表示长度为5的sqlstate_value类型的错误代码；
- MySQL_error_code ：匹配数值类型错误代码；
- 错误名称：表示DECLARE … CONDITION定义的错误条件名称。
- SQLWARNING ：匹配所有以01开头的SQLSTATE错误代码；
- NOT FOUND ：匹配所有以02开头的SQLSTATE错误代码；
- SQLEXCEPTION ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“ SET 变量 = 值 ”这样的简单语句，也可以是使用 BEGIN … END 编写的复合语句。

定义处理程序的几种方式，代码如下：

#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

2.3 案例

创建一个名称为“InsertDataWithCondition”的存储过程，代码如下。在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到sqlstate_value值为23000时，执行EXIT操作，并且将@proc_value的值设置为-1。

DELIMITER //
CREATE PROCEDURE InsertDataWithCondition()
BEGIN
DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000' ;
DECLARE EXIT HANDLER FOR duplicate_entry SET @proc_value = -1;
SET @x = 1;
INSERT INTO departments(department_name) VALUES('测试');
SET @x = 2;
INSERT INTO departments(department_name) VALUES('测试');
SET @x = 3;
END //
DELIMITER ;
 CALL InsertDataWithCondition();
 SELECT @x,@proc_value;

3. 流程控制

解决复杂问题不可能通过一个 SQL 语句完成，我们需要执行多个 SQL 操作。流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序，是我们完成复杂操作必不可少的一部分。只要是执行的程序，流程就分为三大类：

顺序结构：程序从上往下依次执行
分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
循环结构：程序满足一定条件下，重复执行一组语句

针对于MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。

条件判断语句：IF 语句和 CASE 语句
循环语句：LOOP、WHILE 和 REPEAT 语句
跳转语句：ITERATE 和 LEAVE 语句

3.1 分支结构-If语句

IF语句用来进行条件判断，根据条件执行不同的语句，使用在begin…end中，语法如下

IF search_condition THEM statement_list
[elseif search_condition THEM statement_list]...
[else statement_list]
end if

举例：声明存储过程“update_salary_by_eid1”,定义in参数emp_id,输入员工编号，薪资如果低于5000元，就更新薪资为5500元；薪资如果大于等于5000元且低于6000的，就更新薪资为7000元；；其他的涨薪100元。

delimiter $$
create procedure update_salary_by_eid1(in emp_id int)
begin
declare emp_salary double;
select salary into emp_salary from emps where employee_id=emp_id;
if emp_salary<5000
then update emps set salary=5500 where employee_id=emp_id;
elseif emp_salary<6000 then update emps set salary=7000 where employee_id=emp_id;
else update emps set salary=salary+100 where employee_id=emp_id;
end if;
end;
$$
delimiter ;
call update_salary_by_eid1(100)

3.2 分支结构-CASE

3.2.1 语法结构

语法结构1：

#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

语法结构2

#情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

3.2.2 举例

使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。

CASE val
WHEN 1 THEN SELECT 'val is 1';
WHEN 2 THEN SELECT 'val is 2';
ELSE SELECT 'val is not 1 or 2';
END CASE;

使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0。

CASE
WHEN val IS NULL THEN SELECT 'val is null';
WHEN val < 0 THEN SELECT 'val is less than 0';
WHEN val > 0 THEN SELECT 'val is greater than 0';
ELSE SELECT 'val is 0';
END CASE;

明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。判断该员工的入职年限，如果是0年，薪资涨50；如果是1年，薪资涨100；如果是2年，薪资涨200；如果是3年，薪资涨300；如果是4年，薪资涨400；其他的涨薪500。

DELIMITER //
CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGIN
DECLARE emp_sal DOUBLE;
DECLARE hire_year DOUBLE;
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT ROUND(DATEDIFF(CURDATE(),hire_date)/365) INTO hire_year FROM employees
WHERE employee_id = emp_id;
CASE hire_year
WHEN 0 THEN UPDATE employees SET salary=salary+50 WHERE employee_id = emp_id;
WHEN 1 THEN UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
WHEN 2 THEN UPDATE employees SET salary=salary+200 WHERE employee_id = emp_id;
WHEN 3 THEN UPDATE employees SET salary=salary+300 WHERE employee_id = emp_id;
WHEN 4 THEN UPDATE employees SET salary=salary+400 WHERE employee_id = emp_id;
ELSE UPDATE employees SET salary=salary+500 WHERE employee_id = emp_id;
END CASE;
END //
DELIMITER ;

3.3 循环结构-LOOP

LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。
基本语法

[begin_laber:]LOOP 
	statement_list
end LOOP [end_laber]

begin_laber与end_laber：表示循环开始和结束的标志，两个标志必须相同
statement：表示需要循环执行的语句

3.3.1 举例

使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程

DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id +1;
IF id >= 10 THEN LEAVE add_loop;
END IF;
END LOOP add_loop

举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程“update_salary_loop()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。

DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
DECLARE avg_salary DOUBLE;
DECLARE loop_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_salary FROM employees;
label_loop:LOOP
IF avg_salary >= 12000 THEN LEAVE label_loop;
END IF;
UPDATE employees SET salary = salary * 1.1;
SET loop_count = loop_count + 1;
SELECT AVG(salary) INTO avg_salary FROM employees;
END LOOP label_loop;
SET num = loop_count;
END //
DELIMITER ;

3.3.2 跳出循环

LEAVE语句主要用于跳出循环的控制

leave 循环标志

iferate语句也是用来跳出循环，但是iterate语句时跳出本次循环，然后直接进行下一次循环

leave 循环标志

3.4 循环结构-repeat

REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。REPEAT语句的基本格式如下：

[begin_laber:]repeat 
	statement_list
	UNTIL 结束循环的条件表达式
end repeat [end_laber]

3.4.1 举例

repeat 
	set @count=@count+1
	until @count=100
	end repeat

循环执行count加1的操作，当count的值为100跳出循环

3.5 循环结构-WHILE

WHILE语句也是有条件循环的循环语句，但WHILE语句是当满足条件时，执行循环内的语句，语法如下

[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];

while_label为WHILE语句的标注名称；如果循环条件结果为真，WHILE语句内的语句或语句群被执行，直至循环条件为假，退出循环。

3.5.1 举例

while @count<100 do
set @count=@count+1
end while

判断count值是否小于100，结果为真则执行循环体，否则跳出循环

4. 游标

4.1 什么是游标

4.1 什么是游标（或光标）
虽然我们也可以通过筛选条件 WHERE 和 HAVING，或者是限定返回记录的关键字 LIMIT 返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录，并对记录的数据进行处理。这个时候，就可以用到游标。游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。
在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。
MySQL中游标可以在存储过程和函数中使用。
比如，我们查询了 emps 数据表中工资高于15000的员工都有哪些：

SELECT employee_id,last_name,salary FROM emps
WHERE salary > 15000;

在这里插入图片描述
这里我们就可以通过游标来操作数据行
游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。
如果我们想要使用游标，一般需要经历四个步骤。不同的 DBMS 中，使用游标的语法可能略有不同。

4.2 游标的使用

第一步，声明游标
在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement;

+ 这里 select_statement 代表的是SELECT 语句，返回一个用于创建游标的结果集。

第二步，打开游标
当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备。
语法如下

OPEN cursor_name

第三步，使用游标（从游标中取得数据）

FETCH cursor_name INTO var_name [, var_name] ...

这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。
注意：var_name必须在声明游标之前就定义好。

FETCH cur_emp INTO emp_id, emp_sal ;

注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误。
第四步，关闭游标

CLOSE cursor_name

有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标就不能通过FETCH来使用

4.3 举例

创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。

DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT
total_count INT)
BEGIN
DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
DECLARE emp_count INT DEFAULT 0; #记录循环个数
#定义游标
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
#打开游标
OPEN emp_cursor;
REPEAT
#使用游标（从游标中获取数据）
FETCH emp_cursor INTO cursor_salary;
SET sum_salary = sum_salary + cursor_salary;
SET emp_count = emp_count + 1;
UNTIL sum_salary >= limit_total_salary
END REPEAT;
SET total_count = emp_count;
#关闭游标
CLOSE emp_cursor;
END //
DELIMITER ;

4.4 小结

游标是 MySQL 的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。
键议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

第四章触发器

在实际开发中，我们经常会遇到这样的情况：有 2 个或者多个相互关联的表，如 商品信息 和 库存信息 分别存放在 2 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。
这样一来，我们就必须把这两个关联的操作步骤写到程序里面，而且要用事务包裹起来，确保这两个操作成为一个原子操作，要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手动维护，这样就很容易忘记其中的一步，导致数据缺失。
这个时候，咱们可以使用触发器。你可以创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了。

1. 触发器的概述

MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。
触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。
当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

2. 触发器的创建

2.1 语法

创建触发器的语法结构是：

create trigger 触发器名称
{before|after}{insert|update|delete} on 表名
for each row 
触发器执行的语句快

表名：表示触发器监控的对象
before|after：表示触发的时间，before表示在事件之前触发，after表示在事件之后触发
insert|update|delete：表示触发的事件
- insert：表示插入记录时触发
- update：表示更新记录时触发
- delete：表示删除记录时触发

2.2 代码举例

举例1

创建数据库

CREATE TABLE test_trigger (
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30)
);
CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
);

创建触发器：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向
test_trigger_log数据表中插入before_insert的日志信息。

delimiter $$
create trigger before_insert
before insert on test_trigger
for each row 
begin 
insert into test_trigger_log(t_log)
values('before_insert');
end $$
delimiter ;

向test_trigger数据表中插入数据，会自动执行触发器

INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');

查看test_trigger_log数据表中的数据

select * from test_trigger_log

举例2

定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为’HY000’的错误，从而使得添加失败。

DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
DECLARE mgrsalary DOUBLE;
SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
#NEW表示添加的数据
IF NEW.salary > mgrsalary THEN
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
END IF;
END //
DELIMITER ;

#测试
DESC employees;

#添加成功：依然触发了触发器salary_check_trigger的执行
INSERT INTO employees(employee_id,last_name,email,hire_date,job_id,salary,manager_id)
VALUES(300,'Tom','tom@126.com',CURDATE(),'AD_VP',8000,103);

#添加失败
INSERT INTO employees(employee_id,last_name,email,hire_date,job_id,salary,manager_id)
VALUES(301,'Tom1','tom1@126.com',CURDATE(),'AD_VP',10000,103);

SELECT * FROM employees;

3. 查看触发器

3.1 使用show triggers语句查看触发器

在MySQL软件中，不能创建具有相同名字的触发器，另外，对于具有相同触发程序动作时间和事件的给定表，不能有两个触发器，因此在创建触发器之前要查看已经存在的触发器
使用SQL语句show triggers来实现,其语法形式如下

show triggers

3.2 查看系统表triggers实现查看触发器

在MySQL中，在系统表information_schema中存在一个所有触发器信息的系统表triggers,所有可以查看系统表triggers来获取触发器信息

use information_schema
select * from triggers

4 删除触发器

drop trigger 触发器名称

雷小道

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
MySQL（视图、存储过程与函数、流程控制、触发器）

语句如下定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。说明：定义条件和处理程序在存储过程、存储函数中都是支持的定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。
复制链接

扫一扫