存储过程

创建

create procedure 存储过程名称([参数列表])
begin
    -- sql语句
end;

调用

call 名称([参数]);

查看

-- 查询指定数据库的存储过程及状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='数据库名';

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

删除

drop procedure [if exists] 存储过程名称;

通过命令行定义存储过程时，通过delimiter设置sql语句的结束符

delimiter $$ -- 设置了$$后，分号就不能当作结束符了，需要使用$$
-- 然后接下来再写存储过程
-- 写完后可以再改回分号

示例

create table student (
    name varchar(10) comment '姓名',
    id int comment '学号' primary key auto_increment,
    class int comment '班级'
) comment '学生表', default charset 'utf8';

insert into student (name, class)values ('张三' , 1), ('李四', 2), ('王五', 3), ('赵六', 1);

show variables like 'character%';
create procedure p1()
begin
    select count(*) from student;


end;

call p1();

drop procedure p1;

变量

• 系统变量

show global variables;;
select @@global.autocommit;
set @@global.autocommit = 0;
set global autocommit = 1;

• 用户变量

set @myname = 'itcast';
set @mygender := '男', @myhobby := 'java'; -- 相比于=，推荐使用:=

select @myname, @mygender, @myhobby;

select @mycolor := 'red';

select count(*) into @mycnt from student; -- 将count值赋给mycnt变量

 　　用户自定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL

• 局部变量

只在存储过程中生效，需要declare声明

• • 声明

declare 变量名 变量类型[default···]; -- 如果有默认值，可以通过default赋值
-- 变量类型就是数据库字段类型

• • 赋值

set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名;

• • 查看

select 变量名;

• • 示例

create procedure p2()
begin
    declare stu_count int;
    declare stu_count2 int default 0;

    set stu_count := 1;
    select count(*) into stu_count2 from student;
    select stu_count, stu_count2;
end;

call p2();

 

分支语法

if 条件 then

elseif 条件 then

else

endif;

• 示例

create procedure p3()
begin
    declare stu_score int default 0;
    select score into stu_score from student where name = '张三';
    if stu_score >= 85 then
        update student set degree = '优秀' where name = '张三';
    elseif stu_score >= 60 then
        update student set degree = '及格' where name = '张三';
    else
        update student set degree = '不及格' where name = '张三';
    end if;
end;

call p3();

 

带参数的procedure

•  示例

create procedure p4(in stu_score int, out stu_degree varchar(10))
begin

    if stu_score >= 85 then
        set stu_degree := 'perfect';
    elseif stu_score >= 60 then
        set stu_degree := 'ok';
    else
        set stu_degree := 'not ok';
    end if;
end;

drop procedure p4;


select score into @stu_score from student where name = '李四';
call p4(@stu_score, @stu_degree);
select @stu_degree;

选择分支

 

循环

 

 

• while循环　　

while 条件 do

end while;

• repeat

repeat
    -- sql逻辑
    
    until 条件
end repeat;

其实就是c++中的do while

• loop

　　

-- leave 退出循环，相当于c++中的break
-- iterate 跳过当前循环，进行下一次循环，相当于c++中的continue


[label:] loop
    -- sql逻辑
　　-- leave label
　　-- iterate label
　　-- 都配合label使用，lebel相当于loop的名称

end loop [label];

 

存储查询结果集

• 游标

•  条件处理程序

satement：表示处理结束时，可以顺便进行的操作，如退出fetch游标的循环时，可以 close 游标

• 示例

create procedure p5(IN uscore int)
begin
    -- fetch into 的变量应该在游标声明之前
    declare uname varchar(10) ;
    declare uclass int;

    declare result cursor for select name, class from student where score >= uscore;
    -- 定义一个handler，用于跳出fetch cursor的死循环
    declare exit handler for not found close result;

    drop table if exists course;
    create table if not exists course (
        name varchar(10) comment '姓名' primary key ,
        class int comment '选课'
    ) comment '选课表', default CHARSET = utf8;

    open result;
    while TRUE do
        fetch result into uname, uclass;
        insert into course values(uname, uclass);
        end while;

end;

drop procedure p5;
call p5(60);

 

存储函数

有返回值的存储过程

 其中type为返回值类型，characteristic为返回值说明