mysql11存储过程

1.简介
2.创建存储过程语法
3.参数说明
4.调用存储过程
5.删除存储过程
6.查看存储过程
7.基本示例
8.存储过程中变量
9.流程控制

 

1.简介

　　存储过程如同一门程序设计语言，同样包含了数据类型、流程控制、输入和输出和它自己的函数库。它是事先经过编译并存储在数据库中的一段sql语句的集合。

 

2.创建存储过程语法

create procedure sp_name(参数)
begin
.........
end

 

 

3.参数说明

参数分为三类

IN 输入参数：表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值，外部的变量不会变化

OUT 输出参数：该值不会传入到存储函数。该值可在存储过程内部被改变，并可返回，在存储过程中修改该参数的值，外部的变量会变化

INOUT 输入输出参数：调用时指定，并且可被改变和返回，在存储过程中修改该参数的值，外部的变量会变化

 

4.调用存储过程

call sp_name(参数)

 

5.删除存储过程

drop procedure sp_name

不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

 

6.查看存储过程

1）查看存储过程或函数的状态

show procedure status like 存储过程

 

2）查看存储过程的定义

show create procedure 存储过程或函数名

 

3）通过查看information_schema.routines了解存储过程的信息

select * from rountines where rounine_name =存储过程名

 

 

7.基本示例

7.1无参数

delimiter是用来定义结束符的 这里采用$$(自定义)作为结束符。存储过程定义完成后，$$标识结束

delimiter $$
CREATE PROCEDURE select_cust()
    BEGIN
        SELECT * FROM jcustomer WHERE `Name` LIKE '%陈%';
    END $$

CALL select_cust();

 

7.2IN参数

注意：参数的格式是 IN/OUT/INOUT 参数名 类型

CREATE  PROCEDURE select_cust3(IN cust_name VARCHAR(32))
BEGIN
        SELECT * FROM jcustomer WHERE `Name` LIKE CONCAT('%',cust_name,'%');
        SET cust_name='陈真';
        SELECT cust_name;
    END

set @a='陈';
CALL select_cust3(@a);
SELECT @a;

两次查看cust_name的值的结果如下图所示。第一次的值为：陈真，第二次调用完存储过程再次查看a的值为：陈，说明用户变量a，传入到存储过程中，在存储过程中对它赋值，但是结果被没有被返回，变量a的值还是：陈

 

 

 

7.3OUT参数

CREATE  PROCEDURE select_cust5(OUT cust_name VARCHAR(32))
BEGIN
        SELECT cust_name;
        SET cust_name='陈';
        SELECT * FROM jcustomer WHERE `Name` LIKE CONCAT('%',cust_name,'%');
        SELECT cust_name;
    END

set @a='陈小春';
CALL select_cust5(@a);
SELECT @a;

三次查看cust_name的值如下图所示，第一次为空，说明变量cust_name的值没有被传入到存储过程中，第二次赋值后的值为：陈，第三次在调用完存储过程后再次查看用户变量a的值，值已从陈小春变为陈，说明存储过程中a的值返回了，用户变量a的值发上变化

 

 

 

7.4INOUT参数

CREATE DEFINER=`root`@`localhost` PROCEDURE `select_cust6`(INOUT cust_name VARCHAR(32))
BEGIN
        SELECT cust_name;
        SET cust_name='陈';
        SELECT * FROM jcustomer WHERE `Name` LIKE CONCAT('%',cust_name,'%');
        SELECT cust_name;
    END

set @a='陈小春';
CALL select_cust6(@a);
SELECT @a;

三次查看cust_name的值如下图所示，说明值既被传入，也被返回

 

 

 

8.存储过程中变量

 https://www.cnblogs.com/jthr/p/15223715.html

 

9.流程控制

9.1CASE

1)简单

delimiter  $$
CREATE  PROCEDURE case_temp1 (INOUT cust_name VARCHAR(32))
BEGIN
        CASE cust_name
            WHEN '陈' THEN 
                SET cust_name='陈小春';
            WHEN '霍' THEN 
                SET cust_name='霍元甲';
            WHEN '李' THEN 
                SET cust_name='李小龙';
            ELSE 
                SET cust_name='啦啦啦';
        END CASE;
    END $$

set @a='陈';
CALL case_temp1(@a);
SELECT @a;

 

2)判断

delimiter  $$
CREATE  PROCEDURE case_temp6 (INOUT cust INTEGER)
BEGIN
        SELECT cust;
        CASE 
            WHEN cust>=0 and cust<10  THEN 
                SELECT '个位数';
            WHEN cust>=10 and cust<100 THEN 
                SELECT '十位数';
            WHEN cust>=100 and cust<1000  THEN 
                SELECT '千位数';
            ELSE 
                SELECT '其它';
        END CASE;
    END $$

set @a=80;
CALL case_temp6(@a);

 

 

9.2if

1）语法

if  condition1  then ...

[elseif condition2 then...,...]

[else...]

end if 

 

2）示例

delimiter  $$
CREATE  PROCEDURE  if_temp2 (INOUT cust INTEGER)
BEGIN
        if cust>=0 and cust<10 THEN  
             SELECT '个位数';
        ELSEIF  cust>=10 and cust<100 THEN  
             SELECT '十位数';
        ELSE 
                SELECT '其它';
        END IF;
    END $$

set @a=80;
CALL if_temp2(@a);

 

9.3while

1）语法

while condition do

...

end while;

执行while语句时，会先判断condition条件是否为真，是则执行循环体，否则退出循环

 

2）示例

delimiter  $$
CREATE  PROCEDURE  while_temp1 (INOUT cust INTEGER)
BEGIN
        WHILE cust < 100 DO
            SELECT cust;
            SET cust=cust+1;
        END WHILE;
    END $$

set @a=95;
CALL while_temp1(@a);

 

 

9.4REPEAT

1）语法

repeat

...

until condition

end repeat

repeat循环语句先执行一次循环体，之后判断condition条件，为真则退出循环，否则继续执行循环。

 

2）示例

delimiter  $$
CREATE  PROCEDURE  repeat_temp1 (INOUT cust INTEGER)
BEGIN
        REPEAT 
            SELECT cust;
            SET cust=cust+1;
        UNTIL cust >= 100
        END REPEAT;
    END $$

set @a=95;
CALL repeat_temp1(@a);

 

9.5LOOP

 1）语法

loop

...

end loop

LOOP循环语句没有内置的循环条件，但可以通过leave语句退出循环。

 

2）示例

delimiter  $$
CREATE  PROCEDURE  loop_temp1 (INOUT cust INTEGER)
BEGIN
        myloop:LOOP 
            SELECT cust;
            SET cust=cust+1;
            IF cust>= 100 THEN
                LEAVE myloop;
            END IF;
        END LOOP;
    END $$

myloop:LOOP  myloop是自定义的名字，通过LEAVE 名字;跳出循环

set @a=95;
CALL loop_temp1(@a);

 

 

9.6LEAVE

1）语法

leave label

用来退出带标签的语句块或者循环。用在 BEGIN ... END中或者循环中 (LOOP, REPEAT, WHILE)

label就是名字，用法如下

　　名字:LOOP   (9.5LOOP有示例)

　　名字:while

　　名字：repeat

　　名字：BEGIN

9.7ITERATE
1）语法

ITERATE  label;

只能出现在循环LOOP、REPEAT和WHILE 中(有标签)。跳出本次循环，进入一次新的循环

 

2）示例

CREATE  PROCEDURE loop_temp6(INOUT cust INTEGER)
BEGIN
        myloop:LOOP 
        SET cust=cust+1;
            IF cust<=98 THEN
                ITERATE myloop;
            ELSEIF cust> 98 AND cust<=100 THEN
                SELECT cust;
            ELSEIF cust>100 THEN
                LEAVE myloop;
            END IF;
        END LOOP;
    END

SET @a=95;
CALL loop_temp6(@a);