MYSQL-如何写好存储过程和存储函数？

Bertha 。 2022-11-01 04:27 106阅读 0赞

# MYSQL-如何写好存储过程和存储函数？ #

## 1. 如何写好存储过程？ ##

1. 确定输入参数、输出参数、参数类型
    2. 使用命名代码块，适当离开代码块。[LEAVE label_main;]
    3. 声明局部变量
    4. 异常捕捉：内部异常（异常代码）和外部异常（自定义捕捉）
    /* 内部异常：使用异常代码的异常 外部异常：自定义的异常 */
    5. MySQL5.6提供了get diagnostic语句来获取错误缓冲区的内容，然后把这些内容输出到不同范围域的变量里，以便我们后续灵活操作。
    实例：GET DIAGNOSTICS CONDITION 1
    /* GET [CURRENT] DIAGNOSTICS { statement_information_item[, statement_information_item] ... | CONDITION condition_number condition_information_item [, condition_information_item] ... } statement_information_item: target = statement_information_item_name condition_information_item: target = condition_information_item_name statement_information_item_name: NUMBER | ROW_COUNT condition_information_item_name: CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_SQLSTATE | MESSAGE_TEXT 异常内容 | MYSQL_ERRNO 错误编码 | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME condition_number, target: (see following discussion) */
        
    mysql> drop table db_test.tb_name;
    1051 - Unknown table 'db_test.tb_name'
    mysql> GET DIAGNOSTICS CONDITION 1
        -> @p3=MYSQL_ERRNO,@p4=MESSAGE_TEXT,@p5=RETURNED_SQLSTATE;
    Query OK, 0 rows affected
    
    mysql> select @p3,@p4,@p5;
    +------+-----------------------------+--------+
    | @p3 | @p4                        | @p5   |
    +------+-----------------------------+--------+
    | 1051 | Unknown table 'db_test.tb_name' | 42S02 |
    +------+-----------------------------+--------+
    1 row in set
    
    
    6. 善于使用内置函数
    SELECT UUID_SHORT();
    SELECT UUID();
    7. 灵活使用DML语句
    INSERT INTO tb_name select语句;   -- 直接插入数据，遇到已存在的数据，则报错。
    REPLACE INTO tb_name select语句;  -- 替换数据，数据已存在则不插入数据，数据不存在则直接插入数据。
    SELECT col_name INTO var_name FROM tb_name;
    8. 灵活使用流程语句
    9. 将部分功能分离成一个简单存储过程，使用调用的方式简化复杂存储过程
    10. 对于赋值语句，set语句可以用=或:=赋值，select语句中只能用:=赋值
    11. 注意commit提交事务和rollback回滚事务
    12. 善于声明并使用光标

## 2. 如何书写存储函数？ ##

1. 声明函数变量
    2. 声明返回值类型
    3. 声明局部变量
    4. 变量赋值
    5. 流程控制语句
    6. 必须要有一个有效的return语句,且返回值的类型与声明的返回值类型一致。
    7. 善于使用DML语句

**存储过程和存储函数总结：**  
**定义存储过程是为了实现某一功能，定义存储函数是为了实现某一计算，在定义两者时，要时刻记住定义存储过程或存储函数的目的，不要迷失在某段代码中，要有大局观。**