MYSQL存储过程--函数

介绍

基本语法

1.创建

create procedure 名字([参数列表])
begin 
            
   --SQL语句
end;

2.调用

call 名称 ([参数])

3.查看

select * from information_schema,routines where routine_schema =‘xxx’;
-- 查询指定数据库的存储过程及状态信息

show create procedure 名称; -- 查询某个存储过程的定义

4.删除

drop procedure [id exists] 名称;

演示示例：

-- 存储过程基本语法
-- 创建
create procedure p1()
begin
select count(*) from student;
end;
-- 调用
call p1();
-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
show create procedure p1;
-- 删除
drop procedure if exists p1;

变量

在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量

系统变量

设置系统变量

演示示例：

-- 查看系统变量

show session variables ;

show session variables like 'auto%';

show global variables like 'auto%';

select @@global.autocommit;

select @@session.autocommit;


-- 设置系统变量

set session autocommit = 1;

insert into course(id, name) VALUES (6, 'ES');

set global autocommit = 0;

select @@global.autocommit;

用户定义变量

演示示例：

-- 赋值


set @myname = 'itcast';

set @myage := 10;

set @mygender := '男',@myhobby := 'java';

select @mycolor := 'red';

select count(*) into @mycount from tb_user;

-- 使用

select @myname,@myage,@mygender,@myhobby;

select @mycolor , @mycount;

select @abc;

局部变量

演示示例：

-- 声明局部变量 - declare
-- 赋值

create procedure p2()

begin

    declare stu_count int default 0;

    select count(*) into stu_count from student;

    select stu_count;

end;

call p2();

If

create procedure p3()
begin
    declare score int default 58;
    declare result varchar(10);


    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
        select result;
end;


call p3();

上述的需求我们虽然已经实现了，但是也存在一些问题，比如：score 分数我们是在存储过程中定义死的，而且最终计算出来的分数等级，我们也仅仅是最终查询展示出来而已。那么我们能不能，把score分数动态的传递进来，计算出来的分数等级是否可以作为返回值返回呢？
答案是肯定的，我们可以通过接下来所讲解的 参数 来解决上述的问题

参数

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
    -- SQL语句
END ;

2). 案例一
根据传入参数score，判定当前分数对应的分数等级，并返回。
score >= 85分，等级为优秀。
score >= 60分 且 score < 85分，等级为及格。
score < 60分，等级为不及格

create procedure p4(in score int, out result varchar(10))
begin
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;
-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);


select @result;

3). 案例二
将传入的200分制的分数，进行换算，换算成百分制，然后返回

create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;


set @score = 198;

call p5(@score);

select @score;

case

1). 介绍
case结构及作用，和我们在基础篇中所讲解的流程控制函数很类似。有两种语法格式

语法1

-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，
执行statement_list2， 否则就执行 statement_list


CASE case_value

    WHEN when_value1 THEN statement_list1

    [ WHEN when_value2 THEN statement_list2] ...

    [ ELSE statement_list ]

END CASE;

语法2

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
立时，执行statement_list2， 否则就执行 statement_list


CASE

    WHEN search_condition1 THEN statement_list1

    [WHEN search_condition2 THEN statement_list2] ...

    [ELSE statement_list]

END CASE;

案例

根据传入的月份，判定月份所属的季节（要求采用case结构）。
1-3月份，为第一季度
4-6月份，为第二季度
7-9月份，为第三季度
10-12月份，为第四季度

create procedure p6(in month int)

begin

    declare result varchar(10);

    case


        when month >= 1 and month <= 3 then

            set result := '第一季度';

        when month >= 4 and month <= 6 then

            set result := '第二季度';

        when month >= 7 and month <= 9 then

            set result := '第三季度';

        when month >= 10 and month <= 12 then

            set result := '第四季度';

    else

    set result := '非法参数';

    end case ;

    select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);

end;

call p6(16);

while

案例：

计算从1累加到n的值，n为传入的参数值。

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环


create procedure p7(in n int)

begin

    declare total int default 0;

    while n>0 do

        set total := total + n;

        set n := n - 1;

    end while;

    select total;

end;


call p7(100);

repeat

案例：

计算从1累加到n的值，n为传入的参数值。(使用repeat实现)

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环

create procedure p8(in n int)

begin

    declare total int default 0;

    repeat


        set total := total + n;

        set n := n - 1;

    until n <= 0

    end repeat;


    select total;

end;


call p8(10);


call p8(100);

loop

案例1

计算从1累加到n的值，n为传入的参数值.

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx

create procedure p9(in n int)

begin

    declare total int default 0;


    sum:loop

        if n<=0 then

            leave sum;

        end if;


        set total := total + n;

        set n := n - 1;

    end loop sum;


    select total;

end;



call p9(100);

案例2

计算从1到n之间的偶数累加的值，n为传入的参数值

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx

create procedure p10(in n int)

    begin

        declare total int default 0;

        sum:loop

            if n<=0 then

                leave sum;

            end if;

            if n%2 = 1 then

                set n := n - 1;

                iterate sum;

            end if;

        set total := total + n;

        set n := n - 1;

        end loop sum;



        select total;



end;



call p10(100);

游标

案例

根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名
（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表
(id,name,profession)中。

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

create procedure p11(in uage int)

begin

    declare uname varchar(100);

    declare upro varchar(100);

    declare u_cursor cursor for select name,profession from tb_user where age <=uage;

    drop table if exists tb_user_pro;

    create table if not exists tb_user_pro(

        id int primary key auto_increment,

        name varchar(100),

        profession varchar(100)


    );

    open u_cursor;

    while true do

        fetch u_cursor into uname,upro;

        insert into tb_user_pro values (null, uname, upro);

    end while;

    close u_cursor;

end;


call p11(30);

上述的存储过程，最终我们在调用的过程中，会报错，之所以报错是因为上面的while循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行但是此时，tb_user_pro表结构及其数据都已经插入成功了，我们可以直接刷新表结构，检查表结构中的数据。

上述的功能，虽然我们实现了，但是逻辑并不完善，而且程序执行完毕，获取不到数据，数据库还报错。 接下来，我们就需要来完成这个存储过程，并且解决这个问题。要想解决这个问题，就需要通过MySQL中提供的 条件处理程序 Handler 来解决

条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

2). 案例
我们继续来完成在上一小节提出的这个需求，并解决其中的问题。
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名
（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表
(id,name,profession)中。
A. 通过SQLSTATE指定具体的状态码

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

create procedure p11(in uage int)

begin

    declare uname varchar(100);

    declare upro varchar(100);

    declare u_cursor cursor for select name,profession from tb_user where age <=uage;

    -- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出

    declare exit handler for SQLSTATE '02000' close u_cursor;

    drop table if exists tb_user_pro;

    create table if not exists tb_user_pro(

        id int primary key auto_increment,

        name varchar(100),

        profession varchar(100)

    );

    open u_cursor;

    while true do

        fetch u_cursor into uname,upro;

        insert into tb_user_pro values (null, uname, upro);

    end while;

    close u_cursor;

end;






call p11(30);

通过SQLSTATE的代码简写方式 NOT FOUND
02 开头的状态码，代码简写为 NOT FOUND

create procedure p12(in uage int)

begin

    declare uname varchar(100);

    declare upro varchar(100);


    declare u_cursor cursor for select name,profession from tb_user where age <=uage;

        -- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02开头时，将关闭游标u_cursor，并退出

    declare exit handler for not found close u_cursor;

    drop table if exists tb_user_pro;

    create table if not exists tb_user_pro(

        id int primary key auto_increment,

        name varchar(100),

        profession varchar(100)

    );

    open u_cursor;

    while true do

        fetch u_cursor into uname,upro;

        insert into tb_user_pro values (null, uname, upro);

    end while;

    close u_cursor;

end;




call p12(30);

存储函数

1). 介绍
存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])

RETURNS type [characteristic ...]

BEGIN

    -- SQL语句

    RETURN ...;

END ;

characteristic说明：
DETERMINISTIC：相同的输入参数总是产生相同的结果
CREATE FUNCTION 存储函数名称 ([ 参数列表 ])

NO SQL ：不包含 SQL 语句。
READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。
2). 案例
计算从1累加到n的值，n为传入的参数值。
 

create function fun1(n int)

returns int deterministic

begin

    declare total int default 0;

    while n>0 do

        set total := total + n;

        set n := n - 1;

    end while;



    return total;

end;


select fun1(50);

在mysql8.0版本中binlog默认是开启的，一旦开启了，mysql就要求在定义存储过程时，需要指定
characteristic特性，否则就会报如下错误