数据库编程两大神器-存储过程和函数

一、什么是过程化sql

1.基本概念：

基本的sql 都是高度非过程化的语言，过程化sql 是对SQL 的拓展，使其增加了过程化语句功能，过程化sql 程序的基本结构是块。所有的过程化SQL都是由块组成的。这些块可以相互嵌套，每个块都可以单独完成一个逻辑操作。

例如：

declare
set 
 ...
 /*定义部分*/
 ...
 /*执行部分*/
 begin
     sql语句、过程化SQL的流程控制语句
 exception
     异常处理部分
 end;

2.过程化sql的两种形式

过程化SQL程序的基本结构是块,所有的过程化SQL程序都是由块组成的.

• 匿名块
  • 每次执行都需要进行编译，不能被存储到数据库中，不能被其他过程化sql块调用。
• 命名块
  • 编译后被保存在数据库中，可以反复调用，运行速度较快。

二、过程化 SQL 的常见语法

2.1 变量定义和赋值

DECLARE a int #只能在基本块中使用，无法独立使用，例如在存储过程中使用
a = 1

SET @A = 1 # 定义并赋值变量，可独立使用

2.2 条件控制语句

IF condition THEN 
        ...
END IF ;


IF condition THEN 
     ...
        
ELSE 
    ...
END IF;

2.3 循环控制语句

-- WHILE循环语法：
-- 	WHILE
-- 			条件 DO
-- 			循环体;
-- 		
-- 	END WHILE;


CREATE PROCEDURE total ( max_num INT ) 
BEGIN
-- 定义部分
	DECLARE
		sum INT DEFAULT 0;
	DECLARE
		i INT DEFAULT 1;
-- 循环部分
	WHILE
			i <= max_num DO
			
			SET sum = sum + i;
		
		SET i = i + 1;
		
	END WHILE;
-- 打印结果	
	SELECT
		sum;
END;
-- 调用
CALL total ( 10 );
-- 删除存储过程
DROP PROCEDURE
IF
	EXISTS total;

-- LOOP 循环法，需要自己主动 LEAVE 
-- loop_name:LOOP
-- 	...
-- END LOOP


CREATE PROCEDURE total ( max_num INT ) 
BEGIN
-- 定义部分
	DECLARE
		sum INT DEFAULT 0;
	DECLARE
		i INT DEFAULT 1;
-- 循环部分
 loop_name:LOOP -- 循环开始
            IF i>max_num THEN 
                LEAVE loop_name;  -- 类似 boeak
            END IF;
            SET sum=sum+i;
            SET i=i+1;
        END LOOP;  -- 循环结束
        SELECT sum; -- 输出结果
-- 打印结果	
	SELECT
		sum;
END;
-- 调用
CALL total ( 10 );
-- 删除存储过程
DROP PROCEDURE
IF
	EXISTS total;

-- REPEAT UNTIL 循环法
-- REPEAT -- 循环开始
--            SET sum=sum+i;
--            SET i=i+1;
--        UNTIL i>max_num 
-- END REPEAT;

CREATE PROCEDURE total ( max_num INT ) 
BEGIN
-- 定义部分
	DECLARE
		sum INT DEFAULT 0;
	DECLARE
		i INT DEFAULT 1;
-- 循环部分
REPEAT -- 循环开始
           SET sum=sum+i;
           SET i=i+1;
       UNTIL i>max_num 
END REPEAT;
        SELECT sum; -- 输出结果
-- 打印结果	
	SELECT
		sum;
END;
-- 调用
CALL total ( 10 );
-- 删除存储过程
DROP PROCEDURE
IF
	EXISTS total;

2.4 错误处理

过程化sql 在出现异常的时候，应该让程序在产生异常的语句停下来，根据异常的类型去执行异常处理语句。

2.4.1 标准格式如下：

DECLARE handler_type HANDLER FOR condition_value[,...] statement

• Handler type (CONTINUE, EXIT) ：
  • 处理类型,继续或退出
• Handler condition (SQLSTATE, MySQL error code, named condition) ：
  • 触发条件
• Hander actions(错误触发的操作)

发生错误的条件有：

1. mysql错误代码

2. ANSI-standard SQLSTATE code. 具体可见：http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html

3. 命名条件，可自定可使用系统内置的SQLEXCEPTION, SQLWARNING,和 NOT FOUND.

当同时使用MySQl错误码,标准SQLSTATE错误码,命名条件(SQLEXCEPTION)来定义错误处理时，其捕获顺序是（只可捕获一条错误）:
MYSQL码->SQLSTATE->命名条件

2.4.2 作用域：

begni…end内，哪果错误处理定义在begin … end内，则在该begin…end之外的错误不会被捕获。

例如：

DECLARE EXIT HANDLER FOR SQLSTATE '23000' BEGIN END;

DROP PROCEDURE IF EXISTS test;
DELIMITER ;;
CREATE PROCEDURE test (_id INT , _name varchar(20)) 
BEGIN
-- 遇到重复键值就退出
DECLARE EXIT HANDLER FOR SQLSTATE '23000' BEGIN END;

INSERT INTO `user`(id, `user_name`)VALUE(_id, _name);

END;;
DELIMITER ;
-- 调用
CALL test (1,1);
CALL test (1,1); -- 主键冲突直接跳过

三、存储过程

3.1 什么是存储过程

这是数据库编程的一大利器。存储过程是由过程化SQL 语句书写的过程，这个过程经过编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时直接调用即可。

3.2 存储过程的优点

（1） 由于存储过程不像解释执行的sql 语句那样在提出操作请求时才进行语法分析和优化工作，因而运行效率高，它提供了在服务器端快速执行sql 语句的有效途径
（2）存储过程降低了客户机和和服务器之间的通信量。客户机的上的应用程序只要通过网络向服务器发出调用存储过程的名字和参数，就可以让关系型数据库管理系统执行其中的多条sql语句并进行数据处理。只有最终的处理结果才返回客户端。
（3）方便实施企业规则。可以把企业规则的运算程序写成存储过程放入数据库服务器中，由关系数据库系统管理，既有利于集中控制，又能够方便的进行维护。当企业规则发生变化时只要修改存储过程即可，无需更改其他的应用程序

3.3 存储过程的增删改查

3.3.1 增加存储过程

CREATE PROCEDURE 过程名 （[参数 1, 参数2...])
BEGIN

END;

3.3.2 删除存储过程

-- 存储就删除
DORP PROCEDURE IF EXISTS  过程名 

3.3.3 执行存储过程

CALL 过程名 （[参数1，参数2，...]）

例如增加字段的存储过程可写为：

DELIMITER ;; -- 定义结束符号，默认是 ; ,主要是避免过程块中存在 ; , 让 mysql 以为语句提前结束

CREATE PROCEDURE `AddColumnIfNotExists`(tableName varchar(50), columnName varchar(50), dbType nvarchar(100))
BEGIN
      DECLARE _tableCount    INT; -- 表的数量
      DECLARE _columnCount   INT; -- 字段的数量
      SET _tableCount = -- 统计符合表的数量
             (SELECT COUNT(1)
                FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_SCHEMA = (SELECT schema()) AND TABLE_NAME = tableName);

      SET _columnCount = --统计符合字段名称的数量
             (SELECT COUNT(1)
                FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_SCHEMA = (SELECT schema()) AND TABLE_NAME = tableName AND COLUMN_NAME = columnName);

      IF _tableCount = 1 AND _columnCount = 0 -- 如何符合名称的表和字段都是唯一的
      THEN
         SET @_sqlText = -- 造修改的sql
                CONCAT(' ALTER TABLE `',
                       tableName,
                       '` ADD COLUMN `',
                       columnName,
                       '` ',
                       dbType);

              PREPARE stmt1 FROM @_sqlText; -- 预处理
              EXECUTE stmt1; -- 执行
              DEALLOCATE PREPARE stmt1; -- 释放
      END IF;

END;;
              
DELIMITER ; -- 恢复结束符号

四、函数

4.1 什么是函数

这里主要是指自定义函数，我们知道mysql 本身就提供了很多的系统函数使用，但是有时候可能还需要一些自定义的函数。函数和存储过程的最大区别是，函数是需要有返回值的。

4.2 函数的增删改

4.2.1 增加函数

DELIMITER;;
CREATE FUNCTION fun_test( _a INT, _b INT ) RETURNS INT BEGIN
	RETURN ( _a + _b );
END;;
DELIMITER;

4.2.2 删除函数

-- 和删除存储过程类似
DROP FUNCTION IF EXISTS fun_test

4.3 如何调用函数

-- 和调用普通的函数一样，如上面的函数调用则为

select fun_test(1,2) -- 3

五、总结

本文我们讲解了数据库中过程化SQL的概念，定义，以及常见的语法。并讲解了数据库两大编程神器，存储过程及函数，通过这两个东西，可以实现很多的数据库相关的编程，即使不使用其他后端的语言，你也可以轻松实现很多数据统计和数据清洗业务。
存储过程不仅可以提高mysql 服务器的处理性能，更是大公司建立规范的好工具，例如日常对字段、索引的增加删除。通过创建存储过程，使用者无需写出完整的执行语句，减少了开发者的时间，并且不需要担心新增的字段或者索引是否符合公司规范，有效提高了工作效率。