存储过程与存储函数

存储过程与存储函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程与存储函数来执行已经定义好的SQL语句。存储过程与存储函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程与存储函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。本篇博文将介绍存储过程和存储函数的含义、作用，以及创建使用查看修改及删除存储过程与存储函数的方法。

1、创建存储过程与存储函数

在数据库系统中，为了保证数据的完整性和一致性，同时也为了提高其应用性能，大多数数据库常采用存储过程和存储函数技术。存储过程和存储函数经常是一组Sql语句的组合，这些语句被当做整体存入MySQL数据库服务器中，用户定义的存储函数不能用于修改全局库状态，但该函数可以从查询中被唤醒调用，也可以像存储过程一样通过语句执行。

1.1、创建存储过程

在MySQL中，创建存储过程的基本形式如下：

CREATE PROCEDURE sp_name([proc_parameter[,...]])
[characteristic ...] routine_body

其中sp_name参数是存储过程的名称，proc_parameter表示存储过程的参数列表；characteristic参数指定存储过程的特性；routine_body参数是SQL代码的的内容，可以用BEGIN…END来标识代码的开始和结束。
proc_parameter中的参数由3部分组成，分别是输入输出类型，参数名称，参数类型、其形式为[IN|OUT|INOUT]param_name type。其中IN表示输入参数，OUT表示 输出参数，INOUT表示既可以输入也可以输出；param_name参数是存储过程参数名称；type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型。
一个存储过程包括名字、参数列表，还可以包括很多SQL语句集。下面创造一个存储过程，其代码如下

DELIMITER//
 create procedure pro_name(in parameter integer)
 BEGIN
 DECLARE variable VARCHAR(20);
 IF parameter = 1 then
set variable='MYSQL';
 ELSE
 set variable='PHP';
END IF;
INSERT INTO tb(name) VALUES(variable);
END;

MySQL中存储过程的建立以关键字CREATE PROCEDURE开始，后面仅跟存储过程的名称和参数。MySQL的存储过程名称不区分大小写，例如PROCE1()和proce1()代表同一存储过程名。存储过程名或存储函数名不能与MySQL数据库中的内建函数重名。
MySQL存储过程的语句块以BEGIN开始，END结束。语句体中可以包含变量的声明、控制语句、SQL查询语句等。由于存储过程内部语句要以分号结束，所以在定义存储过程前，应将语句结束标志“;”更改为其他字符，并且降低该字符在存储过程中出现的机率，更改结束标志可以用关键字“DELIMITER”定义，例如DELIMITER//
存储过程创建之后，可用如下语句进行删除(参数pro_name指存储过程名)
DROP PROCEDURE proc_name
下面创建一个名称为proc_count的存储过程。在创建该存储过程之前，需要先创建一个名称为tb_borrow1的数据表。

create table tb_borrow1
(
id int(10) not null auto_increament primary key,
readerid int(10),
bookid int(10),
borrowTime date,
backTime date,
operator varchar(30),
ifback tinyint(1) default '0',
)

创建一个统计指定图书借阅次数的存储过程，主要是通过创建一个名称为proc_count的存储过程，实现统计tb_borrow1的存储过程，实现统计tb_borrow1数据表中指定图书编号的图书借阅次数。代码如下：

DELIMITER //
create procedure proc_count(in id int, OUT borrowCount int)
reads sql data
begin
select count(*) into borrowCount from tb_borrow1 where bookid ="+id+";
end
//

上述代码中定义了一个输出变量borrowCount 和输入变量id。存储过程应用了select语句从tb_borrow1表中获取指定图书的记录总数，最终将结果传递给变量borrowCount。
这里是通过将查询结果保存在一个输出变量中返回的，事实上还可以将输出结果通过结果集返回，具体的代码如下

DELIMITER//
CREATE PROCEDURE pro_count1(IN id int)
reads sql data
begin
select count(*) from tb_borrow1 where bookid=id;
end
//

执行上述代码完毕后，没有报出任何错误信息就表示存储函数已经创建成功，以后就可以调用这个存储过程实现相应的功能。调用存储过程后数据库会执行存储过程中的SQL语句。
MySQL中默认的语句结束符为分号;，存储过程中的SQL语句需要分号来结束。为了避免冲突，首先用DELIMITER//将MySQL的结束符设置为‘//’，最后再用“DELIMITER;”来将结束符恢复成分号。这与创建触发器是一样的。

1.2、创建存储函数

创建存储函数与创建存储过程大体相同，创建存储函数的基本形式如下：

CREATE FUNCTION sp_name([func_parameter[,...]])
return type
[characteristic ...] routine_body

创建存储函数参数的说明

参数	说明
sp_name	存储函数的说明
func_parameter	存储函数的参数列表
RETURNS type	指定返回值的类型
characteristic	指定存储过程的特性
routine_body	SQL代码的内容

1.3、变量的应用

MySQL存储过程中的参数主要有局部参数和全局参数两种，这两种参数又可以成为局部变量和全局变量。局部变量只在定义该局部变量begin…end范围内有效，全局变量在整个存储过程范围内均有效。

1.3.1、局部变量

在MySQL中，局部变量以关键字declare声明，后跟变量名和变量类型，基本语法如下：
declare var_name[,...] type [default value]
declare 是用来声明变量的，var_name参数是设置变量的名称，如果用户需要，也可以定义多个变量；type参数用来指定变量的类型；default value的作用是指定变量的默认值，不对该参数进行设置时，其默认值是null.
例如，声明一个局部变量，不设置其默认值 declare id int
下面将实现声明一个局部变量的同时，为其设定默认值declare id int default 10

1.3.2、全局变量

MySQL中全局变量不必声明即可使用，全局变量在整个过程中有效，全局变量以字符“@”作为起始字符。

1.3.3、为变量赋值

在MySQL中，除了声明局部变量和全局变量的时候可以为其设置默认值以外，还可以应用以下两种方式为其赋值。

• 使用set关键字为变量赋值
  mysql中可以使用set关键字为变量赋值，set语句的基本语法如下：set var_name = expr[,var_name = expr]...
  set关键字用来为变量赋值；var_name参数是变量的名称；expr是参数的赋值表达式。一个set语句可以同时为多个变量赋值，各个变量的赋值语句用“,”隔开。例如为mr赋值，代码set mr = 10；
• 使用select… into 语句为变量赋值
  使用select … into 语句也可以为变量赋值，其语法结构如下：select col_name[,...] into var_name[,...] from table_name where condition
  其中col_name参数标识查询的字段的名称；var_name参数是变量的名称；table_name参数为指定数据表的名称；condition参数为指定的查询条件。
  例如：从tb_bokinfo表中查询barcode为“98765”的记录，将该记录的price字段内容赋值给变量book_price。其关键代码如下：select price into book_price from tb_bookinfo where barcode='98765';
  上述赋值语句必须存在于创建的存储过程中，且需将赋值的语句放置在begin…end之间。若脱离此范围，该变量将不能使用或被赋值。

1.4、光标的运用

通过MySQL查询数据库，其结果可能为多条记录。在存储过程和函数中使用光标可以实现逐条读取结果集中的记录。光标的使用包括声明光标（declare cursor）、打开光标（open cursor）、使用光标（fetch cursor）和关闭光标（close cursor）。值得一提的是，光标必须声明在处理程序之前，且声明在变量和条件之后。

1.4.1、声明光标

在MySQL中，声明光标仍使用declare关键字， 其语法格式如下：
declare cursor_name cursor for select_statement
cursor_name是光标的名称，光标名称使用和表名同样的规则；select_statement 是一个select语句，返回一行或多行数据。其中这个语句也可以在存储过程中定义多个光标，但是必须保证每个光标名称的唯一性，既每个光标必须有自己唯一的名称。
通过上述定义来声明光标cursor_book,其代码如下：
declare cursor_book cursor for select barcode,bookname,price from tb_bookinfo where bookid=4;
这里select子句中不能包含into子句，并且光标只能在存储过程或存储函数中使用，上述代码不能单独执行。

1.4.2、打开光标

在声明光标之后，要从光标中提取数据，需先打开光标，在MySQL中使用open关键字来打开光标，其基本的语法如下：open cursor_name，其中cursor_name参数表示光标的名称。在程序中，一个光标可以打开多次，由于可能在用户打开光标后，其他用户或程序正在更新数据表，所以可能会导致用户在每次打开光标后，显示的结果都不同。打开上面已经声明的光标：open cursor_book

1.4.3、使用光标

光标在顺利打开后，可以使用fetch … into 语句来读取数据，其语法如下：
fetch cursor_name into var_name[,var_name]...
其中cursor_name代表已经打开光标的名称；var_name参数表示将光标中的select语句查询出来的信息存入该参数中。var_name是存放数据的变量名，必须在声明光标前定义好。fetch … into 语句与select … into 语句具有相同的意义。
将已打开的光标cursor_book中由select语句查询出来的信息存入tmp_barcode、tmp_bookname和tmp_price中，其中tmp_barcode、tmp_bookname和tmp_price必须在使用前定义。其代码如下：fetch cursor_book into tmp_barcode,tmp_bookname,tmp_price;

1.4.4、关闭光标

光标使用完毕后，要及时关闭。在MySQL中采用close关键字关闭光标，其语法格式如下：close cursor_name
cursor_name参数表示光标名称，下面关闭已打开的光标cursor_book，代码如下：close cursor_book。对于已关闭的光标，在其关闭之后则不能使用fetch来使用光标。光标在使用完毕后一定要关闭。

2、存储过程和存储函数的调用

存储过程和存储函数都是存储在服务器的SQL语句的集合。要使用这些一定定义好的存储过程和存储函数就必须要通过调用的方式来实现。对存储过程和函数的操作主要可以分为调用、查看、修改和删除。

2.1、调用存储过程

存储过程的调用在前面的示例中多次被用到。MySQL使用call来调用存储过程。调用存储过程后，数据库系统将执行存储过程中的语句，然后将结果返回给输出值。call语句的基本语法形式如下：call sp_name([parameter[,...]]);
其中sp_name是存储过程的名称；parameter是存储过程的参数。
调用统计图书借阅次数的存储过程，既创建的存储proc_count，调用代码如下：

set @bookid=7;
call proc_count(@bookid,@borrowcount);
select @borrowcount;

2.2、调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法相同。用户自定义的存储函数与MySQL内部函数性质相同。区别在于，存储函数是用户自定义的，而内部函数由MySQL自带。其语法结构如下:
select function_name([parameter[,...]);
调用统计图书借阅次数的存储函数，创建的存储函数func_count，代码如下：set @bookid=7; call func_count(@bookid);
存储过程可以使用select语句返回结果集，但是存储函数则不能使用select语句返回结果集，否则将显示如下错误：Not allowed to return a result set from a function

3、查看存储过程和函数

存储过程和函数创建以后，用户可以查看存储过程和函数的状态和定义。用户可以通过show status语句查看存储过程和函数状态，也可以通过show create语句来查看存储过程和函数的定义。

3.1、show status语句

在MySQL中可以通过show status语句查看存储过程和函数的状态。其基本语法结构如下：show {PROCEDURE|function} status [like 'pattern']
其中，PROCEDURE参数表示查询存储过程；function参数表示查询存储函数；like ‘pattern’ 参数用来匹配存储过程或函数名称。

3.2、show create语句

MySQL中可以通过show create语句来查看存储过程和函数的状态。其语法结果如下：show create {PROCEDURE|function} sp_name
其中PROCEDURE参数表示存储过程，function参数表示查询存储函数，sp_name参数表示存储过程或函数的名称。
show status语句只能查看存储过程或函数所操作的数据库对象，如存储过程或函数的名称、类型、定义者、修改时间等信息，并不能查询存储过程或函数的具体定义。如果需要查看详细定义，需要使用show create语句。

4、修改存储过程和函数

修改存储过程和存储函数是指修改已经定义好的存储过程和函数。MySQL中通过alter PROCEDURE 语句来修改存储过程。通过alter function语句来修改函数。
MySQL中修改存储过程和函数的语句语法形式如下：

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

5、删除存储过程和函数

删除存储过程和函数指删除数据库中已经存在的存储过程或存储函数。MySQL中使用drop procedure 语句来删除存储过程，通过drop function语句来删除存储函数。在删除之前，必须确认该存储过程或存储函数没有任何依赖关系，否则可能会导致其他与其关联的存储过程无法运行
drop {procedure|function}[if exists]sp_name
其中sp_name参数表示存储过程或函数的名称，if exists是MySQL的扩展，判断存储过程或函数是否存在，以免发生错误。，删除创建的存储过程proc_count语句 drop procedure proc_count;
当但会结果没有提示警告或者报错时，则说明存储过程或存储函数已经被顺利删除，用于可以通过查询information_schema数据库下的Routine表来确认上面的删除是否成功。