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();