MYSQL数据库之存储过程与存储函数

存储过程和存储函数是指在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和存储函数来指向已经定义好的SQL语句。

创建存储过程的基本形式：

REATE PROCEDURE sp_name ([proc_parameter[...]])
[characteristic ...] routine_body

参数说明：

1、sp_name参数是存储过程的名称

2、proc_parameter表示存储过程的参数列表

3、characteristic参数指定存储过程的特性

4、routine_body参数是SQL代码的内容

proc_parameter中的参数由3部分组成，分别是输入／输出类型、参数名称和参数类型。

其形式为[ IN | OUT | INOUT ]param_name type。

其中，IN表示输入参数；OUT表示输出参数；INOUT表示既可以输入也可以输出；param_name参数是存储过程参数名称；type参数指定存储过程的参数类型，该类型可以为MySQL数据库的任意数据类型。

存储过程例子：

 Mysql中存储过程的建立以关键字CREATE PROCEDURE开始，后面紧跟存储过程的名称和参数。MYSQL的存储过程名称不区分大小写。存储过程名或存储函数名不能与MUSQL数据库中的内建函数重名。

MYSQL存储过程的语句块以begin开始，以END结束。

语句体中可以包含变量的声明，控制语句，SQL查询语句。

由于存储过程内部语句要以分号结束，所以在定义存储过程前，应将语句结束标志';'更改为其他字符，并且应降低该字符在存储过程中的出现的概率，更改结束标志可以使用关键字DELEMITER定义。

如：delimiter //

删除存储过程：

drop procedure proc_name　　　　　　其中proc_name指的是存储过程名

 

创建存储函数

创建存储函数的基本形式为：

CREATE FUNCTION sp_name ([func_parameter[,...]])

RETURNS type

[characteristic ...] routine_body

参数说明：

1、sp_name：存储函数的名称

2、func_parameter：存储函数的参数列表

3、RETURNS：指定返回值的类型

4、characteristic：指定存储过程的特性

5、routine_body：SQL代码的内容

func_parameter可以由多个参数组成，其中每个参数均由参数名称和参数类型组成，结构为：param_name type　　，param_name参数为存储函数的函数名称，type参数用于指定存储函数的参数类型，该类型是MYSQL数据库所支持的类型。

例子：

 存储过程中的参数主要由局部参数和会话参数两种，又可以称为局部变量和会话变量。局部变量只在定义该局部变量的BEGTIN...END范围内有效，会话变量在整个存储过程范围内均有效。

局部变量

局部变量以关键字DECLARE声明，后跟变量名和变量类型

如： DECLARE a int 

在声明局部变量时也可以用关键字DEFAULT为变量指定默认值。如： DECLARE a int default 10

全局变量

MYSQL中的会话变量不必声明即可使用，会话变量在整个过程中有效，会话变量名以字符‘@’作为起始字符。

为变量赋值

MySQL中可以使用关键字DECLARE来定义变量，其语法结构如下。

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

参数说明：

1、DECLARE用来声明变量

2、var_name参数是指变量的名称，如果用户需要，可以同时定义多个变量

3、type参数用来指定变量的类型

4、DEFAULT value的作用是指定变量的默认值，不对该参数进行设置时，其默认值为NULL

MySQL中可以使用关键字SET为变量赋值，其基本语法如下。

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

参数说明：

1、SET关键字用来为变量赋值

2、var_name参数是变量的名称

3、expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用“,”隔开。

另外一种变量赋值

SELECT col_name[,...] INTO var_name[,...] FROM table_name where condition

参数说明

1、col_name参数标识查询的字段名称

2、var_name参数是变量的名称

3、table_name参数为指定数据表的名称

4、condition参数为指定查询条件。

例如，从studentinfo表中查询name为LeonSK的记录，并将该记录下的tel字段内容赋值给变量customer_tel，其关键代码如下。

SELECT tel INTO customer_tel FROM studentinfo WHERE name= 'LeonSK ';

 

光标的作用

通过MySQL查询数据库，其结果可能为多条记录。在存储过程和函数中使用光标可以实现逐条读取结果集中的记录

光标使用包括声明光标（DECLARE CURSOR）、打开光标（OPENCURSOR）、使用光标（FETCH CURSOR）和关闭光标（CLOSE CURSOR）。光标必须声明在处理程序之前、变量和条件之后

声明光标

声明光标仍使用关键字DECLARE，其语法结构如下。

DECLARE cursor_name CURSOR FOR select_statement

参数说明：

1、cursor_name 为光标的名称

2、select_statement是一个select语句，返回一行或多行数据。

光标只能在存储过程或存储函数中使用。不能单独使用。

打开光标

在声明光标之后，要从光标中提取数据，必须首先打开光标。在MySQL中使用关键字OPEN来打开光标，其语法结构如下

OPEN cursor_name　　cursor_name是光标的名称，在程序中，一个光标可以打开多次。

使用光标

可以使用FETCH...INTO语句读取数据，其语法结构为：

FETCH  cursor_name INTO var_name[,var_name]…

参数说明

1、cursor_name代表已经打开光标的名称；

2、var_name参数表示将光标中SELECT语句查询出来的信息存入该参数中。

3、var_name是存放数据的变量名，必须在声明光标前定义好。

4、FETCH…INTO语句与SELECT…INTO语句具有相同的意义。

关闭光标

语法格式：　　CLOSE cursor_name;

 

调用存储过程和存储函数

存储过程和存储函数都是存储在服务器的SQL语句的集合。要使用已经定义好的存储过程和存储函数，必须通过调用的方式来实现。对存储过程和存储函数的操作主要可以分为调用，查看，修改和删除。

调用存储过程

存储过程的调用使用CALL语句来调用存储过程，调度员存储过程后，数据库系统将指向存储过程中的语句，然后将结果返回给输出值。

CALL语句的语法结构：

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

sp_name是存储过程名称，parameter是存储过程的参数。

调用存储函数

存储函数的使用方法：　　SE;ECT function_name([parameter[,…]]);

 

查看存储过程和存储函数

通过SHOW STATUS语句查看存储古城和存储函数的状态。通过SHOW CREATE语句查看存储过程和存储函数的定义

SHOW STATUS语句

查看存储过程和存储函数的状态，语法结构为：

SHOW {PROCEDURE | FUNCTION}STATUS[LIKE 'pattern']

参数说明：

1、PROCEDURE参数表示查询存储过程；

2、FUNCTION参数表示查询存储函数；

3、LIKE 'pattern'参数用来匹配存储过程或存储函数名称。

SHOW CREATE语句

查看存储过程和存储函数的状态，语法结构为：

SHOW CREATE{PROCEDURE | FUNCTION } sp_name;

参数说明：

1、PROCEDURE参数表示查询存储过程；

2、FUNCTION参数表示查询存储函数；

3、sp_name参数表示存储过程或存储函数的名称。

 

修改存储过程和存储函数

修改存储过程和存储函数是指修改已经定义好的存储过程和存储函数。使用ALTER PROCEDURE语句来修改存储过程，通过ALTER FUNCTION语句来修改存储函数

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

参数说明：

 

删除存储过程和存储函数

删除存储过程和存储函数指删除数据库中已经存在的存储过程或存储函数。通过DROP PROCEDURE语句来删除存储过程，通过DROP FUNCTION语句来删除存储函数。

在删除之前，必须确认该存储过程或存储函数没有任何依赖关系，否则可能会导致其他与其关联的存储过程或存储函数无法运行。

删除存储过程和删除函数的语法结构：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

1、sp_name参数表示存储过程或存储函数的名称；

2、IF EXISTS是MySQL的扩展，判断存储过程或存储函数是否存在，以免发生错误。