1.创建一个存储过程，计算100以内的所有整数之和。
DELIMITER $
CREATE PROCEDURE p2(OUT total INT)
COMMENT "计算1至100的总和"
   BEGIN   
             DECLARE num int DEFAULT 0;   
             SET total:=0;   
           WHILE num<100 DO    
                    SET num:=num+1;     
                    SET total:=total+num;   
          END WHILE;
  END $
DELIMITER ;

调用存储过程
CALL p2(@sum);
SELECT @sum;


2.创建一个存储过程，比较两位读者年龄的大小。
DELIMITER $
CREATE PROCEDURE compare_age
(OUT age1 INTEGER,OUT age2 INTEGER,IN name1 VARCHAR(50),
IN name2 VARCHAR(50),OUT result CHAR(20))
   BEGIN
      SELECT YEAR(CURDATE())-YEAR(reader_birthday) INTO age1 
      FROM t_reader WHERE reader_name=name1;
      SELECT YEAR(CURDATE())-YEAR(reader_birthday) INTO age2 
      FROM t_reader WHERE reader_name=name2;
      IF age1>age2 THEN           
            SET result=CONCAT(name1, "的年龄大于",name2);           
      ELSE IF age1=age2 THEN                  
            SET result=CONCAT(n1, "与",n2, "同岁");              
            ELSE          
                 SET result=CONCAT(name1, "的年龄小于",name2);      
            END IF; 
      END IF; 
  END $
DELIMITER ;

调用存储过程
CALL compare_age(@age1,@age2,'肖华','郑琴',@result);
SELECT @result;



3.创建一个存储过程，判断书的价格。
DELIMITER $
CREATE PROCEDURE proc_price
( IN bname VARCHAR(50),OUT price float,OUT result VARCHAR(10))
  BEGIN
        SELECt book_price INTO price 
    FROM t_book 
    WHERE book_name=bname; 
        CASE  
             WHEN price>=100 THEN SET result="昂贵";
             WHEN price<=50 THEN SET result="便宜";
              WHEN price is NULL THEN SET result="无此书籍";
       ELSE SET result="中等";
     END CASE;
   END $
DELIMITER ;

调用存储过程
CALL proc_price('云计算应用技术',@price,@b);
SELECT @price,@b);



