Mysql存储过程

存储过程: 是一组SQL语句集, 可以实现一些比较复杂的逻辑功能, 其实和触发器有点类似, 都可以是一组SQL集, 但是存储过程是主动调用, 且功能比触发器更加强大. 触发器是某事件触发后自动调用.

存储过程的变量定义(ps:最好是在定义前加上delimiter //, 起到过程定义分隔符的作用, mysql中默认的分隔符是; ):
INOUT 既可以是输入也可以是输出函数

CREATE PROCEDURE pro_addr(IN a INT, IN b INT, OUT sum int)
BEGIN
DECLARE c int;
if a IS NULL THEN SET a =0;
END IF;
if b IS NULL THEN SET b = 0;
END if;
SET sum = a+b;
END;

存储过程的控制语句:
if语句:

CREATE PROCEDURE pro_if(IN type INT)
BEGIN
DECLARE c VARCHAR(100);
IF type=0 THEN
SET c = 'param is 0';
ELSEIF type=1 THEN
SET c= 'param is 1';
ELSE SET c = 'param is others, not 0 or 1';
END IF;
SELECT c;
END;

case语句:

CREATE PROCEDURE pro_case(IN type INT)
BEGIN
DECLARE c VARCHAR(100);
CASE type
WHEN 0 THEN
SET c = 'param is 0';
WHEN 1 THEN
SET c= 'param is 1';
ELSE 
SET c = 'param is others, not 0 or 1';
END CASE;
SELECT c;
END;

while语句:

CREATE PROCEDURE pro_while(IN 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;

执行SQL语句:

CREATE PROCEDURE pro_vendor()
BEGIN
SELECT sapId FROM ven_vendor;
END;