一、什么是存储过程和函数
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须要有返回值,而存储过程没有,存储过程的参数可以使用IN、OUT、INOUT类型(其中OUT和INOUT类型的参数可充当返回值),而函数的参数只能是IN类型的。如果有函数从其他类型的数据库迁移到MYSQL,那么就可能因此需要将函数改造成存储过程。
二、创建、修改存储过程或函数
1、创建存储过程
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) --存储过程的参数列表
[characteristic ...] -- 存储过程的特征
routine_body -- 存储过程体
2、创建函数
CREATE FUNCTION sp_name ([func_parameter[,...]]) -- 函数的参数列表
RETURNS type -- 函数的返回值类型
[characteristics ...] -- 函数的特征
routine_body -- 函数体
格式:
proc_parameter:
[IN | OUT | INOUT] param_name type -- 存储过程参数的格式
eg: IN id INT -- 参数为IN类型,参数名为id,参数的数据类型为INT
func_parameter:
param_name type -- 函数参数格式
characteristic:
LANGUAGE SQL -- 说明下面过程的BODY是用SQL语言编写,这条是系统默认的,为今后MYSQL会支持的除SQL外的其他语言支持的存储过程而准备
| [NOT] DETERMINISTIC -- DETERMINISTIC确定的,即当过程输入相同时,输出也相同,默认为NOT DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } -- CONTAINS SQL表示子程序不包含读或写数据的语句。NO SQL表示子程序不包含SQL语句。READ SQL DATA表示子程序包含读数据的语句,但不包含写数据的语句。MODIFIES SQL DATA表示子程序包含写数据的语句。如果这些特征没有明确给出,默认使用的值是CONTAINS SQL.
| SQL SECURITY { DEFINER | INVOKER } -- 可以用来指定子程序用该子程序创建者的许可来执行,还是用调用者的许可来执行。默认值是DEFINER
| COMMENT 'string' -- 存储过程或者函数的注释信息
-- 特征格式
3、调用函数或存储过程
CALL sp_name ([parameter[,...]])
MYSQL的存储过程和函数中允许包含DDL语句,也允许在存储过程中执行提交(COMMIT)或者回滚(ROLLBACK),但是存储过程和函数中不允许执行LOAD DATA INFILE语句。此外,存储过程和函数中可以调用其他的存储过程或函数。
4、删除存储过程或函数
DROP { PROCEDURE | FUNCTION } [IF EXISTS] sp_name
-- 一次只能删除一个存储过程或函数,删除存储过程或者函数需要有该过程或函数的ALTER ROUTINE权限
三、变量的使用
存储过程中可以使用变量,而且在MYSQL5.1版本中,变量是不区分大小写的。
1、变量的定义:通过DECLARE可以定义一个局部变量,该变量的作用范围只能在BEGIN...END块中,可以用在嵌套的块中。变量的定义必须写在复合语句的开头,并且在任何其他语句的前面。可以一次声明多个变量。如果需要,可以使用DEFAULT赋默认值。
语法:
DECLARE var_name[,...] type [DEFAULT value]
eg:DECLARE last_month_start DATE -- 定义一个DATE类型的变量,名称是last_month_start
2、变量的赋值
变量可以直接赋值,或者通过查询赋值。
直接赋值使用SET,可以赋常量或者赋表达式,具体语法如下:
SET var_name = expr [, var_name = expr] ...
也可以通过查询将结果赋给变量,这要求查询返回的结果必须只有一行,具体语法如下:
SELECT col_name [,...] INTO var_name [,...] table_expr