2-7 函数与存储过程

1 变量

1. 1. 普通变量

声明变量

# DECLARE声明变量关键字

# DECLARE 变量名 数据类型 默认值;

DECLARE no INT default 0;

变量赋值

# SET变量赋值关键字

SET no = 2;

1. 1. 会话变量

# 会话变量，变量名前面加一个@符号： @变量名，无须单独声明，直接赋值 就可以

SET @res = 12;

2 函数FUNCTION

1. 函数与java中的方法一样，有入参，有且只有一个返回值；
2. 函数的返回值，只能是一个值，不能是结果集；

2.1 创建函数

# function_name 自定义函数名

# param 自定义参数名 datatype 参数数据类型

# 参数可以有多个

CREATE FUNCTION [function_name]([param] [datatype]...) 

RETURNS int(11) # 声明返回值数据类型

BEGIN

[自定义sql语句]...

RETURN [指定返回值]; # return返回值关键字

END;

2.2 示例

示例1

CREATE DEFINER = `root`@`localhost` FUNCTION `f1`(price1 INT, price2 INT)

 RETURNS int(11)

BEGIN

DECLARE no1 INT;

SET no1 = 2;

SET @res = price1 + price2 + no1;

RETURN @res;

END;

示例2

BEGIN

DECLARE i INT DEFAULT 10;

WHILE i < do_no DO

SET i=i+1;

END WHILE;

RETURN i;

END;

示例3

BEGIN

SET @res = (SELECT s_name FROM student_info LIMIT 1);

RETURN @res;

END;

2.3 调用函数

select [function_name](param1, param2...);

示例：

SELECT f_add(1, 2);

3 存储过程PROCEDURE

3.1 简介

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

1. 存储过程有入参，入参可以是多个；
2. 存储过程有返回值，返回值可以是一个结果集。

3.2 优点

1. 存储过程可封装，并隐藏复杂的业务逻辑；
2. 存储过程可以回传值，并可以接受参数；
3. 存储过程可以使用EXECUTE语句来运行；
4. 存储过程可以用在数据检验，分布式锁等；
5. 封装复杂操作 ，当对数据库进行复杂操作时（如对多个表进行更新，删除时），可用存储过程将此复杂操作封装起来与数据库提供的事务处理结合一起使用；
6. 存储程序只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度；
7. 减少网络流量  因为存储过程存储在服务器上，并在服务器上运行。

3.3 缺点

1. 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
2. 存储过程的性能调校与撰写，受限于各种数据库系统。

3.4 参数类型

存储过程的入参有3种类型，注意这里的参数类型指的不是数据类型：

IN	输入参数类型	表示调用者向过程传入值（传入值可以是字面量true/false或变量）
OUT	输出参数类型	表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT	输入输出参数类型	既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
输出参数（OUT/INOUT）要求在调用procedure时传入的参数为会话变量，否则会报此错

3.5 创建存储过程

CREATE PROCEDURE [name](type param datatype,type param2 datatype...)

begin

    [sql语句...];

end; 

# name 自定义存储过程名

# type 参数类型 IN/OUT/INOUT

# datatype 参数数据类型

3.6 示例

CREATE PROCEDURE `p1`(IN `p_sex` varchar(2),IN u_name varchar(55))

BEGIN

SELECT * FROM student_info WHERE s_sex = p_sex and s_name = u_name;

END;

3.7 调用存储过程

CALL p1('男','张三');

4 游标CURSOR

4.1 简介

1. 游标是一组类似于数组的具有排序的结果集。
2. 在mysql中游标只能用于存储过程和函数。
3. mysql的游标是向前只读的，也就是说，你只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录。

4.2 声明游标

DECLARE [name] CURSOR FOR [select sql];

4.3 应用

DECLARE p_id INT;

DECLARE p_name VARHCAR(55);

# 声明变量，用作循环终止标识

DECLARE curStatus INT DEFAULT 1;

# 声明游标，必须在所有执行sql（select/insert/update/delete）之前声明游标

DECLARE ids CURSOR FOR 

SELECT id,name FROM student_info WHERE class_no = 1;

# 声明handler(处理器)，handler会自动地向下读取游标的一行数据

# 当handler读不到数据时(not found)表明已经读完了游标的数据

# 此时设置curStatus = 0

# not found 也可以写成 SQLSTATE '02000'

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET curStatus = 0;

# 开启游标

OPEN ids;

#遍历游标

WHILE curStatus = 1 DO

# 向下读取一行数据，并将这行数据赋值给与其字段对应的变量

FETCH ids INTO p_id,p_name;

# 执行sql语句

# select/insert/update/delete...

END WHILE;

# 关闭游标

CLOSE ids;