mysql存储过程

1.存储过程之加法

drop PROCEDURE if EXISTS pr_add;
create PROCEDURE pr_add(a int, b int)
BEGIN 
	DECLARE c int;
	if a < 100 then set a = a + 1;
	end if;
	if b > 3 then set b = b + 3;
	end if;
	set c = a + b;
	select c as sum;
end

调用

call pr_add(12,20);

或者

set @a = 12;
set @b  = 32;
call pr_add(@a, @b);

2.存储过程之if

drop PROCEDURE if exists getBiggerOne;
create PROCEDURE getBiggerOne(a int, b int)
BEGIN
	IF a < b THEN
		SELECT b as bigger;
	ELSEIF a > b THEN
		SELECT a as bigger;
	ELSE
		SELECT b as bigger;
	END IF;
END

3.存储过程之case

drop PROCEDURE if exists getRange;
create PROCEDURE getRange(a int)
BEGIN
	DECLARE rangeStr varchar(100);
	case a
	when 0 then
		set rangeStr = '0';
	when 1 then
		set rangeStr = '1';
	when 2 then
		set rangeStr = '2';
	ELSE
		set rangestr = 'not 0 1 2';
	end case;
	SELECT rangeStr;
END

4.存储过程之while

drop PROCEDURE if exists sumn;
create PROCEDURE sumn(n int)
BEGIN
	DECLARE sum int;
	DECLARE i int;
	set sum = 0;
	set i = 0;
	while i<= n DO
		set sum = sum + i;
		set i = i + 1;
	end WHILE;
	SELECT sum;
END

5.存储过程之输入与输出（输入和输出分开）

drop PROCEDURE if exists sumn;
create PROCEDURE sumn(IN n int, OUT s int)
BEGIN
	DECLARE sum int;
	DECLARE i int;
	set sum = 0;
	set i = 0;
	while i<= n DO
		set sum = sum + i;
		set i = i + 1;
	end WHILE;
	set s = sum;
END

调用

call sumn(4,@s);
select @s;

6.存储过程之输入输出混用：

drop PROCEDURE if exists swapnum;
create PROCEDURE swapnum(INOUT a int, INOUT b int)
BEGIN
	DECLARE n int DEFAULT 0;
	set n = a;
	set a = b;
	set b = n;
END

调用

set @a = 4,@b = 3;
call swapnum(@a, @b);
SELECT @a,@b;