五、数据库编程

存储过程

1.存储过程的基本概念

存储过程是一组为了完成某项特定功能的SQL语句集，其实质就是存储在数据库中的代码。它可以由声明式的sql语句和过程式的sql语句组成。

优点：
- 可增强sql语言的功能和灵活性
- 良好的封装性
- 高性能
- 可减少网络流量
- 可作为一种安全机制确保数据库的安全性和数据的完整性

2.创建存储过程

DELIMITER命令

DELIMITER ?; //?:用户定义的结束符
DELIMITER ; //重新使用';'作为结束符

使用CREATE PROCEDURE语句创建存储过程

/*
sp_name：指定存储过程的名称，默认在当前数据库中创建
proc_parameter：指定存储过程中的参数列表，格式为：[IN | OUT | INOUT] parameter_name type
routine_body：存储过程的主体部分，也成为存储过程体
*/
CREATE PROCEDURE sp_name([proc_parameter[, ...]])
    routine_body

在mysql_test中创建一个存储过程，用于实现给定表customers中一个客户id号即可修改该客户的性别为指定性别：

mysql> delimiter ?
mysql> create procedure sp_update_sex(in cid int, in csex char(1))
    -> begin
    -> update mysql_test.customers
    -> set c_sex = csex
    -> where c_id = cid;
    -> end ?

3.存储过程体

使用DECLARE语句声明局部变量
1. 只能在存储过程体的BEGIN...END语句块中使用
2. 必须在存储过程的开头处声明
3. 作用范围仅限制在声明它的BEGIN...END语句块中
4. 不同于用户变量
局部变量与用户变量的区别：
1. 局部变量声明时，在其前面没有@符号，并且它只能在声明它的BEGIN...END语句块中的语句使用。
2. 用户变量在声明时，会在其名称前使用@符号，同时已声明的用户变量存在于整个会话之中。
```
/*
var_name：指定局部变量的名称
type：指定局部变量的类型
DEFAULT value：局部变量设置默认值
*/
DECLARE var_name[, ...] type [DEFAULT value]
```
例：声明一个局部变量cid：
```
declare cid int(10) default 1;
```
使用SET语句为局部变量赋值：
```
SET var_name = expr[, var_name = exper]...
```
例：为局部变量cid赋予一个整数值910
```
set cid = 910;
```

使用SELECT...INTO语句把选定列的值直接存储在局部变量中

/*
col_name：指定列名
var_name：指定要赋值的变量名
table_expr：select语句中from子句及后面的语法部分
*/
SELECT col_name[, ...] INTO var_name[, ...] table_expr

流程控制语句

条件判断语句

IF...THEN...ELSE语句

IF 条件 THEN 
    表达式1
ELSE
    表达式2
END IF;

CASE语句

循环语句

WHILE语句
```
WHILE 条件
    表达式
END WHILE;
```
REPEAT语句
```
repeat
    表达式
END repeat;
```
LOOP语句
```
loop
    表达式
END loop;
```

ITERATE语句：用于表示退出当前循环

使用DECLARE CURSOR语句创建游标

//cursor_name：指定要创建的游标名称
DECLARE cursor_name CURSOR FRO select_statement

使用OPEN语句打开游标
```
OPEN cursor_name
```

使用FETCH...INTO语句来读取数据

/*
cursor_name：指定已打开的游标
var_name：指定存放数据的变量名
*/
FETCH cursor_name INTO var_name[, var_name]...

使用CLOSE语句关闭游标
```
CLOSE cursor_name
```

4.调用存储过程

使用CALL语句调用存储过程

/*
sp_name：指定被调用的存储过程的名称
parameter：指定调用存储过程所需要的参数
*/
CALL sp_name([parameter[, ...]])
CALL sp_name[()]

调用数据库mysql_test中的存储过程sp_update_sex，将客户id为909的客户性别改为f：

mysql> call sp_update_sex(909, 'f');

5.删除存储过程

使用DROP PROCEDURE语句删除存储过程

drop procedure [if exists] sp_name;

存储函数

1.什么是存储函数

存储函数与存储过程一样，是由sql语句和过程式语句组成的代码片段。

存储函数	VS	存储过程
不能拥有输出参数		可以拥有输出参数
必须包含一条return语句		不允许包含return语句
可以直接调用存储函数，不要call语句		需要call语句调用存储过程

2.创建存储函数

使用CREATE FUNCTION语句创建存储函数

/*
fn_name：指定存储函数的名称
func_parameter：指定存储函数的参数，格式：parameter_name type
RETURNS type：声明存储函数返回值的数据类型
routine_body：指定存储函数的主体部分，也称存储函数体
*/
CREATE FUNCTION fn_name([func_parameter[, ...]])
RETURNS type
routine_body

在数据库mysql_test中创建一个存储函数，要求该函数能根据给定的客户id返回该客户的性别，如果数据库中没有给定的客户id，则返回没有该客户：

mysql> use mysql_test;
mysql> delimiter ?
mysql> create function fn_sex_with_id(cid int)
    -> returns char(20)
    -> deterministic
    -> begin
    -> declare sex char(20);
    -> select c_sex into sex from mysql_test.customers
    -> where c_id = cid;
    -> if sex is null then
    -> return(select 'not exists');
    -> else if sex = 'm' then
    -> return(select 'male');
    -> else 
    -> return(select 'female');
    -> end if;
    -> end if;
    -> end ?

3.调用存储函数

使用关键字SELECT调用存储函数

SELECT fn_name([fn_param[, ...]])

4.删除存储函数

使用DROP FUNCTION语句删除存储函数

DROP FUNCTION [IF EXISTS] fn_name;