数据库之存储过程和函数

目录

一、存储过程和函数概述

二、创建并调用存储过程和函数

1.创建存储过程

2.创建存储函数

3.调用存储过程和函数

三、关于存储过程和函数的表达式

1.变量

1.变量的分类

2.在存储过程和函数中应用变量

2.定义条件和处理程序

1.定义条件

2.定义处理程序

3.游标的使用

1.定义游标

2.打开游标

3.使用游标

4.关闭游标

4.流程控制的使用

四、查看存储过程和函数

1.查看存储过程和函数的状态

2.查看存储过程和函数的定义

3.查看存储过程和函数的信息

五、修改和删除存储过程和函数

1.修改存储过程和函数

2.删除存储过程和函数

---

一、存储过程和函数概述

存储过程和函数具有以下优点：

1.允许标准组件式编程：存储过程和函数在创建后可以在程序中被多次调用，有效提高了SQL语句的重要性、共享性和可移植性。

2.较快的执行速度：如果某一操作包含大量的事务处理代码，并且被多次执行，那么存储过程要比批处理的执行执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器会对其进行分析优化，并将最终执行计划存储在系统中，而批处理的事务处理语句在每次运行时都要进行编译和优化。

3.减少网络流量：对于大量的SQL语句，将其组织成存储过程，会比一条一条的调用SQL语句要大大节省网络流量，降低网络负载。

4.安全：数据库管理员通过设置执行某一存储过程的权限，从而限制相应数据的访问权限，避免非授权用户对数据的访问，保证数据的安全。

---

存储过程和函数也存在一定的缺陷：

1.存储过程和函数的编写比单个SQL语句的编写要复杂很多，需要用户具有更高的技能和更丰富的经验。

2.在编写存储过程和函数时，需要创建这些数据库对象的权限。

---

二、创建并调用存储过程和函数

存储程序可以分为存储过程和函数。存储过程和函数的操作主要包括创建存储过程和函数、调用存储过程和函数、查看存储过程和函数，以及修改和删除存储过程和函数。

---

1.创建存储过程

创建存储过程使用SQL语句CREATE PROCEDURE来实现，其语法形式如下：

CREATE PROCEDURE proc_name([proc_parameter[,...]])

[characteristic...] routine_body

---

（1）CREATE PROCEDURE为创建存储过程的关键字。

（2）proc_name表示要创建的存储过程名。

（3）proc_parameter表示存储过程的参数，参数形式如下：

[IN|OUT|INOUT] parameter_name TYPE

其中IN表示输入参数，可把外界的数据存储到存储过程当中；OUT表示输出参数，可把存储过程的运算结果传递到外界；INOUT表示输入输出参数，既可以把外界的数据传递到存储过程当中，又可以把存储过程的运算结果传递到外界；parameter_name表示参数名；TYPE表示参数的数据类型。

注：存储过程中的参数名不要与数据表中的字段名重复，否则系统会报错。

（4）characteristic表示存储过程的特性，可取值及其意义如下：

LANGUAGE SQL：表示存储过程的routine_body部分使用SQL语言编写，当前系统支持的语言为SQL。

[NOT]DETERMINISTIC：DETERMINISTIC表示存储过程的执行结果是确定的，就是每次输入相同的参数并执行存储过程后，得到的结果是相同的；默认为NOT DETERMINISTIC，表示执行结果不确定，即相同的输入可能得到不同的结果。

{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}：指明子程序使用SQL语句的限制。CONTAINS SQL为默认值，表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序不包含SQL语句；READS SQL DATA表示子程序包含读取数据的语句，但不包含写数据的语句；MODIFIES SQL DATA表示子程序包含写入数据的语句。

SQL SECURITY {DEFINER|INVOKER}：指定可执行存储过程的用户，DEFINER表示只有创建者才能执行，INVOKER表示拥有权限的调用者可以执行。

COMMENT ‘string’：表示存储过程或者函数的注释信息。

（5）routine_body表示需要执行的SQL语句的集合，可以使用BEGIN表示开始，使用END表示结束。

---

2.创建存储函数

创建存储函数使用SQL语句CREATE FUNCTION来实现，其语法形式如下：

CREATE FUNCTION func_name([parameter_name[,...]])

RETURNS TYPE

[characteristic...] routine_body

---

CREATE FUNCTION为创建存储函数的关键字；func_name表示存储函数名；parameter_name表示存储函数参数名；TYPE表示函数返回值的数据类型；characteristic指定存储函数的特性，取值与创建存储函数参数名；routine_body表示函数体。

---

3.调用存储过程和函数

存储过程必须使用关键字CALL调用，而存储函数与MySQL内置函数的调用相同，使用关键字SELECT。

1.调用存储过程

通常使用关键字CALL调用存储过程，其语法形式如下：

CAL procedure_name({parameter[,...]});

其中的parameter表示变量名，存储过程的返回值将赋予该变量。

2.调用存储函数

通常使用关键字SELECT调用存储函数，其语法形式如下：

SELECT function_name([parameter[,...]]);

---

三、关于存储过程和函数的表达式

1.变量

变量是表达式中最基本的元素，可用于存储临时数据。

1.变量的分类

用户变量：带有前缀@，只能被定义它的用户使用，作用于当前整个连接，当前连接断开后，所定义的用户变量会被全部释放。用户变量不用提前定义就可以直接使用。

局部变量：没有前缀，一般用于SQL语句块中，比如存储过程的BEGIN...END中。其作用域仅限于该语句块，在语句块执行完毕后，局部变量就会被释放。局部变量使用前需要先通过DECLARE声明。如果没有声明，则初始值为NULL。

系统变量：带有前缀@@，MySQL有许多已经设置默认值的系统变量。系统变量包含全局变量和会话变量。全局变量会影响整个服务器，而会话变量只影响个人客户端连接。

---

2.在存储过程和函数中应用变量

局部变量可以在子程序中定义并应用，其作用范围是BEGIN...END语句块。

---

（1）定义变量

在存储过程中使用DECLARE语句定义局部变量，其语法形式如下：

DECLARE var_name[,...] type [DEFAULT value];

上述语句中，var_name为局部变量名称，type为变量的数据类型，DEFUALT value是为变量指定的默认值。如果没有DEFAULT value，初始值为NULL。

例如，定义一个INT类型的变量，名称为var1：

DECLARE var1 INT;

注：变量的定义必须在复合语句开头，并且在任何其他语句前面。也就是说，DECLARE语句在存储过程和函数中使用时，必须出现在BEGIN...END语句块的最前面，并且变量名不区分大小写。可以一次声明多个相同类型的变量。

---

（2）为变量赋值

定义变量之后，可以使用SET关键字为变量赋值，语法形式如下：

SET var_name = expr [,var_name = expr]...;

变量值可以为常量或者表达式。

另外，也可以使用SELECT...INTO...查询语句将查询结果赋给变量，这要求查询结果必须只有一行，具体语法形式如下：

SELECT col_name[,......] INTO var_name[,......] FROM table_name;

Col_name为字段名，var_name为变量名。

---

2.定义条件和处理程序

条件和处理程序是MySQL提供的一种异常处理机制，定义条件是事先定义程序执行过程中可能会遇到的问题；定义处理程序是定义在遇到问题时执行的相应的处理方法，并且保证存储过程和函数在遇到问题时不终止。

---

1.定义条件

在MySQL中定义条件使用DECLARE...CONDITION语句，其语法形式如下：

DECLARE condition_name CONDITION FOR [condition_type];

上述语句中，condition_name表示条件名。Condition_type表示条件的类型，其可取值及其意义如下：

Mysql_error_code：表示数值类型错处代码。

Sqlstate_value：表示长度为5的字符串类型错误代码。

注：数值类型的错误代码不要使用0，因为0表示成功而不是错误；字符串类型的错误代码不要使用‘00’，因为‘00’表示成功而不是错误。

---

2.定义处理程序

在定义条件之后，可以使用DECLARE...HANDLER语句定义处理程序，语法形式如下：

DECLARE handler_type HANDLER FOR condition_value[...] statement

下面简单介绍上述语句中各组成部分及其意义。

---

（1）handler_type为异常处理方式，可取值及其意义如下：

CONTINUE：表示遇到错误不处理，程序继续执行。

EXIT：表示遇到错误立即退出程序。

UNDO：表示遇到错误后撤回之前的操作（目前MySQL暂不支持）。

---

（2）condition_value表示错误值，可取值及其意义如下：

Mysql_error_code：表示数值类型错处代码。

Sqlstate_value：表示包含5个字符串类型错误值。

Condition_name：表示使用DECLARE...CONDITION语句定义的条件名。

SQLWARNING：匹配所有01开头的SQLSTATE错误代码。

NOT FOUND：匹配所有02开头的SQLSTATE错误代码。

SQLEXCEPTION：匹配所有未被SQLWARNING和NOT FOUND捕获的SQLSTATE错误代码。

---

（3）statement为程序语句段，表示在遇到定义的异常条件时，需要执行的存储过程或函数。

定义处理程序有以下6中方法：

方法一：捕获sqlstate_value

方法二：捕获mysql_error_code

方法三：先定义条件，然后再调用条件

方法四：使用SQLWRENING

方法五：使用NOT FOUND

方法六：使用SQLEXCEPTION

---

3.游标的使用

在存储过程和函数中，当查询语句返回多条记录时，可以使用游标对结果集进行逐条读取。

---

1.定义游标

在MySQL中，使用DECLARE关键字来定义游标，其语法形式如下：

DECLARE cursor_name CURSOR FOR select_statement;

上述语句中，cursor_name表示游标名，select_statement表示SELECT语句，返回一个用于创建游标的结果集。

---

2.打开游标

打开游标的关键字为OPEN，其语法形式如下：

OPEN cursor_name;

注：在打开一个游标时，游标并不指向第一条记录，而是指向第一条记录的前边。

---

3.使用游标

使用游标的关键字是FETCH，其语法形式如下：

FETCH cursor_name INTO var_name [,var_name] ...

上述语句的作用是将定义游标cursor_name时查询出的数据赋予变量var_name。

---

4.关闭游标

关闭游标的关键字为CLOSE，其语法形式如下：

CLOSE cursor_name;

---

4.流程控制的使用

流程控制语句是指可以控制程序运行顺序的指令，程序运行顺序主要包括顺序执行、条件执行和循环执行。MySQL支持的流程控制语句包括IF语句、CASE语句、LOOP语句、REPEAT语句、WHILE语句、LEAVE语句、ITERATE语句和RETURN语句。

1.IF语句

IF实现条件判断，语句中可以包含多个判断条件，系统会根据条件的结果是否为TRUE执行相应的操作，语法形式如下：

IF search_condition THEN statement_list

[ELSEIT search_condition THEN statement_list]...

[ELSE statement_list]

END IF

上述语句中，search_condition为判断条件，statement_list为相应操作，如果所有判断条件均不为TRUE，则执行ELSE子句中的操作。

---

2.CASE语句

CASE语句可以实现比IF语句更复杂的条件操作，该语句有两种使用形式。

第1种语法形式如下：

CASE case_expr

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list]...

[ELSE statement_list]

END CASE

上述语句中，case_expr表示判断条件的表达式，将此表达式与每个WHEN子句中的when_value值进行比较，直到与其中一个相等，此时，执行相应THEN子句中的statement_list。如果表达式与所有when_value值都不相等，则执行ELSE子句中的statement_list。

第2种语法形式如下：

CASE case_expr

WHEN search_condition THEN statement_list

[WHEN search_condition THEN statement_list]...

[ELSE statement_list]

END CASE

上述语句中，系统会对每个WHEN子句中的search_condition表达式进行判断，直到某个search_condition表达式为TRUE，此时将执行其对应的THEN子句中的statement_list。如果所有search_condition表达式的值都不为TRUE，则执行ELSE子句中的statement_list。

---

3.LOOP语句和LEAVE语句

LOOP语句可以实现简单的循环，使得系统能够重复执行循环结构内的语句列表。该语句列表由一条或多条语句组成，每条语句使用（；）隔开。语法形式如下：

[loop_label:]LOOP

Statement_list

END LOOP[end_list]

上述语句中，loop_list表示LOOP语句的标注名称（可以省略），statement_list表示需要循环执行的SQL语句。

如果不在statement_list中增加退出循环的语句，LOOP语句可以实现简单的死循环。使用LEAVE语句可以退出循环。语法形式如下：

LEAVE label;

其中，label参数表示循环的标注名。

---

4.REPEAT语句

REPEAT语句可以实现一个带条件判断的循环结构。语法形式如下：

[repeat_label:]REPEAT

Statement_list

UNTIL search_condition

END REPEAT [repeat_label]

repeat_label表示REPEAT语句的标注名称（可以省略），每次SQL语句statement_list执行完毕后，会对条件search_condition进行判断，如果结果为TRUE，循环结束，否则继续执行循环中的语句。

---

5.WHILE语句

WHILE语句同样可以实现一个带条件判断的循环结构，但与REPEAT语句不同的是，WHILE语句会先对条件进行判断，如果为TRUE，才会执行需要循环的操作，否则终止循环，语法形式如下：

[while_label:]WHILE search_condition DO

Statement_list

END WHILE[while_label]

上述语句中，while_label为WHILE语句的标注名称，search_condition为判断条件，statement_list为需要循环的操作。

---

6.ITERATE语句

ITERATE语句只可以出现在LOOP语句、REPEAT语句和WHILE语句中，意义为再次执行循环，语法形式如下：

ITERATE label;

上述语句中，label表示循环的标志。

---

四、查看存储过程和函数

1.查看存储过程和函数的状态

使用SHOW STATUS语句可以查看存储过程和函数的状态。基本语法形式如下：

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE ‘pf_name’];

PROCEDURE或FUNCTION指定查看的是存储过程还是函数，LIKE语句指定存储过程和函数的名称。

主要参数及其意义如下：

Db：表示存储过程或函数所属数据库。

Name：表示存储过程或函数名。

Type：表示是存储过程还是函数。

Definer：表示创建存储过程或函数的用户。

Modified：表示最后修改日期。

Created：表示创建日期。

Security_type：表示MySQL在执行存储过程和函数的时候，是以创建函数的权限来执行，还是以调用者的权限来执行。

---

2.查看存储过程和函数的定义

使用SHOW CREATE语句可以查看存储过程和函数的定义语句，语法形式如下：

SHOW CREATE {PROCEDURE|FUNCTION} pf_name;

PROCEDURE或FUNCTION指定查看的是存储过程还是函数，pf_name指定存储过程或函数名。

主要参数及其意义如下：

Procedure：表示存储过程名。

Sql_name：表示SQL语句的模式。

Create Procedure：表示存储过程的定义语句。

---

3.查看存储过程和函数的信息

在MySQL中，存储过程和函数的信息存储在系统数据库information_schema中的routines表中，查看存储过程和函数详细信息的语法形式如下：

SELECT * FROM information_schema.routines

WHERE ROUTINE_NAME=’pf_name’;

上述语句中，ROUTINE_NAME指定存储过程或函数名，如果有存储过程和存储函数名相同，还可以使用ROUTINE_TYPE指定类型。

主要参数及其意义如下：

ROUTINE_CATALOG：表示存储过程或函数的目录。

ROUTINE_SCHEMA：表示存储过程或函数所属数据库。

ROUTINE_NAME：表示存储过程或函数名。

ROUTINE_TYPE：表示是存储过程还是存储函数。

ROUTINE_DEFINITION：表示BEGIN...END语句。

SECURITY_TYPE：表示MySQL在执行存储过程和函数的时候，是以创建用户的权限来执行，还是以调用者的权限来执行。

---

五、修改和删除存储过程和函数

1.修改存储过程和函数

在MySQL中，使用ALTER关键字可以修改存储过程和函数，基本语法形式如下：

ALTER {PROCEDURE|FUNCTION} pf_name [characteristic...];

上述语句中，pf_name表示存储过程或函数名。characteristic表示存储过程和函数的特性，其可取值有CONTAINS SQL，NO SQL，READS SQL DATA，MODIFIES SQL DATA，SQL SECURITY{DEFINER|INVOKER}，各值得意义与创建存储过程和函数时相同。

---

注：修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句，这两个语句结构相同，参数也一样。并且它们与创建存储过程和函数得语句中的参数也基本一样。

不能使用关键字ALTER更改存储过程的参数或子程序，如果需要修改，必须删除存储过程后再重新创建。

---

2.删除存储过程和函数

在MySQL中，删除存储过程和函数可以使用DROP语句，语法形式如下：

DROP {PROCEDURE|FUNCTION} [IF EXISTS] pf_name;

pf_name为要删除的存储过程或函数名。使用IF EXISTS可以在执行删除操作时，先判断存储过程和函数是否存在，避免系统报错。