一、存储过程

1.存储过程的基本概念

是一组为了完成某项特定功能的SQL语句集，实质上就是一段存储在数据库中的代码
使用存储过程好处
①可增强SQL语言的功能与灵活性
②良好的封装性
③高性能
执行一次后，其执行规划就驻留在高速缓冲存储器中。
④可减少网络流量
⑤作为一种安全机制来确保数据库的安全性和数据完整性

2.创建存储过程

使用DELIMITER命令

DELIMITER $$

例如，将结束符修改为两个感叹号

DELIMITER !!

使用CREATE PROCEDURE创建存储过程

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
routine_body

mysql> use xue
Database changed
mysql> delimiter $$
mysql> create procedure sp_update_sex(in cid int,in csex char(1))
    -> begin
    -> update customers set cust_sex=csex where cust_id=cid;
    -> end $$
Query OK, 0 rows affected (0.04 sec)

3.存储过程体

①局部变量
使用DECLARE语句来声明局部变量。

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

DECLARE cid int(10);

注意事项：
ⅰ局部变量只能在存储过程体的BEGIN...ENG语句块中声明
ⅱ局部变量必须在存储过程体的开头处声明。
ⅲ作用范围仅限于声明它的BEGIN...ENG语句块。
ⅳ局部变量声明时，在其前面没有使用@符号，并且只能被声明它的BEGIN...END使用，而用户变量在声明时，会在其前面使用@符号，存在于整个会话之中。

②SET语句
为局部变量赋值，

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

SET cid = 910;

③SELECT...INTO语句
把选定列的值直接存储到局部变量中。

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

返回的结果集只能有一行数据

④流程控制语句
⑴条件判断语句
常用的条件判断语句有IF...THEN...ELSE语句和CASE语句
⑵循环语句
常用的有WHILE、REPEAT和LOOP语句。
语句中还可以使用ITERATE语句

⑤游标
是一个被SELECT语句检索出来的结果集。
步骤
ⅰ声明游标
使用DECLARE CURSOR

DECLARE cursor_name CURSOR FOR select_statement

ⅱ打开游标

OPEN cursor_name

ⅲ读取数据
可以使用FETCH...INTO语句读取数据。

FETCH cursor_name INTO var_name[,var_name]...

ⅳ关闭游标

CLOSE cursor_name

例如，

1.创建存储过程
mysql> use xue;
Database changed
mysql> DELIMITER $$
mysql> CREATE PROCEDURE sp_sumofrow(OUT ROWS INT)
    -> BEGIN
    -> DECLARE cid INT;
    -> DECLARE FOUND BOOLEAN DEFAULT TRUE;
    -> DECLARE cur_cid CURSOR FOR
    -> SELECT cust_id FROM customers;
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND
    -> SET FOUND = FALSE;
    -> SET ROWS = 0;
    -> OPEN cur_cid;
    -> FETCH cur_cid INTO cid;
    -> WHILE FOUND DO
    -> SET ROWS = ROWS+1;
    -> FETCH cur_cid INTO cid;
    -> END WHILE;
    -> CLOSE cur_cid;
    -> END$$
Query OK, 0 rows affected (0.02 sec)

2.调用存储过程
mysql> CALL SP_SUMOFROW(@rows);
Query OK, 0 rows affected, 1 warning (0.00 sec)

3. 查看
mysql> select @rows;
+-------+
| @rows |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

使用游标时需要注意
ⅰ游标只能用于存储过程或存储函数中，不能单独在查询操作中使用
ⅱ在存储过程或存储函数中可以定义多个游标，但在一个BEGIN...END中每一个游标的名字必须是唯一的。
ⅲ游标不是一条SELECT语句，是被SELECT语句检索出来的结果集。

4.调用存储过程

使用CALL语句

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

例如

mysql> CALL sp_update_sex(909,'M');
Query OK, 0 rows affected (0.00 sec)

5.删除存储过程

使用DROP PROCEDURE

DROP PROCEDURE[IF EXISTS]sp_name;

mysql> drop procedure sp_update_sex;
Query OK, 0 rows affected (0.04 sec)

二、存储函数

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

1.创建存储函数

使用CREATE FUNCTION

CREATE FUNCTION sp_name([func_parameter[,...]])
RETURNS type
routine_body

mysql> use xue
Database changed
mysql> DELIMITER $$
mysql> CREATE FUNCTION fn_search(cid int)
    -> RETURNS CHAR(2)
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE SEX CHAR(2);
    -> SELECT cust_sex INTO SEX FROM customers
    -> WHERE cust_id = cid;
    -> IF SEX IS NULL THEN
    -> RETURN(SELECT '没有该客户');
    -> ELSE IF SEX = 'F' THEN
    -> RETURN(SELECT '女');
    -> ELSE RETURN(SELECT '男');
    -> END IF;
    -> END IF;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

2.调用存储函数

使用SELECT调用

SELECT sp_name([func_parameter[,...]])

mysql> select fn_search(903);
+----------------+
| fn_search(903) |
+----------------+
| 男             |
+----------------+
1 row in set (0.00 sec)

3.删除存储函数

DROP FUNCTION[IF EXISTS] sp_name;