WHCSRL 技术网

MySQL存储过程

存储过程

前言:存储过程和函数:类似于 java 中的方法

好处:1.提高代码的重用性

​ 2.简化操作

​ 3.减少了编译次数并减少了和服务器的连接次数,提高了效率

建表语句在最后

存储过程

含义:一组预先编译好的SQL语句的集合,理解成为批处理语句

一,创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
		存储过程体(一组合法的SQL语句)
END
  • 1
  • 2
  • 3
  • 4

注意,参数列表包含三部分:参数模式,参数名,参数类型

举例

#参数模式:
IN:该参数可以作为输入,也就是该参数需要调用方传入值
OUT:该参数可以作为输出,也就是该参数可以作为返回值
INOUT:该参数既可以作为输入又可以作为输出,也就是该函数既需要传入值,又可以返回值

2、如果存储过程体仅仅只有一句话,BEGIN END 可以省略
存储过程体中的每条SQL语句的结尾要求必须加分号。
存储过程的结尾可以使用 DELIMITER 重新设置
语法:
DELIMITER 结束标记
案例
DELIMITER $
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

二、调用语法

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

例子

1、空参列表

#创建
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	   INSERT INTO admin (username,password)
		 VALUES ('tom','000'),('jery','000'),('Lili','000'),('liing','000'),('rose','000');
END $

#调用
DELIMITER;
CALL myp1() 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

2、创建带in模式参数的存储过程

例子一:根据女神名,查询对应的男神信息

DELIMITER //
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
		  SELECT bo.*
			FROM boys bo
			RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
			WHERE b.name = beautyName;
END //

DELIMITER;
CALL myp2('小昭');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

例子二:创建存储过程实现,用户是否登录成功

DELIMITER //
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
		DECLARE result INT DEFAULT 0; #声明并初始化   算一条SQL语句
		
		SELECT COUNT(*) INTO result #赋值
		FROM admin
		WHERE admin.username=username
		and admin.password=password;   #算一条SQL语句
		
		SELECT if(result>0,'成功','失败'); #使用   算一条SQL语句
END //

#调用
DELIMITER;
CALL myp3('张飞','8888') 

DELIMITER;
CALL myp3('john','8888')
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

3.创建带 out 模式的存储过程

例子三:根据女神名,返回对应的男神的名字

#创建
DELIMITER // 
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
		 SELECT bo.boyName INTO boyName
		 FROM boys bo
		 INNER JOIN beauty b ON bo.id = b.boyfriend_id
		 WHERE b.name= beautyName;	
END//

#调用
SELECT @bName;  #这是一句SQL
DELIMITER;  #这是一句SQL
CALL myp4('小昭',@bName);  #这是一句SQL
 
#调用
DELIMITER;  #这是一句SQL
CALL myp4('小昭',@bName);  #这是一句SQL
SELECT @bName  #这是一句SQL
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

例子四:根据女神名,返回对应的男神名和男神魅力值

#创建
DELIMITER //
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
     SELECT bo.boyName,bo.userCP INTO boyName,userCP
		 FROM boys bo
		 INNER JOIN beauty b ON bo.id = b.boyfriend_id
		 WHERE b.name = beautyName;
END//


#调用
DELIMITER;
CALL myp5('小昭',@bName,@userCP);
SELECT @bName,@userCP;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

4、创建带 inout 模式参数的存储过程

例子五:传入 a 和 b 两个值,最终a 和 b 都翻倍并返回

DELIMITER// 
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
BEGIN
		 SET a = a*2;
		 SET b = b*2;
END//

SET @n = 10;
SET @m = 20;
DELIMITER;
CALL myp6(@n,@m);
SELECT @n,@n

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

存储过程的删除

DROP PROCEDURE 存储过程名
DROP PROCEDURE myp9;
  • 1
  • 2

查看存储过程的信息

SHOW CREATE PROCEDURE myp2;
  • 1
CREATE TABLE `admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `password` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


CREATE TABLE `beauty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `sex` char(1) DEFAULT '女',
  `borndate` datetime DEFAULT '1987-01-01 00:00:00',
  `phone` varchar(11) NOT NULL,
  `photo` blob,
  `boyfriend_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;


CREATE TABLE `boys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `boyName` varchar(20) DEFAULT NULL,
  `userCP` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


INSERT INTO `girls`.`admin` (`id`, `username`, `password`) VALUES (1, 'john', '8888');
INSERT INTO `girls`.`admin` (`id`, `username`, `password`) VALUES (2, 'lyt', '6666');
INSERT INTO `girls`.`admin` (`id`, `username`, `password`) VALUES (3, 'tom', '000');
INSERT INTO `girls`.`admin` (`id`, `username`, `password`) VALUES (4, 'jery', '000');
INSERT INTO `girls`.`admin` (`id`, `username`, `password`) VALUES (5, 'Lili', '000');
INSERT INTO `girls`.`admin` (`id`, `username`, `password`) VALUES (6, 'liing', '000');
INSERT INTO `girls`.`admin` (`id`, `username`, `password`) VALUES (7, 'rose', '000');
INSERT INTO `girls`.`admin` (`id`, `username`, `password`) VALUES (8, 'xiaolizi', '9999');


INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (1, '柳岩', '女', '1988-02-03 00:00:00', '18209876577', NULL, 8);
INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (2, '宋佳', '女', '1987-12-30 00:00:00', '18219876577', NULL, 9);
INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (3, 'Angelababy', '女', '1989-02-03 00:00:00', '18209876567', NULL, 3);
INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (4, '热巴', '女', '1993-02-03 00:00:00', '18209876579', NULL, 2);
INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (5, '周冬雨', '女', '1992-02-03 00:00:00', '18209179577', NULL, 9);
INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (6, '周芷若', '女', '1988-02-03 00:00:00', '18209876577', NULL, 1);
INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (7, '岳灵珊', '女', '1987-12-30 00:00:00', '18219876577', NULL, 9);
INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (8, '小昭', '女', '1989-02-03 00:00:00', '18209876567', NULL, 1);
INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (9, '双儿', '女', '1993-02-03 00:00:00', '18209876579', NULL, 9);
INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (10, '王语嫣', '女', '1992-02-03 00:00:00', '18209179577', NULL, 4);
INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (11, '夏雪', '女', '1993-02-03 00:00:00', '18209876579', NULL, 9);
INSERT INTO `girls`.`beauty` (`id`, `name`, `sex`, `borndate`, `phone`, `photo`, `boyfriend_id`) VALUES (12, '赵敏', '女', '1992-02-03 00:00:00', '18209179577', NULL, 1);



INSERT INTO `girls`.`boys` (`id`, `boyName`, `userCP`) VALUES (1, '张无忌', 100);
INSERT INTO `girls`.`boys` (`id`, `boyName`, `userCP`) VALUES (2, '村里闹坤了', 800);
INSERT INTO `girls`.`boys` (`id`, `boyName`, `userCP`) VALUES (3, '黄晓明', 50);
INSERT INTO `girls`.`boys` (`id`, `boyName`, `userCP`) VALUES (4, '段誉', 300);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
推荐阅读