MySQL存储过程的简单用法

情景1：使用存储过程做简单的四则运算

传入三个整形参数，求他们的和，用第三个参数作为返回

DELIMITER $$

USE `school`$$ !-- 选择表名

DROP PROCEDURE IF EXISTS `proc_2`$$ !-- 设置存储过程的名字

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_2`(a INT,b INT,INOUT c INT)
BEGIN
            SET c=a+b+c;
            
        END$$

DELIMITER ;

调用方法

SET @c  = 9; !--声明变量
CALL proc_2(20,30,@c);
SELECT @c AS result;

返回结果：一个单行单列的表，列名叫result

除此以外，直接用select 后面也可以跟四则运算，如下

SET @c = 0;
SET @b = 2;
SET @a = 1;
SELECT @a+@b+@c AS result;

情景2：把查表后得到的数据返回，具有极大的灵活性，查询多次后进行四则运算

例如求老师表和学生表的总行数之和

DELIMITER $$

USE `school`$$

DROP PROCEDURE IF EXISTS `proc1`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`(OUT c INT)
BEGIN			DECLARE a INT ; !-- 记录老师的数量
			DECLARE b INT ; !-- 记录学生的数量
			SELECT COUNT(*)INTO a  FROM teachers ; !--把查询结果放到变量里
			SELECT COUNT(*)INTO b  FROM students;
			SET c = a+b;
		END$$

DELIMITER ;

调用方法

SET @c = 0;
CALL proc1(@c);
SELECT @c AS result;

除此以外，还可以直接由select进行相加

SELECT (SELECT COUNT(*) FROM teachers)+(SELECT COUNT(*)FROM students) AS result;

很可惜，使用select直接进行相加，每个子表只能是单行单列

情景3：循环操作

例如输入一个数，把这个数循环加一后输出

DELIMITER $$

USE `school`$$

DROP PROCEDURE IF EXISTS `procloop`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `procloop`(INOUT a INT) !--如果没有INOUT修饰，那么不会把a修改后的值返回，调用结果仍然是初始值
BEGIN
            lp:LOOP
            SET a=a+1;        
            IF(a>6) THEN !--条件语句
            LEAVE lp;!-- 退出循环
            END IF;
            END LOOP;
        END$$

DELIMITER ;

调用方法：

SET @a = 1;
CALL procloop(@a);
SELECT @a;

另外还有一种循环语句repeat，可以不需要if语句，调用方法与上面相同

DELIMITER $$
DROP PROCEDURE IF EXISTS `school`.`procloop`$$
CREATE PROCEDURE `school`.`procloop`(INOUT a INT)
    	BEGIN
			w1:REPEAT
			SET a=a+1;
			UNTIL a>6 !-- repeate无需if语句
			END REPEAT;
		END$$

DELIMITER ;

还有一种do while语句，用法也差不多

DELIMITER $$
DROP PROCEDURE IF EXISTS `school`.`procloop`$$
CREATE PROCEDURE `school`.`procloop`(INOUT a INT)
    	BEGIN
			w1:WHILE(a<6) DO
			SET a=a+1;
			END WHILE;
	END$$

DELIMITER ;

情景4：多层if嵌套

传两个参数ab，一个参数c用于判断执行什么方法，如果c=0就取出ab当中较大的那个，如果c=1就计算ab的差值

DELIMITER $$
DROP PROCEDURE IF EXISTS `school`.`proc1`$$
CREATE PROCEDURE `school`.`proc1`(a INT,b INT,method INT,OUT c INT)
        BEGIN
        IF (method=0) THEN
            IF(a>b) THEN
            SET c=a;
            
            ELSE IF (a<b) THEN
            SET c=b;
            ELSE 
            SET c=b;
            END IF;
            END IF;
        
        ELSE IF(method=1)THEN
            IF(a>b) THEN
            SET c=a-b;
            
            ELSE IF (a<b) THEN
            SET c=b-a;
            ELSE 
            SET c=0;
            END IF;
            END IF;
        END IF;!-- else if也需要end if
        END IF;
    END$$

DELIMITER ;

情景5：存储过程的异常处理

DELIMITER $$

USE `school`$$

DROP PROCEDURE IF EXISTS `proc1`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`()
BEGIN
            
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
                BEGIN
                    DECLARE haha VARCHAR(20) ;
                    SET haha='出错啦';
                    INSERT INTO teachers(teacher_name,teacher_id)VALUES('出异常啦',NULL);
                    SELECT haha;
                END;
            !-- HANDLER下面是可能会出异常的语句
            INSERT INTO teachers(teacher_name,teacher_id)VALUES(1,2025); !-- 这里会由于主键重复而报异常，注意，如果数据格式错误不会出异常，如int型出错会插入0
        END$$

DELIMITER ;

调用方法：

CALL proc1();

如果出错，返回结果是单行单列的值：'出错啦'