MariaDB/MySQL存儲過程和函數(shù)

作者：碼農(nóng)世界 2023-02-28 11:29:09

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

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

雖然存儲過程和存儲函數(shù)在功能實現(xiàn)上有些區(qū)別，但在使用方法上幾乎一致。

1.創(chuàng)建存儲過程、函數(shù)

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

-- 創(chuàng)建存儲過程和函數(shù)
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的存儲過程和函數(shù)中，允許存在DML和DDL語句。且存儲過程中還允許(存儲函數(shù)不允許)使用SQL事務(wù)類型的語句，例如提交commit。當(dāng)然，肯定也支持嵌套其他存儲過程或函數(shù)。

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

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

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

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

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

例如：

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

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

以下是一個函數(shù)的創(chuàng)建和使用示例：

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

它們表示的是參數(shù)的類型。

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

OUT參數(shù)類型表示將存儲過程的返回值傳遞給調(diào)用者。其初始值為NULL，當(dāng)存儲過程返回時，這個值對調(diào)用者來說是可見的。

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

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

默認(rèn)每個參數(shù)都是IN。要指定其他類型的參數(shù)，可以在參數(shù)名前面使用關(guān)鍵字OUT或INOUT。

(1).IN參數(shù)類型。

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

以下是IN類型參數(shù)的示例。

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參數(shù)類型。

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

以下是OUT類型參數(shù)的示例。在此示例中，傳入@a給proc()，最后將count(*)賦值給out參數(shù)cnt，cnt代表的就是傳入?yún)?shù)@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參數(shù)類型。

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

以下是INOUT參數(shù)類型的示例。

/* 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參數(shù)來判斷大小，因為它繼承了傳遞時的值 */
        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.修改和刪除存儲過程、函數(shù)

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

-- 修改存儲過程和函數(shù)
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 }

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

drop {procedure|function} [if exists] sp_name

3.查看存儲過程、函數(shù)信息

查看存儲過程和函數(shù)的信息。

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

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

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開始的序列值，表示存儲過程中所執(zhí)行的動作先后順序。第二列是mariadb要執(zhí)行的動作，這些動作是基于存儲過程中的源語句進(jìn)行設(shè)置的。

責(zé)任編輯：武曉燕來源：今日頭條

存儲函數(shù)MySQL