MySQL——存储过程与函数（个人笔记整理）

存储过程的定义

存储过程是一组为了完成特定功能的SQL语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数来执行它。

应用场景

例如需要大串复杂的SQL语句，或者说在编写SQL语句的过程中还需要设置一些变量的值或者循环，这个时候就完全有必要编写一个存储过程。

存储过程的创建和调用

语法：

【创建过程

        开始

        声明 （变量）

        结束】

CREATE PROCEDURE [name](参数)

BEGIN

DECLARE(声明)  variable   →定义变量

        [SQL语句块]

END;

调用：CALL procedure_name(参数) ;

存储过程的变量

变量定义： DECLARE 变量名1 类型1    【即，声明变量】

变量赋值： SET 变量名1 = 表达式1      【即，设置变量】

# 在查询窗口使用变量（不需要定义）

SET @a = 1;

SELECT @a;

SET @b = @a*10+3;

SELECT @a,@b;

示例

SET @p_in=10,@p_out=20,@p_inout=30

#创建存储过程
CREATE PROCEDURE proc_param(
IN p_in INT,OUT p_out INT,INOUT p_inout INT)
BEGIN
SET p_in=p_in+2, p_out=p_out+2, p_inout=p_inout+2;
SELECT p_in,p_out,p_inout FROM dual;
END;

#调用存储过程
call proc_param(@p_in,@p_out,@p_inout);
select @p__in,@p_out,@p_inout;         #查看初始变量
           

➢ IN 输入参数：调用者向过程传入值，本身不改变。

➢ OUT 输出参数：调用者向过程传出值，本身改变。

 ➢ INOUT 输入输出参数：既传入也传出。

 ➢ 也可以无参数

存储过程的IF条件判断

IF语句一般在存储过程或者函数中使用，如下：

# 创建存储过程
CREATE PROCEDURE proc_if(in input int)
BEGIN
    IF input>1 then select '大于1';
    elseif input>0 then select '负数';
    else select '其他';
    END if;
END;

#调用存储过程
call proc_if(10);
           

 存储过程的while循环

WHILE语句，先判断后运行

语法

WHILE [判断语句] DO

[执行内容]  

END WHILE;

示例

CREATE PROCEDURE proc_while(in input int)
BEGIN
DECLARE var int DEFAULT 0;
DROP TABLE IF EXISTS twhile;
CREATE TABLE IF NOT EXISTS twhile(var CHAR(10));
    WHILE var<input DO
    SET var=var+1;
    INSERT INTO twhile VALUES(var);
    END WHILE;
SELECT * FROM twhile;
END;

#调用存储过程
call proc_while(10);
           

存储过程的REPEAT循环

REPEAT（意为：重复）语句，先运行后判断

语法

REPEAT[执行内容]

UNTIL[判断语句]     -->此处无分号

END REPEAT；

示例 

CREATE PROCEDURE proc_rep(in input int)
BEGIN
DECLARE var int DEFAULT 0;
DROP TABLE IF EXISTS twhile;
CREATE TABLE IF NOT EXISTS twhile(var CHAR(10));
    repeat SET var = var+1;
    INSERT INTO twhile VALUES(var);
    UNTIL var>= input 
    END REPEAT;
SELECT * from twhile;
END;

call proc_rep(3);
           

 存储过程的LOOP循环

LOOP语句（意为：循环），运行直到遇到LEAVE

语法

[标签名]:LOOP

[执行内容]

LEAVE[标签名]

[执行内容]

END LOOP;

示例 

CREATE PROCEDURE proc_loop(in input int)
BEGIN
DELCARE var int DEFAULT 0;
DROP TABLE IF EXISTS twhile;
CREATE TABLE IF NOT EXISTS twhile(var CHAR(10));
    loop1:LOOP    # loop1为标签名
    SET var=var+1;
    INSERT INTO twhile VALUES(var);
    IF var = input THEN LEAVE loop1;
    END IF;    #结束if语句
    END LOOP;  #结束LOOP循环
SELECT * FROM twhile;
END;      # 结束存储过程

call proc_loop(5);      #调用存储过程
           

游标

定义

游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果，是在先从数据表中检索出数据再逐条读取查询结果集中的记录。

在MySQL中并没有一种描述表中单一记录的表达形式，除非使

用 WHERE子句来限制只有一条记录被选中，所以有时必须借助于游标来进行逐条记录的数据处理

 ➢ 在循环处理、存储过程、函数中使用，用来循环获取结果集的单个结果。

➢ 特点：1.只读：不能被更新    2.不可卷动：即只能沿一个方向，且不能跳行

➢ 游标的使用一般分为4个步骤，主要是：定义游标-->打开游标-->使用游标-->关闭游标

➢ 游标的使用涉及到4个关键词：DECLARE、OPEN、FETCH（获取）、CLOSE

1. 定义游标

语法：

DECLARE <游标名> CURSOR FOR SELECT 语句        #cursor意为光标

# 示例
DECLARE mycursor CURSOR FOR SELECT id FROM student;
           

2.打开游标

• open<游标名> 

3.使用游标

• 使用游标需要关键字FETCH....INTO来取出数据，取出的数据需要用变量保存
• FETCH  [NEXT | PRIOR | FIRST | LAST ] FROM <游标名>INTO [变量名1,变量名2…]
• 变量参数即[变量名1,变量名2,变量名3] 必须在游标使用之前定义

4.关闭游标

CLOSE mycursor

CLOSE 释放游标使用的所有内部内存和资源，每个游标不再需要时都应该关闭 

实例（未使用循环）

CREATE PROCEDURE proc_cur()    #创建存储过程
BEGIN              
    DECLARE snamel varchar(10);   #声明变量
    DECLARE id1 INT;
    #1.定义一个游标mucursor
    DECLARE mycursor CURSOR FOR SELECT id,sname from student;
    #2.打开游标
    OPEN mycursor;   
    #3.使用游标
    FETCH NEXT FROM mycursor INTO id1,sname1;
    #4.展示结果
    CLOSE mycursor  #5.关闭游标
END；          #结束存储过程

call proc_cur();    #调用存储过程

    
           

注：不使用循环则只能返回一个结果

➢ FETCH是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标

溢出的错误。

➢ 游标溢出时会引发MySQL预定义的NOT FOUND错误，可以指定当引发NOT FOUND错误时定义一个CONTINUE

事件，当这个事件发生时修改DONE变量的值。

✓ DECLARE DONE INT DEFAULT 0; --创建结束标志变量

✓ DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE=1; --指定游标循环结束时的返回值

实例（使用循环）

使用循环之后可以返回游标的所有结果

CREATE PROCEDURE proc_cur_while()       #创建存储过程
BEGIN                                   #开始
    DECLARE sname VARCHAR(10);          #声明变量
    DECLARE id INT；
    DECLARE done int default 0;       #创建结束标志变量
    #1.定义游标
    DECLARE mycursor CURSOR FOR SELECT id,sname from student;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;    #指定游标循环结束时的返回值
    #2.打开游标
    open mycursor;
    WHILE done = 0 DO
    #3.使用游标
    FETCH NEXT FROM mycursor INTO id，sname;  
    #4.展示结果
    SELECT id,sname;
    END WHILE;    #结束while循环
    CLOSE mycursor2；    #关闭游标
END;

call proc_cur_while();
           

查看存储过程

#查看有哪些存储过程

SELECT name FROM MySQL.proc WHERE db='test1';

#查看存储过程的状态

SHOW procedure status WHERE db='test1';

# 查看创建存储过程的代码

SHOW CREATE PROCEDURE test1.proc_cur;

#删除存储过程

DROP PROCEDURE IF EXISTS proc_cur_while2;

➢ 优点

• 存储过程可封装，并隐藏复杂的商业逻辑。

• 存储过程可以回传值，并可以接受参数。

• 增强SQL语言的功能和灵活性。

• 存储过程可以用在数据检验，强制实行商业逻辑等。

➢ 缺点

• 调试麻烦。

• 可移植性差、可维护性差。