MySQL存储过程的简单用法

女爷i 2022-08-10 10:57 174阅读 0赞

情景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();

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