code学习

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

① 创建存储过程声明局部变量时不加 @​

​(如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型参数,值将会发生改变。

⑦ 用户变量

  • @param形式;
  • set定义和赋值;
  • 作用域范围为全局;
  • 用户变量名对大小写不敏感。

设置用户变量的一个途径是执行SET语句:

SET @var_name = expr [, @var_name = expr] ...

对于SET,可以使用=或:=作为赋值符号。

分配给每个变量的expr可以为整数、实数、字符串或者NULL值。      

可以先在用户变量中保存值然后在以后引用它,这样可以将值从一个语句传递到另一个语句。

用户变量与连接有关(会话级别)。

也就是说,一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时,该客户端连接的所有变量将自动释放。

存储过程中使用用户变量(不建议):

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      

测试如下图:

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

⑧ 调用存储过程和函数方式不同

select function_name();-- 调用函数
call procedure_name();-- 调用存储过程      

点击查看Mysql 存储过程讲解:

​​存储过程详解​​ 【javascript:void(0)】

​​存储过程控制语句小结​​ 【javascript:void(0)】

【1】无参数

CREATE DEFINER=`root`@`localhost` PROCEDURE `indentity`()
BEGIN
  #Routine body goes here...
  SELECT COUNT(*)+1 as count FROM c_user;
END      
  • result as follows :
MySQL中不同类型参数存储过程示例

【2】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 :
MySQL中不同类型参数存储过程示例

如图所示,虽然存储过程中的 id值改变了。但并不影响变量 @id 的值。

如果在存储过程内部改变了@id值,如下:

set @id :=100;      

那么,无论在存储过程内部还是外部查看该变量值,都将改变:

select @id;

-- 结果100。      

【3】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 :
MySQL中不同类型参数存储过程示例

如图所示,传入的参数 != 10,则id将在默认值下[20] +1=21 ;

【4】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 :
MySQL中不同类型参数存储过程示例
  • test code:
set @id = 0;
CALL proc1(@id);
select @id;      

如图所示,out 型参数会改变 传入的参数值( @id )。

即使在存储过程中重新给@id赋值,如下:

set @id := 100;      

那么存储过程结束后,查看@id :

select @id;

--结果 : 14      

【5】同时拥有 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 :
MySQL中不同类型参数存储过程示例

【6】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 :
MySQL中不同类型参数存储过程示例

如图所示,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型参数最终值      

继续阅读