MySQL储存过程详解

1 概述

存储过程就像是程序中的子程序，存储在数据库中。存储过程可以有名称、参数列表和 SQL 语句。使用 CALL 语句调用。

2 特点

• 运行速度快。利用缓存的优势等。
• 可移植的

好处：

• 简化应用开发人员的工作。
• 增强安全性。
• 减少数据在数据库和应用服务器之间的传输。

3 案例

实际运行创建一个存储存储过程

DELIMITER;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `selectitem`()
BEGIN
	#Routine body goes here...
SELECT * from sys_role;
END;;
DELIMITER;

说明：

分为几部分:
DELIMITER 设置结束符，设置;;的原因是避免使用;时会直接导致创建存储过程中出现;时会结束。
create 创建
DEFINER=`root`@`localhost` 声明定义者
PROCEDURE 存储过程关键字
`selectitem`() 存储过程名称

-- 执行的逻辑体
BEGIN
	#Routine body goes here...
SELECT * from sys_role;
END

调用存储过程，使用call命令

call selectitem`

4 特征子句(了解)

在创建一个存储过程时可以添加对这个过程的特征，称为特征子句。有以下特征

• COMMENT

它用于描述存储的例程

• LANGUAGE SQL

表明储存过程使用SQL编写

• NOT DETERMINISTIC

不确定性，相同的输入参数产生不同结果

• CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA

CONTAINS SQL：包含 SQL （默认值）

NO SQL：不包含 SQL 语句

READS SQL DATA：含读取数据的语句，不含写入数据的语句

MODIFIES SQL DATA：包含写入数据的语句

• SQL SECURITY { DEFINER | INVOKER }

指定安全上下文，默认值是 DEFINER，就是执行该储存过程的方式。

如果是DEFINER ，执行存储过程前验证definer对应的用户是否存在，是否拥有执行权限。

如果是INVOKER ，执行存储过程前验证调用该储存过程的用户是否有对应权限。

4.1举例

CREATE PROCEDURE p2 ()
    LANGUAGE SQL
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
    SELECT * FROM student; 

5 复合语句

复合语句是一个包含执行块，变量，条件等的处理程序。

5.6版本开始Mysql有以下复合语句：

• BEGIN … END

• Statement Label 陈述标签

• DECLARE 声明

• Variables in Stored Programs 存储程序中的变量

• Flow Control Statements 流程控制声明

• Cursors 游标

• Condition Handling 状况处理

5.1 BEGIN … END

BEGIN … END是很常用的语句块，通常代表的是一个操作域。用于编写复合语句。

CREATE DEFINER=`root`@`localhost` PROCEDURE `userpre`()
 COMMENT '测试存储过程'
BEGIN
	#Routine body goes here...
	SELECT * from user;
END

在一个BEGIN … END块内的局部变量与其他BEGIN … END块的局部变量不共享

5.2 DECLARE

DECLARE 能够用于在存储过程中声明变量。 需要在BEGIN… END 复合语句中使用。在使用前先声明。

CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`()
    COMMENT '测试存储过程'
BEGIN
	-- 局部变量
	DECLARE fr int;
	#Routine body goes here...
	SELECT * from user;
END

局部变量，只在声明它们的 BEGIN END 块中有效。

CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`()
    COMMENT '测试存储过程'
BEGIN
	DECLARE fr int;
	set fr = 6 + 2;
	SELECT fr;
END

6 用户变量

除了有局部变量，还有用户变量，定义使用@变量名。一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

CREATE DEFINER=`root`@`localhost` PROCEDURE `user_test_pre`(