理解MySQL存储过程和函数-木庄网络博客

本文整理自网络，侵删。

一、概述

一提到存储过程可能就会引出另一个话题就是存储过程的优缺点，这里也不做讨论，一般别人问我我就这样回答你觉得它好你就用它。因为mysql中存储过程和函数的语法非常接近所以就放在一起，主要区别就是函数必须有返回值（return），并且函数的参数只有IN类型而存储过程有IN、OUT、INOUT这三种类型。

二、语法

创建存储过程和函数语法

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
  [characteristic ...] routine_body
 
CREATE FUNCTION sp_name ([func_parameter[,...]])
  RETURNS type
  [characteristic ...] routine_body
  
  proc_parameter:
  [ IN | OUT | INOUT ] param_name type
  
  func_parameter:
  param_name type
 
type:
  Any valid MySQL data type
 
characteristic:
  LANGUAGE SQL
 | [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
 | COMMENT 'string'
 
routine_body:
  Valid SQL procedure statement or statements

语法来自官方自带的参考手册，characteristic语法块是需要注意的地方，先用一个例子来介绍。

例子：

#创建数据库
DROP DATABASE IF EXISTS Dpro;
CREATE DATABASE Dpro
CHARACTER SET utf8
;

USE Dpro;

#创建部门表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT NOT NULL PRIMARY KEY COMMENT '主键',
 name VARCHAR(20) NOT NULL COMMENT '人名',
 depid INT NOT NULL COMMENT '部门id'
);

#插入测试数据
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',101),(4,'李',102),(5,'郭',103);

#创建存储过程
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;

END$$
DELIMITER ;

#执行存储过程
CALL Pro_Employee(101,@pcount);

SELECT @pcount;

语法解释：

在创建存储过程的时候一般都会用DELIMITER$$.....END$$ DELIMITER ;放在开头和结束，目的就是避免mysql把存储过程内部的";"解释成结束符号，最后通过“DELIMITER ;”来告知存储过程结束。

主要解释characteristic部分：

LANGUAGE SQL：用来说明语句部分是SQL语句，未来可能会支持其它类型的语句。

[NOT] DETERMINISTIC：如果程序或线程总是对同样的输入参数产生同样的结果，则被认为它是“确定的”，否则就是“非确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC，默认的就是NOT DETERMINISTIC（非确定的）CONTAINS SQL：表示子程序不包含读或写数据的语句。

NO SQL：表示子程序不包含SQL语句。

READS SQL DATA：表示子程序包含读数据的语句，但不包含写数据的语句。

MODIFIES SQL DATA：表示子程序包含写数据的语句。

SQL SECURITY DEFINER：表示执行存储过程中的程序是由创建该存储过程的用户的权限来执行。

SQL SECURITY INVOKER：表示执行存储过程中的程序是由调用该存储过程的用户的权限来执行。（例如上面的存储过程我写的是由调用该存储过程的用户的权限来执行，当前存储过程是用来查询Employee表，如果我当前执行存储过程的用户没有查询Employee表的权限那么就会返回权限不足的错误，如果换成DEFINER如果存储过程是由ROOT用户创建那么任何一个用户登入调用存储过程都可以执行，因为执行存储过程的权限变成了root）

COMMENT 'string'：备注，和创建表的字段备注一样。

注意：在编写存储过程和函数时建议明确指定上面characteristic部分的状态，特别是存在复制的环境中，如果创建函数不明确指定这些状态会报错，从一个非复制环境将带函数的数据库迁移到复制环境的机器上如果没有明确指定DETERMINISTIC, NO SQL, or READS SQL DATA该三个状态也会报错。

阅读剩余部分