第八章 存储过程与存储函数

8.1存储过程

　　概念

　　　　一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。

　　　　存储过程的优点：

　　　　　　存储过程增强了SQL语言的功能和灵活性

　　　　　　存储过程允许标准组件是编程。

　　　　　　存储过程能实现较快的执行速度。

　　　　　　存储过程能过减少网络流量。

　　　　　　存储过程可被作为一种安全机制来充分利用。

　　1. 创建和使用存储过程

　　　　（1）存储过程

　　　　　　创建存储过程语法格式：

create procedure sp_name ([proc_parameter[，..]])
[characteristic ..] routine_body 

　　　　　　技巧：创建存储过程时，系统默认指定contains SQL，表示存储过程中使用了SQL语句。但是，如果存储过程中没有使用SQL语句，最好设置为no SQL。

　　　　　　调用存储过程语法格式：

call sp_name([parameter[，…]]

　　　　　　说明：sp_name为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。

　　　　（2）delimiter命令

　　　　　　可以用MySQL delimiter来改变默认的结束标志。

　　　　　　语法格式：

delimiter $$

 

　　　　　　说明：$$是用户定义的结束符，通常使用一些特殊的符号。

　　　　　　【例1】把结束符改为##，执行select 1+1##。

 

　　2. 变量

　　　　（1）declare 语句申明局部变量

　　　　　　存储过程和函数可以定义和使用变量，它们可以用来存储临时结果。

　　　　　　declare语法格式: 

declare var_name1 [,var_name2] . . . type [ default value ]

　　　　（2）用set语句给变量赋值

 

　　　　　　set语法格式: 

set var_name = exper[,var_name = exper]

　　　　　　说明：declare 定义的变量的作用范围是begin … end块内，只能在块中使用。set 定义的变量用户变量。

　　　　（3）使用select语句给变量赋值

　　　　　　select语法格式:

select col_name[,. . . ] into var_name[, . . .] table_expr

　　　　　　【例4】定义一个存储过程，作用是输出连个字符串拼接后的值

 

 

　　3. 定义条件和处理

　　　　（1）定义条件

　　　　　　语法格式：

declare condition_name condition for condition_value
condition_value
SQLstate[value] SQLstate_value
| MySQL_error_code

　　　　　　【例5】 下面定义"error 1111 (13d12)"这个错误，名称为can_not_find。

 

　　　　　　方法一：

　　　　　　　　使用SQLstate_value declare can_not_find condition for SQLstate '13d12' ;

 

　　　　　　方法二：

　　　　　　　　使用MySQL_error_code declare can_not_find condition for 1111 ;

 

　　　　（2）定义处理程序

　　　　　　语法格式：

declare handler_type handler for 
condition_value[,...] sp_statement  
handler_type:  
    continue | exit | undo  
condition_value:  
    SQLstate [value] SQLstate_value |
condition_name  | SQLwarning  
       | not found  | SQLexception  | MySQL_error_code

 

　　4. 游标的使用

　　　　我们可以认为游标就是一个cursor，就是一个标识，用来标识数据取到什么地方了。你也可以把它理解成数组中的下标。

　　　　游标(cursor)具有以下特性：

　　　　　　（1）只读的，不能更新的

　　　　　　（2）不滚动的

　　　　　　（3）不敏感的，不敏感意为服务器可以活不可以复制它的结果表

　　　　　　说明：游标(cursor)必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。

　　　　　　（1）游标的声明

　　　　　　　　语法格式：

declare cursorname cursor for select _ statement

　　　　　　　　注意：这里的select子句不能有into子句。

　　　　　　（2）打开游标

　　　　　　　　语法格式：

Open cursor_ name 

　　　　　　（3）读取游标

　　　　　　　　语法格式：

fetch  cursor_name into var_ name [， var_name] …  

　　　　　　　　说明：var_name是存放数据的变量名。

　　　　　　（4）关闭游标

　　　　　　　　游标使用完以后，要及时关闭。关闭游标使用close语句

　　　　　　　　语法格式：

close cursorname  

　　　　　　【例6】利用游标读取student表中总人数，此功能可以直接使用count函数直接完成，此实例主要为演示游标的使用方法。

　　5. 流程的控制

　　　　存储过程和函数中可以使用流程控制来控制语句的执行。

　　　　（1）if语句

 　　　　　　语法形式：

if search_condition then statement_list
[elseif search_condition then statement_list]
…
[else search_condition then statement_list]
end if

　　　　（2）case语句

　　　　　　语法形式：

case case_value
    when when_value then statement_list
    [when when_value then statement_list]
    …
    [else statement_list]
end case

　　　　（3）loop语句

　　　　　　loop语句可以使用某些特定的语句重复执行，实现简单的循环。

　　　　　　语法形式：

[begin_label:] loop
    statement_list
end loop [end_label]

　　　　（4）case语句

　　　　　　leave语句主要用于跳出循环。

　　　　　　语法形式：

level label

　　　　（5）itebate语句

　　　　　　itebate语句主要用于跳出本次循环，然后进入下一轮循环。

　　　　　　语法形式：

itebate label

　　　　（6）repate语句

　　　　　　repate语句是有条件控制的循环语句。

　　　　　　语法形式：

[begin_label:] repeat
    statement_list
     until search_confition
end repeat [end_label]

　　　　（7）while语句也是有条件控制的循环语句。

　　6. 查看存储过程

　　　　(1) 查看存储过程的状态

　　　　　　查看存储状态时需要通过show status语句，该语句还适用于查看自定义函数的状态。

　　　　　　语法格式：

show{ procedure | function} status [like  ‘pattern’];   

　　　　　　【例14】查看studentcount 存储过程的状态

　　　　(2) 查看存储过程的具体信息

　　　　　　如果要查看存储过程的详细信息，要使用show create语句

　　　　　　语法格式：

show create { procedure | function} sp_name;   

　　　　(3) 查看所有的存储过程

　　`　　　　语法格式：

select * from information_schema.routines [where routine_name = '名称'];    

　　　　　　【例15】通过select语句查询出存储过程studentcount 的信息

 

　　7. 修改存储过程

　　　　修改存储过程是指修改已经定义好的存储过程

　　　　语法格式：

alter procedure sp_name [characteristic ..]                                                                      
characteristic:                                                                      
{ contains SQL | no SQL | reads SQL data | modifies SQL data }                                                                       
| SQL security { definer | invoker }                                                                       
| comment 'string'   

　　　　【例17】修改存储过程studentcount的定义。

 

　　8. 删除存储过程

 　　　　存储过程创建后需要删除时使用drop procedure语句。在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之管理的存储过程无法运行。

 　　　　语法格式：

drop procedure [if exists]sp_name; 

　　　　【例19】删除存储过程studentcount

 

 

8.2 存储函数

　　1. 概念

　　　　　　存储函数是一种与存储过程十分相似的过程式数据库对象。

　　　　　　它与存储过程一样，都是由SQL语句和过程式语句组成的代码片段，并且可以被应用程序和其他SQL语句调用。

　　2. 存储过程和函数区别

　　　　存储过程和函数存在以下几个区别：

　　　　　　一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

　　　　　　对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。

　　　　　　存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。

　　　　　　存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用。

　　3. 创建和使用存储函数

　　　　（1）创建函数

　　　　　　创建存储函数语法格式：

create function sp_name ([func_parameter[，..]])                                                                      
returns type                                                                      
[characteristic ..] routine_body 

　　　　　　说明：在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。

　　4. 查看存储函数

　　　　(1) 查看存储函数的具体信息

　　　　　　如果要查看存储函数的详细信息，要使用show create语句

　　　　　　语法格式：

show create { procedure | function} sp_name;  

　　　　　　【例15】查看numofstudent 自定义函数的具体信息，包含函数的名称、定义、字符集等信息。

　　5. 修改存储函数

　　　　修改存储函数是指修改已经定义好的存储函数

　　　　语法格式：

alter procedure sp_name [characteristic ..]                                                                      
characteristic:                                                                      
{ contains SQL | no SQL | reads SQL data | modifies SQL data }                                                                       
| SQL security { definer | invoker }                                                                       
| comment 'string'   

　　6. 删除存储函数

　　　　存储函数创建后需要删除时使用drop function语句。

　　　　语法格式：

drop function [if exists]sp_name;