Home >Database >Mysql Tutorial >MySQL存储过程传参之in,out,inout参数用法_MySQL

MySQL存储过程传参之in,out,inout参数用法_MySQL

WBOY
WBOYOriginal
2016-06-01 13:04:042129browse

存储过程传参:存储过程的括号里,可以声明参数。 语法是 create procedure p([in/out/inout] 参数名 参数类型 ..) in :给参数传入值,定义的参数就得到了值 out:模式定义的参数只能在过程体内部赋值,表示该参数可以将某个值传递回调用他的过程(在存储过程内部,该参数初始值为 null,无论调用者是否给存储过程参数设置值) inout:调用者还可以通过 inout 参数传递值给存储过程,也可以从存储过程内部传值给调用者
如果仅仅想把数据传给 MySQL 存储过程,那就使用“in” 类型参数; 如果仅仅从 MySQL 存储过程返回值,那就使用“out” 类型参数; 如果需要把数据传给 MySQL 存储过程,还要经过一些计算后再传回给我们,此时,要使用“inout” 类型参数。 MySQL 存储过程参数如果不显式指定"in"、"out"、"inout",则默认为"in"。
实例一:存储过程传参 in

DELIMITER $$
CREATE PROCEDURE p1(IN num INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE total INT DEFAULT 0;
  WHILE i<=num DO
    SET total := i + total;
    SET i := i+1;
  END WHILE;
  SELECT total;
END$$
\

实例二:存储过程传参 out
CREATE PROCEDURE p2(OUT num INT)
BEGIN
 SELECT num AS num_1;
 IF (num IS NOT NULL) THEN
 SET num = num + 1;
 SELECT num AS num_2;
 ELSE
 SELECT 1 INTO num;
 END IF;
 SELECT num AS num_3;
END$$
SET @num = 10$$
CALL p2(@num)$$
SELECT @num AS num_out$$
\

实例三:存储过程传参 inout
CREATE PROCEDURE p3(INOUT age INT)
BEGIN
  SET age := age + 20;
END$$
set @currage =18$$
call p3(@currage)$$
select @currage$$


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn