mysql 存储过程 跳转_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 xINT)

BEGIN

SELECT* FROM DEMO WHERE ID =x;

END$$

DELIMITER;

调用存储过程

CALL PRO(2)

4、输入输出参数的存储过程

DROP PROCEDURE IF EXISTS PRO;

DELIMITER $$

CREATE PROCEDURE PRO(IN xINT, 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 xINT, OUT res VARCHAR(64))

BEGIN

IF x= 1THEN

UPDATE DEMO SET NAME= 'AAA' WHERE ID =x;

ELSEIF x= 2THEN

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 xINT, OUT res VARCHAR(64))

BEGIN

CASE x

WHEN1THEN

UPDATE DEMO SET NAME= 'AAA' WHERE ID =x;

WHEN2THEN

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

DECLAREvarINT;

SETvar = 0;

WHILEvar

INSERT INTO DEMO (NAME) VALUES ('NAME' + var);

SETvar = 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

DECLAREvarINT;

SETvar = 0;

REPEAT

INSERT INTO DEMO (NAME) VALUES ('NAME' + var);

SETvar = var + 1;

UNTILvar >=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

DECLAREvarINT;

SETvar = 0;

LOOP_LABLE: LOOP

INSERT INTO DEMO (NAME) VALUES ('NAME' + var);

SETvar = var + 1;

IFvar >=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

DECLAREvarINT;

SETvar = 0;

LOOP_LABLE: LOOP

IFvar = 2THEN

SETvar = var + 1;

ITERATE LOOP_LABLE;

END IF;

INSERT INTO DEMO (NAME) VALUES ('NAME' + var);

SETvar = var + 1;

IFvar >=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 FORselect id,username fromt_user;--将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;--打开游标

OPEN cur_account;--遍历

read_loop: LOOP

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