Mysql - 存儲過程/自定義函數

在資料庫操作中, 尤其是碰到一些複雜一些的系統, 不可避免的, 會用到函數/自定義函數, 或者存儲過程. 

實際項目中, 自定義函數和存儲過程是越少越好, 因為這個東西多了, 也是一個非常難以維護的地方. 

一、自定義函數

1. 例子

mysql提供的函數, 不在這一篇講了, 這裡主要貼一下自定義函數.  前臺js插件裡面有一個zTree, 不知道大家知不知道, 效果是這樣的:

這種結構的數據, 在資料庫中, 我一般會設計到一個表中

create table ztree
(
    id int(11) not null PRIMARY key auto_increment,
    name varchar(20) not null comment '節點名稱',
    pid int(11) not null comment '父節點id'
) comment '樹形表';

然後插入數據:

如果你拿到一個節點A, 想要獲取A下麵的節點(不只是子節點哦), 那麼通過一個自定義函數來做, 能方便許多.

delimiter $
DROP FUNCTIONIF EXISTS GetChildNodes ;
CREATE FUNCTION `GetChildNodes` (`rootId` INT) RETURNS VARCHAR (1000)
BEGIN
    DECLARE res VARCHAR (1000) DEFAULT '-1'; 
    DECLARE temp VARCHAR (1000) DEFAULT CAST(rootId AS CHAR);
    -- SET res = '' ;
    -- SET DECLARE = CAST(rootId AS CHAR) ;
    WHILE DECLARE IS NOT NULL DO

        SET res = CONCAT(res, ',', temp) ; 

        SELECT GROUP_CONCAT(id) INTO temp FROM ztree WHERE FIND_IN_SET(pid, temp) > 0 ;
        
    END WHILE ; 
    RETURN res ;
    END$
delimiter ;

 這裡, 我將res的值預設為-1, 這樣的話, 就可以在查詢的時候, 將這個結果拼入sql中, 還是比較方便的. 

mysql中, 自定義函數的調用, 使用 select, 接下來, 就看一下之前的成果:

SELECT GetChildNodes (2);

2. 語法

自定義函數與存儲過程有一個很明顯的地方, 就是, 自定義函數是有返回值的, 並且需要通過return的方式返回. 而存儲過程沒有return返回值. 但是, 程式在執行存儲過程的時候, 其實是可以得到一個結果集的.

語法:

　　create function 函數名 (參數名 參數類型) returns 參數類型

　　begin

　　return result;

　　end

 1) 自定義函數傳參與存儲過程不同, 不需要指定 in/out. 

 2) 自定義函數可以用到別的sql語句中, 可以單獨使用, 也可以混入別的sql中使用

二、存儲過程

既然前面已經講了自定義函數的語法, 那這裡就先上存儲過程的語法, 以便比較

1. 語法

　　CREATE PROCEDURE 存儲過程名稱 (IN 參數名 參數類型, OUT 參數名 參數類型)

　　begin

　　end

這裡的參數都是非必須的, 可以有 IN/OUT 都是可以沒有的

從語法格式上看, 與自定義函數的框架大致是一樣的, 只是其中的細節不同.

1) 存儲過程沒有return返回值, 但是卻可以通過OUT的方式, 來修改傳入的參數, 可以當做是一種返回值, 

2) 存儲過程在end之前, 可以加上一句 select語句, 以便程式讀取到結果集. 所以存儲過程能返回的值其實更多

3) 並不能混入別的sql中使用, 只能通過 call 的方式, 單獨使用

2. 例子

之前的項目中, 我碰到一個生成流水號的功能. 當時, 我是通過藉助資料庫的方式, 來生成流水號的. 

我這裡的流水號, 由首碼, 時間, 流水碼 三部分組成

先建一張流水號表

CREATE TABLE `serialno` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id',
  `Pre` varchar(10) NOT NULL COMMENT '編號',
  `Description` varchar(10) DEFAULT NULL COMMENT '說明',
  `Res` varchar(20) DEFAULT NULL COMMENT '流水號(不加編號的)',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='流水號表'

有了這張表, 就可以開始存儲過程了.

delimiter $
drop PROCEDURE if EXISTS p_GetSerialNo;
CREATE PROCEDURE `p_GetSerialNo`(IN preValue VARCHAR(10), IN preDate VARCHAR(10),IN des varchar(20), in length int)
BEGIN
    DECLARE  t_error INT DEFAULT 0;
    DECLARE  resValue VARCHAR(20) DEFAULT NULL;
    -- DECLARE
    --     CONTINUE HANDLER FOR SQLEXCEPTION,
    --     SQLWARNING,
    --     NOT FOUND
    -- SET t_error = 1;
    
    START TRANSACTION;

    SELECT Res INTO resValue FROM serialno WHERE Pre=preValue;
    IF resValue IS NULL  THEN
        SET resValue= CONCAT(preDate, LPAD(1, length, '0'));
        INSERT INTO serialno (Pre, Description, Res) VALUES (preValue, des, resValue);
    ELSE
        IF preDate =  (SUBSTRING(resValue,1,8) + '0') THEN
            SET resValue = CAST(resValue AS SIGNED) + 1;
            if preDate <> SUBSTRING(resValue,1,8) THEN
                set t_error = -1;
            end if;
        ELSE
            SET resValue= CONCAT(preDate, LPAD(1, length, '0'));
        END IF;
        UPDATE serialno SET Res = resValue WHERE Pre = preValue;
    END IF;
    
    #IF t_error = 1 then 
    IF @@error_count <> 0 | t_error <> 0 THEN
        ROLLBACK;
        select t_error;
    ELSE 
        COMMIT;
        SELECT CONCAT(preValue, resValue);
    END IF;
END $
delimiter ;

這裡的參數preValue為首碼, preDate為8位的日期,格式如:"20161227", 參數des為說明, 此處並不參與邏輯, 只是更新一個欄位. 最後一個length欄位, 表示流水號的位數. 流水號的位數不能設置的太過小, 得視業務來確定. 當流水號溢出時, 會返回-1.

OK, 來看一下效果:

call p_GetSerialNo( 'b', '20170101',  'b', 4);