mysql存储过程与函数

您现在的位置：首页>教程>编程开发>mysql数据库 > mysql存储过程与函数 mysql存储过程与函数 感谢 3lian8 的投递 时间：2014-04-09 来源：三联教程 存储过程 存储过程是一段代码，由存储在一个数据库的目录中、声明式的和过程式的sql语句组成，可以从一个程

  您现在的位置：首页 > 教程 > 编程开发 > mysql数据库 > mysql存储过程与函数

mysql存储过程与函数

感谢 3lian8 的投递 时间：2014-04-09 来源：三联教程　

 　　存储过程

　　存储过程是一段代码，由存储在一个数据库的目录中、声明式的和过程式的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

    -> 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

    -> 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)

相关文章

标签:

[返回三联首页] [返回mysql数据库栏目] / [加入三联文集]