CREATE PROCEDURE 存储过程名(实参列表)
BEGIN
存储过程体(一组有效的sql语句)
END
参数列表包含三部分
参数模式 参数名 参数类型
IN NAME VARCHAR(20)
IN:该参数作为输入 也就是该参数需要调用方传入值
OUT:该参数作为输出 也就是该参数作为返回值
INOUT:该参数既可以作为输出,也可以作为输入 也就是该参数既需要传入值,也可以作为返回值
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 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_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
循环体
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;