mysql存储过程
1、sql
DROP TABLE IF EXISTS `demo`; CREATE TABLE `demo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 39 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of demo -- ---------------------------- INSERT INTO `demo` VALUES (1, '测试'); INSERT INTO `demo` VALUES (2, '测试2'); INSERT INTO `demo` VALUES (3, '测试3'); INSERT INTO `demo` VALUES (32, '测试2'); INSERT INTO `demo` VALUES (33, '测试2'); INSERT INTO `demo` VALUES (34, NULL); INSERT INTO `demo` VALUES (35, '测试3'); INSERT INTO `demo` VALUES (36, '测试3'); INSERT INTO `demo` VALUES (37, '测试3'); INSERT INTO `demo` VALUES (38, 'xxx'); SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名称', `birthday` date NULL DEFAULT NULL COMMENT '生日', `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别', `address` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_user -- ---------------------------- INSERT INTO `t_user` VALUES (1, '王五', NULL, '2', NULL); INSERT INTO `t_user` VALUES (2, '张三', '2014-07-10', '1', '北京市'); INSERT INTO `t_user` VALUES (3, '张小明', NULL, '1', '河南郑州'); INSERT INTO `t_user` VALUES (4, '陈小明', NULL, '1', '河南郑州'); INSERT INTO `t_user` VALUES (5, '张三丰', NULL, '1', '河南郑州'); INSERT INTO `t_user` VALUES (6, '陈小明', NULL, '1', '河南郑州'); INSERT INTO `t_user` VALUES (7, '王五', NULL, NULL, NULL); INSERT INTO `t_user` VALUES (8, '小A', '2015-06-27', '2', '北京'); INSERT INTO `t_user` VALUES (9, '小B', '2015-06-27', '2', '北京'); INSERT INTO `t_user` VALUES (10, '小C', '2015-06-27', '1', '北京'); INSERT INTO `t_user` VALUES (11, '小D', '2015-06-27', '2', '北京'); SET FOREIGN_KEY_CHECKS = 1;
2、无参数存储过程
DROP PROCEDURE IF EXISTS PRO; DELIMITER $$ CREATE PROCEDURE PRO() BEGIN SELECT * FROM DEMO; END$$ DELIMITER; 调用存储过程 CALL PRO()
3、输入参数的存储过程
DROP PROCEDURE IF EXISTS PRO; DELIMITER $$ CREATE PROCEDURE PRO(IN x INT) BEGIN SELECT * FROM DEMO WHERE ID = x; END$$ DELIMITER; 调用存储过程 CALL PRO(2)
4、输入输出参数的存储过程
DROP PROCEDURE IF EXISTS PRO; DELIMITER $$ CREATE PROCEDURE PRO(IN x INT, OUT res VARCHAR(64)) BEGIN SELECT NAME INTO res FROM DEMO WHERE ID = x; END$$ DELIMITER; 调用存储过程 CALL PRO(2, @res); SELECT @res;
5、if-then-else 语句
DROP PROCEDURE IF EXISTS PRO; DELIMITER $$ CREATE PROCEDURE PRO(IN x INT, OUT res VARCHAR(64)) BEGIN IF x = 1 THEN UPDATE DEMO SET NAME = 'AAA' WHERE ID = x; ELSEIF x = 2 THEN UPDATE DEMO SET NAME = 'BBB' WHERE ID = x; ELSE UPDATE DEMO SET NAME = 'CCC' WHERE ID = x; END IF; SELECT NAME INTO res FROM DEMO WHERE ID = x; END$$ DELIMITER; 调用存储过程 CALL PRO(2, @res); SELECT @res;
6、case语句:
DROP PROCEDURE IF EXISTS PRO; DELIMITER $$ CREATE PROCEDURE PRO(IN x INT, OUT res VARCHAR(64)) BEGIN CASE x WHEN 1 THEN UPDATE DEMO SET NAME = 'AAA' WHERE ID = x; WHEN 2 THEN UPDATE DEMO SET NAME = 'BBB' WHERE ID = x; ELSE UPDATE DEMO SET NAME = 'CCC' WHERE ID = x; END CASE; SELECT NAME INTO res FROM DEMO WHERE ID = x; END$$ DELIMITER; 调用存储过程 CALL PRO(2, @res); SELECT @res;
7、while ···· end while:
DROP PROCEDURE IF EXISTS PRO; DELIMITER $$ CREATE PROCEDURE PRO(IN x INT, OUT res INT) BEGIN DECLARE var INT; SET var = 0; WHILE var < x DO INSERT INTO DEMO (NAME) VALUES ('NAME' + var); SET var = var + 1; END WHILE; SELECT COUNT(1) INTO res FROM DEMO; END$$ DELIMITER; 调用存储过程 CALL PRO(3, @res); SELECT @res;
8、repeat···· end repea：
它在执行操作后检查结果，而 while 则是执行前进行检查。
DROP PROCEDURE IF EXISTS PRO; DELIMITER $$ CREATE PROCEDURE PRO(IN x INT, OUT res INT) BEGIN DECLARE var INT; SET var = 0; REPEAT INSERT INTO DEMO (NAME) VALUES ('NAME' + var); SET var = var + 1; UNTIL var >= x END REPEAT; SELECT COUNT(1) INTO res FROM DEMO; END$$ DELIMITER; 调用存储过程 CALL PRO(3, @res); SELECT @res;
9、loop ·····end loop:
loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。
DROP PROCEDURE IF EXISTS PRO; DELIMITER $$ CREATE PROCEDURE PRO(IN x INT, OUT res INT) BEGIN DECLARE var INT; SET var = 0; LOOP_LABLE: LOOP INSERT INTO DEMO (NAME) VALUES ('NAME' + var); SET var = var + 1; IF var >=x THEN LEAVE LOOP_LABLE; END IF; END LOOP; SELECT COUNT(1) INTO res FROM DEMO; END$$ DELIMITER; 调用存储过程 CALL PRO(3, @res); SELECT @res;
10、ITERATE迭代：
LOOP 及 LEAVE、ITERATE
这里LOOP用来标记循环；
而LEAVE表示离开循环，好比编程里面的break一样；
ITERATE则继续循环，好比编程里面的continue一样。
DROP PROCEDURE IF EXISTS PRO; DELIMITER $$ CREATE PROCEDURE PRO(IN x INT, OUT res INT) BEGIN DECLARE var INT; SET var = 0; LOOP_LABLE: LOOP IF var = 2 THEN SET var = var + 1; ITERATE LOOP_LABLE; END IF; INSERT INTO DEMO (NAME) VALUES ('NAME' + var); SET var = var + 1; IF var >= x THEN LEAVE LOOP_LABLE; END IF; END LOOP; SELECT COUNT(1) INTO res FROM DEMO; END$$ DELIMITER; 调用存储过程 CALL PRO(4, @res); SELECT @res;
11、游标
MySQL中的游标可以理解成一个可迭代对象(类比Python中的列表、字典等可迭代对象)，它可以用来存储select 语句查询到的结果集，这个结果集可以包含多行数据，从而使我们可以使用迭代的方法从游标中依次取出每行数据。
MySQL游标的特点：
1.只读：无法通过光标更新基础表中的数据。
2.不可滚动：只能按照select语句确定的顺序获取行。不能以相反的顺序获取行。 此外，不能跳过行或跳转到结果集中的特定行。
3.敏感：有两种游标：敏感游标和不敏感游标。敏感游标指向实际数据，不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快，因为它不需要临时拷贝数据。MySQL游标是敏感的。
声明游标
游标声明必须在变量声明之后。如果在变量声明之前声明游标，MySQL将会发出一个错误。游标必须始终与select语句相关联。
declare cursor_name cursor for select_statement;
打开游标
使用open语句打开游标，只有先打开游标才能读取数据。
open cursor_name;
读取游标
使用fetch语句来检索游标指向的一行数据，并将游标移动到结果集中的下一行。
fetch cursor_name into var_name;
关闭游标
使用close语句关闭游标。
close cursor_name;
当游标不再使用时，应该关闭它。 当使用MySQL游标时，还必须声明一个notfound处理程序来处理当游标找不到任何行时的情况。 因为每次调用fetch语句时，游标会尝试依次读取结果集中的每一行数据。 当游标到达结果集的末尾时，它将无法获得数据，并且会产生一个条件。 处理程序用于处理这种情况。
declare continue handler for not found set type = 1;
type是一个变量，示游标到达结果集的结尾。
DROP PROCEDURE IF EXISTS PRO; DELIMITER $$ CREATE PROCEDURE PRO() BEGIN DECLARE id_tmp INT; DECLARE name_tmp VARCHAR(64); -- 遍历数据结束标志 DECLARE done INT DEFAULT FALSE; -- 游标 DECLARE cur_account CURSOR FOR select id,username from t_user; -- 将结束标志绑定到游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 打开游标 OPEN cur_account; -- 遍历 read_loop: LOOP FETCH NEXT from cur_account INTO id_tmp,name_tmp; IF done THEN LEAVE read_loop; END IF; INSERT INTO DEMO(ID,NAME) VALUES(id_tmp, name_tmp); END LOOP; CLOSE cur_account; END$$ DELIMITER; 调用存储过程 CALL PRO();
