【MySQL进阶】存储过程和触发器

一.存储过程1.存储过程是什么存储过程是为了完成特定功能的SQL语句集编写完成后存储在数据库当中用户根据存储过程的名字和参数来执行并获得相对应的结果简单来说其实就是c和java中的方法2.特点1.封装性将业务逻辑封装在了数据库内部降低了应用程序的复杂性2.可维护性集中管理数据库操作便于维护和更新3.可复用性可以被多次调用提高代码的复用性3.优缺点优点1.性能优化存储过程执行的速度比单个SQL语句快这是因为在存储过程第一次创建时MySQL就会先预编译并存储在数据库中而单条SQL语句在执行过程中还要进行解析2.安全性用户只能够间接访问到数据库3.代码复用可以被重复调用减少了重复代码提高了可维护性4.事务管理在存储过程中可以实现复杂的事务逻辑5.降低耦合底层表结构发生变化时只需要去修改存储过程应用程序改动小缺点1.可移植性差存储过程不能跨数据库移植更换数据库时需要重新编写2.调试困难支持存储过程调试的数据库比较少开发和维护困难3.不适合高并发场景在高并发场景中存储过程可能会增加数据库压力数据库平时进行增删改的业务量就已经很高了如果在高并发场景还要去执行存储过程数据库压力会非常大所以在平常会将需要存储过程处理的业务程序给移动到应用程序当中4.语法1 创建delimiter //将结束符改为 //create procedure存储过程名[参数列表]beginSQL语句;end//delimiter ; 将结束符改为 ;2 调用call存储过程名(参数);3 查看---- 查看存储过程名select*fromimformation_schema.routineswhereroutines_schema 数据库名;---- 查看创建存储过程的SQL语句show create procedure存储过程名;4 删除drop procedure[ifexists] 存储过程名;在创建过程中为什么要将结束符进行修改呢这是因为在命令行中执行创建存储过程时是以 ; 结束的所以在创建的过程中就会结束造成语法错误虽然在可视化工具中会自动对这种情况进行优化就算不修改结束符也可以正常编译但是在编写完脚本之后这些脚本上传到服务器是统一在命令行环境中执行的所以还是推荐在创建存储过程中都进行修改。5.使用二.变量1.系统变量系统变量是MySQL服务器的配置变量控制服务器的性能和行为分为全局变量(GLOBAL)和会话变量(SESSION)例子在微信中我的昵称叫张三在微信群中我可以将我的群昵称改为李四那么张三就是一个全局变量李四就是一个会话变量。全局变量就是在启动服务器时会读取的值会话变量可以基于全局变量去针对每个会话进行修改。比如在开启会话时autocommit默认是ON我们就可以针对会话将其修改成OFF查看系统变量--查看所有的系统变量show[global| session] variables;-- 查看指定的系统变量show[global| session] variables xxx;-- 查看指定的系统变量可以用%进行模糊查询show[global| session] variables %xxx%;-- 用select查看指定的系统变量select[global| session].xxx;设置系统变量set[global| session] 系统变量名 值;set[global| session].系统变量 值;注意1.如果没有指定global或者session默认为session2.会话关闭时设置的会话变量会失效新建会话时读取全局变量为初始值3.MySQL重庆时设置的全局变量会失效要永久生效需要修改配置文件2.用户自定义变量用户自定义变量是在SQL会话定义的变量不需要提前声明作用域为当前会话赋值-- 方式一SETvar_name expr [,var_name]...;-- 方式二【推荐】SETvar_name : expr [,var_name]...;-- 方式三 在SELECT语句中SELECTvar_name : expr [,var_name]...;-- 方式四 查询结果赋值给⾃定义变量SELECT列名 INTOvar_name FROM 表名 WHERE ...;推荐使用方法二和方法四因为在MySQL中“ ”有赋值和判等的作用所以用“ : ”来明确赋值操作3.局部变量局部变量是定义在存储过程函数和触发器中的需要提前声明作用域在声明的BEGIN.....END块中语法声明declarevar_name 类型 [default 默认值];赋值-- 方式一set变量名 : 值;-- 方式二select列名intovar_namefrom表名where...... ;4.注意事项1.变量名不区分大小写2.在存储过程和函数中局部变量必须在使用前声明在触发器中自定义的局部变量也是要提前声明的只不过在触发器有专属的new和old变量这些时MySQL的内置变量不需要我们声明3.用户自定义变量在会话结束后生效局部变量在存储过程和函数结束时失效4.避免使用保留字作为变量名比如select这种已经定义好的有特殊意义的字段就叫作保留字三.SQL编程1.条件判断 —— if语句语法if 条件1then.......[elseif 条件2then......else......]endif;示例2.参数分类1.IN 输入类型调用存储过程时要传入的值值类型2. OUT 输出类型作为存储过程中的返回值3.INOUT 输入输出类型既可以作为传入的值也可以作为返回值引用类型注值类型在方法中对变量进行的修改只在方法中生效在外部不生效引用类似在方法中对变量进行的修改外部可以访问到修改后的值语法DELIMITER //-- 创建存储过程CREATEPROCEDURE存储过程名 ([IN/OUT/INOUT 参数名 参数类型] [,...])BEGIN-- SQL 语句END //DELIMITER ;示例3.case语句语法语法一----- 只有当case_value 等于对应的when_value时才会执行对应的statement_listcasecase_valuewhenwhen_valuethenstatement_list;[whenwhen_valuethenstatement_list;].......[elsestatement_list;]endcase;语法二-- 只有当search_condition满足时才会执行对应的statement_listcasewhensearch_conditionthenstatement_list;[whensearch_conditionthenstatement_list;].......[elsestatement_list;]endcase;示例传入⼀个状态码输出该状态码表示的含义根据传入的月份输出该月份属于哪个季度注意事项1.多分支或者多判断语句可以用case语句代替if语句case为if的简化版本2.SQL中的CASE不具备向下穿透的特性在Java中的CASE语句中如果不写break会向下穿透3.statement_list由一条或者多条SQL语句构成且不能为空不过可以用begin.....end块来进行占位4.若case_value和when_value都不匹配或者search_condition都不满足并且不包含ELSE子句就会报错4.循环1 where语法WHILEsearch_conditionDOstatement_listENDWHILE;示例传入一数n计算从1累加到n的值2 repeat类似do....whiel循环语法REPEATstatement_listUNTILsearch_conditionENDREPEAT;进入循环会先执行一次stateme_list直到满足search_condition后就不再执行退出循环示例3 looploop也可以实现简单的循环但是不像前面两个循环可以达到某个条件自动退出循环。使用loop时当达到某个条件想要终止当前循环或者是退出整个循环要配合LEVELlabel退出整个循环类似JAVA中的breakITERATElabel终止当前循环类似JAVA中的continue语法[begin_label]LOOPstatement_listENDLOOP[end_label];begin_label和end_label相当于给这个LOOP循环起个名字不是必须写但是如果都写的话名字要一样示例5.游标是一个数据库对象允许在存储过程和函数中对查询到的结果进行检索如下图对已经查询出来的结果集游标就可以向下依次遍历每条数据刚开始游标不会指向任何一条记录注意MySQL的游标是只读的不能进行更新操作语法1.要在使用前声明openfetchclose分别是打开游标获取游标记录和关闭游标2.游标必须声明在条件处理程序之前并且局部变量必须声明在条件处理程序和游标之前-- 声明declare游标名cursor for查询语句; 此处只是定义游标-- 打开游标open游标名; 真正的执行查询语句并把查询语句和游标进行关联-- 获取游标记录fetch游标名 into 变量[,变量......];此处变量是提前定义好的用于保存记录中的值-- 关闭游标close游标名;示例-- 传入班级编号查询学生表中属于该班级的学生信息并将符合条件的学生信息写如到新表中delimiter //DROP PROCEDURE IF EXISTSp10;CREATE PROCEDUREp10(INclass_id INT)BEGIN-- 1.首先声明变量-- 学生变量DECLAREstudent_nameVARCHAR(20);DECLAREclass_nameVARCHAR(20);-- 声明游标DECLAREs_cursorCURSOR FORSELECTs.name , c.name FROM student s , class cWHEREs.class_id c.idANDc.id class_id;-- 2.创建新表CREATE TABLEt_student_class(idBIGINT PRIMARY KEYAUTO_INCREMENT,student_nameVARCHAR(20),class_nameVARCHAR(20));-- 3.开启游标OPENs_cursor;-- 4.遍历结果集WHILE TRUE DO(这边还不知道以什么条件结束暂时先这样写到后面条件处理程序会解决)-- 5.获取游标记录FETCHs_cursorINTOstudent_name,class_name;-- 6.将获取到的数据插入到新表INSERT INTOt_student_classVALUES(NULL,student_name,class_name);END WHILE;-- 7.关闭游标CLOSEs_cursor;END//delimiter;CALLp10(1);可以看到因为while被我们写成了一个死循环所以游标会一直向下遍历直到没有记录所以会报错在接下来的条件处理程序能够解决6.条件处理程序定义条件事先定义程序在执行过程中可能会遇到的问题处理程序定义了在遇到问题时应该采取什么处理方式其实就跟JAVA中的异常类似遇到异常进行捕获和处理语法-- 声明条件处理程序declare handler_actionHANDLERFORcondition_value[,condition_value].....statement;handler_action:{continue-- 继续执行当前程序exit-- 终止执行当前程序}condition_value:{mysql_error_code-- MYSQL错误码|SQLSTATE[VALUE] sqlstate_value-- 状态码|SQLWARNING-- 所有以01开头的SQLSTATE代码|NOTFOUND-- 所有以02开头的SQLSTATE代码|SQLEXCEPTION-- 所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码}condition_value就类似JAVA中要捕获哪个类型的异常示例现在已经了解了条件处理程序了那就可以来解决刚才使用游标时遇到的问题了-- 传入班级编号查询学生表中属于该班级的学生信息并将符合条件的学生信息写如到新表中delimiter //DROP PROCEDURE IF EXISTSp10;CREATE PROCEDUREp10(INclass_id INT)BEGIN-- 1.首先声明变量-- 学生变量DECLAREstudent_nameVARCHAR(20);DECLAREclass_nameVARCHAR(20);-- 定义游标结束标识符DECLAREis_done boolDEFAULT TRUE;-- 声明游标DECLAREs_cursorCURSOR FORSELECTs.name , c.name FROM student s , class cWHEREs.class_id c.idANDc.id class_id;-- 声明条件处理程序DECLARE CONTINUE HANDLER FOR NOTfound set is_done :FALSE;-- 2.创建新表CREATE TABLEt_student_class(idBIGINT PRIMARY KEYAUTO_INCREMENT,student_nameVARCHAR(20),class_nameVARCHAR(20));-- 3.开启游标OPENs_cursor;-- 4.遍历结果集WHILEis_doneDO-- 5.获取游标记录FETCHs_cursorINTOstudent_name,class_name;-- 6.将获取到的数据插入到新表INSERT INTOt_student_classVALUES(NULL,student_name,class_name);END WHILE;SELECT*FROMt_student_class;-- 7.关闭游标CLOSEs_cursor;END//delimiter;CALLp10(1);那我们发现这样修改完之后虽然不报错但是新表中却有重复的数据。这是因为在游标向下遍历到空的记录行时条件处理程序就会接管将is_done设置成false然后程序需要继续向下进行那么条件处理程序就将游标重置为了它所获取到的上一条记录的位置也就是遍历到空记录行时又被重置为了沙悟净这条记录那其实在is_done变为false时就应该退出循环而不是继续进行循环这样才不会出现错误。所以使用LOOP即可-- 4.遍历结果集read_label:LOOP-- 5.获取游标记录FETCHs_cursorINTOstudent_name,class_name;IF NOTis_doneTHENLEAVEread_label;END IF;-- 6.将获取到的数据插入到新表INSERT INTOt_student_classVALUES(NULL,student_name,class_name);END LOOPread_label;SELECT * FROMt_student_class;这边还有一个可能会犯的错误如果我们把判断退出的语句放在了获取游标记录的代码之前还是会造成插入重复数据的情况。这是因为如果是先判断再获取记录的话如果游标已经到了空记录行此时条件处理程序虽然已经将is_done设置为false可是因为判断语句在前无法进行判断所以此时游标就会被重置为上一条记录继续进行插入所以应该是先获取游标记录看是否已经为空如果为空就直接退出就算条件处理程序重置了游标也无法进行插入就不会出现重复数据的情况了7.存储函数MySQL中的存储函数相当于有返回值的存储过程参数只能是IN类型所以在写参数的时候IN可以省略。存储过程和存储函数的主要区别就在于存储函数一定有返回值而存储过程不一定语法那么在MySQL8.0中binlog开启时必须指定characteristic特征不然会报错CREATE FUNCTION存储函数名称 ([参数列表])RETURNStype [characteristic ...]BEGIN-- SQL语句RETURN...;END;characteristic:[NOT]DETERMINISTIC--表示相同的输入参数总是产生[不同]相同的结果|NO SQL--不包含SQL语句|READS SQLDATA--包含读取数据的语句如select|MODIFIES SQLDATA-- 包含写入数据的语句如updatedelete-- 使用存储函数select存储函数名称 ([参数列表]);示例四.触发器1.什么叫触发器是一个与表关联的对象在进行insertupdatedelete操作时执行并触发指定触发器定义的SQL语句可在表操作之前或之后触发这被称为触发时间MySQL支持三种触发器insert触发器update触发器delete触发器。可以使用NEW和OLD关键字来引用触发器执行中发生变化的记录内容行级触发器当进行表操作时每影响了多少行数据触发器就触发多少次。比如进行了update操作影响了10行那么触发器就会对这10条数据都触发一次。行级触发器可以获得更新前和更新后的值语句级触发器当进行表操作时无论影响了多少行数据语句级触发器都只会在操作前或后触发一次。主要用于一些全局性的操作注意MySQL中只支持行级触发器不支持语句级触发器2.语法-- 创建CREATE TRIGGER[IFNOT EXISTS] trigger_nametrigger_time trigger_eventONtbl_nameFOR EACHROWBEGINtrigger_stmt;END;trigger_time: { BEFORE | AFTER }trigger_event: {INSERT|UPDATE|DELETE}-- 查看SHOWTRIGGERS;-- 删除, 如果没有指定schema_name默认为当前数据库DROP TRIGGER[IFEXISTS] [schema_name.]trigger_name;3.示例通过触发器记录学生表的变更日志将变更日志写入日志表student_log中包含增加修改和删 除操作创建日志表插入触发器当我们插入数据时插入触发器就会触发并执行定义的SQL语句更新触发器由于是进行更新操作所以可以引用更新前的记录和更新后的记录当进行多条记录的更新每影响多少行更新触发器就触发多少次删除触发器当删除记录时总结本文介绍了什么是存储结构变量有哪几种SQL编码的各个部分以及触发器。其实大部分的内容其实和JAVA的编程是类似的只是有一些细微的差别比如存储过程其实就算JAVA中的方法还有在SQL编码部分提到的条件处理程序跟JAVA中的异常思路是一样的。在变量部分关注各个变量的作用域需不需要提前声明最主要的和常用的其实就是局部变量。那触发器其实就可以理解是进行表操作的一个子程序并不影响表操作这个主要程序。