存储过程

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

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

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

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

3、输入\输出参数INOUT：既可以充当输入参数，也可以充当输出参数。

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

局部变量：

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 a = 1;
set a := 1;
set a = 1,b := a;

leave语句

mysql> create procedure test (in a integer)
    -> block : begin
    -> leave block;
    -> end//

iterate语句

进入一个循环。

call语句

调用存储过程。

if-esle语句

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

while 语句

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

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

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

mysql> create procedure test (out b integer)
    -> begin
    -> select count(*) into b from student;
    -> end//

mysql> set @b = 0//
mysql> call test(@b)//
mysql> select @b//
+------+
| @b   |
+------+
|    4 |
+------+

mysql> create procedure test (out b integer)
    -> begin
    -> select stu_id into b from student;
    -> end//

mysql> call test(@b)//
ERROR 1172 (42000): Result consisted of more than one row

怎么办？

使用游标访问多行数据

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

drop 语句

存储函数

区别：

格式：

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)

mysql> select * from student//
+------+---------+------+-------+
| name | address | sid  | score |
+------+---------+------+-------+
| zh   | beijing |    1 |    70 |
+------+---------+------+-------+
1 row in set (0.00 sec)

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)

mysql> select dd(1)//
+-------+
| dd(1) |
+-------+
|    70 |
+-------+
1 row in set (0.00 sec)

mysql存储过程与函数,古老的榕树,5-wow.com