MySQL中不同类型参数存储过程示例

关联博文：

存储过程中的控制语句
PLSQL存储过程
MySQL中不同类型参数存储过程示例
MyBatis调用存储过程

【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型参数最终值