Day78（mysql，存储过程，函数，流程控制）

存储过程

#存储过程和函数
 /*
存储过程和函数：类似于java中的方法
好处：
1、提高代码的重用性
2、简化操作
*/
#存储过程
 /*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
*/
#一、创建语法
 CREATE PROCEDURE 存储过程名 (参数列表)
BEGIN
  存储过程体（一组合法的SQL语句）
END #注意：
 /*
1、参数列表包含三部分
参数模式  参数名  参数类型
举例：
in stuname varchar(20)

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $
*/
#二、调用语法
 CALL 存储过程名 (实参列表);

#--------------------------------案例演示-----------------------------------
#1.空参列表
#案例：插入到admin表中五条记录
 SELECT
  *
FROM
  admin;

DELIMITER $

CREATE PROCEDURE myp1 ()
BEGIN
  INSERT INTO admin (username, `password`)
  VALUES
    ('john1', '0000'),
    ('lily', '0000'),
    ('rose', '0000'),
    ('jack', '0000'),
    ('tom', '0000');
END $

#调用
 CALL myp1 () $

#2.创建带in模式参数的存储过程
 #案例1：创建存储过程实现 根据女神名，查询对应的男神信息
 DELIMITER $

CREATE PROCEDURE myp2 (IN beautyName VARCHAR (20))
BEGIN
  SELECT
    *
  FROM
    boys
  WHERE id =
    (SELECT
      boyfriend_id
    FROM
      beauty
    WHERE NAME = beautyName);
END $

#调用
 CALL myp2 ('刘亦菲') $

#案例2 ：创建存储过程实现，用户是否登录成功
 CREATE PROCEDURE myp3 (
  IN username VARCHAR (20),
  `password` VARCHAR (20)
)
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 myp3 ('张飞', '8888') $

#3.创建out 模式参数的存储过程
#案例1：根据输入的女神名，返回对应的男神名
 CREATE PROCEDURE myp4 (
  IN beautyname VARCHAR (20),
  OUT boyname VARCHAR (20)
)
BEGIN
  SELECT
    b.`boyName` INTO boyname
  FROM
    boys b
  WHERE b.id =
    (SELECT
      boyfriend_id
    FROM
      beauty
    WHERE `name` = beautyname);
END $

CALL myp4 ('刘亦菲', @bname) $

SELECT
  @banme $

#案例2：根据输入的女神名，返回对应的男神名和魅力值
 CREATE PROCEDURE myp5 (
  IN beautyName VARCHAR (20),
  OUT boyname VARCHAR (20),
  OUT usercp INT
)
BEGIN
  SELECT
    b.boyName,
    b.usercp INTO boyname,
    usercp
  FROM
    boys b
  WHERE b.id =
    (SELECT
      boyfriend_id
    FROM
      beauty
    WHERE `name` = beautyname);
END $

CALL myp5 ('刘亦菲', @name, @cp) $

SELECT
  @name,
  @cp $

#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
 CREATE PROCEDURE myp6 (INOUT a INT, INOUT b INT)
BEGIN
  SET a = a * 2;
  SET b = b * 2;
END $

SET @a = 2 $

SET @b = 3 $

CALL myp6 (@a, @b) $

SELECT
  @a,
  @b $

#三、删除存储过程
#语法：drop procedure 存储过程名
 DROP PROCEDURE myp1;
DROP PROCEDURE p2,
p3;
# 错误的
#四、查看存储过程的信息
 DESC myp2;
# 错误的
 SHOW CREATE PROCEDURE myp2;
#案例讲解
#一、创建存储过程实现传入用户名和密码，插入到admin表中
 CREATE PROCEDURE pr1 (
  IN username VARCHAR (20),
  IN pasword VARCHAR (20)
)
BEGIN
  INSERT INTO admin (username, `password`)
  VALUES
    (username, pasword);
END $

CALL pr1 ('lulala', '1234') $

SELECT
  *
FROM
  admin $

#二、创建存储过程实现传入女神编号，返回女神名称和女神电话
 CREATE PROCEDURE pr2 (
  IN bId INT,
  OUT bname VARCHAR (20),
  OUT phone VARCHAR (20)
)
BEGIN
  SELECT
    b.name,
    b.phone INTO bname,
    phone
  FROM
    beauty b
  WHERE b.id = bid;
END $

CALL pr2 (5, @n, @p) $

SELECT
  @n,
  @p $

#三、创建存储存储过程或函数实现传入两个女神生日，返回大小
 CREATE PROCEDURE pr3 (
  IN bd1 DATETIME,
  IN bd2 DATETIME,
  OUT result INT
)
BEGIN
  SELECT
    DATEDIFF (bd1, bd2) INTO result;
END $

CALL pr3 (
  '1992-01-01 00:00:00',
  '1993-01-01 00:00:00',
  @r
) $

#四、创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回
 CREATE PROCEDURE pr4 (
  IN date1 DATETIME,
  OUT date2 VARCHAR (50)
)
BEGIN
  SELECT
    DATE_FORMAT (date1, '%y年%m月%d日') INTO date2;
END $

CALL pr4 (1992-01-01, @a) $

SELECT
  @a $

#五、创建存储过程或函数实现传入女神名称，返回：女神 and 男神  格式的字符串
 CREATE PROCEDURE pr5 (
  IN bname VARCHAR (20),
  OUT bandbo VARCHAR (30)
)
BEGIN
  SELECT
    CONCAT (
      b.name,
      ' and ',
      IFNULL (boyname, 'null')
    ) INTO bandbo
  FROM
    beauty b
    LEFT JOIN boys bo
      ON bo.id = b.boyfriend_id
  WHERE b.name = bname;
END $

CALL pr5 ('刘亦菲', @a) $

SELECT
  @a $

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

CALL pr6 (2, 4) $

函数

#函数
 /*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果
*/
#一、创建语法
 CREATE FUNCTION 函数名 (参数列表) RETURNS 返回类型
BEGIN
  函数体
END
/*
注意：
1.参数列表 包含两部分：
参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议
return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记
*/
#二、调用语法
 SELECT
  函数名 (参数列表) #------------------------------案例演示----------------------------
#1.无参有返回
#案例：返回公司的员工个数
   CREATE FUNCTION f1 () RETURNS INT
  BEGIN
    DECLARE c INT DEFAULT 0;

SELECT
  COUNT (*) INTO c
FROM
  employees;

RETURN c;

END $
SELECT
f1 () $ #2.有参有返回
#案例1：根据员工名，返回它的工资
 CREATE FUNCTION f2 (ename VARCHAR (20)) RETURNS DOUBLE
BEGIN
  DECLARE sal INT DEFAULT 0;
  
  SELECT
    salary INTO sal
  FROM
    employees
  WHERE last_name = ename;
  
  RETURN sal;
  
END $
SELECT
  f2 ('Chen') $ #案例2：根据部门名，返回该部门的平均工资
   CREATE FUNCTION f3 (dname VARCHAR (20)) RETURNS DOUBLE
  BEGIN
    DECLARE sal DOUBLE DEFAULT 0;

SELECT
  AVG (salary) INTO sal
FROM
  employees e
  JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_name = dname;

RETURN sal;

END $
SELECT
f3 ('IT') $ #三、查看函数
 SHOW CREATE FUNCTION f3;

#四、删除函数
 DROP FUNCTION f3;

#案例
#一、创建函数，实现传入两个float，返回二者之和
 CREATE FUNCTION f4 (a FLOAT, b FLOAT) RETURNS FLOAT
BEGIN
  DECLARE c FLOAT DEFAULT 0;
  
  SET c = a + b;
  
  RETURN c;
  
END $

流程控制

#流程控制结构
 /*
顺序、分支、循环
*/
#一、分支结构
#1.if函数
 /*
语法：if(条件,值1，值2)
功能：实现双分支
应用在begin end中或外面
*/
#2.case结构
 /*
语法：
情况1：类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end 
情况2：
case 
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end 
应用在begin end 中或外面
*/
#3.if结构
 /*
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能：类似于多重if
只能应用在begin end 中
*/
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，
#       如果成绩>60,返回C，否则返回D
 CREATE FUNCTION f5 (score INT) RETURNS VARCHAR (10)
BEGIN
  DECLARE grade VARCHAR (10);
  
  IF score > 90
  THEN SET grade = 'A';
  
  ELSEIF score > 80
  THEN SET grade = 'B';
  
  ELSEIF score > 60
  THEN SET grade = 'C';
  
  ELSE SET grade = 'D';
  
  END IF;
  
  RETURN grade;
  
END $
SELECT
  f5 (88) $ #案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
   CREATE PROCEDURE prtest1 (IN sal DOUBLE)
  BEGIN
    IF sal < 2000
    THEN
    DELETE
    FROM
      employees
    WHERE employees.`salary` = sal;

ELSEIF sal > 2000
AND sal < 5000
THEN
UPDATE
  employees
SET
  salary = salary + 1000
WHERE employees.`salary` = sal;

ELSE
UPDATE
  employees
SET
  salary = salary + 500
WHERE employees.`salary` = sal;

END IF;

END $ #案例1：创建函数，实现传入成绩，如果成绩>90,返回A，
#       如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
 CREATE FUNCTION f6 (score INT) RETURNS CHAR
BEGIN
DECLARE g CHAR;

CASE
  WHEN score > 90
  THEN SET g = 'A';
  
  WHEN score > 80
  THEN SET g = 'B';
  
  WHEN score > 60
  THEN SET g = 'C';
  
  ELSE SET g = 'D';
  
END CASE;

RETURN g;

END $
SELECT
f6 (77) $ #二、循环结构
 /*
分类：
while、loop、repeat
循环控制：
iterate类似于 continue，继续，结束本次循环，继续下一次
leave 类似于  break，跳出，结束当前所在的循环
*/
#1.while
 /*
语法：
【标签:】while 循环条件 do
	循环体;
end while【 标签】;
联想：
while(循环条件){
	循环体;
}
*/
#2.loop
 /*
语法：
【标签:】loop
	循环体;
end loop 【标签】;
可以用来模拟简单的死循环
*/
#3.repeat
 /*
语法：
【标签：】repeat
	循环体;
until 结束循环的条件
end repeat 【标签】;
*/
#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
 CREATE PROCEDURE pro1 (IN insertTimes INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  
  WHILE
    i <= insertTimes DO
    INSERT INTO admin (username, `password`)
    VALUES
      (CONCAT ('Rose', i), '666');
    
    SET i = i + 1;
    
  END WHILE;
  
END $ CALL pro1 (3) $ #2.添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
 CREATE PROCEDURE pro2 (insertCount INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  
  a :
  WHILE
    i <= insertCount DO
    INSERT INTO admin (username, `password`)
    VALUES
      (CONCAT ('Jack', i), i);
    
    IF i >= 20
    THEN LEAVE a;
    
    END IF;
    
    SET i = i + 1;
    
  END WHILE a;
  
END $ CALL pro2 (21) $
SELECT
  *
FROM
  admin;

#3.添加iterate语句
 #案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
 CREATE PROCEDURE pro3 (insertCounts INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  
  a :
  WHILE
    i <= insertCounts DO SET i = i + 1;
    
    IF i % 2 != 0
    THEN ITERATE a;
    
    END IF;
    
    INSERT INTO admin (username, `password`)
    VALUES
      (CONCAT ('Tom', i), i);
    
  END WHILE a;
  
END $ CALL pro3 (24) $ #案例讲解：
 /*一、已知表stringcontent
其中字段：
id 自增长
content varchar(20)

向该表插入指定个数的，随机的字符串
*/
CREATE TABLE stringcontent (
  id INT PRIMARY KEY AUTO_INCREMENT,
  content VARCHAR (20)
);

DELIMITER $

CREATE PROCEDURE protest (times INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE str VARCHAR (26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
  DECLARE startIndex INT;
  #代表初始索引
   DECLARE len INT;
  #代表截取的字符长度
   WHILE
    i <= times DO SET startIndex = FLOOR (RAND () * 26+1);
    #代表初始索引，随机范围1-26
     SET len = FLOOR (RAND () * (20- startIndex + 1) + 1);
    #代表截取长度，随机范围1-（20-startIndex+1）
     INSERT INTO stringcontent (content)
    VALUES
      (SUBSTR (str, startIndex, len));
    SET i = i + 1;
  END WHILE;
END $

CALL test_randstr_insert (10) $

SELECT
  *
FROM
  stringcontent;