Mysql-存储过程

perfree

2019-02-17 / 5 评论 / 3817 阅读 / 0 点赞

02/17

存储过程创建语法

CREATE PROCEDURE 存储过程名(实参列表)
BEGIN
		存储过程体(一组有效的sql语句)
END

实参列表：

参数列表包含三部分
参数模式参数名参数类型
IN NAME VARCHAR(20)

参数模式：

	IN：该参数作为输入  也就是该参数需要调用方传入值
	OUT：该参数作为输出  也就是该参数作为返回值
	INOUT：该参数既可以作为输出，也可以作为输入 也就是该参数既需要传入值，也可以作为返回值

注意事项

如果存储过程只有一句话的时候，BEGIN END可以省略
存储过程中每条sql语句结尾都需要加";"
存储过程的结尾可以使用delimiter设置

调用语法

CALL 存储过程名(实参列表)

接下来练习下存储过程：

数据库准备

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '张三', '20', '男');
INSERT INTO `user` VALUES ('2', '李四', '21', '男');
INSERT INTO `user` VALUES ('3', '张琦', '20', '女');

插入五条数据

先查看一下数据库的数据：

接着我们创建一个插入五条数据的存储过程

CREATE PROCEDURE demo01()
BEGIN
	INSERT INTO `user` (name,age,sex)
VALUES
	('老大',25,'男'),
	('老二',23,'男'),
	('老三',22,'女'),
	('老四',21,'男'),
	('老五',20,'女');
END

运行：

调用存储过程：

CALL demo01();

运行：

接着查询下数据：

条件语句

if 条件 then
 statement
 else
 statement
 end if;

示例,根据传入的参数修改id为1的用户修改年龄，如果传入1，年龄改为18，如果传入其他，改为17：

CREATE PROCEDURE demo02(in a INTEGER)
BEGIN
DECLARE num INT;
IF a =1 THEN SET num=18;
ELSE SET num = 17;
END IF;
UPDATE `user` SET age=num WHERE id=1;
END;

注DECLARE为定义一个变量，上边定义了一个int类型的变量num
运行：

调用存储过程并传入1：

call demo02(1);

运行：

查询下数据看是否改变：

ok，接着我们测试传入其他值：

call demo02(3);

运行：

查看数据：

循环语句

while循环

WHILE expression DO
 statements
 END WHILE;

示例,写一个存储过程，传入一个数值如2，返回0+1+2的结果：

CREATE PROCEDURE demo04(in a INT,OUT b INT)
BEGIN
DECLARE sum INTEGER DEFAULT 0;
DECLARE c INTEGER DEFAULT 0;-- DEFAULT指该变量的默认值
WHILE c<=a DO
SET sum = sum+c;
SET c = c+1;
END WHILE;
SET b = sum;
END

运行：

调用存储过程，调用out参数的存储过程如下：

call demo04(2,@b);

运行：

查询：

select @b;

结果：

LOOP循环

loop 循环语法：
loop_name:loop
         if 条件 THEN -- 满足条件时离开循环
                leave loop_name;  -- 和 break 差不多都是结束训话
        end if；
 end loop;

示例，还去实现上边while的例子：

CREATE PROCEDURE demo05(in a INT)
BEGIN
DECLARE c INTEGER DEFAULT 0;
DECLARE b INTEGER DEFAULT 0;-- DEFAULT指该变量的默认值
myLoop:LOOP
IF b> a THEN
LEAVE myLoop;
END IF;
SET c = c+b;
SET b =b+1;
END LOOP;
SELECT c;
END

运行：

调用存储过程：

call demo05(2);

运行：

repeat 循环

repeat 循环语法
 repeat
     循环体
 until 条件 end repeat;

同样，还是实现上边的例子：

CREATE PROCEDURE demo06(a INT)
BEGIN
DECLARE b INT DEFAULT 0;
DECLARE c INT DEFAULT 0;
REPEAT
SET c = c+b;
SET b = b+1;
UNTIL b>a END REPEAT;
SELECT c;
END

运行：