mysql存储过程与函数

mysql存储过程与函数

存储过程

存储过程是一段代码,由存储在一个数据库的目录中、声明式的和过程式的sql语句组成,可以从一个程序、触发器或者另一个存储过程调用它从而激活它。

每个存储过程包含至少3部分:一个参数列表、一个存储过程、一个名字。

一个数据库中的存储过程的名字必须是唯一的,就像表的名字一样。

一个参数列表可以有0个、1个或多个参数,通过这些参数,过程就可以和外界联系。

存储过程支持3中参数类型:

1、输入参数IN:数据可以传递到存储过程;

2、输出参数OUT:数据可以由存储过程传到外界;

3、输入输出参数INOUT:既可以充当输入参数,也可以充当输出参数。

就像c语言函数一样,即使没有参数,过程名后面还是需要跟一对括号。

存储过程以begin开始end结束,且之间还可以嵌套begin-end块。

局部变量:

declare 变量列表 变量类型 [default 默认值]

存储过程不仅可以使用局部变量,还可以使用全局变量。

默认值不仅限于直接量,还可以是符合表达式,也可以是标量子查询。

?

1

2

3

4

5

6

7

8

mysql> delimiter //

mysql> create procedure test (in a integer)

-> begin

-> declare b integer default

-> (select count(*) from student );

-> end

-> //

Query OK, 0 rows affected (0.42 sec)

set语句

set用于给一个变量赋值。如:

?

1

2

3

set a = 1;

set a := 1;

set a = 1,b := a;

leave语句

离开一个块(循环块或者语句块),类似于break;

如下,进入begin后立即离开。

?

1

2

3

4

mysql> create procedure test (in a integer)

-> block : begin

-> leave block;

-> end//

iterate语句

进入一个循环。

call语句

调用存储过程。

if-esle语句

格式:

if 条件 then 语句 ;

elseif 条件 then 语句;

esle 语句;

end if

?

1

2

3

4

5

6

7

8

9

mysql> create procedure test (in a integer)

-> begin

-> declare b integer;

-> if a < 60 then set b = -1;

-> elseif a >60 then set b = 1;

-> else set b = 0;

-> end if;

-> end

-> //

case语句

格式:

case

when 条件 then 语句;

when 条件 then 语句;

else 语句;

end case;

while 语句

格式:

while 条件 do

语句;

end while;

?

1

2

3

4

5

6

7

mysql> create procedure test (in a integer)

-> begin

-> declare b integer default 1;

-> while b < a do

-> set b = b + 1;

-> end while;

-> end//

repeat语句

格式:

repeat

语句;

until 条件 end repeat;

?

1

2

3

4

5

6

7

mysql> create procedure test (in a integer)

-> begin

-> declare b integer default 1;

-> repeat

-> set b = b + 1;

-> until b > a end repeat;

-> end//

loop语句

格式:

loop

if或case条件 leave loop;

语句;

end loop;

?

1

2

3

4

5

6

7

8

9

mysql> create procedure test (in a integer)

-> begin

-> declare b integer default 1;

-> loop_block: loop

-> if b > a then leave loop_block;

-> end if;

-> set b = b + 1;

-> end loop;

-> end//

select into 语句

用于将select的查询结果赋值给过程内的变量。

?

1

2

3

4

mysql> create procedure test (out b integer)

-> begin

-> select count(*) into b from student;

-> end//

现在student内有4条数据,调用test如下:

?

1

2

3

4

5

6

7

8

mysql> set @b = 0//

mysql> call test(@b)//

mysql> select @b//

+------+

@b |

+------+

4 |

+------+

如果select语句查询的结果包含有多行,直接使用into赋值时不可行的。比如:

?

1

2

3

4

mysql> create procedure test (out b integer)

-> begin

-> select stu_id into b from student;

-> end//

虽然语法正确,但是在调用时报错:

?

1

2

mysql> call test(@b)//

ERROR 1172 (42000): Result consisted of more than one row

怎么办?

使用游标访问多行数据

使用游标涉及到4个语句:

declare cursor(声明游标)、open cursor、fetch cursor(获取一行数据)、close cursor。

格式:

declare 游标名字 cursor for 表查询语句

如下:统计student表有多少行数据。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

mysql> create procedure test (out a integer)

-> begin

-> declare found boolean default true;--found 用于 判断是否到最后一行

-> declare b integer;

-> declare num cursor for select stu_id from student; --定义游标

-> declare continue handler for not found set found = false;

-> set a = 0;

-> open num;--打开游标

-> fetch num into b;--读取一行

-> while found do

-> set a = a+1;

-> fetch num into b;

-> end while;

-> close num;--关闭游标

-> end//

declare handler语句

存储过程在执行时可能会出现错误,declare handler语句涌来解决当出现错误时应该怎么做。

格式:

declare handler for <条件> 处理办法

包括:continue、exit、undo

<条件>包括:、sqlwarning、not found、sqlexception

所以上面的“declare continue handler for not found set found = false;”就是当游标到达行尾是继续执行过程并且set found = false。

drop 语句

删除存储过程;

drop procedure [if exists] 过程名

存储函数

存储函数与存储过程很相似:都是由sql语句和过程式语句所组成的代码片段,可以从应用程序和sql语句调用。

区别:

1。存储函数可以拥有输入参数,但是不能拥有输出参数。存储函数本身就是输出参数。

2.存储函数的调用和调用熟悉的表两函数一样,不能使用一个call语句调用存储函数。

3.存储函数必须包含一个return语句。

格式:

create function 函数名(<参数列表>) return 返回类型

begin

函数体;

end

?

1

2

3

4

5

6

7

8

9

10

11

12

mysql> create function dd(ss char(20))

-> returns date

-> begin

-> return (date(ss));

-> end//

mysql> select dd('2012-12-12 12:12:12')//

+---------------------------+

dd('2012-12-12 12:12:12') |

+---------------------------+

2012-12-12 |

+---------------------------+

1 row in set (0.00 sec)

?

1

2

3

4

5

6

7

mysql> select * from student//

+------+---------+------+-------+

name | address | sid | score |

+------+---------+------+-------+

zh | beijing | 1 | 70 |

+------+---------+------+-------+

1 row in set (0.00 sec)

?

1

2

3

4

5

mysql> create function dd(id int)

-> returns int

-> begin return (select score from student where id=id);

-> end//

Query OK, 0 rows affected (0.00 sec)

?

1

2

3

4

5

6

7

mysql> select dd(1)//

+-------+

dd(1) |

+-------+

70 |

+-------+

1 row in set (0.00 sec)