MySql存储过程和函数

426 阅读4分钟

「这是我参与11月更文挑战的第2天,活动详情查看:2021最后一次更文挑战

存储过程

含义:一组预先编译好的的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 存储过程名(实参列表);

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

image.png

创建带out模式参数的存储过程

image-20211031233943287

image.png 创建带inout模式参数的存储过程

image.png

删除存储过程 语法:drop procedure存储过程名

DROP PROCEDURE p1;

查看存储过程的信息

SHOW CREATE PROCEDURE myp2;

函数

一、含义:

一组预先编译好的额sql语句的集合,理解成批处理语句

1、提高代码的重用性

2、简化操作

3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率

与存储过程的区别

存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量跟新

函数:有且仅有1个返回,适合做处理数据后返回一个结果

创建语法

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

注意

1、参数列表 包含两部分:

函数名 参数类型

2、函数体:肯定会有return语句,如果没有会报错

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

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

4、使用 delimiter 语句设置结束标记

二、调用语法

SELECT 函数名(函数列表)

三、查看函数

SHOW CREATE FUNCTION myf3;

四、删除函数

DROP FUNCTION myf3

案例:

#创建函数,实现传入两个float,返回二者之和
CREATE FUNCTION MY2(A DOUBLE,B DOUBLE) RETURN DOUBLE
BEGIN
    
    DECLARE RESULT INT DEFAULT 0;
    SELECT A+B INTO RESULT;
    SET RESULT=A+B;
    RETURN RESULT;
​
END $

流程控制结构

顺序结构:程序从上往下依次执行

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

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

一、分支结构

if函数

功能:实现简单的双分支

语法:

if(表达式1,表达式2,表达式3)

执行顺序:

如果表达式1成立,则if函数返回表达式2的值,否则返回表达式3的值

应用:任何地方

if结构

功能:实现多重分支

语法:

if 条件1 then 语句1if 条件 2 then 语句2;
...
else 语句n
end if;

应用在begin end语句块中

case结构

情况1:类似于java中的switch语句,一般用于实现等值判断

语法:

                    CASE   变量|表达式|字段
                    WHEN   要判断的值 THEN 返回的值1或语句1;
                    WHEN   要判断的值 THEN 返回的值2或语句1;
                    
                    ELSE    要返回的值n或语句1;
                    END CASE;

情况2:类似于java中的多重if语句,一般用于区间判断

语法:

                    CASE
                    WHEN 要判断的条件1 THEN 返回的值1或语句1;
                    WHEN 要判断的条件2 THEN 返回的值2或语句1;
                    
                    ELSE 要返回的值n或语句1;
                    END CASE;

特点:

1、可以作为表达式,嵌套在其他语句中使用,可以放置在任何地方,BEGIN END中或BEGIN END的外面

可以作为独立的语句去使用,只能放在BEGIN END

2、如果WHEN中的值满足或条件成立,则执行对应的THEN后面的语句,并且技术CASE

如果都不满足,则执行ELSE中的语句或值

3、ELSE可以省略,如果省略,并且所有的WHEN条件都不满足,则返回NULL

案例:

CREATE PROCEDURE test_case(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  test_case(95)$

二、循环结构

分类:

while、loop、repeat

循环控制:

iterate 类似于continue 表示继续的意思,结束本次循环 继续下一次

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

while结构

语法:

【标签】 while 循环条件 do
​
            循环体;
​
            end while 【标签】

loop结构

语法:

【标签】 loop
        循环体;
end loop 【标签】;

注意:这个循环不会自己结束,需要配合循环控制;常用来模拟死循环;

repeat结构

语法:

【标签】 repeat
        循环体;
until 结束循环的条件
end repeat 【标签】;

例题:

使用leave控制结构

DELIMITER $
DROP PROCEDURE test$
CREATE PROCEDURE test(IN insertcount INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    a:WHILE i<=insertcount DO
        INSERT INTO admin(username ,password) VALUES(CONCAT('小花',i),001);
        IF(i>=20) THEN LEAVE a;
        END IF;
        set insertcount=insertcount+1;
      END WHILE A;
​
END $

使用iterate控制结构

DROP PROCEDURE test1$
CREATE PROCEDURE test(IN insertcount INT)
BEGIN
        DECLARE i INT DEFAULT 0;
        a:WHILE i<=insertcount DO
                SET i=i+1;
                IF MOD(i,2)!=0 THEN ITERATE a;
                END IF;
                INSERT INTO admin(username ,password) VALUES(CONCAT('小花',i),001);
          END WHILE a;
​
END $
CALL test(20)$