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语句
存储过程和存储函数总结:
定义存储过程是为了实现某一功能,定义存储函数是为了实现某一计算,在定义两者时,要时刻记住定义存储过程或存储函数的目的,不要迷失在某段代码中,要有大局观。