【mysql 存储过程&流程控制 09】

一、存储过程

1、存储过程含义：一组预先编译好的sql语句的集合，理解成批处理语句

语法：

#1、创建语法

CREATE PROCEDURE  存储过程名(参数列表)
BEGIN
    存储过程体（一组合法有效的方法体）
END

注意：

1、参数列表包含三部分
参数模式，参数名，参数类型
举例：
IN stuname VARCHAR(20)
参数模式：
IN 该参数可以作为输入，也就是说该参数需要调用方传入值
OUT 该参数可以作为删除，也就是该参数可以作为返回值
INOUT  该参数既可以作为输入又可以作为输出，也就是该参数即需要传入值，又可以返回值
2、如果存储过程体仅仅只有一句话，BEGIN END 可以省略
存储过程体中每条SQL语句的结尾要求必须加分号。
存储过程的结尾可以使用DELIMITER重新设置
语法：
DELIMITER 结束标记
案例：
DELIMITER $

#调用语法

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

案例：

#1、空参列表
#案例：插入到admin表中的五条记录

准备工作：查看admin表中的数据
USE girls;
SELECT * FROM admin;

具体步骤

#已$作为结尾标记
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    INSERT INTO admin(username,PASSWORD) VALUES
    ('rose','00000'),
    ('jock','00000'),
    ('tom','00000'),
    ('cat','00000'),
    ('back','00000');
END $ -- 需要在end之后加标记

CALL mypl() $

编写以后再mysql终端内执行

 注意要添加结尾标记

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

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

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 $

CALL myp2('倪妮')$

编写以后再mysql终端内执行

 #案例2：创建存储过程实现，用户是否登录成功

CREATE PROCEDURE myp3(IN username VARCHAR(20),IN passwd VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0; #result变量声明
    
    SELECT COUNT(*)  INTO result #把count(*)赋值给result
    FROM admin 
    WHERE admin.`username`=username AND PASSWORD = passwd;
    
    SELECT IF(result>0,'成功','失败');

END $

CALL myp3('join','8888')$

编写以后再mysql终端内执行

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

#案例1：根据女神名，返回对应的男神名

DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
    SELECT bo.`boyName` INTO  boyName
    FROM boys bo RIGHT JOIN beauty b 
    ON bo.id = b.boyfriend_id    
    WHERE b.name = beautyName;


END $

#定义用户变量bname用于接受返回的男神名
#set @bName$  这一步可以忽略不写
CALL myp4('倪妮',@bName) $
SELECT @bName$

编写以后再mysql终端内执行

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

DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN

    SELECT bo.boyName ,bo.userCP INTO boyName,userCP   注意：给out变量赋值
    FROM boys bo RIGHT JOIN beauty b 
    ON bo.id = b.`boyfriend_id`
    WHERE b.name = beautyName;

END $

CALL myp5('倪妮',@bName,@userCp)$
SELECT @bName,@userCp $

编写以后再mysql终端内执行

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

#案例1：传入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 @a=10$
SET @b=20$
CALL myp6(@a,@b) $
SELECT @a,@b $

2、删除存储过程

语法：drop procedure 存储过程名  

DROP PROCEDURE myp1;

注意：一次只能删除一个存储过程

3、查看存储过程的信息

SHOW CREATE PROCEDURE myp2;

存储过程不能直接修改，可以先删除然后在用sql语句修改

案例：#创建存储过程实现传入一个日期，格式化成xx年xx月xx日并返回

CREATE PROCEDURE myp7(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
    SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
    
END $

CALL myp7(NOW(),@strDate)$
SELECT @strDate$

#创建存储过程实现传入女神名，返回：女神and男神  格式的字符串

CREATE PROCEDURE myp8(IN gName VARCHAR(50),OUT str VARCHAR(50))
BEGIN 
    SELECT CONCAT(gName,'and',IFNULL(boyName,'null')) INTO str
    FROM beauty b LEFT JOIN boys bo 
    ON b.`boyfriend_id` = bo.`id`
    WHERE b.`name` = gName;

END $

CALL myp8('赵丽颖',@str)$
SELECT @str$

二、函数

含义：一组预先编译好的sql语句的集合，理解成批处理语句

区别：

存储过程：可以有0个返回，也可以有多个返回适合做批量插入，批量更新

函数：有且仅有1个返回，适合做处理数据后返回的一个结果

1、创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END

注意：

1）参数列表包含两部分：参数名和参数类型

2)函数体：肯定有return语句，如果没有回报错

如果return语句没有放在函数体的最后也不会报错，但不建议

3）函数体中仅有一句话，则可以省略begin end

4）使用delimiter 语句设置结束标记。例如：delimiter $   -->结尾还的加上；如：select * from be $;

2、调用语法

SELECT 函数名(参数列表)

 #案例演示
#1、无参又反悔
#案例1：返回公司的员工个数

CREATE FUNCTION myf1() RETURNS INT
BEGIN

    DECLARE c INT DEFAULT 0; #定义变量用于接收返回值
    SELECT COUNT(*) INTO c
    FROM employees;
    RETURN c;

END $

SELECT myf1()$

#案例2：根据员工名，返回他的工资

CREATE FUNCTION myf2(eName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    SET @sal=0; #定义用户变量
    SELECT    Salary INTO @sal
    FROM employees
    WHERE Last_name = eName;
    
    RETURN @sal;

END $

SELECT myf2('Ernst')$

#案例3：根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(dpartName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    DECLARE avgSal DOUBLE DEFAULT 0;
    SELECT AVG(Salary) INTO avgSal
    FROM employees e JOIN departments d
    ON e.department_id = d.department_id
    WHERE d.department_name = dpartName;
    
    RETURN avgSal;
END $

SELECT myf3('Adm')$

 2、查看函数

SHOW CREATE FUNCTION myf3;

3、删除函数

DROP FUNCTION myf3;

#案例：创建函数，实现传入两个float,返回二者之和

CREATE FUNCTION myf4(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
    SET @res=num1+num2;
    RETURN @res;

END $

SELECT myf4(1.1,1.2)$
DROP FUNCTION myf4;

三、流程控制语句

 顺序结构：程序从上往下一次执行

分支结构：程序从两条或多条路径中选择一条去执行

循环结构：程序在满足一定条件的基础上，重复执行一段代码

1、分支结构

#1、if函数  if(表达式1，表达式2，表达式3)

执行顺序：如果表达式1成立，执行表达式2，否则执行表达式3

#2、case结构

情况1：和Java中的switch相似，一般实现等值判断

语法：

　　case 变量|表达式|字段

　　when 要判断的值1  then 返回的值1或语句1；

　　when 要判断的值2  then 返回的值2或语句2；

　　...

　　else 要返回的值n或语句n;

　　end case；

情况2：类似于Java中的if...elseif...else 这样的多重语句

语法：

　　case

　　when 要判断的条件1  then 返回的值1或语句1；

　　when 要判断的条件2  then 返回的值2或语句2；

　　...

　　else 返回的值n或语句n;

　　end case;

# 案例：

#案例1：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩90-100，显示A，80-90，显示B，60-80 显示C，否则显示D

DELIMITER $
CREATE PROCEDURE mypp1(IN score INT)
BEGIN
    CASE 
    WHEN score >=90 AND score <=100 THEN SELECT 'A';
    WHEN score >=80 THEN SELECT 'B';
    WHEN score >=60 THEN SELECT 'C';
    ELSE SELECT 'D';
    END CASE;

END $

CALL mypp1(96)$

#3、if结构

语法：

if 条件1 then 语句1；

elseif 条件2 then 语句2；

...

【else 语句n；】

end if；

只能应用在begin end中

#案例：根据传入的成绩，来显示等级，比如传入的成绩90-100，返回A，80-90，返回B，60-80 返回C，否则返回D

CREATE FUNCTION myff1(score INT) RETURNS CHAR
BEGIN
    
    IF score>=90 AND score <=100 THEN RETURN 'A';
    ELSEIF score >=80 THEN RETURN 'B';
    ELSEIF score >=60 THEN RETURN 'C';
    ELSE RETURN 'D';
    END IF;
    
END $

SELECT myff1(96)$

#4、循环结构

必须放到begin end 里面

分类：while ， loop，repeat

循环控制：

iterate类似于continue继续 ，结束本次循环，继续下一次

leave 类似于break 跳出，结束当前所在的循环

1）while

语法：

【标签：】while 循环条件 do

　　　　　　循环体；

end while 【标签】；

2）loop

语法:

【标签：】loop

　　　　　　循环体；

end loop 【标签】；

可以模拟简单的死循环

3）repeat

语法：

【标签：】repeat

　　　　　　循环体；

until 结束循环的条件

end repeat 【标签】；

#案例：批量插入，根据次数插入到admin表中多条记录

CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=insertCount DO
        INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('Rose',i),'666');
        SET i=i+1;
    END WHILE;

END $    

CALL pro_while1(50) $

# 添加leave语句 批量插入，根据次数插入到admin表中多条记录，如果次数>20 则停止

CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    a:WHILE i<=insertCount DO
        INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('liyin',i),'666');
        IF i>=20 THEN LEAVE a;
        END IF;
        SET i=i+1;
    END WHILE a;

END $    

CALL pro_while1(50) $

#添加iterate语句，批量插入，根据次数插入到admin表中多条记录，只插入偶数次

 

CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    a:WHILE i<=insertCount DO
        SET i=i+1;
        IF MOD(i,2) THEN ITERATE a;
        END IF;
        INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('liyin',i),'666');
        
    END WHILE a;

END $    

CALL pro_while1(50) $

======Java实现本案例的方式=====
int i=1;
FOR(i<insertCount){
    i++;
    if(i%2==0){
        continue;    
        }
    插入语句
}

 

#金典案例

已知表stringcontent

其中字段：id自增长  connect varchar(20)

向该表插入指定个数的随机的字符串

USE test;
CREATE TABLE stringcontent(
    id INT PRIMARY KEY AUTO_INCREMENT,
    connect VARCHAR(50)
);
DESC  stringcontent;

DROP TABLE IF EXISTS test_randstr_insert;

DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
    
   #定义一个循环变量i,表示插入的次数
   DECLARE i INT DEFAULT 1;
   #定义一个字符串，用于取数
   DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwsyz';
   #插入指定个数的字符串，用到了substr(str,index,len)函数
 
   DECLARE startIndex INT DEFAULT 1; -- 定义一个索引用于实现substr函数内的index,并设置成随机数
   DECLARE len INT DEFAULT 1; -- 定义长度实现substr函数内的len,并设置成随机数
   
   WHILE i<= insertCount DO
   
    SET startIndex = FLOOR(RAND()*26+1);-- 设置起始索引为随机数，rand()是0-1，*26变成了0-25.788这样，所以要加1，并向下取整
    SET len = FLOOR(RAND()*(26-startIndex+1)+1);
    INSERT INTO stringcontent (connect) VALUES (SUBSTR(str,startIndex,len));
    SET i=i+1;

   END WHILE;
       
END $

CALL test_randstr_insert(10) $