MySql--存储过程

　　MySql中的存储过程可以用来转换数据、数据迁移、制作报表，它类似于编程语言，一次执行成功，就可以随时被调用，完成指定的功能操作。

/*************************存储过程******************************************/	
1、创建存储过程
	mysql> delimiter ##
	mysql> create procedure my_prc() begin select * from newInfo; end##
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> call my_prc##
	+----+--------+-------+-----------+-----------+
	| id | name   | score | subjectid | teacherid |
	+----+--------+-------+-----------+-----------+
	|  1 | 小明   | 78.00 |         2 |         1 |
	|  2 | 小红   | 87.00 |         3 |         2 |
	|  3 | 小李   | 67.00 |         1 |         3 |
	|  4 | 小胡   | 99.00 |         9 |        10 |
	+----+--------+-------+-----------+-----------+
	4 rows in set (0.00 sec)

2、修改存储过程
	mysql> drop procedure if exists my_prc##
	Query OK, 0 rows affected (0.00 sec)

	mysql> create procedure my_prc() 
		-> begin 
		-> select * from newInfo where id = 3;
		-> end##
	Query OK, 0 rows affected (0.00 sec)

3、删除存储过程
	mysql> drop procedure if exists my_prc##
	Query OK, 0 rows affected (0.00 sec)
	
4、存储过程中的变量使用
	mysql> create procedure my_prc(a int, b int)
		-> begin
		->     declare c int;
		->     set c = a + b;
		->     select c as SUM;
		-> end##
	Query OK, 0 rows affected (0.00 sec)

	mysql> call my_prc(2,4)##
	+------+
	| SUM  |
	+------+
	|    6 |
	+------+
	1 row in set (0.00 sec)

	Query OK, 0 rows affected (0.00 sec)

5、IF条件控制语句
	mysql> create procedure my_prc(a int, b int)
		-> begin
		->     IF (a > b) THEN
		->         select a as MAX;
		->     ELSE
		->         select b as MAX;
		->     END IF;
		-> end##
	Query OK, 0 rows affected (0.00 sec)

	mysql> call my_prc(2,4)
		-> ##
	+------+
	| MAX  |
	+------+
	|    4 |
	+------+
	1 row in set (0.00 sec)

	Query OK, 0 rows affected (0.00 sec)
	
6、CASE条件控制语句
	mysql> create procedure my_prc(a int, b int, ope varchar(1)) 
		-> begin     
		->     CASE ope     
		-> 	   WHEN '+'  THEN select a+b as '和';   
		->     WHEN '-'  THEN select a-b as '差';   
		->     WHEN '*'  THEN select a*b as '积';    
		->     WHEN '/'  THEN select a/b as '商';    
		->     END CASE;
		-> end##
	Query OK, 0 rows affected (0.00 sec)

	mysql> call my_prc(2,4,'-')##
	+------+
	| 差   |
	+------+
	|   -2 |
	+------+
	1 row in set (0.04 sec)

7、LOOP循环控制语句
	mysql> create procedure my_prc(a int)
		-> begin
		->     label : LOOP
		->        set a = a + 1;
		->     IF (a < 10)
		->     THEN select 'first label';
		->     ELSE 
		->     ITERATE label;
		->     END IF;
		->     LEAVE label;
		->     END LOOP label;
		-> end##
	Query OK, 0 rows affected (0.00 sec)

	mysql> call my_prc(5)##
	+-------------+
	| first label |
	+-------------+
	| first label |
	+-------------+
	1 row in set (0.00 sec)

8、WHILE语句
	mysql> create procedure my_prc()
		-> begin
		->     declare xx INT DEFAULT 10;
		->     WHILE xx > 0 DO
		->         set xx = xx - 1;
		->     END WHILE;
		->     set @vx = xx;
		-> end##
	Query OK, 0 rows affected (0.03 sec)

	mysql> call my_prc()##
	Query OK, 0 rows affected (0.00 sec)

	mysql> select @vx##
	+------+
	| @vx  |
	+------+
	|    0 |
	+------+
	1 row in set (0.00 sec)

/*****************************************************************************/