MySql-存储过程和函数

1 存储过程和函数的引入

2 创建存储过程和函数
（1）创建存储过程

DELIMITER &&
CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT)
   READS SQL DATA
   BEGIN 
     SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT;
   END 
   &&
DELIMITER ;

CALL pro_book(1,@total);//调用上面的存储过程

（2）创建存储函数

DELIMITER &&
CREATE FUNCTION func_book (bookId INT)
 RETURNS VARCHAR(20)
 BEGIN 
  RETURN ( SELECT bookName FROM t_book WHERE id=bookId );
 END 
    &&
DELIMITER ;

SELECT func_book(2);//调用上面的存储函数

（3）变量的使用
1.定义变量

DELIMITER &&
CREATE PROCEDURE pro_user()
	BEGIN
	 DECLARE a,b VARCHAR(20) ;
	 INSERT INTO t_user VALUES(NULL,a,b);
	END 
&&
DELIMITER ;

2.为变量赋值

DELIMITER &&
CREATE PROCEDURE pro_user2()
	BEGIN
	 DECLARE a,b VARCHAR(20) ;
	 SET a='java1234',b='123456';
	 INSERT INTO t_user VALUES(NULL,a,b);
	END 
&&
DELIMITER ;

DELIMITER &&
CREATE PROCEDURE pro_user3()
	BEGIN
	 DECLARE a,b VARCHAR(20) ;
	 SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;
	 INSERT INTO t_user VALUES(NULL,a,b);
	END 
&&
DELIMITER ;

（4）游标的使用

1.声明游标

2.打开游标

3.使用游标

4.关闭游标

DELIMITER &&
CREATE PROCEDURE pro_user4()
	BEGIN
	 DECLARE a,b VARCHAR(20) ;
	 DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2;
	 OPEN cur_t_user2;
	 FETCH cur_t_user2 INTO a,b;
	 INSERT INTO t_user VALUES(NULL,a,b);
	 CLOSE cur_t_user2;
	END 
&&
DELIMITER ;

（5）流程控制的使用

1.IF语句

DELIMITER &&
CREATE PROCEDURE pro_user5(IN bookId INT)
	BEGIN
	 SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
	 IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
	 ELSE
	   INSERT INTO t_user VALUES(NULL,'2312312','2321312');
	 END IF ;
	END 
&&
DELIMITER ;

2.CASE语句

DELIMITER &&
CREATE PROCEDURE pro_user6(IN bookId INT)
	BEGIN
	 SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
	 CASE @num
	  WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
	  WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'2312312','2321312');
	  ELSE INSERT INTO t_user VALUES(NULL,'231231221321312','2321312321312');
	 END CASE ;
	END 
&&
DELIMITER ;

3.LOOP、LEAVE语句

DELIMITER &&
CREATE PROCEDURE pro_user7(IN totalNum INT)
	BEGIN
	  aaa:LOOP
	    SET totalNum=totalNum-1;
	    IF totalNum=0 THEN LEAVE aaa ;
	    ELSE INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
	    END IF ;
	  END LOOP aaa ;
	END 
&&
DELIMITER ;

4.ITERATE语句

DELIMITER &&
CREATE PROCEDURE pro_user8(IN totalNum INT)
	BEGIN
	  aaa:LOOP
	    SET totalNum=totalNum-1;
	    IF totalNum=0 THEN LEAVE aaa ;
	    ELSEIF totalNum=3 THEN ITERATE aaa ;
	    END IF ;
	    INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
	  END LOOP aaa ;
	END 
&&
DELIMITER ;

5.REPEAT语句

DELIMITER &&
CREATE PROCEDURE pro_user9(IN totalNum INT)
	BEGIN
	  REPEAT
	     SET totalNum=totalNum-1;
	     INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
	     UNTIL totalNum=1 
	  END REPEAT;
	END 
&&
DELIMITER ;

6.WHILE语句

DELIMITER &&
CREATE PROCEDURE pro_user10(IN totalNum INT)
	BEGIN
	 WHILE totalNum>0 DO
	  INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
	  SET totalNum=totalNum-1;
	 END WHILE ;
	END 
&&
DELIMITER ;

调用上面的语句

CALL pro_user();
CALL pro_user2();
CALL pro_user3();
CALL pro_user4();
CALL pro_user5(5);
CALL pro_user6(6);
CALL pro_user7(11);
CALL pro_user8(11);
CALL pro_user9(11);
CALL pro_user10(10);

3 调用存储过程和函数
（1）调用存储过程

（2）调用存储函数

4 查看存储过程和函数
（1）SHOW STATUS语句查看存储过程和函数的状态

SHOW PROCEDURE STATUS LIKE 'pro_book';

（2）SHOW CREATE语句查看存储过程和函数的定义

SHOW CREATE PROCEDURE pro_book;

（3）从information_schema.Routines表中查看存储过程和函数的信息

5 修改存储过程和函数

ALTER PROCEDURE pro_book  COMMENT '我来测试一个COMMENT';

6 删除存储过程和函数

DROP PROCEDURE pro_user3;