数据库编程：存储过程和储存函数

存储过程

一，存储过程的基本概念

存储过程是一组为了完成某项特定功能的SQL语句集，其实质上就是一段存储在数据库中的代码，他是由声明式SQL语句（如create，uopdate，seletct等语句）和过程式SQL语句（如if…then…else控制结构语句）组成。这组语句集经过编译后会存储在数据库中，用户只需要指定存储过程的名字并给定参数就可以随时调用并执行，而不必重新编译，因此这种通过定义一段程序存储在数据库中的方式，可加大数据库操作语句的执行效率。

一个存储过程是一个可编程的函数，同时可看作是在数据库编程中对面向对象方式的模拟，它允许控制数据的访问方式。使用存储过程通常具有以下优点：

1.可增强SQL语言的功能和灵活性；
2.良好的封装性
3.高性能
4.可减少网络流量
5.存储过程可作为一种安全机制来确保数据库的安全性和数据的完整性

二，创建存储过程并且调用存储过程

在mysql数据库中通过命令行的方式来创建存储过程时，经常用到一个十分重要的命令，即delimiter命令，使用delimiter命令，将mysql语句的结束语标志临时修改为其他符号，从而使mysql服务器可以完整的处理存储过程中所以的mysql语句，而后可通过delimiter命令再将mysql语句的标志符号改回默认符号，即（；）。

delimiter !! //自定义整个存储过程的结束符为!!

delimiter ; //如若希望换回默认的结束符号，就执行这条命令

在mysql中，创建存储过程的语法是：

create procedure sp_name (proc_parameter,proc_parameter...)
routine_body

格式含义：
sp_name：存储过程的名称，默认在当前数据库中创建
proc_parameter：指定存储过程的参数列表，mysql存储过程支持三种类型的参数，即输入参数，输出参数和输入/输出参数，分别用（in，out，inout）表示
routine_body：表示存储过程的主体部分，这个部分以begin开始，以end结束

举例一个应用题，在数据库mysql_test中创建一个存储过程为sp_update_sex，并且将客户id号为909的客户性别修改为男性“M”

use mysql_test;       //应用一个数据库
delimiter !!        //自定义整个过程的结束符
create procedure sp_update_sex (in cid int,in csex char(1))
	begin
		update customers set cust_sex=csex where cust_id=cid;
	end!!

call sp_update_sex(909,"M");  //调用存储函数

删除存储过程

存储过程在被创建后，会被保存在服务器上以供使用，直至删除，在mysql中可以使用drop procedure语句来删除数据库中已经创建的存储过程，举例：

// 删除数据库mysql_test中存储过程sp_update_sex
drop procedure sp_update_sex；

//防止因删除不存在的存储函数而引发的错误，可加上关键字if exists
drop procedure if exists sp_update_sex；

存储函数

存储函数和存储过程一样，都是由sql语句和过程式语句所组成的代码片段，并且可以被应用程序和其他sql语句调用

存储过程和存储函数和区别：

1.存储函数不能拥有输出参数，这是因为存储函数自身就是输出参数，而存储过程可以拥有输出参数
2.可以直接对存储函数进行调用，且不需要使用call语句，而对存储过程进行调用需要用call语句
3.存储函数中必须包含一条return语句，而这条特殊的sql语句不允许包含于存储过程中

创建存储函数

在mysql中，可以使用create function语句来创建一个存储函数，语法格式为：

create function sp_name(func_parameter...) //func_parameter的语法格式为：parameter_name type
	returns type
	routine_body

语法格式：
sp_name：存储函数的名称，注意的是存储函数不能和存储过程同一个名字
func_parameter：存储函数的参数，注意这的参数只有名称和类型，不能指定关键字（in，out，inout）
returns：用于声明存储函数返回值的数据类型，type代表返回值的数据类型
routine_body：存储函数的主题部分，也是存储函数体。

应用题，在数据库mysql_test中创建一个存储函数，要求该函数能根据给定的客户id号返回客户的性别，如果数据库中没有给定的客户id号，则返回“没有该客户”

use mysql_test;
delimiter !!
create function fn_search(cid int)
	returns char(2)
	deterministic
	begin
		declare sex char(2);
		select cust_sex into sex from customers
			where cust_id=cid;
		if sex is null then 
			return(select"没有该客户");
		else if sex="F" then
				return(select"女");
				else return(select"男");
				end if;
		end if;
	end !!

调用存储函数,使用关键字select调用

应用题，调用数据库mysql_test中存储函数fn_search。

select fn_search<904>;

删除存储函数

在存储函数被创建后，会被保存在服务器上以供使用，直至被删除，删除存储函数方法和删除存储过程的方法基本是一样的。使用drop function语句来删除

// 删除数据库mysql_test中存储函数sp_update_sex
drop procedure fn_search；

//防止因删除不存在的存储函数而引发的错误，可加上关键字if exists
drop procedure if exists fn_search；