MySQL存储程序（procedure存储过程、function存储函数、delimiter关键字）
原创
©著作权归作者所有：来自51CTO博客作者董哥的黑板报的原创作品，请联系作者获取转载授权，否则将追究法律责任
存储程序分为存储过程和存储函数
- 可以使用CALL来调用存储过程，只能输出变量返回值。存储过程可以调用其他存储过程。
- 函数可以从语句外调用，也能返回标量值。
什么是存储过程？
- 简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法。
- ps：存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用。
为什么要使用存储过程？
- 通过把处理封装在容易使用的单元中，简化复杂的操作。
- 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的（这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性）。
- 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化（这一点的延伸就是安全性）。
- 提高性能。因为使用存储过程比使用单独的SQL语句要快。
- 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。
存储过程的一些难点
- 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能。
- 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，单不允许它们创建存储过程。
存储过程的特性
- 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能。
- 函数的普遍特性：模块化，封装，代码复用。
- 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤。
DELIMITER的使用
- mysql默认的结束符为；但是存储过程中可能出现多个分号导致结束。所以需要用这个关键字设置SQL语句结束符，使用完之后可以再改回来。
- 要避免使用\，因为\是mysql的转义字符。
一、存储过程、存储函数的创建
存储过程
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body;
- sp_name：存储过程的名称。
- proc_parameter：存储过程的参数列表。
- routine_body：是SQL代码的内容（用BEGIN和END来表示SQL代码的开始和结束）。
- proc_parameter格式如下：
- IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出。
- param_name参数的名称。
- type参数的类型。
- 例如，创建一个名为AvgFruitPrice的存储过程，返回所有水果的平均价格。
DELIMITER //
create procedure AvgFruitPrice()
begin
select avg(f_price) as avgprice from fruits;
end;
//
DELIMITER //
create procedure Proc()
begin
select * from fruits;
end;
//
- 例如，创建名称为CountProc的存储过程（将count(*)的结果放入参数param1中）。
DELIMITER //
create procedure CountProc(out paraml int)
begin
select count(*) into param1 from fruits;
end;
//
存储函数
CREATE FUNCTION func_name ([func_parameter[,...]])
RETURNS type
[characteristic...] routine_body
- func_name：存储函数的名称。
- func_parameter：存储函数的参数列表。
- RETURNS type：表示函数返回数据的类型。
- characteristic：指定存储函数的特性。
- 如果存储函数的RETURN语句返回一个类型不同于函数的RETURNS子句中指定类型的值，返回值将被强制转为恰当的类型。
- 例如，创建名为NameByZip的存储函数，返回select语句的查询结果，数值类型为字符串类型。
DELIMITER //
create function NameByZip()
returns varchar(50)
return (select s_name from suppliers where s_call='48075');
//
二、存储过程的参数介绍
- 对于IN参数，调用者会把一个值传递到过程里。这个过程可以对这个值进行修改，但在过程返回之后任何修改对调用者来说都是不可见的。
- OUT参数则正好相反。过程把某个值赋给这个参数，但在过程返回后该值可以被调用者访问。
- INOUT参数则允许调用者向过程传递一个值，然后再取回一个值。
- 显式指定参数类型的方法是，在参数列表里的参数名前面使用IN、OUT或INOUT。如果没有为参数指定类型，则其默认类型为IN。
- 使用OUT或IN0UT参数的方法是，在调用过程时指定一个变量名。过程可以设置参数值，相应的变量将在过程返回时获得那个值。如果想让某个存储过程返回多个结果值，那么参数类型OUT和INOUT将非常有用。(由于存储函数只能返回一个值，因此它并不适用于这种情形)。
- 下面这个过程演示了OUT参数的用法。它可以分别统计出student表里的男生和女生人数，并通过参数返回这两个统计值，让调用者可以访问它们：
DELIMITER //
CREATE PROCEDURE count_students_by_sex (OUT p_male INT, OUT p_female INT)
BEGIN
SET p_male = (SELECT COUNT(*) FROM student WHERE sex='M');
SET p_female = (SELECT COUNT(*) FROM student WHERE sex='F');
END;
//
- 在调用此过程时，请把各个参数替换成相应的用户定义变量。此过程将把统计值放到这些参数里。在它返回之后，这些变量会包含那些统计值：
CALL count_students_by_sex(@male_count, @female_count);
SELECT @male_count, @female_count;
- 难道只有用户定义义变量才能作为参数进行传递吗？非也。如果在另一个存储程序里调用count_students_by_sex()，那么在这个程序中定义的局部变量或参数可以作为参数传递给count_students_by_sex()。
- 需要额外参数的例子有很多。例如，你可以编写一个存储过程，让它具有一个IN参数，用于指明某次测试或测验在score表里的ID。
- 这个存储过程可以根据相关的分数计算描述性统计(如平均值、标准偏差、极差等)，然后通过OUT参数把所有这些值传递给调用者。
- 关键字IN、OUT和INOUT都不能用于存储函数、触发器和事件。对于存储函数，所有参数都像IN参数。触发器和事件则根本就没有参数。
三、变量的使用
四、定义条件和处理程序
五、光标的使用
六、流程控制语句的使用
演示案例
- 获得一份订单合计，需要对合计增加营业税，不过只针对某些顾客（或者是你所在地区的那些顾客），定义的存储过程如下。
- 参数：
- onumer：订单编号。
- taxable：是否增加营业税。
- ototal：将返回的结果保存在这个参数中返回。
- total：总价。
- taxrate：税率，此存储过程中默认为6%。
delimiter //
create procedure ordertotal(in onubmer int,in taxable boolean,out ototal decimal(8,2))
comment 'Obtain order toal,optionally adding tax'
begin
declare total decimal(8,2);
declare taxrate int default 6;
select sum(item_price*quantity) from orderitems where order_num=onubmer into total;
if taxable then
select total+(total/100*taxrate) into total;
end if;
select total into ototal;
end;
//
七、调用存储过程和函数
调用存储过程
- 存储过程有多种调用方法。
- 存储过程必须使用CALL语句调用，并且，存储过程和数据库相关，如果，要执行其他数据库中的存储过程，需要指定数据库名称。
- 格式如下：
- sp_name，为存储过程的名称。
- parameter，为存储过程的参数。
CALL sp_name ([parameter [,……]])
- 例如，定义存储过程（将存储过程的结果保存在参数2中）。
调用存储函数
- 在 MySQL中，存储函数的使用方法与 MySQL 内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与 MySQL 的内部函数是一个性质的。 区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。
- 例如，定义如下存储函数。
八、查看存储过程和函数
- MySQL存储了，存储过程的状态信息。
- 可以使用SHOW STATUS语句，或SHOW CREATE语句来查看，也可以直接从系统的information_schema数据库中查询。
SHOW STATUS
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]- PROCEDURE和FUNCTION，分别表示查看存储过程和函数。
- LIKE语句，表示匹配的名称。
- 这个语句是一个MySQL扩展，返回子程序的特征，比如，数据库、名字、类型、创建者以创建、修改日期 。
- 如果，没有指定样式，根据使用的语句，所有存储程序或存储函数的信息都被列出。
- 例如，下面的语句，获取数据库中所有名称，以C开头的存储过程的信息。可以看出，存储过程所在的数据库为sys，存储过程的名称为create_synonym_db等信息。
SHOW CREATE
SHOW CREATE {PROCEDURE | FUNCTION} sp_name- 这个语句，也是一个MySQL的扩展。类似于SHOW CREATE TABLE，返回一个可用来重新创建，已命名子程序的确切字符串
- 例如：
INFORMATION_SCHEMA.Routines
- 从information_schema.Routines表中，查看存储过程的信息。
- MySQL中，存储过程和函数的信息，存储在information_schema数据库下的Routines表中。
SELECT * FROM information_schema,Routines
WHERE ROUTINE_NAME='sp_name';
- ROUTINE_NAME存储的是存储过程和函数的名称。
- sp_name：存储过程或函数的名称。
九、修改存储过程和函数
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic……]- sp_name：表示存储过程或函数的名称。
- characteristic：表示要修改存储过程的哪个部分。取值如下：
- CONTAINS SQL，表示子程序包含SQL语句，但是，不包含读或写数据的语句。
- NO SQL，表示子程序中，不包含SQL语句。
- READS SQL DATA，表示子程序中，包含读数据的语句。
- MODIFIES DATA，表示子程序中，包含写数据的语句。
- SQL SECURITY {DEFINER | INVOKER}，指明谁有权限来执行。
- DEFINER，表示只有定义者，自己才能够执行。
- INVOKER，表示调用者可以执行。
- COMMENT’string’，表示注释信息。
演示案例
- 修改存储过程CountProc的定义 。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。
演示案例
- 修改存储过程CountProc的定义 。将读写权限改为READS SQL DATA，并加上注释信息“find name”。
十、删除存储过程和函数
- 当数据库存储废弃的存储过程/函数时，需要删除 。MySQL中，使用DROP语句删除存储过程/函数。
- 格式如下：
- sp_name：为删除的存储过程名称。
- IF EXISTS：表示如果程序不存在，可以避免发生错误，产生一个警告，该警告可以使用SHOW WARNINGS进行查询。
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name;
演示案例
