存储过程
存储过程的创建语法如下:
CREATE
[DEFINER = user]
PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
routine_body 可以是简单的一句SELECT 和INSERT等语句,也可以是BEGIN…END组成的复合语句,在BEGIN…END中,可以使用IF、WHILE等控制语句组成的复杂逻辑。
DEFINER
DEFINER子句指定了在常规执行时为具有SQL SECURITY DEFINER特性的例程检查访问权限时要使用的MySQL账户。
显示指定,值应该是 ‘user_name’@‘host_name’, CURRENT_USER, 或 CURRENT_USER()。省略时等价于DEFINER = CURRENT_USER。
characteristic
- LANGUAGE SQL,这个是默认的,说是为以后支持SQL以外的其他语言准备的,先忽略
- [NOT] DETERMINISTIC:DETERMINISTIC指的是每次输入一样,那么输出也是一样的。默认是NOT DETERMINISTIC。官方文档只是提到,建议与实际结果保持一致,不然有可能会影响性能。
- { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:
- CONTAINS SQL:表示不包含读或写数据的语句,这是默认值
- NO SQL:表示不包含SQL语句
- READS SQL DATA: 表示包含读数据的语句
- MODIFIES SQL DATA :表示包含写数据的语句
- SQL SECURITY:取值是DEFINER 或INVOKER,指明是以存储过程的创建者还是调用者的权限来执行。如果是INVOKER,存储过程内部访问了调用者不能访问的资源,会报错。默认值是DEFINER 。
参数、本地变量、返回值的处理
- 严格模式下,数据类型不匹配或溢出,会报错
- 只有标量值可以赋值。像SET x = (SELECT 1, 2)这样的语句是无效的。
- 如果声明中包含CHARACTER SET,那么将使用指定的字符集及其默认的排序方式。如果COLLATE属性也存在,那么将使用该排序而不是默认的排序。如果没有指定字符集,会使用存储过程创建时数据库的字符集。之后改变数据库的字符集,不会自动应用到已存在的存储过程。
函数
函数的创建语法如下:
CREATE
[DEFINER = user]
FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
函数的创建与存储过程相似,但是函数一定要有返回值(RETURNS type)。而且存储过程的参数可以用IN、OUT、INOUT等类型,函数的参数其实只是相当于IN。
函数与存储过程的对比
- 关键字不同,一个是FUNCTION ,另一个是PROCEDURE
- 函数必须要有返回值,存储过程没有
- 函数参数只能是IN,存储过程的参数类型可以是IN、OUT、INOUT
- 函数内部不能出现显示或者隐含的commit,所以create table 这样的语句不允许出现在函数内部;存储过程可以出现DDL
- 存储过程需要使用call来调用,而函数需要在SQL表达式中使用
- 存储过程内部可以调用函数,但是不可以在函数中调用存储过程
简单来说,存储过程可以看成一系列SQL语句的集合体,而函数是为完成特定计算任务而设计的。