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

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语句

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