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;