---
title: MySQL存储过程
date: 2018-03-30 16:00:23
description: "MySQL存储过程定义，与函数的对比，结合游标、错误处理写的样例代码"
categories: [技术总结]
tags: [MySQL, 代码块]
---

## 存储过程的定义
```sql
DELIMITER $$
CREATE PROCEDURE 存储过程的名字(IN 参数1 参数1类型，OUT 参数2 参数2类型,INOUT 参数3 类型) 
过程选项
BEGIN
过程内容
END
$$
DELIMITER ;
```

## 函数和存储过程

### 相同点
1. 创建好了之后，都是存储在数据库上的对象，只需提供名称就可以调用
```sql
SELECT get_teacher_name_fn();
CALL get_teacher_name_proc();
```
1. 重复调用
1. 增强安全性 可以根据用户权限访问

### 不同点
1. 函数只有一个返回值，至少一个
     存储过程可以多个
1. 函数体内可以使用select .. into 为某变量赋值，不能返回结果集;存储过程可以返回结果集
1. 函数一般嵌入在sql语句中存储过程一般单独调用call
1. 函数体内 事务语句有限制，存储过程没有。

## 存储过程 游标 错误处理（维护用户编号）
```sql
--set_account_data 重新生成用户编号

BEGIN
  DECLARE temp_id INT(8);                                        /*用户id*/
  DECLARE temp_manager INT(8);                            /*上级id*/
  DECLARE temp_accounter_no VARCHAR(64);        /*上级编码*/
    DECLARE temp_max_no VARCHAR(64);                    /*上级的最大下级编码*/
    DECLARE max_no VARCHAR(64);                                /*编码*/
    DECLARE str1 VARCHAR(64);                                    /*编码*/
    DECLARE temp_no INT(8);                                        /*编码*/
    DECLARE temp_level INT(8);                                /*级次*/
    DECLARE state VARCHAR(30);                                /*错误处理监听变量*/

    /*定义用户表游标*/
    DECLARE account_cursor CURSOR FOR SELECT id,manager FROM account ORDER BY manager,id;

    /*定义错误处理监听，用于结束游标循环*/
    DECLARE CONTINUE HANDLER FOR 1329
    BEGIN
        SET state = 'error';
    END; 

  OPEN account_cursor;
  REPEAT
        FETCH account_cursor INTO temp_id,temp_manager;
        IF (temp_id = 1) THEN
            UPDATE account SET leaf = 0,no = '01',level = 1 WHERE id = 1;
        ELSE
            /*设置上级leaf为0*/
            UPDATE account SET leaf = 0 WHERE id = temp_manager;
            /*查询上级编号*/
            SELECT no INTO temp_accounter_no FROM account WHERE id = temp_manager;
            /*设置上级编码*/
            UPDATE account SET pno = temp_accounter_no WHERE id = temp_id;
            /*查询上级原有的最大下级编码*/
            SELECT MAX(no) INTO temp_max_no FROM account WHERE pno = temp_accounter_no;
            /*如果最大下级编码为空，生成新的编码，否则把原来的编码加一*/
            IF (temp_max_no IS NULL) THEN
                SET max_no = concat(temp_accounter_no, '0001');
            ELSE 
                SET str1 = SUBSTR(temp_max_no,LENGTH(temp_max_no)-3,4);
                SET temp_no = str1;
                SET temp_no = temp_no + 1;
                SET str1 = temp_no;
                IF (LENGTH(str1) = 1) THEN
                    SET str1 = concat('000', str1);
                ELSEIF (LENGTH(str1) = 2) THEN
                    SET str1 = concat('00', str1);
                ELSEIF (LENGTH(str1) = 3) THEN
                    SET str1 = concat('0', str1);            
                END IF;
                SET max_no = concat(temp_accounter_no, str1);
            END IF;
            UPDATE account SET no = max_no WHERE id = temp_id;
            SET temp_level = (LENGTH(max_no) + 2) / 4;
            UPDATE account SET level = temp_level WHERE id = temp_id;
        END IF;
        UNTIL state = 'error'
    END REPEAT;
    CLOSE account_cursor;
    /*修改leaf为null的为1*/
    UPDATE account SET leaf = 1 WHERE leaf IS NULL;
    RETURN 0;
END
```