1、MySQL的自定義函數(shù)（FUNCTION）

1.1、創(chuàng)建函數(shù)

MySQL 數(shù)據(jù)庫創(chuàng)建函數(shù)（Function）的語法：

CREATE FUNCTION func_name ( [func_parameter] )  -- 括號是必須的，參數(shù)是可選的
RETURNS type
[ characteristic ...] routine_body

說明如下：

• CREATE FUNCTION：用來創(chuàng)建函數(shù)的關(guān)鍵字
• func_name：函數(shù)名
• func_parameters：函數(shù)的參數(shù)列表，參數(shù)列表的形式為：[IN | OUT | INOUT] param_name type。IN：表示輸入?yún)?shù)；OUT：表示輸出參數(shù)；INOUT：表示既可以輸入也可以輸出；param_name：表示參數(shù)的名稱；type：表示參數(shù)的類型，該類型可以是MySQL數(shù)據(jù)庫中的任意類型；
• RETURNS type：函數(shù)返回數(shù)據(jù)的類型
• characteristic：指定存儲函數(shù)的特性，取值與存儲過程時相同
• routine_body：函數(shù)體。函數(shù)體由SQL代碼構(gòu)成，可以是簡單SQL查詢語句或者是復(fù)合結(jié)構(gòu)SQL語句。函數(shù)體若是復(fù)合結(jié)構(gòu)（多行代碼）時，必須使用 begin...end 語句。復(fù)合結(jié)構(gòu)可以包含聲明、流程控制，需結(jié)合使用 delimiter 來轉(zhuǎn)換(;)結(jié)束標(biāo)識符。

函數(shù)體必須得有 return 語句，如果沒有就會報錯。return 語句可以不放在函數(shù)體的最后，但不建議這么做。函數(shù)體中如果只有一條語句，則可以不使用 begin...end 語句。

下面示例分別創(chuàng)建一個隨機(jī)生成字符串和隨機(jī)生成編號的函數(shù)，代碼如下：

-- 隨機(jī)產(chǎn)生字符串
drop function if exists rand_string;  -- 先判斷是否已存在同名函數(shù)，如果已存在則先刪除
DELIMITER $$ -- 兩個 $$ 表示結(jié)束
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i < n do
        set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
        set i=i+1;
    end while;
    return return_str;
end $$
DELIMITER ;

-- 隨機(jī)生成編號
drop function if exists rand_num;
DELIMITER $$
create function rand_num() 
returns int(5)
begin
    declare i int default 0;
    set i=floor(100+rand()*10);
    return i;
end $$
DELIMITER ;

自定義函數(shù)的調(diào)用和其他普通函數(shù)的調(diào)用一樣，示例如下：

select rand_string(5);
select rand_num();

結(jié)果示例如下：

1.2、刪除函數(shù)

刪除函數(shù)的語句：

drop function function_name;

drop function [if exists] funcName;   -- 可以加個判斷，是否存在

-- 示例：
drop function if exists rand_num;

刪除函數(shù)的語法只需寫上函數(shù)名即可，函數(shù)的參數(shù)可以不用寫出來。

1.3、delimiter（設(shè)置分隔符的關(guān)鍵字）

delimiter 是分隔符的意思，在 mysql 中默認(rèn)的分隔符是分號（即 ; ）。默認(rèn)情況下，mysql 在遇到分號 ; 時，則認(rèn)為該語句已結(jié)束，在回車后，mysql 就會執(zhí)行該條語句。但有時候，可能我們并不希望這樣。比如在創(chuàng)建自定義函數(shù)或者創(chuàng)建存儲過程時，我們可能會輸入多條語句，并且都帶有分號，但此時我們并不希望 mysql 立即執(zhí)行這些語句。此時我們可以通過 delimiter 關(guān)鍵字來將分隔符臨時指定為其它符號，指定完后會在當(dāng)前會話中有效。

語法為：

DELIMITER 加你想指定為分隔符的字符

-- 示例：
DELIMITER $$   -- 指定 $$ 為分隔符
DELIMITER //   -- 指定 // 為分隔符
DELIMITER ;    -- 指定 ; 為分隔符

比如，創(chuàng)建自定義函數(shù)：

DELIMITER $$   -- 先指定分隔符為 $$，其實(shí)可以指定為任意符號，比如 //、;;、@@等等
create function rand_num() 
returns int(5)
begin
    ...
end $$      -- 以指定的分隔符結(jié)束
DELIMITER ;   -- 重新指定分號為分隔符

上面就是，先將分隔符設(shè)置為 $$, 直到遇到下一個 $$，才整體執(zhí)行語句。執(zhí)行完后在最后一行 delimiter ; 又重新將 mysql 的分隔符設(shè)置為分號，如果不修改的話，本次會話中的所有分隔符都以 $$ 為準(zhǔn)。

1.4、創(chuàng)建函數(shù)時報錯has none of DETERMINISTIC...

在創(chuàng)建函數(shù)時，MySQL可能會報以下錯誤：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

這是因?yàn)閙ysql 默認(rèn)不允許創(chuàng)建自定義函數(shù)（安全性的考慮），此時我們需要將參數(shù) log_bin_trust_function_creators 設(shè)置為開啟狀態(tài)。

可以通過以下命令查看 log_bin_trust_function_creators 參數(shù)：

show variables like 'log_bin_trust_function_creators';

如下則為關(guān)閉狀態(tài)：

執(zhí)行以下命令將參數(shù) log_bin_trust_function_creators 設(shè)置為開啟狀態(tài)：

set global log_bin_trust_function_creators=1;

再次查看時即可以看到為開啟狀態(tài)：

但這樣只是臨時設(shè)置，重啟終端后該設(shè)置即會失效。如果要配置永久的，需要在配置文件的 [mysqld] 上配置以下屬性：

log_bin_trust_function_creators=1

2、存儲過程（procedure）

存儲過程是一組可編程的函數(shù)，是為了完成特定功能的SQL語句集，經(jīng)編譯創(chuàng)建并保存在數(shù)據(jù)庫中，用戶可通過指定存儲過程的名字并給定參數(shù)（需要時）來調(diào)用執(zhí)行。

存儲過程就是具有名字的一段代碼，用來完成一個特定的功能。創(chuàng)建的存儲過程保存在數(shù)據(jù)庫的數(shù)據(jù)字典中。

使用存儲過程的好處：

• 批量操作數(shù)據(jù)。存儲過程可以將一些重復(fù)性很高的操作，比如批量插入數(shù)據(jù)、批量刪除數(shù)據(jù)等，封裝到一個存儲過程中，簡化了對這些SQL的調(diào)用。
• 批量處理：SQL+循環(huán)，減少流量，也就是“跑批”
• 統(tǒng)一接口，確保數(shù)據(jù)的安全

2.1、創(chuàng)建存儲過程（create procedure）

創(chuàng)建語法：

create procedure 存儲過程名 ([params])
BEGIN
    存儲過程體（一組合法的SQL語句）
END

說明如下：

• 參數(shù)列表（params）：如果有多個參數(shù)則用逗號 , 分隔開，一個參數(shù)包括三部分：參數(shù)模式、參數(shù)名、參數(shù)類型，如：in name varchar(20)。參數(shù)模式有：in 輸入、out 輸出、inout 輸入輸出參數(shù)。
• IN 參數(shù)：該參數(shù)作為輸入，必須在調(diào)用存儲過程時指定，在存儲過程中修改該參數(shù)的值不能被返回，為默認(rèn)值。僅需要將數(shù)據(jù)傳入存儲過程，并不需要返回計算后的該值。只能當(dāng)做傳入?yún)?shù)
• OUT 參數(shù)：該參數(shù)作為輸出，該值可在存儲過程內(nèi)部被改變，并可返回。不接受外部傳入的數(shù)據(jù)，僅返回計算之后的值。只能當(dāng)做轉(zhuǎn)出參數(shù)。也就是說，即使傳值給 OUT 參數(shù)，該參數(shù)也無法得到你傳的值，得到的會是一個 null 值。
• INOUT 參數(shù)：該參數(shù)即可作為輸入，又可做為輸出，也就是該參數(shù)既需要傳入值，又可以返回值。可當(dāng)做傳入轉(zhuǎn)出參數(shù)

如果存儲過程體只有一條語句，則可以不用 begin...end。存儲過程體中的每條 SQL 語句的結(jié)尾要求必須寫分號。

示例如下：

-- 創(chuàng)建一個循環(huán)往dept表插入數(shù)據(jù)的存儲過程
drop procedure if exists insert_dept;
delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))   -- start為起始，max_num為插入的數(shù)量
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
        set i = i+1;
        insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
    until i=max_num
    end repeat;
    commit;   -- 循環(huán)之后，一次性commit，避免多次連接數(shù)據(jù)庫。數(shù)據(jù)量太大的話可以改為多少條commit一次
end $$
DELIMITER ;

使用 call 關(guān)鍵字來調(diào)用存儲過程，如下：

CALL insert_dept(100, 5000);

2.2、刪除存儲過程（drop procedure）

刪除語法如下：

drop procedure [if exists] 存儲過程名;

-- 示例如下：
drop procedure my_insert;
drop procedure if exists my_insert;   -- 先判斷是否存在再刪除

2.3、存儲過程體的循環(huán)寫法

存儲過程體里面循環(huán)的寫法主要有以下：

-- 創(chuàng)建存儲過程
drop procedure if exists my_proc;
delimiter $$
create procedure my_proc()   
begin
     -- 第一種，while循環(huán)
     while i < 3 do 
            select i;
            set i = i +1;
    end while;

    -- 第二種，repeat循環(huán)
    REPEAT
            select j;
            set j = j +1;
    UNTIL j > 3 
    END REPEAT;


    -- 第三種，loop循環(huán)
    test_loop: LOOP
    select startDate;
        #開始時間加一天
        set startDate = DATE_FORMAT(date_add(startDate,interval 1 day),"%Y%m%d");
        IF startDate>endDate THEN
        LEAVE test_loop; 
    END IF; 
    END LOOP test_loop;
    

end $$
DELIMITER ;

如果有多個參數(shù)用","分割開