MySQL 的存储过程和存储函数

---

存储过程

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。

存储过程的优点：
(1)减少网络通信量。
调用一个行数不多的存储过程与直接调用SQL 语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL 语句，那么其性能绝对比一条一条的调用SQL 语句要高得多。
(2)执行速度更快。
有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。
(3)更强的适应性。
由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。
(4) 分布式工作。
应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

MySQL的存储过程
存储过程是数据库的一个重要的功能，MySQL 5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

MySQL存储过程的创建

		CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体
		DELIMITER //
		  CREATE PROCEDURE myproc(OUT s int)
		    BEGIN
		      SELECT COUNT(*) INTO s FROM students;
		    END
		    //
		DELIMITER ;

1. 分隔符
   MySQL默认以";“为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个”//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。
2. 参数
   存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT: IN 表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出的参数.
3. 过程体
   过程体的开始与结束使用BEGIN与END进行标识。

---

例子

创建一个存储过程：

		DROP PROCEDURE IF EXISTS getStuById;
		DELIMITER //  -- 定义存储过程结束符号为//
		CREATE PROCEDURE getStuById(INOUT userId INT(11),OUT username VARCHAR(255)) -- 定义输入与输出参数
		BEGIN
		   SELECT id,name INTO userId,username FROM t_user WHERE id = userId;
		END // 
		DELIMITER ;

查看我们创建的存储过程：

		SHOW PROCEDURE STATUS LIKE 'get%'

调用一个存储过程：

		set @id=1; //只有INOUT类型才需要先设置一个变量
		CALL test.getStuById(@id,@name);
		SELECT @id AS userId,@name AS username;

其他操作

定义变量

		DECLARE name,address VARCHAR; //定义两个varchar类型变量
		DECLARE age INT DEFAULT 18;//定义一个默认值为18的整型变量age
		SET name = 'moke';  //为name变量设置值

流程控制

		DELIMITER //
		CREATE PROCEDURE testIf(OUT result VARCHAR(255))
		BEGIN
		     DECLARE flag VARCHAR(255);
		     SET flag= 'a';
		     IF val IS NULL
		        THEN SET result = 'IS NULL';
		     	ELSE SET result = 'IS NOT NULL';
		     END IF;
		END //
		DELIMITER ;

…

---

存储函数

存储函数与存储过程本质上是一样的，都是封装一系列SQL语句，简化调用。
创建一个存储函数：

	DROP FUNCTION IF EXISTS getStuNameById;
   	DELIMITER //
   	CREATE FUNCTION getStuNameById(userId INT)  -- 默认是IN，但是不能写上去。stuId视为输入的临时变量
   	RETURNS VARCHAR(255)   -- 指明返回值类型
   	RETURN  (SELECT name FROM t_user WHERE id = userId); //  -- 指明SQL语句,并使用结束标记。注意分号位置
   	DELIMITER ;

查看创建的存储函数：

		SHOW FUNCTION STATUS

调用存储函数：

		SELECT getStuNameById(2);

与存储过程的区别：
更改存储函数的 SQL 语句为：

		RETURN  (SELECT id,name FROM t_user WHERE id = userId);

此时我们再次调用getStuNameById存储函数会报错：

存储函数只能指明一列数据作为结果，而存储过程能够指明多列数据作为结果