Mysql 之存储过程

[b]简介：[/b]
MySQL 存储过程是从 MySQL 5.0 开始逐渐增加新的功能。存储过程可以提高数据库的处理速度，同时也可以提高数据库编程的灵活性。


[b]优点：[/b]
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。


[b]创建语法：[/b]

create procedure 存储过程名字()   
(   
   [in|out|inout] 参数 datatype   
)   
begin   
   sql语句;   
end;  

[b]实例：[/b]

DELIMITER $$
ALTER PROCEDURE `pro_add`
(a INT,b INT)
BEGIN
	DECLARE c INT;
	IF a IS NULL THEN 
		SET a = 0;
	END IF;

	IF b IS NULL THEN 
		SET b = 0;
	END IF;
	SET c = a + b;
	SELECT c AS SUM;
END$$

DELIMITER ;

[color=red]说明：MySQL中默认的语句结束符为分号（;）。存储过程中的SQL语句需要分号来结束。为了避免冲突，首先用"DELIMITER &&"将MySQL的结束符设置为&&。最后再用"DELIMITER ;"来将结束符恢复成分号,否则会出现不能编译的问题。
[/color]


[b]存储过程调用：[/b]

call pro_add(10,20);

output : SUM 
         30

[b]存储过程修改[/b]

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

[color=red]说明：看MySql官方文档针对此修改存储过程语法，只能改名字和定义不能改里面的内容。需要删除重新创建。[/color]

[b]实例：[/b]

DELIMITER $$

DROP PROCEDURE IF EXISTS `pro_add`$$

CREATE PROCEDURE `pro_add`(a INT,b INT)
BEGIN
	DECLARE c INT;

	IF a IS NULL THEN 
		SET a = 0;
	END IF;

	IF b IS NULL THEN 
		SET b = 0;
	END IF;
	SET c = a + b;
	SELECT c AS SUM;
END$$

DELIMITER ;

[b]存储过程删除：[/b]

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

[b]实例：[/b]

DROP PROCEDURE IF EXISTS `pro_add`;
或 
DROP PROCEDURE IF EXISTS pro_add;

" 非 "
DROP PROCEDURE IF EXISTS 'pro_add';

[color=red]
说明： IF EXISTS 子句是一个MySQL的扩展。如果程序或函数不存在，它防止发生错误。产生一个可以用SHOW WARNINGS查看的警告。
[/color]