存储过程(二)

客官°小女子只卖身不卖艺 2023-08-17 15:38 144阅读 0赞

## 查看存储过程 ##

procedure status like ["procedure_name"]

## 删除存储过程 ##

procedure "procedure_name";

## 使用带参存储过程 ##

存储过程涉及到的参数分为三种:

*  `IN 输入参数`: 表示调用者向过程传入值 (传入值可以是字面量或变量)
 *  `OUT 输出参数`: 表示过程向调用者传出值 (可以返回多个值)(传出值只能是变量)
 *  `INOUT 输入输出参数`: 既表示调用者向过程传入值, 又表示过程向调用者传出值 (值只能是变量)

> 注意:
> 
>  *  输入值使用in参数。
>  *  返回值使用out参数。
>  *  inout参数就尽量的少用。

#### **示例**  ####

三种参数的使用示例 (从用户表中获取指定 id 的用户的名称和生日):

drop procedure if exists demo1; 
    delimiter //
    create procedure demo1(
        in user_id int,
        out user_name varchar(20),
        inout user_birthday datetime
    )
    begin 
        select username, birthday into user_name, user_birthday from user where user_id=user_id;
    end//
    delimiter ;

## 变量 ##

### 定义 ###

局部变量的声明需要放在存储过程体的开始, 使用 declare 关键字进行定义.

### **语法:** ###

DECLARE variable_name datatype [DEFAULT value];

datatype 是 MySQL 的数据类型.

### 变量赋值 ###

为变量赋值使用关键字`SET`.

### 语法: ###

SET variable_name = 表达式

表达式可以是具体值、运算表达式, 或者只检索出一行一列的语句.

### 用户变量 ###

MySQL 客户端可以使用用户变量, 用户变量格式: `@variable_name`.

**示例**

drop procedure if exists demo1;
    delimiter //
    create procedure demo1(
    out user_name varchar(32)
    )
    begin
    declare name varchar(32) default null;
    set name = (select username from user where user_id = 1);
    select name into user_name;
    end//
    delimiter ;

// 调用
    call demo1(@name);
    select @name;

## 控制语句 ##

### 条件语句 ###

**1. if-then-else**

**语法:**

// 1. if-then语句
    if 条件表达式 then
    执行语句
    end if;
    // 2. if-then-else 语句
    if 条件表达式 then
    执行语句
    else
    执行语句
    end if;

**2. case 语句**   
case 语句的作用于 switch 相似, 和`when`/`then`关键字一起使用.

**语法:**

set var1 = 2;
    case var1
    when 0 then -- 也可以写作 when var1 = 0 then
    执行语句
    when 1 then
    执行语句
    when 2 then
    执行语句
    else
    执行语句
    end case;

### 循环语句 ###

**1. while … end while 语句**

**语法:**

set var1 = 1;
    while var1 < 3 do
    执行语句
    end while;

**2. repeat … end repeat 语句**

repeat 语句会先执行一次循环, 然后再进行循环判断.

**语法:**

set var1 = 1;
    repeat
    执行语句
    set var1 = var1 + 1;
    until var1 > 3;
    end repeat;

**3. loop… end loo 语句**   
loop 循环不设置初始条件和结束条件, 需要结束循环时, 通过`leave`关键字结束循环

**语法:**

set var1 = 0;
    LOOP_LABEL:loop
    执行语句
    set var1 = var1 + 1;
    if var1 > 3 then
    leave LOOP_LABEL;
    end if;
    end loop;

**4. LABLE 标号:**   
可以在 begin/while/repeat/loop 语句前设置 LABLE 循环标记, 在需要的时候通过 leave 关键字跳出指定的循环.

**5. ITERATE 语句**   
ITERATE 关键字用于跳过本次循环, 进行下一次循环.

**语法:**

set var1 = 0;
    LOOP_LABLE loop:
    if var1 = 3 then
    set var1 = var1 + 2;
    ITERATE LOOP_LABLE;
    end if;
     
    if var1 >= 5 then
    leave LOOP_LABLE;
    end if;
    set var1 = var1 + 1;
    end loop;

## 游标 ##

游标用于遍历多行语句, 且只能在存储过程或函数中使用. 使用游标时, 需要先声明游标.

#### **语法:** ####

DECLARE cursor_name CURSOR
    FOR
        select语句

#### **示例:** ####

循环遍历用户, 将超过有效期的用户账号设置为无效:   
SQL 结构:

![1261026-20190726201648016-45645214.png][]

DROP PROCEDURE IF EXISTS demo3;
    DELIMITER //
    # 创建存储过程
    CREATE PROCEDURE demo3(
        IN disabled_date DATE # 从外部传入失效日期
    )
    BEGIN
        DECLARE user_id INT; # 用户ID
        DECLARE user_expire_date DATE; # 用户有效日期
        DECLARE done INT DEFAULT 1;
        # 声明一个游标
        DECLARE cursor1 CURSOR
        FOR
        SELECT expire_date, id FROM user;
        # 定义一个程序出现异常时的处理条件. 从最后一行取出数据后, 游标后移, 没有数据, MySQL 会出现 "SQLSTATE '02000'" 的错误. 出现该错误时设done为1, 且继续运行程序
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 0;
        OPEN cursor1; # 打开游标
        WHILE done = 1 DO # 执行循环, 遍历数据
            # 从游标中获取数据, 检索了几列数据就需要几个变量接收数据
            FETCH cursor1 INTO user_expire_date, user_id;
            # 执行条件判断, 过期时, 失效用户
            IF user_expire_date <= disabled_date THEN
                UPDATE user SET status = 0 WHERE id = user_id;
            END IF;
        END WHILE;
        CLOSE cursor1; # 关闭游标, 释放资源
    END //
    DELIMITER ;
     
    # 调用存储过程
    call demo3("2019-07-10");

## 异常 ##

对于 SQL 语句运行中可以预测到的错误, 可以通过定义条件和处理程序进行处理, 避免程序因异常终止.

### 定义条件 ###

定义条件通过 DECLARE 关键字实现.

#### 语法 ####

DECLARE condition_name CONDITION FOR condition_value;

*  condition\_name: 条件名
 *  condition\_value: 触发条件的条件值

### 定义处理程序 ###

处理程序在指定错误发生时, 将立刻执行指定的操作. 定义处理程序通过 DECLARE 关键字实现.

#### 语法 ####

DECLARE handler_type HANDLER FOR condition_name | condition_value 处理语句;

*  handler\_type: 错误发生时的处理方式, 有 3 种方式可取, CONTINUE: 不处理错误, 继续运行; EXIT: 中止运行, 退出程序; UNDO: 撤回之前的操作, 终止运行.
 *  condition\_name: 条件名
 *  condition\_value: 条件值

### 捕获异常 ###

捕获异常有两种方式:

// 1. 定义条件, 通过条件捕获异常
    DECLARE not_next_row CONDITION FOR SQLSTATE'02000'; # 定义条件, 定义条件要在定义游标前
    DECLARE CONTINUE HANDLER FOR not_next_row SET stop_flag = 1; # 定义处理程序, 定义处理程序要在定义游标后
    // 2. 直接在处理程序中定义触发条件值捕获异常
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stop_flag = 1;

转载于:https://www.cnblogs.com/fxyy/p/11252656.html

[1261026-20190726201648016-45645214.png]: /images/20230810/0154b43a9f284e88b838645c4525f528.png