MySQL存储过程

MySQL存储过程



含义：一组预先编译好的SQL语句的集合，理解成批处语句（一组语句）
存储过程函数：
类似于java中的方法
把实现的语句封装到方法中，对外值暴露方法名

好处：
1.提高代码重用性
2.简化操作
3.减少了编译次数：编译过一次 就不用编译了
4.减少了和服务器连接次数，提高了效率
简单，安全，性能高

实现的过程：
应用程序中，每一句SQL的执行，都会连接一次数据库服务器
如果把几句话包装到存储过程函数只用连接一次，大大减少了连接服务器的次数

一、创建procedure 存储过程

1.创建语法

CREATE procedure 存储过程名(参数列表)
begin
存储过程体（一组合法的SQL语句）
end

注意：

1.参数列表包含三部分。参数的模式 参数名 参数类型（比java多了一个参数模式）
例如
　　 in stuname varchar（20）
参数模式
in 进口：作为输入，需要调用者传入值
out 出口：作为输出，返回值
inout 进出口：既可以输入，又可以输出。既需要传入值，又能返回值

2.如果存储过程体只有一句话，begin end可以省略。类似java中的if和whell的大括号省略
3.存储体中的每条SQL语句必须以分号结尾
4.存储过程的结尾通过 delimiter重新设置
语法：delimiter 结束标记（可以是任意一个符号）
例如：
delimiter $（SQLyog 不支持，需要在SQL客户端dos窗口中使用）

2.调用语法：
call 存储过程名(实参列表);
和声明方法的形参个数类型顺序一样
输入的传入值，输出的不用

CREATE TABLE admin(
id INT,
username VARCHAR(20),
password INT
)

-----------------------------------------------------------------------#1.空参列表-------------------------------------

#案例：插入到admin表中五条记录
#1.结束标记的设置
delimiter $
#2.创建存储过程函数
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,password)
VALUES(‘join1’,‘0000’),(‘lily’,‘0000’)(‘rose’,‘0000’)(‘jack’,‘0000’)(‘tom’,‘0000’);
END $
#3.调用
CALL myp1()$;

#2.创建带in模式参数的存储过程。IN 可以省略，不建议。默认是in

#案例1：创建存储过程实现根据女神名，查询对应的男神信息
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 $;

#调用：参数写法，直接写个常量值，类型对应就行。放变量也行
CALL myp2(‘桥本环奈’)$;

#案例2：创建存储过程实现，用户是否登录成功（传入用户名密码）

/*
SELECT 查询列表，查询列表，查询列表 INTO 变量名，变量名，变量名
就是把查询的东西赋值给变量
*/

CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DEFAULT’’; #声明并初始化

        SELECT COUNT(*) INTO result
        FROM admin
        WHERE admin.username=username
        AND admin.`password`=`password`;
        /*
        把查询的内容赋值个变量result
        查询admin全部的内容
        当表中的名字和变量的名字密码都相同
        */
        SELECT result;#使用

END $
#调用
CALL myp3(‘张飞’,8888)$;

CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT COUNT(*) INTO result
FROM admin
WHERE admin.username=username
AND admin.password=password;
SELECT IF(result>0,‘成功’,‘失败’);
END $
#调用
CALL myp3(‘张飞’,8888)$;

CREATE PROCEDURE myp5(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DEFAULT’’; #声明并初始化

        SELECT COUNT(*) INTO result
        FROM admin
        WHERE admin.username=username
        AND admin.`password`=`password`;
        SELECT IF(result>0,'成功','失败');

END $

#3.创建带out模式的存储过程
带out模式的存储过程不需要return语句，可以自动返回

#案例：带一个返回值的
#根据对应的女神名返回对应的男神名
delimiter $
CREATE PROCEDURE myp6(IN beautyname VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
JOIN beauty b
ON bo.id=b.boyfriend_id
WHERE bo.boyName=b.name;
END $

#调用
#1.先创建一个变量
SET @bname;(可以不写直接使用)

CALL myp6(‘桥本环奈’,@bname)$
SELECT @bname$

#案例：根据女神名，返回对应的男神名和男神魅力值

delimiter $
CREATE PROCEDURE myp7(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$
CALL myp7(‘柳岩’,@bname,@usercp);$

#4.带inout的模式参数的存储过程

#案例：传入a和b两个值，最终a和b都翻倍并返回值
delimiter $
CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN
SET a=a2;
SET b=b2;
END$

#调用
/*
不能直接调用，也不能放常量值，ab的返回值就找不到了。所以需要穿给两个有值的变量
*/
#先定义两个用户变量
SET @m=10$
SET @n=20$
#将两个用户变量赋值给ab，并且接受返回值
CALL myp8(@m,@n)$ #既输入又输出
#查询
SELECT m,n$

---

二、删除存储过程
语法：
drop PROCEDURE 存储过程名
一次只能删除一个，不支持多删
DROP PROCEDURE myp;

三、查看存储过程
DESC myp2;#查看不了，只能对表使用
正确方法
SHOW CREATE PROCEDURE myp7;

四、修改
很少修改
也只能修改创建时的特点
CREATE PROCEDURE myp7(in beautyname VARCHAR(20),OUT boyname VARCHAR(20),OUT userCP INT)这句的特点
begin end 中的语句不能修改
如果要修该只能删除，不能修改存储过程的逻辑语句

---

练习

#1.创建存储过程实现传入用户名和密码，插入到admin表中
CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20))
BEGIN
INSERT INTO admin(admin.username,PASSWORD)
VALUES(username,loginPwd);
END$
#2.创建存储过程或函数实现传入女神编号，返回女神姓名和女神电话
CREATE PROCEDURE test_pro2(IN id INT,OUT name VARCHAR(20),OUT phone VARCHAR(20))
BEGIN
SELECT b.name,b.phone INTO name,phone
FROM beauty b
WHERE b.id=id;
END $

#3.创建存储过程或函数实现传入两个女神的生日，返回大小
CREATE PROCEDURE test_pro3(IN birth1 datetime,IN birth2 datetime,OUT result INT)
BEGIN
SELECT DATEDIFF(birth1,birth2) INTO result;
END $

#4.创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回
/*
日期转文本 DATE_FORMAT
文本转日期 STR_TO_DATE
*/
CREATE PROCEDURE test_pro4(IN mydate datetime,OUT serDate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,’%y年%m月%d日’) INTO serDate;

END $

CALL test_pro4(now(),@str)$
SELECT @str$

#5.创建存储过程或函数实现传入女神名称，返回 女神 and 男神 格式的字符串
CREATE PROCEDURE test_pro5(IN beautyname VARCHAR(20),OUT str VARCHAR(50))
BEGIN
SELECT CONCAT(beautyname,‘and’,IFNULL(boyName,‘null’))INTO str
FROM boys bo
RIGHT JOIN beauty b
ON b.boyfriend_id=bo.id
WHERE b.name=beautyname;
END $

CALL test_pro5(‘柳岩’,@str)$
SELECT @str$

#6.创建存储过程或函数，更具传入的条目数和起始索引，查询beauty表的记录
CREATE PROCEDURE test_pro6(IN startindex INT,IN size INT)
BEGIN
SELECT * FROM beauty LIMIT startindex,size;
END $

CALL test_pro6(3,5)$