mysql——存储过程

存储过程

类似于java中的方法
优点：
    ①提高代码的重用性
    ②简化操作
    
存储过程
定义：一组预先编译好的SQL语句的集合，可以理解为批处理语句
优点：
    ①提高代码的重用性
    ②简化操作
    ③减少编译次数和与数据库连接的次数，提高效率
语法：

一、语法创建

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

注意：
1、参数列表包含三部分
参数模式 参数名 参数类型
IN stuname VARCHAR(20);

①参数模式
IN：该模式修饰的参数可以作为输入，参数需要调用方传入值
OUT：该模式修饰的参数可以作为输出，参数可以作为返回值
INOUT：该模式修饰的参数可以作为输入和输出，该参数既需要传入值，又可以返回值
2、如果存储过程体只有一句话，则BEGIN END可以省略，其余情况不行。。
存储过程体的每条SQL语句结尾必须要用分号结尾。
存储过程的结果可以使用DELIMITER重新设置
语法：
    DELIMITER 结束标记
    如：DELIMITER $

二、语法使用(调用)

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

1、空参列表

案例1：插入到admin表中五条记录
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    INSERT INTO admin(username,`password`) 
    VALUES('join1','0000'),('tom','0000'),('lily','0000'),('rose','0000'),('jack','0000');
END $

CALL myp1()$

2、带IN模式的参数存储过程

案例1：创建存储过程实现根据女神名查询对应的男神信息

DELIMITER $
USE girls;
CREATE PROCEDURE myp2(IN beautyname VARCHAR(20))
BEGIN
    SELECT b.*
    FROM boys b
    RIGHT JOIN beauty g ON b.id=g.boyfriend_id
    WHERE g.name=beautyname;
END $

CALL myp2('王语嫣');

案例2：创建存储过程，验证用户是否登录成功

DELIMITER $
USE girls$
CREATE PROCEDURE myp3(IN username VARCHAR(10),IN `password` VARCHAR(10))
BEGIN 
    DECLARE result VARCHAR(20) DEFAULT '哈哈';    #声明并初始化
    SELECT COUNT(*) INTO result    #赋值
    FROM admin a
    WHERE a.username=username
    AND a.password=PASSWORD;
    
    SELECT result;    #使用
END $

CALL myp3('john','8888');

DELIMITER $
USE girls$
CREATE PROCEDURE myp4(IN username VARCHAR(10),IN `password` VARCHAR(10))
BEGIN 
    DECLARE result INT DEFAULT 0;    #声明并初始化
    SELECT COUNT(*) INTO result    #赋值
    FROM admin a
    WHERE a.username=username
    AND a.password=PASSWORD;
    
    SELECT IF(result>0,'成功','失败');    #使用
END $
CALL myp4('john','8888');

3、创建带OUT模式的存储过程
案例1：根据女神名返回对应的男生名

DELIMITER $
USE girls$
CREATE PROCEDURE myp5(IN beautyname VARCHAR(20),OUT boyname VARCHAR(20))
BEGIN
    SELECT b.boyName INTO boyname
    FROM boys b
    LEFT JOIN beauty g ON g.boyfriend_id=b.id
    WHERE g.name=beautyname;
END $

SET @bname='null';
CALL myp5('小昭',@boyname);
SELECT @boyname;

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

DELIMITER $
USE girls$
CREATE PROCEDURE myp6(IN gname VARCHAR(20),OUT bname VARCHAR(20),OUT ucp INT)
BEGIN
    SELECT b.boyName,b.userCP INTO bname,ucp
    FROM boys b
    LEFT JOIN beauty g ON g.boyfriend_id=b.id
    WHERE g.name=gname;
END $

SET @bname,@ucp;
CALL myp6('王语嫣',@bname,@ucp);
SELECT @bname 姓名,@ucp 魅力值;

4、创建带INOUT模式参数的存储过程
案例1：传入a和b，使a，b翻倍

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

SET @c=1;
SET @d=2;
CALL myp7(@c,@d);
SELECT @a,@b;

1、创建存储过程或函数实现传入用户名和密码，插入到 admin 表中

DELIMITER $
USE girls$
CREATE PROCEDURE testv1(IN username VARCHAR(20),IN `password` VARCHAR(20))
BEGIN
    INSERT INTO admin(admin.username,admin.password) VALUES(username,`password`);
END $

CALL testv1('ll','0612');
CALL testv1('xxl','1023');

SELECT * FROM admin;

2、创建存储过程或函数实现传入女神编号，返回女神名称和女神电话

DELIMITER $
USE girls$
CREATE PROCEDURE testv2(IN id INT,OUT gname VARCHAR(20),OUT pnum VARCHAR(20))
BEGIN 
    SELECT g.name,g.phone INTO gname,pnum
    FROM beauty g
    WHERE g.id=id;
END $

CALL testv2(2,@gn,@pn);
SELECT @gn 女神名,@pn 女神电话;

3、创建存储存储过程或函数实现传入两个女神生日，返回大小

DELIMITER $
USE girls$
CREATE PROCEDURE testv3(IN gb1 DATETIME,IN gb2 DATETIME,OUT cp INT)
BEGIN
    SELECT DATEDIFF(gb1,gb2) INTO cp;
END $

CALL testv3('2014-03-08','1997-06-12',@result);
SELECT @result;

二、删除存储过程

语法：
    DROP PROCEDURE 存储过程;
    
DROP PROCEDURE myp3;
DROP PROCEDURE myp4,myp5;    #不允许一次删除多个

三、查看存储过程的信息

SHOW CREATE PROCEDURE myp4;

4、创建存储过程或函数实现传入一个日期，格式化成 xx 年 xx 月 xx 日并返回

DELIMITER $
CREATE PROCEDURE testv4(IN date1 DATETIME,OUT date2 VARCHAR(20))
BEGIN
    SELECT DATE_FORMAT(date1,'%Y年%m月%d日') INTO date2;
END $

CALL testv4('1997-06-12',@result);
SELECT @result;

5、创建存储过程或函数实现传入女神名称，返回：女神 AND 男神 格式的字符串
如 传入 ：小昭
返回： 小昭 AND 张无忌

DROP PROCEDURE testv5;
DELIMITER $
USE girls$
CREATE PROCEDURE testv5(IN gname VARCHAR(20),OUT result VARCHAR(20))
BEGIN
    #declare result varchar(20);
    SELECT CONCAT(NAME,' AND ',IFNULL(boyName,'null')) INTO result
    FROM boys b
    JOIN beauty g ON b.id=g.boyfriend_id
    WHERE g.name=gname;
END $

CALL testv5('小昭',@result);
SELECT @result;

6、创建存储过程或函数，根据传入的条目数和起始索引，查询 beauty 表的记录

DROP PROCEDURE testv6;
DELIMITER $
USE girls$
CREATE PROCEDURE testv6(IN beginnum INT,IN allnum INT)
BEGIN
    SELECT *
    FROM beauty
    LIMIT beginnum,allnum;
END $

CALL testv6(1,30);