SQL的存储过程，语法示例

存储过程

类型	含义
IN	作为输入，调用时需要传入值
OUT	作为输出，该参数作为返回值
INOUT	既可以输入参数，也可以输出参数

 

语法：
    创建
        create procedure 存储过程名称(
        in 参数名称1 参数数据类型，
        out 参数名称2 参数数据类型,
        inout 参数名称3 参数数据类型
        )
        begin
            ————SQL语句
        end;
    调用
        call 存储过程名称(参数1，@参数名称2)        # in和out
        
        set @参数名称2 = X;
        call 存储过程名称(@参数名称2);        #inout
        select @参数名称2;
    查看
        show create procedure 存储过程名称;
    删除
        drop procedure if exists 存储过程名称; 

用户变量

​ 定义：用户自定义的变量，即用户变量，用户变量不用提前声明，在使用的时候直接用@变量名使用即可。作用域为当前连接

    创建用户变量    
        set @变量名 = 值
        select 字段名 into @变量名 from 表名;    @把字段的值赋给用户变量
    使用用户变量
        select @变量名;

局部变量

定义：局部变量是根据需要定义的在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin…end块

声明：    
    declare 变量名 变量类型 [default x]; #default为默认值，此时默认值为x
赋值：
    set 变量名 = 值;
    select 字段名 into 变量名 from 表名....;
    
eg：
    create procedure p()
    begin
        declare stu_count int default 0;
        select count(*) into stu_count from student;
        select stu_count;
    end;
    call p();        #把count(*)的值赋给了stu_count

逻辑语句 if else

语法：
    if 条件1 then
        代码1
    elseif 条件2 then        #可选
        代码2
    else                #可选
        代码3
    end if;
    
    如果 符合条件1 则进入代码1，否则如果符合条件2则进入代码2，否则进入代码3
    
eg：
    create procedure p(
    in sc char(10),
    in sc2 char(10)
    )
    begin    
        declare result varchar(10);
        declare sum int;
        select grade into sum from score where sno = sc and cno = sc2; 
        if sum >= 85 then
            set result = '优秀';
        elseif sum>=60 then
            set result = '及格';
        else
            set result = '不及格';
        end if;
        select result;
    end;
    call p('2015001','c02');

分支函数 case

语法1：
    case 表达式
        when when_value1 then statement_list1
        when when_value2 then statement_list3
        ....
        else statement_list
语法2：
    case 
        when 表达式1 then 代码1
        when 表达式2 then 代码2
        .....
        else
            代码3
    end case;
eg:
    create PROCEDURE p2(
    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 p2(1);

循环

while

满足条件后，再次执行循环体中的sql语句

# 先判断条件，如果条件为true，则执行逻辑，否则，不执行
while 条件 do
    sql逻辑代码
end while;

eg:
    #计算从1累加到n的值，n为传入的参数
    create procedure p3(
    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 p3(3);

repeat

# 先判断条件，当满足条件时退出循环
#先执行一次逻辑，然后判定逻辑是否满足，如果满足则退出，否则继续
repeat
    sql逻辑代码
    until 条件
end repeat;

eg:
    create procedure p4(
    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 p4(3);

loop

loop实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现简单的死循环，loop可以配合以下两个语句使用

1. leave ：配合循环使用，退出循环
2. iterate ：必须用在循环中，作用和 C++的continue相同

   语法：
       标记名:loop
           SQL逻辑代码
       end loop 标记名;
       
       leave lable;   #退出指定标记的循环体
       iterate label;  #直接进入下一次循环
       
   eg:
        create procedure p5(
       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 p5(3);
      
      #如果加到奇数的话iterate
          create PROCEDURE p6(
       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 p6(3);

游标

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。

声明游标：
        declare 游标名 cursor for 查询语句;
打开游标：                #使用游标之前需要先打开游标
        open 游标名;
获取游标记录：
        fetch 游标名 into 变量;
关闭游标：
        close 游标名; 
        
eg:
    根据传入的参数age，查询students表中，所有学生出生日期小于age的用户姓名和专业，并将用户的姓名和专业插入到一个新表中
    思路：A：声明游标，B：创建表，C：开启游标，D：获取游标中的记录，E：插入数据到新表，F：关闭游标
    create PROCEDURE p7(
    in uage date            #传入出生日期
    )
    begin
        declare usname varchar(100);        #保存符合条件的学生信息的两个局部变量
        declare upro varchar(100);
        
        declare u_cursor cursor for         #声明游标，
        select sname,smajor from students where sbirthday<uage;    #找出 出生日期大于uage的

    #如果存在则删除，这样的话就保证表中只显示比uage大的学生信息
        drop table if exists tb_user;                                                                                                
        create table if not exists tb_user(        #创建表
            id int primary key auto_increment,    #id设置自增主键
            uname varchar(100),
            sdapt varchar(100)
        );
        open u_cursor;                #打开游标
        while true do                #进入while循环
            fetch u_cursor into usname,upro;     # 把游标中的记录存入两个局部变量
            insert into tb_user values(null,usname,upro);    #插入新表
        end while;
        close u_cursor;        #关闭游标
    end;
    call p7('1999-03-1');

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是in类型的。

语法：
    create function 存储函数名(参数列表)
    returns type [characteristic...]
    begin
        SQL语句
        return ...;
    end;
    
    characteristic说明：
                deterministic：相同的输入参数总是产生相同的结果
                no sql         ：不包含sql语句
                reads sql data： 包含读取数据的语句，但不包含写入数据的语句

eg：    
    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(10);

注意：存储函数必须要有返回值