存储过程、自定义函数、事务

一、存储过程

1.1 存储过程的概念：

        是一种预先编译好的 SQL 代码块，可以在需要执行该代码块的时候直接调用。存储过程可以接受参数，执行特定的操作，并返回结果集或输出参数等数据。通常情况下，存储过程会被封装到数据库中，方便多个应用程序或用户共享和重复使。

 存储过程的优点：

        与动态 SQL 相比，存储过程具有以下优点：

        （1）提高性能：存储过程只需要编译一次，就可以反复调用，从而减少了 SQL 编译的开销，提高了查询的效率。

        （2）安全性：存储过程可以设权限，限制对数据库的访问，避免了 SQL 注入等安全问题。

        （3）可维护性：存储过程将逻辑代码集中在数据库中，易于统一管理维护，方便修改迭代。

        （4）可重用性：存储过程可被多个应用程序或用户共享和重复使用，提高了代码可重用性。

        （5）支持事务：存储过程可以包含多条 SQL 语句，支持事务处理，保证了数据的完整性和一致性。  

        在使用存储过程时，需要注意以下几点：

         （1）存储过程需要事先定义和编译，因此需要一定的开发成本和维护成本。

        （2）存储过程不支持动态 SQL，需要在编码时考虑使用。

        （3）存储过程不能方便地跨数据库平台使用，需要考虑平台兼容性。

1.2 基本操作

（1）存储过程的创建：

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

（2）使用call来调用存储过程：

-- 调用存储过程语法结构：CALL 名称 ([ 参数 ])
call p1()；

 （3） 查看存储过程：

-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名称';  
 -- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称 ; 
-- 查看所有的存储过程
show procedure status;  

（4）案例实现：

create procedure p1()
begin
    insert into tb_user values (1,'ming',18);
    insert into tb_user values (2,'hong',19);
    insert into tb_user values (3,'hua',17);
    update tb_user set age = 20 where name = 'ming';
end;
call p1();
select * from tb_user;

 1.3 存储过程中的变量：

在MySQL中变量分为三种类型: 系统变量、用户变量、局部变量。

（1）系统变量：系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）和会话变量（SESSION）。全局变量在可以在所有的会话中使用，会话变量只能在当前会话中使用。

-- 查看全局的变量
SHOW GLOBAL VARIABLES;
-- 查看session变量
SHOW SESSION VARIABLES;

（2）用户变量：是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量名" 使用就可以。

-- 定义用户变量
set @a = 'a';
-- 对用户变量进行查看
select @a;
-- 另一种方法
set @b:=21;
select @b;

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

-- 声明变量
DECLARE 变量名 变量类型 [DEFAULT ... ] ;

-- 变量赋值
SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;

（4）代码实现：

create procedure p2()
begin
    -- 定义一个变量存储平均年龄
    declare avg_age int default 0;
    -- 定义一个变量存储最小年龄
    declare min_age int;
    -- 求出平均年龄
    select avg(age) into avg_age from tb_user;
    -- 求出最小年龄
    set min_age = (select age from tb_user order by age asc limit 1);
    -- 相减
    select avg_age - min_age as '年龄差';
end;
call p2;

-- 结果是2

1.4 存储过程IF判断：

（1）基本语法格式：

IF 条件1 THEN
	.....
ELSEIF 条件2 THEN     -- 可选
	.....
ELSE 				 -- 可选
	.....
END IF;

代码实现：

create procedure p3()
BEGIN
    declare score int default 90;
    if score > 90 then
        select '优秀';
    elseif score<=90 and score>80 then
        select '良好';
    elseif score<=80 and score>70 then
        select '一般';
    elseif score<=70 and score>60 then
        select '及格';
    else
        select '不及格';
    end if;
end;
call p3;

-- 结果输出‘良好’

1.5 存储过程的参数：

        在存储过程中，只能在存储名称( )中传递参数，不可以在call存储过程的时候返回参数。参数主要分为输入参数（in）、输出参数（out）、输入输出参数（inout）。在定义存储过程的参数的时候，先定义参数是in、out或者是inout，然后再定义参数的名称和类型。

（1）代码实现：

create procedure p4(in score int, out result varchar(10))
BEGIN
    if score > 90 then
        set result =  '优秀';
    elseif score<=90 and score>80 then
        set result = '良好';
    elseif score<=80 and score>70 then
        set result = '一般';
    elseif score<=70 and score>60 then
        set result = '及格';
    else
        set result = '不及格';
    end if;
end;
call p4(95, @result);
select @result;

-- 结果为'优秀'

（2）案例实现：判断是否是闰年

-- 判断是不是闰年
create procedure is_leap_year(in year int)
begin
    if year % 4 =0 and year % 100 != 0 then
        select '是闰年';
    elseif year % 400 = 0 then
        select '是闰年';
    else
        select '不是闰年，是平年';
    end if;
end;
call is_leap_year(2022);   -- 不是闰年，是平年

call is_leap_year(2020);   -- 是闰年

1.6 存储过程中的CASE：

        CASE语法是用于条件判断操作的一种方式，它类似于其他编程语言中的switch或if语句，允许基于条件表达式的值来选择要执行的操作。

        CASE语法有两种形式：simple和searched。其中，simple形式适用于对一个值进行等值比较的情况，而searched形式适用于对多个条件进行比较的情况。

（1）simple case的基本语法：

        使用Simple CASE时，只需指定要比较的表达式，并为每个可能的结果分配一个代码块。

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE result
END;
-- 其中，expression表示被比较的表达式，value1、value2等表示表达式可能具有的值，
-- result1、result2等则表示相应值时的结果，result是默认结果。
-- 注意，在Simple CASE中，每个比较值必须是常量。

代码实现：

create procedure simple_case(in grade char, out result varchar(10))
begin
    set result =
        case grade
            when 'A' then '优秀'
            when 'B' then '良好'
            when 'C' then '及格'
            else '不及格'
        end ;
end;
call simple_case('B', @result);
select @result;

-- 结果为：'良好'

（2）searched CASE 基本语法：

        使用Searched CASE时，可以根据多个条件选择结果，每个条件可有一个独立的布尔表达式。

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END;
-- 其中，condition1、condition2等表示要测试的条件，
-- result1、result2等表示相应条件为TRUE时的结果，result是默认结果。

代码实现：

-- 案例一：
create procedure searched_case(in grade int, out result varchar(10))
begin
    set result =
            case
                when grade>90 then '优秀'
                when grade<=90 and grade>80 then '良好'
                when grade<=80 and grade>60 then '及格'
                else '不及格'
                end ;
end;
call searched_case(85, @result);
select @result;

-- 案例二：
create procedure month_p(in month int,out result varchar(50))
BEGIN
    case
        when month>=1 and month<=3 then
            set result = '第一季度';
        when month>=4 and month<=6 then
            set result = '第二季度';
        when month>=7 and month<=9 then
            set result = '第三季度';
        when month>=10 and month<=12 then
            set result = '第四季度';
        end case;
END;
call month_p(6, @result);
select @result;

-- 以上两种是不同的写法

1.7 循环操作：

（1）while的使用：先判定条件，如果条件为true，则执行SQL逻辑，否则，不执行SQL逻辑

-- 基本语法：
WHILE 条件 DO
	SQL逻辑...
END WHILE;

  代码实现：

create procedure while_p(in count int)
begin
    while count > 0 do
        select 'while的操作';
        set count = count-1;
    end while ;
end;
call while_p(3);
-- 会执行3次

（2）repeat的使用：

-- 基本语法
REPEAT
	SQL逻辑...
	UNTIL 条件
END REPEAT;

代码实现：

create procedure repeat_p(in count int)
begin
    declare result int default 0;
    -- 会先执行一次(有点类似于java中的while)
    repeat
        set result = result + count;
        select concat('result = ', result);
        set count = count + 1;
    -- 满足条件时就终止
    until count > 6
    end repeat;
end;
call repeat_p(1);
-- 会执行6次，结果为21

（3）loop的使用：

-- loop的定义，
[begin_label:] LOOP
	SQL逻辑...
END LOOP [end_label];

-- loop的中断
LEAVE label; 		-- 退出指定标记的循环体,相当于break
ITERATE label; 		-- 直接进入下一次循环，相当于continue

代码实现：

create procedure loop_p(in count int)
begin
    declare result int default 0;
    -- 定义循环（自定义循环的名称）
    sum: loop
        -- 判断循环中断条件
        if count > 6 then
            -- 中断循环，相当于break
            leave sum;
        end if;
        if count = 4 then
            set count = count + 1;
            -- 中断此次循环，相当于continue
            iterate sum;
        end if;
        -- 计算结果
        set result = result + count;
        set count = count+1;
    end loop;
    select result;
end;
call loop_p(0);
-- 结果为17

1.8 游标

        游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环处理。游标的使用包括游标的声明（DECLARE ）、游标的打开（OPEN）、游标的获取（FETCH ）和 游标的关闭（CLOSE）。

（1）语法如下：

-- （1）游标的声明
DECLARE 游标名称 CURSOR FOR 查询语句 ;

-- （2）游标的打开
OPEN 游标名

-- （3）游标的获取
FETCH 游标名 into 变量 [, 变量, 变量……]；

-- （4）游标的关闭
CLOSE 游标名

（2）代码实现：

create procedure cursor_p()
begin
    declare salary decimal(7,2) default 0; -- 定义工资变量
    -- 创建游标
    declare my_cursor cursor for select sal from emp;
    -- 声明异常
    declare exit handler for sqlstate '02000' close my_cursor;
    -- 开启游标
    open my_cursor;
    -- 获取游标中的数据
    while true do
        fetch my_cursor into salary;
        -- 输出结果
        select salary;
    end while;
    -- 关闭游标
    close my_cursor;
end;
call cursor_p();
-- 15个结果

存储过程的案例实现：

        流水单是一个有自生含义的字符串，可以从中了解当前订单下单的时间，下单的数量等等。流水单也具备当天增量功能，即在一天之内是递增编号，但是过了一天之后又从0开始

CREATE PROCEDURE generate_serial_number()
BEGIN
  DECLARE current_date VARCHAR(8);
  DECLARE current_number INT;
  DECLARE new_number INT;

  -- 获取当前日期和递增数字
  SET current_date = DATE_FORMAT(CURRENT_DATE(), '%Y%m%d');
  SELECT RIGHT(serial_number, 4) INTO current_number
  FROM orders
  WHERE DATE(order_date) = CURRENT_DATE()
  ORDER BY serial_number DESC
  LIMIT 1;

  -- 生成新的流水单号
  SET new_number = IF(current_number IS NULL, 1, current_number + 1);
  INSERT INTO orders (serial_number, order_date) VALUES (CONCAT(current_date, '-', LPAD(new_number, 4, '0')), CURRENT_DATE());

  -- 返回新的流水单号
  SELECT CONCAT(current_date, '-', LPAD(new_number, 4, '0')) AS new_serial_number;
END；

二、自定义函数

2.1 什么是MySQL中的自定义函数：

        在MySQL中，自定义函数是用户根据自己的需求和逻辑创建的函数。这些函数可以根据特定的输入参数执行一系列操作，并返回一个结果。与内置函数不同，自定义函数是根据用户自己的定义和实现来完成特定任务的。可以通过使用SQL语句和流程控制语句（如条件判断和循环）来实现更复杂的逻辑。可以在函数中执行各种操作，如计算、字符串处理、日期转换、条件判断等。

        创建自定义函数通常需要使用 CREATE FUNCTION 语句，并在其中编写函数的逻辑。您可以使用 BEGIN 和 END 来定义函数的开始和结束，并在其间编写具体的代码。您还可以使用变量来存储中间结果，并使用 RETURN 语句来返回最终的结果。

        自定义函数可以返回一个值，也可以返回一个表或结果集。根据函数的定义，可以根据需要指定函数的参数和返回值的数据类型。自定义函数可以通过函数名称和参数来调用，并获得函数返回的结果。

        注意：自定义函数在MySQL中有一些限制，例如不支持长时间运行的函数或包含非确定性操作的函数。此外，自定义函数的性能可能会受到影响，因此需要根据具体情况进行评估和优化。

        通过创建自定义函数，您可以将常用的操作封装到一个函数中，提高代码的复用性和可维护性。自定义函数还允许您在查询中使用自定义的逻辑，使查询更加灵活和方便。

2.2 自定义函数的基本使用：

（1）函数的创建需要开启日志：

set global log_bin_trust_function_creators=TRUE;

（2）创建自定义函数：

create function function_name(参数列表) returns 返回值类型
BEGIN
	函数体内容
END;

（3）调用自定义函数：

select function_name();

（4）案例实现：

-- 创建一个函数，可以输入日期类型，返回是否是闰年，如果是返回1，如果不是返回0
create function is_leap(join_time date) returns bit
begin
    declare year int default year(join_time); -- 转换成年
    if year % 400 = 0 then
        return 1;
    elseif year % 4 = 0 and year % 100 <> 0 then
        return 1;
    else  return 0;
    end if;
end;
-- 查看当前年是否是闰年（true是闰年，false不是闰年）
select is_leap(now());
-- 查看有没有闰年入学的学生
select * from studentinfo where is_leap(STUJOINTIME) = true;

三、触发器

3.1 什么是MySQL中的触发器：

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

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

3.2 触发器的种类：

触发器类型	NEW和OLD
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

3.3 触发器的使用：

（1）触发器的创建：

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	trigger_stmt ; -- 相关代码
END;

（2）查看触发器：

SHOW TRIGGERS;

（3）删除触发器：

-- 如果没有指定 schema_name，默认为当前数据库 。
DROP TRIGGER [schema_name.]trigger_name ; 

（4）案例实现：

-- 创建日志表
create table tb_user_log(
    id int primary key auto_increment,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int not null comment '操作的ID',
    operate_params varchar(500) comment '操作参数'
);
-- 创建触发器
create trigger tb_user_log_trigger
after insert
on tb_user for each row
begin
    -- older 原来的数据， new是新的数据
    insert into tb_user_log values (null,
    '插入操作',
    now(),
    NEW.id,
    concat('插入',NEW.name,'的信息')
    );
end;
-- 插入数据
insert into tb_user values (null,'xi',20);

         comment：表示的注释，可以不用写。

三、事务

3.1 什么是MySQL中的事务

        在MySQL中，事务（Transaction）是指一系列数据库操作（例如插入、更新、删除等）的逻辑单元，它要么全部成功执行，要么全部回滚到事务开始之前的状态。事务的目的是确保数据库操作的一致性、隔离性、持久性和原子性。

3.2 事务具有以下四个特性（常被称为 ACID 特性）：

        （1）原子性（Atomicity）：事务中的所有操作要么全部成功执行，要么全部回滚，不存在部分执行的情况。如果事务失败或中断，所有操作将被撤销，数据库将回滚到事务开始之前状态。

        （2）一致性（Consistency）：事务执行的结果必须使数据库从一个一致的状态转换到另一个一致的状态。这意味着事务的执行不会破坏数据库的完整性约束和业务规则。

        （3）隔离性（Isolation）：并发执行的事务之间是相互隔离的，每个事务在逻辑上都感觉不到其他事务的存在。每个事务的操作不会被其他事务干扰或影响。

        （4）持久性（Durability）：一旦事务提交成功，其所做的修改将永久保存在数据库中，即使在数据库发生故障或重新启动后，修改的数据仍然存在。

3.3 控制事务的边界：

        ① BEGIN 或 START TRANSACTION：标记事务的开始。

        ② COMMIT：提交事务，将所有操作永久保存到数据库中。

        ③ ROLLBACK：回滚事务，撤销所有操作，恢复到事务开始之前的状态。

        默认情况下，MySQL以自动提交模式运行，即每个SQL语句都被视为一个单独的事务，并在执行后自动提交。要使用显式事务，您可以在事务的开始处使用 BEGIN 或 START TRANSACTION，在事务的结束处使用 COMMIT 提交事务，或使用 ROLLBACK 回滚事务。

        事务的使用可以确保数据库操作的一致性和可靠性，尤其在多用户并发访问数据库的情况下，可以防止数据的不一致和冲突。