在MySQL/MariaDB中，存儲過程(stored procedure)、存儲函數(stored function)、觸發器(trigger)、事件(event)統稱為存儲程序(stored programs)。此外，存儲過程和存儲函數還合稱為stored routines。

對于函數來說，除了存儲函數，還有用戶自定義函數(UDF,user defined function)，在MySQL/MariaDB中，用戶自定義函數是存儲函數的擴展，它像一個小程序一樣，需要編譯、安裝后才能運行。這和SQL Server對UDF的定義不同，倒是類似于SQL Server的CLR程序。

雖然存儲過程和存儲函數在功能實現上有些區別，但在使用方法上幾乎一致。

1.創建存儲過程、函數

在MySQL/MariaDB中創建存儲過程、函數的語法如下：其中OR REPLACE是MariaDB 10.1.3版本中才有的，MySQL中不支持OR REPLACE。

-- 創建存儲過程和函數
CREATE [OR REPLACE] PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
CREATE [OR REPLACE] 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:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
routine_body:
    Valid SQL routine statement

在MySQL/MariaDB的存儲過程和函數中，允許存在DML和DDL語句。且存儲過程中還允許(存儲函數不允許)使用SQL事務類型的語句，例如提交commit。當然，肯定也支持嵌套其他存儲過程或函數。

存儲過程的參數有三種類型：IN、OUT和INOUT，下文將詳細分析這三種類型參數。

在創建和修改的語法中，需要說明的就是characteristic部分，這部分基本沒什么用，但可以了解下它們是干什么的。

• language sql：表示后面的body部分使用標準SQL語句編寫，這是默認的。該選項的作用是為了以后支持非SQL語句書寫存儲過程和函數的，例如SQL Server中就有使用.NET寫的CLR存儲過程、函數、觸發器等。但目前，這個還沒有任何意義。
• [not] deterministic：deterministic的意思是確定的。這里的意思是函數返回值是明確的，而非具有隨機性的值。例如，使用了隨機數，使用了now()這樣的函數等。not deterministic表示返回值是不確定的，這是系統默認值。當優化器知道函數返回值是確定值時，將選擇一個更優化的執行計劃。如果返回值是確定的，卻定義為not deterministic，則性能會降低，如果返回值是不確定的，卻定義為deterministic，則報錯。
• {contains sql|no sql|reads sql data|modifies sql data}：這些是提供給服務器的信息類子句，看上去是約束性語句，但MariaDB不會根據它們做任何檢查。也就是說，這些提示符只是給人看的，沒有任何作用。contains sql表示body不包含讀和寫數據的語句，例如SET和DO；no sql表示body不包含SQL語句；reads sql data表示body包含讀數據的語句，但不包含寫數據的語句，如SELECT。modifies sql data表示body包含寫數據的語句，如DELETE/UPDATE；如果沒有指定這些特征值，則使用默認值contains sql。
• sql security：指明執行該程序時以誰的身份執行。definer表示執行時獲取創建者的權限，invoker表示以調用者的身份執行，若調用該程序的用戶對程序中涉及的對象沒有對應的權限則會執行失敗（如lisa用戶有執行存儲過程的權限，但是沒有讀取存儲過程中涉及的表a的權限，那么執行存儲過程時因為讀表失敗而導致執行被拒絕）。默認是definer。
• comment：程序的注釋信息。

當要調用存儲過程或函數時，可以使用call命令調用存儲過程，如call sp_name();；而函數則可以當作表達式一樣進行調用，例如使用select命令select func();，當作表達式賦值給變量set @a=func()。

需要注意的是，在MySQL/MariaDB中，因為語句的結束符是分號";"，在存儲過程或函數創建過程中直接使用分號會導致語句報錯。所以當存儲過程或存儲函數中包含需要使用分號的語句時，應在創建存儲過程或函數之前使用delimiter命令來暫時改變語句結束符，在創建完畢之后再改回結束符為分號";"。

例如：

delimiter $$
create or replace procedure proc()       -- procedure name
begin                                    -- procedure body
    select * from A;
end $$
delimiter ;
--調用存儲過程
call proc();

在上面的語句中，首先定義了結束符為$$；然后判斷了存儲過程proc()是否存在，存在則刪除，之后才開始創建存儲過程。這個存儲過程很簡單，只是一個select語句。創建語句結束之后，再次使用delimiter命令將結束符改回了分號";"。最后使用call命令進行了存儲過程的調用。

以下是一個函數的創建和使用示例：

delimiter $$
create or replace function func1()
    returns int
    return (select count(*) from t1);$$
delimiter ;
set @c=func1();
select func1(),@c;
+---------+------+
| func1() | @c   |
+---------+------+
|       6 |    6 |
+---------+------+

1.1 存儲過程的IN、OUT和INOUT

它們表示的是參數的類型。

IN參數類型表示將調用者給定的值傳遞給存儲過程。存儲過程可能會修改這個值，但是對于調用者來說，在存儲過程返回結果時，所做的修改是不可見的。

OUT參數類型表示將存儲過程的返回值傳遞給調用者。其初始值為NULL，當存儲過程返回時，這個值對調用者來說是可見的。

INOUT參數類型表示由調用者傳遞值給存儲過程，存儲過程可能會修改這個值，當存儲過程返回的時候，所做的修改對調用者來說是可見的。

對于每個OUT或INOUT類型的參數，當調用者在CALL語句中調用存儲過程時，所傳遞的每個用戶變量都可以在存儲過程返回的時候獲取其值。

默認每個參數都是IN。要指定其他類型的參數，可以在參數名前面使用關鍵字OUT或INOUT。

(1).IN參數類型。

IN參數類型是指調用者將某個值傳遞給存儲過程，存儲過程借用這個值來完成某些操作。

以下是IN類型參數的示例。

create or replace table t1(a int);
insert into t1 values(1),(2),(3),(4),(5),(6);
delimiter $$
create or replace procedure proc1(min int,max int)
begin
    select * from t1 where t1.a >= min and t1.a <= max;
end$$
delimiter ;
call proc1(3,5);
+------+
| a    |
+------+
|    3 |
|    4 |
|    5 |
+------+

(2).OUT參數類型。

OUT參數類型是指存儲過程將某個值通過該參數返回給調用者。因此調用者必須傳遞一個用戶變量給存儲過程，用來記錄存儲過程OUT參數的值。這個用戶變量在傳遞給存儲過程之前，可以是一個已賦值的變量，但在傳遞給存儲過程時，將自動初始化為NULL值。

以下是OUT類型參數的示例。在此示例中，傳入@a給proc()，最后將count(*)賦值給out參數cnt，cnt代表的就是傳入參數@a。

delimiter $$
create or replace procedure proc2(out cnt int)
begin
    select count(*) into cnt from A;
end$$
delimiter ;
call proc1(@a);
select @a as a;
     a  
--------
       6

(3).INOUT參數類型。

INOUT參數類型指的是調用者和存儲過程之間傳遞的內容可以互相賦值。INOUT有兩個過程，一個是IN的過程，這個過程是將調用者指定的值傳遞給存儲過程，另一個是OUT的過程，這個過程是存儲過程將某個返回值返回給調用者。因此，調用者傳遞INOUT參數時，也必須傳遞一個用戶變量。但與OUT不同的是，INOUT的用戶變量有初始值，這個初始值會直接應用在存儲過程中。而OUT的用戶變量雖然也有初始值，但這個初始值會自動轉換為NULL。

以下是INOUT參數類型的示例。

/* procedure INOUT */
create or replace table t1(a int);
insert into t1 values(1),(2),(3),(4),(5),(6);
delimiter $$
create or replace procedure proc3(INOUT cnt int,min int,max int)
begin
    if cnt <5 then   /* 直接用cnt這個INOUT參數來判斷大小，因為它繼承了傳遞時的值 */
        select count(*) into cnt from t1 where t1.a >=min and t1.a <=max;
    else
        select count(*) into cnt from t1;
    end if;
end$$
delimiter ;
set @a=3;
set @b=6;
call proc1(@a,3,5);
call proc1(@b,3,5);
select @a,@b;
+------+------+
| @a   | @b   |
+------+------+
|    3 |    6 |
+------+------+

2.修改和刪除存儲過程、函數

可以使用alter語句修改存儲過程、函數，但alter語句只能修改characteristic部分，不支持對body部分和參數部分修改。若要修改它們，只能先刪除再創建。

-- 修改存儲過程和函數
ALTER {PROCEDURE | FUNCTION} proc_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

刪除存儲過程和函數的方式很簡單，直接使用drop命令，但要求有alter權限。

drop {procedure|function} [if exists] sp_name

3.查看存儲過程、函數信息

查看存儲過程和函數的信息。

show {procedure|function} status like 'pattern'; -- 查看routine的基本信息
show create {procedure|function} proc_name;      -- 查看routine的創建語句
show {procedure|function} code routine_name;     -- 查看MariaDB內部是如何操作routine中各語句的

從
information_schema.routines中查看存儲過程和函數的信息，這個比show status更詳細一點。

select * from information_schema.routines where routine_name='xxx'\G

這里說明下show code功能，這個功能必須要在支持debug的MariaDB上才能使用，可以在編譯時使用選項"--with-debug"啟用該功能。

例如：

DELIMITER $$
CREATE PROCEDURE p1 ()
  BEGIN
    DECLARE fanta INT DEFAULT 55;
    DROP TABLE t2;
    LOOP
      INSERT INTO t3 VALUES (fanta);
      END LOOP;
  END$$
delimiter ;
SHOW PROCEDURE CODE p1;
+-----+----------------------------------------+
| Pos | Instruction                            |
+-----+----------------------------------------+
|   0 | set fanta@0 55                         |
|   1 | stmt 9 "DROP TABLE t2"                 |
|   2 | stmt 5 "INSERT INTO t3 VALUES (fanta)" |
|   3 | jump 2                                 |
+-----+----------------------------------------+

其中第一列是從0開始的序列值，表示存儲過程中所執行的動作先后順序。第二列是mariadb要執行的動作，這些動作是基于存儲過程中的源語句進行設置的。