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

call proc1('tom');-- 只有一个in型参数,传值进入
call proc2('tom',@result);--result为out(inout)型参数
select @result;-- 查看返回结果
# 如果@result提前赋值，将会被清空后再传入存储过程内部。

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();-- 调用存储过程

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 ;   

CREATE DEFINER=`root`@`localhost` PROCEDURE `indentity`()
BEGIN
  #Routine body goes here...
  SELECT COUNT(*)+1 as count FROM c_user;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `IDENTITY`(in id INT)
BEGIN
select id; 
-- 这里将传进来的参数先进行了查询，前提是参数必须已经赋值(默认为null)
SELECT COUNT(*)+1 FROM c_user  INTO id ;
select id;
END

set @id = 0; -- -- 参数已经赋值(默认为null)
CALL IDENTITY(@id);
select @id;

set @id :=100;

select @id;
-- 结果100。

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

SET @param1 = 11;
call proc3(@param1);
select @param1;

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

set @id = 0;
CALL proc1(@id);
select @id;

set @id := 100;

select @id;
--结果 : 14

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

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

PROCEDURE `proc_1`(IN `v_1` varchar(30),IN `v_2` bigint,OUT `o_result` int(4))

call proc_name('tom',1001,@result);
select @result; -- 查看out型参数最终值