存储过程(mysql 难懂)

|-----什么是存储过程？

简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；

函数的普遍特性：模块化，封装，代码复用

|----语法结构

DELIMITER ;;

 DELIMITER ;

DELIMITER ;; CREATE PROCEDURE  ups_pro1(IN a INT,IN b INT,OUT SUM INT) BEGIN   IF a IS NULL THEN SET a=0;   END IF;   IF b IS NULL THEN SET b=10;   END IF;   #赋值   SET SUM=a+b; END ;; DELIMITER ;

|---调用  call 调用存储过程

#@s 就不需要定义，直接调用 #调用存储过程用call SET @b=5; CALL ups_pro1(2,@b,@s); SELECT @s AS SUM;

|------@变量 ： 接收存储过程的一个变量，这个变量是不需要定义！

|----select 字段 into(给)变量 from 表名;

#思考：如果调用数据表 DELIMITER ;; CREATE PROCEDURE ups_test_02(IN id INT,OUT question_r VARCHAR(500)) BEGIN          SELECT question INTO question_r FROM questioninfo WHERE questionid=id;      END ;; DELIMITER ;   CALL ups_test_02(1,@str); SELECT @str AS 题干;

|---if then elseif then else end if;

DELIMITER ;; CREATE PROCEDURE ups_pro2(IN tt INT) BEGIN          DECLARE c VARCHAR(200);          IF tt=0 THEN SET c='this is param is 0';          ELSEIF tt=1 THEN SET c='this is param is 1';          ELSE SET c='this is param not know';          END IF;          SELECT c; END ;; DELIMITER ;   CALL ups_pro2(1);

DELIMITER ;; CREATE PROCEDURE ups_test_05(IN id INT) BEGIN          DECLARE name_o VARCHAR(200);#定义的变量          IF(id=1) THEN                    SELECT question INTO name_o FROM questioninfo WHERE questionid=id;          ELSEIF(id=2) THEN                    SELECT question INTO name_o FROM questioninfo WHERE questionid=id;          ELSE                 SET name_o='不知道！';          END IF;          SELECT name_o; END ;; DELIMITER ;

DELIMITER ;; CREATE PROCEDURE ups_pro3(IN tt INT) BEGIN          DECLARE c VARCHAR(200);          CASE WHEN tt=0 THEN SET c='this is param is 0';               WHEN tt=1 THEN SET c='this is param is 1';          ELSE SET c='this is param not know';          END CASE;          SELECT c; END ;; DELIMITER ;   CALL ups_pro3(100);

|----while循环

DELIMITER ;; CREATE PROCEDURE ups_pro4(IN tt INT) BEGIN          DECLARE SUM INT;          DECLARE i INT;          SET SUM=0;          SET i=0;          WHILE i<tt DO               SET SUM=SUM+i;               SET i=i+1;          END WHILE;          SELECT SUM; END ;; DELIMITER ;