数据库系统原理——第五章 数据库编程知识点总结（自考本）

不要着急树苗，终究会长成参天大树的

存储过程

存储过程的基本概念

存储过程是一组为了完成某项特定功能的 SQL 语句集，其实质上就是一段存储在数据库中的代码，它可以由声明式的 SQL 语句（如 CREATE、 UPDATE 和SELECT 等语句） 和过程式 SQL 语句（如 IF…THEN…ELSE 控制结构语句）组成。

使用存储过程通常具有以下一些好处：
（1） 可增强 SQL 语言的功能和灵活性；
（2） 良好的封装性；
（3） 高性能；
（4） 可减少网络流量；
（5） 存储过程可作为一种安全机制来确保数据库的安全性和数据的完整性。

创建存储过程

DELIMITER 命令将 MySQL 语句的结束标志临时修改为其他符号， 从而使得MySQL 服务器可以完整地处理存储过程体中所有的 SQL 语句，而后可通过DELIMITER 命令再将 MySQL 语句的结束标志改回为 MySQL 的默认结束标志，即分号（； ） 。
DELIMITER 命令的使用语法格式是：

DELIMITER $$

在 MySQL 中，是使用 CREATE PROCEDURE 语句来创建存储过程，其常用的语法格式是：

CREATE PROCEDURE sp_name（[proc_parameter[， …]]）
Routine_body

其中，语法项“**proc_parameter”**的语法格式是：

[IN|OUT|INOUT]param_name type

在此语法格式中：
（1） 语法项**“sp_name”用于指定存储过程的名称**，且默认在当前数据库中创建。
（2） 语法项**“proc_parameter”用于指定存储过程的参数列表**。
（3） 语法项**“routine_body”表示存储过程的主体部分**，也称为存储过程体。

存储过程体

局部变量

在存储过程体中可以声明局部变量， 用来存储存储过程体中的临时结果。在MySQL 中，可以使用 DECLARE 语句来声明局部变量，并且同时还可以对该局部变量赋予一个初始值，其使用的语法格式是：

DECLARE var_name[， …]type[DEFAULT value]

其中， 语法项**“var_name”** 用于指定局部变量的名称； 语法项**“type”** 用于声明局部变量的数据类型； DEFAULT 子句用于为局部变量指定一个默认值， 若没有指定，则默认为 NULL。
需要注意的事项如下：
（1） 局部变量只能在存储过程体的 BEGIN…END 语句块中声明。
（2） 局部变量必须在存储过程体的开头处声明。
（3） 局部变量的作用范围仅限于声明它的 BEGIN…END 语句块， 其他语句块中的语句不可以使用它。
（4） 局部变量不同于用户变量，两者的区别是：局部变量声明时，在其前面没有使用 @ 符号，并且它只能被声明它的 BEGIN…END 语句块中的语句所使用；而用户变量在声明时，会在其名称前面使用 @ 符号，同时已声明的用户变量存在于整个会话之中。

SET 语句

在 MySQL 中，可以使用 SET 语句为局部变量赋值，其使用的语法格式是：

SET var_name=expr[， var_name=expr] …

SELECT…INTO 语句

在 MySQL 中，可以使用 SELECT…INTO 语句把选定列的值直接存储到局部变量中，其使用的语法格式是：

SELECT col_name[,…]INTO var_name[,…] table_expr

在此语法格式中：

• 语法项**“col_name”用于指定列名**；
• 语法项**“var_name”用于指定要赋值的变量名**；
• 语法项**“table_expr”表示 SELECT 语句中的 FROM 子句及后面的语法部分。 此外， 需要注意的是：存储过程体中的 SELECT…INTO 语句返回的结果集只能有一行数据**。

流程控制语句

在 MySQL 中，可以在存储过程体中，使用条件判断语句和循环语句这样两类用于控制语句流程的过程式 SQL 语句。
（1）条件判断语句
常用的条件判断语句有 IF…THEN…ELSE 语句和 CASE 语句。它们的使用语法及方式类似于高级程序设计语言。
（2） 循环语句
常用的循环语句有 WHILE 语句、 REPEAR 语句和 LOOP 语句。它们的使用语法及方式同样类似于高级程序设计语言。 此外， 循环语句中还可以使用ITERATE 语句， 但它只能出现在循环语句的 LOOP、 REPEAT 和 WHILE 子句中，用于表示退出当前循环，且重新开始一个循环。

游标

游标是一个被 SELECT 语句检索出来的结果集。
在 MySQL 中，使用游标的具体步骤如下：
（1） 声明游标

DECLARE cursor_name CURSOR FOR select_statement

• 其中，语法项**“cursor_name”用于指定要创建的游标的名称**，其命名规则与表名相同；
• 语法项**“select_statement”用于指定一个 SELECT 语句**，其会返回一行或多行的数据，且需注意此处的 SELECT 语句不能有 INTO 子句。

（2） 打开游标

OPEN cursor_name

其中，语法项“cursor_name”用于指定要打开的游标。

（3） 读取数据

FETCH cursor_name INTO var_name[,var_name]…

• 其中，语法项“cursor_name”用于指定已打开的游标；
• 语法项**“var_name”用于指定存放数据的变量名**。
  （4） 关闭游标

CLOSE cursor_name

其中，语法项“cursor_name”用于要关闭的游标。

在使用游标过程中，需要注意以下几点：
（1） 游标只能用于存储过程或存储函数中，不能单独在查询操作中使用。
（2） 在存储过程或存储函数中可以定义多个游标，但是在一个 BEGIN…END 语句块中每一个游标的名字必须是唯一的。
（3） 游标不是一条 SELECT 语句，是被 SELECT 语句检索出来的结果集。

调用存储过程

CALL sp_name[parameter[,…]]
CALL sp_name[()]

在此语法格式中：
（1） 语法项“sp_name”用于指定被调用的存储过程的名称。如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。
（2） 语法项**“parameter”用于指定调用存储过程所要使用的参数。** 调用语句中参数的个数必须等于存储过程的参数个数。
（3） 当调用没有参数的存储过程时，使用 CALL sp_name()语句与使用 CALLsp_name 语句是相同的。

删除存储过程

DROP PROCEDURE[IF EXISTS] sp_name

其中，语法项**“sp_name”用于指定要删除的存储过程的名称**。

存储函数

存储函数和存储过程的区别：
（1） 存储函数不能拥有输出参数，这是因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。
（2） 可以直接对存储函数进行调用，且不需要使用 CALL 语句；而对存储过程的调用，需要使用 CALL 语句。
（3） 存储函数中必须包含一条 RETURN 语句， 而这条特殊的 SQL 语句不允许包含于存储过程中。

创建存储函数

CREATE FUNCTION sp_name([func_parameter[， …]])
RETURNS type
routine_body

其中， 语法项**“func_parameter”**的语法格式是：

param_name type

在此语法格式中：
（1） 语法项**“sp_name”用于指定存储函数的名称**，需注意，存储函数不能与存储过程具有相同的名字。
（2） 语法项**“func_parameter”用于指定存储函数的参数**，这里的参数只有名称和类型，不能指定关键字“IN”“OUT”和“INOUT”。
（3） RETURNS 子句用于声明存储函数返回值的数据类型，其中 type 用于指定返回值的数据类型。
（4） 语法项**“routine_body”用于指定存储函数的主体部分**，也称为存储函数体。所有在存储过程中使用的 SQL 语句在存储函数中同样也适用，包括前面所介绍的局部变量、 SET 语句，流程控制语句、游标等。但是， 存储函数体中还必须包含一个 RETURN value 语句，其中 value 用于指定存储函数的返回值。

调用存储函数

成功创建存储函数后，就可以如同调用系统内置函数一样，使用关键字 SELECT 对其进行调用，其使用的语法格式是：

SELECT sp_name([func_parameter[， …]])

删除存储函数

在 MySQL 中，可以使用 DROP FUNCTION 语句来实现，其使用的语法格式是：

DROP FUNCTION[IF EXISTS] sp_name

其中， 语法项**“sp_name”指定要删除的存储函数的名称。**