存儲(chǔ)過(guò)程和函數(shù)是事先經(jīng)過(guò)編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段 SQL 語(yǔ)句的集合，調(diào)用存儲(chǔ)過(guò)程和函數(shù)可以簡(jiǎn)化應(yīng)用開發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫(kù)和應(yīng)用服務(wù)器之間的傳輸，對(duì)于提高數(shù)據(jù)處理的效率是有好處的

存儲(chǔ)過(guò)程和函數(shù)的區(qū)別在于函數(shù)必須有返回值，而存儲(chǔ)過(guò)程沒(méi)有，存儲(chǔ)過(guò)程的參數(shù)可以使用 in，out，inout 類型，而函數(shù)的參數(shù)只能是 in 類型的。如果有函數(shù)從其他類型的數(shù)據(jù)庫(kù)遷移到 MySQL，那么就可能因此需要將函數(shù)改造成存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程和函數(shù)的相關(guān)操作

在對(duì)存儲(chǔ)過(guò)程或函數(shù)進(jìn)行操作時(shí)，需要首先確認(rèn)用戶是否具有相應(yīng)的權(quán)限。例如，創(chuàng)建存儲(chǔ)過(guò)程或者函數(shù)需要 CREATE ROUTINE 權(quán)限，修改或者刪除存儲(chǔ)過(guò)程或者函數(shù)需要 ALTER ROUTINE 權(quán)限，執(zhí)行存儲(chǔ)過(guò)程或者函數(shù)需要 EXECUTE 權(quán)限

創(chuàng)建，修改存儲(chǔ)過(guò)程或者函數(shù)

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

    proc_parameter:[ IN | OUT | INOUT ] param_name type

    func_parameter:param_name type

    type:Any valid MySQL data type

    characteristic:LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }| COMMENT 'string'

    routine_body:Valid SQL procedure statement or statements 

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

    characteristic:{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }| COMMENT 'string'

調(diào)用過(guò)程的語(yǔ)法：CALL sp_name([parameter[,...]]);

調(diào)用函數(shù)與調(diào)用過(guò)程存在一些差異?？梢砸哉{(diào)用內(nèi)置函數(shù)的相同方式調(diào)用一個(gè)存儲(chǔ)函數(shù) select functionname();

MySQL 的存儲(chǔ)過(guò)程和函數(shù)中允許包含 DDL 語(yǔ)句，也允許在存儲(chǔ)過(guò)程中執(zhí)行提交（Commit，即確認(rèn)之前的修改）或者回滾（Rollback，即放棄之前的修改），但是存儲(chǔ)過(guò)程和函數(shù)中不允許執(zhí)行 LOAD DATA INFILE 語(yǔ)句。此外，存儲(chǔ)過(guò)程和函數(shù)中可以調(diào)用其他的過(guò)程或者函數(shù)

通常我們?cè)趫?zhí)行創(chuàng)建過(guò)程和函數(shù)之前，都會(huì)通過(guò) DELIMITER $$ 命令將語(yǔ)句的結(jié)束符從 ; 修改成其他符號(hào)，這里使用的是 $$，這樣在過(guò)程和函數(shù)中的 ; 就不會(huì)被 MySQL 解釋成語(yǔ)句的結(jié)束而提示錯(cuò)誤。在存儲(chǔ)過(guò)程或者函數(shù)創(chuàng)建完畢，通過(guò) DELIMITER ; 命令再將結(jié)束符改回成 ;

和視圖的創(chuàng)建語(yǔ)法稍有不同，存儲(chǔ)過(guò)程和函數(shù)的 CREATE 語(yǔ)法不支持使用 CREATE OR REPLACE 對(duì)存儲(chǔ)過(guò)程和函數(shù)進(jìn)行修改，如果需要對(duì)已有的存儲(chǔ)過(guò)程或者函數(shù)進(jìn)行修改，需要執(zhí)行 ALTER 語(yǔ)法

下面對(duì) characteristic 特征值的部分進(jìn)行簡(jiǎn)單的說(shuō)明：

LANGUAGE SQL 說(shuō)明下面過(guò)程的 BODY 是使用 SQL 語(yǔ)言編寫，這條是系統(tǒng)默認(rèn)的，為今后 MySQL 會(huì)支持的除 SQL 外的其他語(yǔ)言支持的存儲(chǔ)過(guò)程而準(zhǔn)備

DETERMINISTIC 確定的，即每次輸入一樣輸出也一樣的程序

NOT DETERMINISTIC 非確定的，默認(rèn)是非確定的。當(dāng)前，這個(gè)特征值還沒(méi)有被優(yōu)化程序使用。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 這些特征值提供子程序使用數(shù)據(jù)的內(nèi)在信息，這些特征值目前只是提供給服務(wù)器，并沒(méi)有根據(jù)這些特征值來(lái)約束過(guò)程實(shí)際使用數(shù)據(jù)的情況

CONTAINS SQL 表示子程序不包含讀或?qū)憯?shù)據(jù)的語(yǔ)句

NO SQL 表示子程序不包含 SQL 語(yǔ)句

READS SQL DATA 表示子程序包含讀數(shù)據(jù)的語(yǔ)句，但不包含寫數(shù)據(jù)的語(yǔ)句

MODIFIES SQL DATA 表示子程序包含寫數(shù)據(jù)的語(yǔ)句。如果這些特征沒(méi)有明確給定，默認(rèn)使用的值是 CONTAINS SQL

SQL SECURITY { DEFINER | INVOKER } 可以用來(lái)指定子程序該用創(chuàng)建子程序者的許可來(lái)執(zhí)行，還是使用調(diào)用者的許可來(lái)執(zhí)行。默認(rèn)值是 DEFINER

COMMENT 'string' 存儲(chǔ)過(guò)程或者函數(shù)的注釋信息

刪除存儲(chǔ)過(guò)程或者函數(shù)

一次只能刪除一個(gè)存儲(chǔ)過(guò)程或者函數(shù)，刪除存儲(chǔ)過(guò)程或者函數(shù)需要有該過(guò)程或者函數(shù)的 ALTER ROUTINE 權(quán)限，具體語(yǔ)法如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

查看存儲(chǔ)過(guò)程或者函數(shù)

查看存儲(chǔ)過(guò)程或者函數(shù)的狀態(tài)

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

查看存儲(chǔ)過(guò)程或者函數(shù)的定義

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

通過(guò)查看 information_schema.Routines 了解存儲(chǔ)過(guò)程和函數(shù)的信息

變量的使用

存儲(chǔ)過(guò)程和函數(shù)中可以使用變量，變量是不區(qū)分大小寫的

通過(guò) DECLARE 可以定義一個(gè)局部變量，該變量的作用范圍只能在 BEGIN…END 塊中，可以用在嵌套的塊中。變量的定義必須寫在復(fù)合語(yǔ)句的開頭，并且在任何其他語(yǔ)句的前面?？梢砸淮温暶鞫鄠€(gè)相同類型的變量。如果需要，可以使用 DEFAULT 賦默認(rèn)值

定義一個(gè)變量的語(yǔ)法如下：

DECLARE var_name[,...] type [DEFAULT value]

直接賦值使用 SET，可以賦常量或者賦表達(dá)式，具體語(yǔ)法如下：

SET var_name = expr [, var_name = expr] ...

也可以通過(guò)查詢將結(jié)果賦給變量，這要求查詢返回的結(jié)果必須只有一行，具體語(yǔ)法如下：

SELECT col_name[,...] INTO var_name[,...] table_expr

定義條件和處理

條件的定義和處理可以用來(lái)定義在處理過(guò)程中遇到問(wèn)題時(shí)相應(yīng)的處理步驟

條件的定義

DECLARE condition_name CONDITION FOR condition_value

    condition_value:SQLSTATE [VALUE] sqlstate_value
        | mysql_error_code

條件的處理

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

    handler_type:CONTINUE | EXIT | UNDO

    condition_value:SQLSTATE [VALUE] sqlstate_value
        | condition_name
        | SQLWARNING
        | NOT FOUND
        | SQLEXCEPTION
        | mysql_error_code

handler_type：CONTINUE 表示繼續(xù)執(zhí)行下面的語(yǔ)句，EXIT 則表示執(zhí)行終止

condition_value 的值可以是通過(guò) DECLARE 定義的 condition_name，可以是 SQLSTATE 的值或者 mysql-error-code 的值或者 SQLWARNING、NOT FOUND、SQLEXCEPTION，這 3 個(gè)值是 3 種定義好的錯(cuò)誤類別，分別代表不同的含義：

• SQLWARNING 是對(duì)所有以 01 開頭的 SQLSTATE 代碼的速記
• NOT FOUND 是對(duì)所有以 02 開頭的 SQLSTATE 代碼的速記
• SQLEXCEPTION 是對(duì)所有沒(méi)有被 SQLWARNING 或 NOT FOUND 捕獲的 SQLSTATE 代碼的速記

光標(biāo)的使用

在存儲(chǔ)過(guò)程和函數(shù)中可以使用光標(biāo)對(duì)結(jié)果集進(jìn)行循環(huán)的處理。光標(biāo)的使用包括光標(biāo)的聲明，OPEN， FETCH 和 CLOSE，其語(yǔ)法分別如下：

• 聲明光標(biāo)：

    DECLARE cursor_name CURSOR FOR select_statement
  

• OPEN 光標(biāo)：

    OPEN cursor_name
  

• FETCH 光標(biāo)：

    FETCH cursor_name INTO var_name [, var_name] ...
  

• CLOSE 光標(biāo)：

    CLOSE cursor_name
  

注意：變量、條件、處理程序、光標(biāo)都是通過(guò) DECLARE 定義的，它們之間是有先后順序的要求的。變量和條件必須在最前面聲明，然后才能是光標(biāo)的聲明，最后才可以是處理程序的聲明

流程控制

if 語(yǔ)句

if 實(shí)現(xiàn)條件判斷，滿足不同的條件執(zhí)行不同的語(yǔ)句列表，具體語(yǔ)法如下：

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

CASE 語(yǔ)句

case 實(shí)現(xiàn)比 if 更復(fù)雜一些的條件構(gòu)造，具體語(yǔ)法如下：

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE

Or：

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

loop 語(yǔ)句

LOOP 實(shí)現(xiàn)簡(jiǎn)單的循環(huán)，退出循環(huán)的條件需要使用其他的語(yǔ)句定義，通?？梢允褂?LEAVE 語(yǔ)句實(shí)現(xiàn)，具體語(yǔ)法如下：

[begin_label:] LOOP
statement_list
END LOOP [end_label]

如果不在 statement_list 中增加退出循環(huán)的語(yǔ)句，那么 LOOP 語(yǔ)句可以用來(lái)實(shí)現(xiàn)簡(jiǎn)單的死循環(huán)

leave 語(yǔ)句

用來(lái)從標(biāo)注的流程構(gòu)造中退出，通常和 BEGIN ... END 或者循環(huán)一起使用

iterate 語(yǔ)句

iterate 語(yǔ)句必須用在循環(huán)中，作用是跳過(guò)當(dāng)前循環(huán)的剩下的語(yǔ)句，直接進(jìn)入下一輪循環(huán)

repeat 語(yǔ)句

有條件的循環(huán)控制語(yǔ)句，當(dāng)滿足條件的時(shí)候退出循環(huán)，具體語(yǔ)法如下：

[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

while 語(yǔ)句

WHILE 語(yǔ)句實(shí)現(xiàn)的也是有條件的循環(huán)控制語(yǔ)句，即當(dāng)滿足條件時(shí)執(zhí)行循環(huán)的內(nèi)容，具體語(yǔ)法如下：

[begin_label:] WHILE search_condition DO
statement_listEND WHILE [end_label]

WHILE 循環(huán)和 REPEAT 循環(huán)的區(qū)別在于：WHILE 是滿足條件才執(zhí)行循環(huán)，REPEAT 是滿足條件退出循環(huán)；WHILE 在首次循環(huán)執(zhí)行之前就判斷條件，所以循環(huán)最少執(zhí)行 0 次，而 REPEAT 是在首次執(zhí)行循環(huán)之后才判斷條件，所以循環(huán)最少執(zhí)行 1 次

事件調(diào)度器

事件調(diào)度器可以將數(shù)據(jù)庫(kù)按自定義的時(shí)間周期觸發(fā)某種操作，可以理解為時(shí)間觸發(fā)器

下面是一個(gè)最簡(jiǎn)單的事件調(diào)度器，每 5 秒向表中插入數(shù)據(jù)

create event myevent
on schedule
every 5 second
do
insert into tablename values(value1);

• 事件名稱在 create event 關(guān)鍵字后指定
• 通過(guò) on schedule 子句指定事件在何時(shí)執(zhí)行及執(zhí)行頻次
• 通過(guò) do 子句指定要執(zhí)行的具體操作或事件

查看事件：show events;

查看調(diào)度器：show variables like '%scheduler' 默認(rèn)是關(guān)閉的

打開調(diào)度器：set global event_scheduler=1;，事件才能啟動(dòng)

查看后臺(tái)進(jìn)程：show processlist;

禁用事件：alter event eventname disable;

刪掉事件：drop event eventname;