/*
摘抄至: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;
运行顺序:先运行存储过程,在运行调用,然后运行查看。