CREATE PROCEDURE pro_test2() BEGIN DECLARE NAME VARCHAR(10); -- 定义变量 SET NAME = '存储过程'; -- 为变量赋值 SELECT NAME; -- 查询变量 END$
DELIMITER ;
-- 调用pro_test2存储过程 CALL pro_test2();
变量的赋值2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
-- 标准语法 SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
-- 定义两个int变量，用于存储男女同学的总分数 DELIMITER $
CREATE PROCEDURE pro_test3() BEGIN DECLARE men,women INT; -- 定义变量 SELECT SUM(score) INTO men FROM student WHERE gender='男'; -- 计算男同学总分数赋值给men SELECT SUM(score) INTO women FROM student WHERE gender='女'; -- 计算女同学总分数赋值给women SELECT men,women; -- 查询变量 END$
DELIMITER ;
-- 调用pro_test3存储过程 CALL pro_test3();
8.3if语句的使用
标准语法
1 2 3 4 5 6
-- 标准语法 IF 判断条件1 THEN 执行的sql语句1; [ELSEIF 判断条件2 THEN 执行的sql语句2;] ... [ELSE 执行的sql语句n;] END IF;
CREATE PROCEDURE pro_test4() BEGIN -- 定义总分数变量 DECLARE total INT; -- 定义分数描述变量 DECLARE description VARCHAR(10); -- 为总分数变量赋值 SELECT SUM(score) INTO total FROM student; -- 判断总分数 IF total >= 380 THEN SET description = '学习优秀'; ELSEIF total >= 320 AND total < 380 THEN SET description = '学习不错'; ELSE SET description = '学习一般'; END IF; -- 查询总成绩和描述信息 SELECT total,description; END$
CREATE PROCEDURE pro_test5(IN total INT) BEGIN -- 定义分数描述变量 DECLARE description VARCHAR(10); -- 判断总分数 IF total >= 380 THEN SET description = '学习优秀'; ELSEIF total >= 320 AND total < 380 THEN SET description = '学习不错'; ELSE SET description = '学习一般'; END IF; -- 查询总成绩和描述信息 SELECT total,description; END$
DELIMITER ;
-- 调用pro_test5存储过程 CALL pro_test5(390); CALL pro_test5((SELECT SUM(score) FROM student));
输出参数
标准语法
1 2 3 4 5 6 7 8 9
DELIMITER $
-- 标准语法 CREATE PROCEDURE 存储过程名称(OUT 参数名 数据类型) BEGIN 执行的sql语句; END$
CREATE PROCEDURE pro_test6(IN total INT,OUT description VARCHAR(10)) BEGIN -- 判断总分数 IF total >= 380 THEN SET description = '学习优秀'; ELSEIF total >= 320 AND total < 380 THEN SET description = '学习不错'; ELSE SET description = '学习一般'; END IF; END$
CREATE PROCEDURE pro_test7(IN total INT) BEGIN -- 定义变量 DECLARE description VARCHAR(10); -- 使用case判断 CASE WHEN total >= 380 THEN SET description = '学习优秀'; WHEN total >= 320 AND total < 380 THEN SET description = '学习不错'; ELSE SET description = '学习一般'; END CASE; -- 查询分数描述信息 SELECT description; END$
DELIMITER ;
-- 调用pro_test7存储过程 CALL pro_test7(390); CALL pro_test7((SELECT SUM(score) FROM student));
8.6while循环
标准语法
1 2 3 4 5 6
-- 标准语法 初始化语句; WHILE 条件判断语句 DO 循环体语句; 条件控制语句; END WHILE;
CREATE PROCEDURE pro_test8() BEGIN -- 定义求和变量 DECLARE result INT DEFAULT 0; -- 定义初始化变量 DECLARE num INT DEFAULT 1; -- while循环 WHILE num <= 100 DO -- 偶数判断 IF num%2=0 THEN SET result = result + num; -- 累加 END IF; -- 让num+1 SET num = num + 1; END WHILE; -- 查询求和结果 SELECT result; END$
DELIMITER ;
-- 调用pro_test8存储过程 CALL pro_test8();
8.7repeat循环
标准语法
1 2 3 4 5 6 7 8 9
-- 标准语法 初始化语句; REPEAT 循环体语句; 条件控制语句; UNTIL 条件判断语句 END REPEAT;
CREATE PROCEDURE pro_test9() BEGIN -- 定义求和变量 DECLARE result INT DEFAULT 0; -- 定义初始化变量 DECLARE num INT DEFAULT 1; -- repeat循环 REPEAT -- 累加 SET result = result + num; -- 让num+1 SET num = num + 1; -- 停止循环 UNTIL num>10 END REPEAT; -- 查询求和结果 SELECT result; END$
CREATE PROCEDURE pro_test10() BEGIN -- 定义求和变量 DECLARE result INT DEFAULT 0; -- 定义初始化变量 DECLARE num INT DEFAULT 1; -- loop循环 l:LOOP -- 条件成立，停止循环 IF num > 10 THEN LEAVE l; END IF; -- 累加 SET result = result + num; -- 让num+1 SET num = num + 1; END LOOP l; -- 查询求和结果 SELECT result; END$
CREATE PROCEDURE pro_test11() BEGIN -- 定义成绩变量 DECLARE s_score INT; -- 创建游标,查询所有学生成绩数据 DECLARE stu_result CURSOR FOR SELECT score FROM student; -- 开启游标 OPEN stu_result; -- 使用游标，遍历结果,拿到第1行数据 FETCH stu_result INTO s_score; -- 将数据保存到stu_score表中 INSERT INTO stu_score VALUES (NULL,s_score); -- 使用游标，遍历结果,拿到第2行数据 FETCH stu_result INTO s_score; -- 将数据保存到stu_score表中 INSERT INTO stu_score VALUES (NULL,s_score); -- 使用游标，遍历结果,拿到第3行数据 FETCH stu_result INTO s_score; -- 将数据保存到stu_score表中 INSERT INTO stu_score VALUES (NULL,s_score); -- 使用游标，遍历结果,拿到第4行数据 FETCH stu_result INTO s_score; -- 将数据保存到stu_score表中 INSERT INTO stu_score VALUES (NULL,s_score); -- 使用游标，遍历结果,拿到第5行数据 FETCH stu_result INTO s_score; -- 将数据保存到stu_score表中 INSERT INTO stu_score VALUES (NULL,s_score); -- 关闭游标 CLOSE stu_result; END$
DELIMITER ;
-- 调用pro_test11存储过程 CALL pro_test11();
-- 查询stu_score表,虽然数据正确，但是在执行存储过程时会报错 SELECT * FROM stu_score;
游标的优化使用(配合循环使用)
1 2 3 4 5 6 7 8 9 10
/* 当游标结束后，会触发游标结束事件。我们可以通过这一特性来完成循环操作 加标记思想： 1.定义一个变量，默认值为0(意味着有数据) 2.当游标结束后，将变量值改为1(意味着没有数据了) */ -- 1.定义一个变量，默认值为0(意味着有数据) DECLARE flag INT DEFAULT 0; -- 2.当游标结束后，将变量值改为1(意味着没有数据了) DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
CREATE PROCEDURE pro_test12() BEGIN -- 定义成绩变量 DECLARE s_score INT; -- 定义标记变量 DECLARE flag INT DEFAULT 0; -- 创建游标，查询所有学生成绩数据 DECLARE stu_result CURSOR FOR SELECT score FROM student; -- 游标结束后，将标记变量改为1 DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1; -- 开启游标 OPEN stu_result; -- 循环使用游标 REPEAT -- 使用游标，遍历结果,拿到数据 FETCH stu_result INTO s_score; -- 将数据保存到stu_score表中 INSERT INTO stu_score VALUES (NULL,s_score); UNTIL flag=1 END REPEAT; -- 关闭游标 CLOSE stu_result; END$
DELIMITER ;
-- 调用pro_test12存储过程 CALL pro_test12();
-- 查询stu_score表 SELECT * FROM stu_score;
9.存储过程的总结
存储过程是 事先经过编译并存储在数据库中的一段 SQL 语句的集合。可以在数据库层面做一些业务处理
说白了存储过程其实就是将sql语句封装为方法，然后可以调用方法执行sql语句而已
存储过程的好处
安全
高效
复用性强
10.存储函数
存储函数和存储过程是非常相似的。存储函数可以做的事情，存储过程也可以做到！
存储函数有返回值，存储过程没有返回值(参数的out其实也相当于是返回数据了)
标准语法
创建存储函数
1 2 3 4 5 6 7 8 9 10 11
DELIMITER $
-- 标准语法 CREATE FUNCTION 函数名称([参数 数据类型]) RETURNS 返回值类型 BEGIN 执行的sql语句; RETURN 结果; END$
CREATE FUNCTION fun_test1() RETURNS INT BEGIN -- 定义统计变量 DECLARE result INT; -- 查询成绩大于95分的学生数量，给统计变量赋值 SELECT COUNT(*) INTO result FROM student WHERE score > 95; -- 返回统计结果 RETURN result; END$
CREATE TRIGGER account_insert AFTER INSERT ON account FOR EACH ROW BEGIN INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}')); END$
DELIMITER ;
-- 向account表添加记录 INSERT INTO account VALUES (NULL,'王五',3000);
CREATE TRIGGER account_update AFTER UPDATE ON account FOR EACH ROW BEGIN INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('修改前{id=',old.id,',name=',old.name,',money=',old.money,'}','修改后{id=',new.id,',name=',new.name,',money=',new.money,'}')); END$
DELIMITER ;
-- 修改account表 UPDATE account SET money=3500 WHERE id=3;
CREATE TRIGGER account_delete AFTER DELETE ON account FOR EACH ROW BEGIN INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}')); END$
-- 创建账户表 CREATE TABLE account( id INT PRIMARY KEY AUTO_INCREMENT, -- 账户id NAME VARCHAR(20), -- 账户名称 money DOUBLE -- 账户余额 ); -- 添加数据 INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);
3.未管理事务演示
1 2 3 4 5 6 7 8
-- 张三给李四转账500元 -- 1.张三账户-500 UPDATE account SET money=money-500 WHERE NAME='张三'; -- 2.李四账户+500 出错了... UPDATE account SET money=money+500 WHERE NAME='李四';
-- 该场景下，这两条sql语句要么同时成功，要么同时失败。就需要被事务所管理！
4.管理事务演示
操作事务的三个步骤
开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败
执行sql语句：执行具体的一条或多条sql语句
结束事务(提交|回滚)
提交：没出现问题，数据进行更新
回滚：出现问题，数据恢复到开启事务时的状态
开启事务
1 2
-- 标准语法 START TRANSACTION;
回滚事务
1 2
-- 标准语法 ROLLBACK;
提交事务
1 2
-- 标准语法 COMMIT;
管理事务演示
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
-- 开启事务 START TRANSACTION;
-- 张三给李四转账500元 -- 1.张三账户-500 UPDATE account SET money=money-500 WHERE NAME='张三'; -- 2.李四账户+500 -- 出错了... UPDATE account SET money=money+500 WHERE NAME='李四';
记录一些后端知识
分享一些笔记心得
吃喝吃喝吃喝吃喝
