mysql存储过程

1、存储过程定义

mysql> delimiter //
mysql> create procedure proc_name (in param integer)
    -> begin
    -> declare variable varchar(20);
    -> if param=1  then
    -> set variable='mysql';
    -> else
    -> set variable='sqlserver';
    -> end if;
    -> select variable;
    -> end;
    -> //
Query OK, 0 rows affected (0.17 sec)

2、if用法：

delimiter //
create procedure example_if(in x int)
begin 
if x=1 then
select 1;
elseif x=2 then
select 2;
else
select 3;
end if;
end
//

mysql> delimiter //
mysql> create procedure example_if(in x int)
    -> begin
    -> if x=1 then
    -> select 1;
    -> elseif x=2 then
    -> select 2;
    -> else
    -> select 3;
    -> end if;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call example_if(2)//
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

3、case 用法

delimiter //
create procedure example_case(in x int)
begin 
case x
when 1 then select 1;
when x=2 then select 2;
else select 3;
end case;
end
//

测试：

mysql> delimiter //
mysql> create procedure example_case(in x int)
    -> begin
    -> case x
    -> when 1 then select 1;
    -> when x=2 then select 2;
    -> else select 3;
    -> end case;
    -> end
    -> //
Query OK, 0 rows affected (0.03 sec)


mysql> call example_case(3)//
+---+
| 3 |
+---+
| 3 |
+---+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.01 sec)

4、while用法

delimiter //
create procedure example_while(out sum int)
begin
declare i int default 1;
declare s int default 0;
while i <= 100 do
set s=s+i;
set i=i+1;
end while;
set sum=s;
end
//

测试：

mysql> create procedure example_while(out sum int)
    -> begin
    -> declare i int default 1;
    -> declare s int default 0;
    -> while i <= 100 do
    -> set s=s+i;
    -> set i=i+1;
    -> end while;
    -> set sum=s;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

调用：

mysql> call example_while(@s)//

mysql> select @s//
+------+
| @s   |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)

5、调用存储过程的语法为 call procedure_name（parameter）

调用存储函数的语法为： select function_name（parameter），函数调用示例

mysql> create function name_of_person(st_id int)
    ->  returns varchar(20)
    ->  begin
    ->  return (select name from workmates where id=st_id);
    ->  end
    ->  //
Query OK, 0 rows affected (0.03 sec)

mysql> select name_of_person(1)//
+-------------------+
| name_of_person(1) |
+-------------------+
| James             |
+-------------------+
1 row in set (0.03 sec)

6、查看存储过程和函数的状态语法

show procedure|function  status like ‘[ proc_name|func_name  ]’

查看创建状态：

show create procedure|function  ‘[ proc_name|func_name  ]’

修改存储过程和函数：

alter procedure|function {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER} | COMMENT ‘string’

删除存储过程和函数

DROP {PROCEDURE | FUNCTION}  [IF EXISTS] sp_name

捕获存储过程中的错误

DECLARE condition_name CONDITION FOR condition_value

condition_value：

SQLSTATE[VALUE] sqlstatus_value | mysql_error_code

示例：

DECLARE can_not_find CONDITION FOR SQLSTATE '42S02';

DECLARE can_not_find CONDITION FOR 1146;

7、光标，使用顺序：声明光标、打开光标、使用光标、关闭光标

 DECLARE cursor_of_workmate CURSOR FOR 
 select id,name,age from workmates where id =1;

 OPEN cursor_of_workmate;

 FETCH cursor_of_workmate INTO temp_id, temp_name, temp_age;

 CLOSE cursor_of_workmate;