mysql数据库的存储过程(五)

/*
	摘抄至:http://www.cnblogs.com/mark-chan/p/5384139.html
	定义:
		SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，
		经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
		存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。
		当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。
		数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

		特点:
			执行效率快，存储过程是在数据的服务器端执行的。
			可移植性差，因为不同数据库的存储过程语法不同。

		存储过程的优点：
		(1).增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
		(2).标准组件式编程：存储过程被创建后，可以在程序中被多次调用，
				而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
		(3).较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，
				那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，
				并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
		(4).减少网络流量：针对同一个数据库对象的操作（如查询、修改），
				如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，
				网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
		(5).作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，
				能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
		MySQL的存储过程
		存储过程是数据库的一个重要的功能，MySQL 5.0以前并不支持存储过程，
		这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程，这样即可以大大提高数据库的处理速度，
		同时也可以提高数据库编程的灵活性。
		

		存储过程的基本语法说明

		DELIMITER $  -- 声明结束符，其中$也可用其他符号代替，例如&、# 、*等
		CREATE PROCEDURE pro_test()  -- 声明存储过程的名称(参数列表)，语法类似函数定义
		BEGIN -- 开始
			  -- 存储过程的sql语句，可以多个sql语句
		SELECT * FROM ssh_employee;
		END $ -- 结束 结束符

		CALL pro_test(); --调用存储过程 
		
		参数列表的类型:
			IN  输入参数，将数据传入到存储过程中。
			OUT 输出参数，执行存储过程返回的结果;
			INOUT 输入输出参数，既可以输入，也可以输出；
*/
-- 声明结束符
DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
		-- 多个sql语句
		SELECT * FROM ssh_employee;
END $

CALL pro_test();
/*
	带有存储输入参数的存储过程
	存入一个员工的id，查询询员工的信息
*/
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT)
BEGIN

	SELECT * FROM ssh_employee WHERE ID = eid;

END $
CALL pro_findById(7);

/*带有输出参数的存储过程*/

DELIMITER %
CREATE PROCEDURE pro_testOUT(OUT str VARCHAR(20))
BEGIN
	-- 给输出参数赋值
		SET str ='这是一个输出参数';
END %
-- 调用
CALL pro_testOUT(@str); -- a、定义一个回话变量str；b、使用会话变量str接受存储过程执行的返回值;
-- 查看变量
SELECT @str;
-- 如何接受返回参数的值？
-- 删除存储过程——DROP PROCEDURE 存储过程名;
DROP PROCEDURE pro_testOUT;

/* 
***mysql的变量*****
		1、全局变量(内置变量):mysql数据库内置变量，在mysql服务器开启时被加载都内存；
		查看所有全局变量命令：show VARIABLES
		修改全局变量: SET 全局变量名=新值;
		查看某一个变量值：SELCET @@变量名；
		2、会话变量
			只存在于当前客户端与数据库服务端的一次连接中，如果连接断开，那么回话变量会全部丢失。
			定义会话变量：set @变量=值;
		3、局部变量
			在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失;
*/  

/*带有输入输出参数的存储过程*/

DELIMITER #
CREATE PROCEDURE pro_testINOUT(INOUT n INT)
BEGIN
	 -- 注意所有参数在存储过程中都是局部变量
		SELECT n;
		SET n= 500;
END #

-- 定义一个会话变量作为输入参数
	SET @n = 100;
	
CALL pro_testINOUT(@n);

SELECT  @n;
/*
	存储过程还可以带有判断,循环
*/
-- 带判断的存储过程
-- 例子: 输入不同的数字，显示不同结果。例如输入1.返回‘满意’。输入2，返回‘比较满意’，输入3.返回‘不满意’，输入其他，返回‘您的输入有误’；

DELIMITER $
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(10))
BEGIN
			IF num = 1 THEN
					SET str = '满意';
			ELSEIF num = 2 THEN
					SET str = '比较满意';
			ELSEIF num = 3 THEN 
					SET str = '不满意';
			ELSE
					SET str = '您的输入有误';
			END IF;
END $
CALL pro_testIF(2,@str);
SELECT @str;
/*
带有循环的存储结构
	例子：计算n(1-9)阶乘。
*/
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT , OUT res INT)
BEGIN
	 -- 定义一个全局变量
		DECLARE i INT DEFAULT 1; -- 循环起始
		DECLARE sum INT DEFAULT 1; -- 存阶乘结果
	
		WHILE i<=num DO	
			SET sum = sum*i;
			SET i=i+1;
		END WHILE;
	SET res = sum;
END $

CALL pro_testWhile(5,@res);
DROP PROCEDURE pro_testWhile;

SELECT @res;

运行顺序:先运行存储过程,在运行调用，然后运行查看。