MySQL 存储过程，语法+示例，超详细！！_mysql存储过程写法案例

存储过程

语法：
	创建
        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 存储过程名称; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

用户变量

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

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

局部变量

定义：局部变量是根据需要定义的在局部生效的变量，访问之前，需要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
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

循环

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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

游标

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

声明游标：
		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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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