一、 简介 存储过程(Stored Procedure)是一组为了完成特定功能的SQL数据库语句集,经编译后存储在网站数据库中。用户通过指定存储过程的名字并给出参数(假如如果该存储过程带有参数)来执行它。存储过程是网站数据库中的一个重要对象,任何一个设计良好的网站数据库应用程序都应该用到存储过程。 虽然经常用的术语是存储过程(stored procedure),但MySQL实际上实现了两中类型,除了存储过程外,还有存储函数(stored routine),它们统称为存储例程。 二、基本格式 1、存储过程 CREATE PROCEDURE 过程名 ([过程参数[,...]]) [特性 ...] 过程体 如创建: CEATE PROCEDURE p1 (a INT) SELECT a; 调用一下:CALL p1(8); 将显示: ------ | a | ------ | 8 | ------ 1 row in set (0.00 sec) 2、存储函数 CREATE FUNCTION 函数名 ([函数参数[,...]]) RETURNS 返回类型 [特性 ...] 函数体 如创建:CREATE FUNCTION f1 (x INT) RETURNS INT RETURN x; /* 过程函数一次只能返回一个值 调用一下:SELECT f1 (3); 将显示: ------- | f1(3) | ------- | 3 | ------- 1 row in set (0.00 sec) 3、过程参数 [ IN | OUT | INOUT ] 参数名 参数类型 4、函数参数 参数名 参数类型 5、返回类型 有效的MySQL数据类型即可 6、过程体/函数体格式如下 BEGIN 有效的SQL数据库语句 END 7、特性(一般不要求) LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' 8、存储过程中参数的IN,OUT,INOUT类型 存储过程可以接受输入参数,并把参数返回给调用方。不过,对于每个参数,需要声明其参数名、数据类型,还要指定此参数是用于向过程传递信息,还是从过程传回信息,或是二者兼有。 为声明参数的作用,使用如下3个关键字之一: ˜ IN : IN参数只用来向过程传递信息,为默认值。 ˜ OUT : OUT参数只用来从过程传回信息。 ˜ INOUT : INOUT参数可以向过程传递信息,假如如果值改变,则可再从过程外调用。 假如如果仅仅想把数据传给 MySQL 存储过程,那就使用“in” 类型参数;假如如果仅仅从 MySQL 存储过程返回值,那就使用“out” 类型参数;假如如果需要把数据传给 MySQL 存储过程,还要经过一些计算后再传回给咱们,此时,要使用“inout” 类型参数。 对于任何声明为OUT或INOUT的参数,,当调用存储过程时需要在参数名前加上@符号,这样该参数就可以在过程外调用了。 下面举三个实例:MySQL 存储过程 “in” 参数:跟 C 语言的函数参数的值传递类似, MySQL 存储过程内部可能会修改此参数,但对 in 类型参数的修改,对调用者(caller)来说是不可见的(not visible)。 create procedure pr_param_in( in id int -- in 类型的 MySQL 存储过程参数)begin if (id is not null) then set id = id 1; end if; select id as id_inner;end;set @id = 10; call pr_param_in(@id); select @id as id_out;mysql> call pr_param_in(@id); ---------- | id_inner | ---------- | 11 | ---------- mysql> select @id as id_out; -------- | id_out | -------- | 10 | -------- 可以看到:用户变量 @id 传入值为 10,执行存储过程后,在过程内部值为:11(id_inner),但外部变量值依旧为:10(id_out)。 MySQL 存储过程 “out” 参数:从存储过程内部传值给调用者。在存储过程内部,该参数初始值为 null,无论调用者是否给存储过程参数设置值。 create procedure pr_param_out( out id int)begin select id as id_inner_1; /* id 初始值为 null*/ if (id is not null) then set id = id 1; select id as id_inner_2; else select 1 into id; end if; select id as id_inner_3;end;set @id = 10; call pr_param_out(@id); select @id as id_out;mysql> set @id = 10;mysql>mysql> call pr_param_out(@id); /*外部变量不能传给存储过程*/ ------------ | id_inner_1 | ------------ | NULL | ------------ ------------ | id_inner_3 | ------------ | 1 | ------------ mysql> select @id as id_out; /*过程将改变的值传给外部变量*/ -------- | id_out | -------- | 1 | -------- 可以看出,虽然咱们设置了用户定义变量 @id 为 10,传递 @id 给存储过程后,在存储过程内部,id 的初始值总是 null(id_inner_1)。最后 id 值(id_out = 1)传回给调用者。 MySQL 存储过程 inout 参数跟 out 类似,都可以从存储过程内部传值给调用者。不同的是:调用者还可以通过 inout 参数传递值给存储过程。 drop procedure if exists pr_param_inout; create procedure pr_param_inout( inout id int)begin select id as id_inner_1; -- id 值为调用者传进来的值 if (id is not null) then set id = id 1; select id as id_inner_2; else select 1 into id; end if; select id as id_inner_3;end;set @id = 10; call pr_param_inout(@id); select @id as id_out;mysql> set @id = 10; mysql>mysql> call pr_param_inout(@id); ------------ | id_inner_1 | ------------ | 10 | ------------ ------------ | id_inner_2 | ------------ | 11 | ------------ ------------ | id_inner_3 | ------------ | 11 | ------------ mysql>mysql> select @id as id_out; -------- | id_out | -------- | 11 | -------- 从结果可以看出:咱们把 @id(10),传给存储过程后,存储过程最后又把计算结果值 11(id_inner_3)传回给调用者。 MySQL 存储过程 inout 参数的行为跟 C 语言函数中的引用传值类似。 通过以上例子:假如如果仅仅想把数据传给 MySQL 存储过程,那就使用“in” 类型参数;假如如果仅仅从 MySQL 存储过程返回值,那就使用“out” 类型参数;假如如果需要把数据传给 MySQL 存储过程,还要经过一些计算后再传回给咱们,此时,要使用“inout” 类型参数。 9、声明和设置变量 (1) 声明变量 在存储例程使用局部变量之前,必须声明局部变量,变量声明通过DECLARE数据库语句实现,其原型如下: DECLARE variable_name TYPE ; 如:DECLARE x VARChAR(254); 在声明变量时,声明必须放在BEGIN/END块中。此外声明必须在执行该块任何其它数据库语句之前进行。 (2) 设置变量 SET数据库语句用来设置声明的存储例程变量值。其原型如下: SET variable_name =value; 如下声明和设置变量过程: DECLARE x INT; SET x = 155; 也可使用SELECT…..INTO数据库语句设置变量。 如: SELECT 155 INTO x; 当然,此变量是声明该变量的BEGIN/END块作用范围内的一个局部变量。假如如果希望在存储例程外使用此变量,需要将其作为OUT变量传递。源码库详解MYSQL查询网站seo优化