Mysql 存储过程

简介

 

语法

 

-- 创建存储过程
create procedure p1()
begin
    select * from student;
end;

-- 调用存储过程
call p1();

-- 查看存储过程  指定数据库
select * from information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'test';
show create procedure p1;

-- 删除存储过程
drop procedure if exists p1;

 在命令行因为；表示输入完成，但是存储过程还没有写完，所以创建时，需要更改结束符表示，等完毕后再更改回；

delimiter $$
create procedure p1()
begin
    select * from student;
end
$$
delimiter;

 变量

系统变量

-- 查看所有系统变量 不带标示默认是session
show variables;
show session variables;
show global variables;

-- 模糊查询
show variables like 'auto%';

-- 指定名称查询
select @@autocommit;
select @@session.autocommit;
select @@global.autocommit;

-- 设置系统参数
set session autocommit = 0;
set global autocommit = 0;
set @@autocommit = 0;
set @@session.autocommit = 0;
set @@global.autocommit = 0;

 无论设置是session或者global的系统变量，当mysql重启后，都是变成默认值，如果想永久修改，需要修改mysql配置文件 /etc/mysql/my.cnf

 用户变量

 创建用户变量

-- 创建用户变量 可以使用 '=' or ':='
set @myname = 'jay';
set @myage := 18, @myhobby := 'music';
-- 查询并定义
select @color := 'red';
-- 将一个查询结果给一个把变量赋值
select count(*) into @count from student;

查询用户变量

-- 查询用户变量
select @myname,@myage,@myhobby;
select @color;

局部变量

 使用关键字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

-- 定义一个存储过程
-- score >= 85 输出优秀
-- score >= 60 输出及格
-- score < 60 输出不及格
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 >= 85 输出优秀
-- score >= 60 输出及格
-- 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;

call p4(68, @result);
select @result;

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

set @score := 78;
call p5(@score);
select @score;

CASE

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(12);

循环

while

create procedure p7(IN n int)
begin
    declare sum int default 0;
    while n > 0
        do
            set sum := sum + n;
            set n := n - 1;
        end while;
    select sum;
end;

call p7(100);

repeat

 

create procedure p8(in n int)
begin
    declare sum int default 0;
    repeat
        set sum := sum + n;
        set n := n - 1;
    until n <= 0
    end repeat;
    select sum;
end;

call p8(100);

 Loop

 

 

create procedure p9(in n int)
begin
    declare sum int default 0;
    total:loop
        if n <= 0 then
            leave total;
        end if;
        set sum := sum + n;
        set n := n - 1;

    end loop total;
    select sum;
end;

call p9(100);

create procedure p10(in n int)
begin
    declare sum int default 0;
    total:loop
        if (n % 2) = 1 then
            set n := n - 1;
            iterate total;
        end if;
        if n <= 0 then
            leave total;
        end if;

        set sum := sum + n;
        set n := n - 2;
    end loop total;
    select sum;
end;

call p10(100);

 游标

-- 根据传入的年龄，查询到符合条件的name和profession
-- 并创建一个新表tb_user_pro有三个字段把查出来的name，profession插入其中
create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    -- 游标的创建需要再局部变量的后面
    declare u_cursor cursor for select * from tb_user where age < uage;
    -- 设置异常处理机制 当异常状态码为'02000'时执行exit操作，并且执行关闭游标的操作
    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;
    -- 循环条件为true,当游标没有区不到内容时，会报错，状态码02000
    while true do
        fetch u_cursor into uname,upro;
        insert into tb_user_pro values (null,uname,upro);
    end while;
    close u_cursor;
end;

 根据条件处理程序，可以把状态码的判断换成not found

 

-- 根据传入的年龄，查询到符合条件的name和profession
-- 并创建一个新表tb_user_pro有三个字段把查出来的name，profession插入其中
create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    -- 游标的创建需要再局部变量的后面
    declare u_cursor cursor for select * from tb_user where age < uage;
    -- 设置异常处理机制 当获取不到数据时时执行exit操作，并且执行关闭游标的操作
    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;
    -- 循环条件为true,当游标没有区不到内容时，会报错，状态码02000 
    while true do
        fetch u_cursor into uname,upro;
        insert into tb_user_pro values (null,uname,upro);
    end while;
    close u_cursor;
end;