【1】前言变量
① 创建存储过程声明局部变量时不加 @(如DECLARE id int unsigned DEFAULT 20 (非用户变量)）;
② 但是无论从Mysql客户端下或者Navicat for MySQL ,使用用户变量参数(用户变量)要加 @ 符号如call proc1(@x);。
③ 调用存储过程使用call procedure(param)；
navicat for mysql 工具下调用：
call proc1('tom');-- 只有一个in型参数,传值进入 call proc2('tom',@result);--result为out(inout)型参数 select @result;-- 查看返回结果 # 如果@result提前赋值，将会被清空后再传入存储过程内部。
④ 存储过程内部查看变量值(navicat 或dos下)；
select @param(用户变量);
select param(局部变量)；
用户变量作用域范围为全局，局部变量只在过程内容可用。
需要说明的是，在触发器或者函数中，不能使用select 形式查看变量值,会提示Not allowed to return a result set from a function[trigger]
⑤ 如果变量未提前赋值，那么默认值为null ,存储过程支持随时随地select查看变量值(这点函数和触发器不具备)。⑥ 如果存储过程中使用@类型变量，表明其使用用户变量，那么调用存储过程时需要为用户变量赋值，默认为null。如果用户变量作为out或者inout型参数，值将会发生改变。
存储过程中使用用户变量(不建议)：
create procedure proc_test(out s int(11)) BEGIN SELECT COUNT(*) INTO s FROM c_user; set @c= s+1; IF @c >13 THEN select s,@c, @c+10; END if; END
更建议如下方式采用局部变量：
create procedure proc_test(out s int(11)) BEGIN declare c int default 0; -- 在此统一声明需要用到的参数; SELECT COUNT(*) INTO s FROM c_user; set c= s+1; IF c >13 THEN select s,c, c+10; END if; END
测试如下图：
⑧ 调用存储过程和函数方式不同
select function_name();-- 调用函数 call procedure_name();-- 调用存储过程
需要额外注意的是，在编写存储过程创建语句的时候，记得要修改MySQL语句控制符 ; 为其他，并在末尾进行恢复，实例如下。
DELIMITER $ create procedure proc_test(out s int(11)) BEGIN declare c int default 0; -- 在此统一声明需要用到的参数; SELECT COUNT(*) INTO s FROM c_user; set c= s+1; IF c >13 THEN select s,c, c+10; END if; END $ DELIMITER ;
【2】不同情况实例
① 无参数
CREATE DEFINER=`root`@`localhost` PROCEDURE `indentity`() BEGIN #Routine body goes here... SELECT COUNT(*)+1 as count FROM c_user; END
result as follows :
② in 型参数
- in型参数不会改变传入的变量值 ;
CREATE DEFINER=`root`@`localhost` PROCEDURE `IDENTITY`(in id INT) BEGIN select id; -- 这里将传进来的参数先进行了查询，前提是参数必须已经赋值(默认为null) SELECT COUNT(*)+1 FROM c_user INTO id ; select id; END
- test code :
set @id = 0; -- -- 参数已经赋值(默认为null) CALL IDENTITY(@id); select @id;
- result as follows :
如图所示，虽然存储过程中的 id值改变了。但并不影响变量 @id 的值。
如果在存储过程内部改变了@id值，如下：
set @id :=100;
那么，无论在存储过程内部还是外部查看该变量值，都将改变：
select @id; -- 结果100。
③ in 型参数和变量的使用
- 数据从外部传入内部使用(值传递)，可以是数值也可以是变量；
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(in param1 INTEGER) begin DECLARE id int unsigned DEFAULT 20; --如果id不声明，下面使用的时候将提示未知系统变量。 IF param1=10 THEN set id = param1; ELSE set id=id+1; SELECT id; end if; end
- test code :
【注意】：
sql创建的时候变量不加 @ ；但是无论从Mysql客户端下或者Navicat for MySQL ,使用的时候参数(变量)要加 @ 符号。
SET @param1 = 11; call proc3(@param1); select @param1;
- result as follows ：
如图所示，传入的参数 ！= 10，则id将在默认值下[20] +1=21 ;
④ out 型参数
- 只允许过程内部使用(不用外部数据)，给外部使用(引用传递：外部的数据会先被清空才会进入内部)；
- 只能是变量(navicat或dos下调用存储过程，如果mybatis中调用存储过程，赋值不起作用)。
- out型参数会在存储过程结束后，将对应的局部变量值重新返回给传入的用户变量。
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`(OUT s int) BEGIN select s;-- 会先清空传入的变量值，此时显示为null SELECT COUNT(*) INTO s FROM c_user; set s = s+1; select s;-- 将此值重新赋值给@id; END
- result as follows ：
- test code:
set @id = 0; CALL proc1(@id); select @id;
如图所示，out 型参数会改变 传入的参数值( @id )。
即使在存储过程中重新给@id赋值，如下：
set @id := 100;
那么存储过程结束后，查看@id :
select @id; --结果 : 14
⑤ 同时拥有 in 和 out 型参数
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user_count`(in sex_id INT,OUT user_count INT) BEGIN IF sex_id = 0 THEN SELECT COUNT(*) FROM p_user WHERE p_user.sex="女" into user_count; ELSE SELECT COUNT(*) FROM p_user WHERE p_user.sex="男" INTO user_count; END IF; END
- result as follows :
⑥ inout 型参数
- 只能是变量；
- 传入内部时值保留只用；
- 存储过程结束后将对应局部变量值重新返回给传入的用户变量；
CREATE DEFINER=`root`@`localhost` PROCEDURE `pinout`(INOUT `pinout` int) BEGIN #Routine body goes here... IF pinout = 0 THEN SELECT COUNT(*) FROM c_user into pinout; ELSE set pinout = pinout+1; END IF; END
- result as follows ：
如图所示，inout 型参数会改变传入的参数（@pinoutt）值。
【Tips：】
当在DOS或者navicat下测试存储过程时，可直接传入参数进行测试：
- 存储过程定义
PROCEDURE `proc_1`(IN `v_1` varchar(30),IN `v_2` bigint,OUT `o_result` int(4))
- test
call proc_name('tom',1001,@result); select @result; -- 查看out型参数最终值
