Mysql-存储过程

存储过程创建语法

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

实参列表:

参数列表包含三部分

    参数模式   参数名   参数类型
            IN       NAME                VARCHAR(20)
参数模式:
    IN:该参数作为输入  也就是该参数需要调用方传入值
    OUT:该参数作为输出  也就是该参数作为返回值
    INOUT:该参数既可以作为输出,也可以作为输入 也就是该参数既需要传入值,也可以作为返回值
注意事项
  1. 如果存储过程只有一句话的时候,BEGIN END可以省略
  2. 存储过程中每条sql语句结尾都需要加";"
  3. 存储过程的结尾可以使用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

运行:

调用存储过程:

call demo06(2);

运行:

其他常用命令

显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等

show procedure status;

显示某一个存储过程的详细信息

show create procedure sp_name;

若之前创建有这个存储过程则删除

drop procedure if exists sp_name;

删除存储过程

drop procedure sp_name;
Last modification:February 17th, 2019 at 06:12 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment