日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務(wù)時(shí)間：8:30-17:00
你可能遇到了下面的問題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
MariaDB/MySQL存儲(chǔ)過程和函數(shù)

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

創(chuàng)新互聯(lián)建站擁有十多年成都網(wǎng)站建設(shè)工作經(jīng)驗(yàn),為各大企業(yè)提供成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)服務(wù)，對(duì)于網(wǎng)頁設(shè)計(jì)、PC網(wǎng)站建設(shè)（電腦版網(wǎng)站建設(shè)）、重慶App定制開發(fā)、wap網(wǎng)站建設(shè)（手機(jī)版網(wǎng)站建設(shè)）、程序開發(fā)、網(wǎng)站優(yōu)化（SEO優(yōu)化）、微網(wǎng)站、國(guó)際域名空間等，憑借多年來在互聯(lián)網(wǎng)的打拼，我們?cè)诨ヂ?lián)網(wǎng)網(wǎng)站建設(shè)行業(yè)積累了很多網(wǎng)站制作、網(wǎng)站設(shè)計(jì)、網(wǎng)絡(luò)營(yíng)銷經(jīng)驗(yàn)，集策劃、開發(fā)、設(shè)計(jì)、營(yíng)銷、管理等網(wǎng)站化運(yùn)作于一體，具備承接各種規(guī)模類型的網(wǎng)站建設(shè)項(xiàng)目的能力。

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

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

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

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

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

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

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

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

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

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

例如：

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

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

以下是一個(gè)函數(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 存儲(chǔ)過程的IN、OUT和INOUT

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

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

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

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

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

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

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

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

以下是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ù)類型是指存儲(chǔ)過程將某個(gè)值通過該參數(shù)返回給調(diào)用者。因此調(diào)用者必須傳遞一個(gè)用戶變量給存儲(chǔ)過程，用來記錄存儲(chǔ)過程OUT參數(shù)的值。這個(gè)用戶變量在傳遞給存儲(chǔ)過程之前，可以是一個(gè)已賦值的變量，但在傳遞給存儲(chǔ)過程時(shí)，將自動(dòng)初始化為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)用者和存儲(chǔ)過程之間傳遞的內(nèi)容可以互相賦值。INOUT有兩個(gè)過程，一個(gè)是IN的過程，這個(gè)過程是將調(diào)用者指定的值傳遞給存儲(chǔ)過程，另一個(gè)是OUT的過程，這個(gè)過程是存儲(chǔ)過程將某個(gè)返回值返回給調(diào)用者。因此，調(diào)用者傳遞INOUT參數(shù)時(shí)，也必須傳遞一個(gè)用戶變量。但與OUT不同的是，INOUT的用戶變量有初始值，這個(gè)初始值會(huì)直接應(yīng)用在存儲(chǔ)過程中。而OUT的用戶變量雖然也有初始值，但這個(gè)初始值會(huì)自動(dòng)轉(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這個(gè)INOUT參數(shù)來判斷大小，因?yàn)樗^承了傳遞時(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.修改和刪除存儲(chǔ)過程、函數(shù)

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

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

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

drop {procedure|function} [if exists] sp_name

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

查看存儲(chǔ)過程和函數(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中查看存儲(chǔ)過程和函數(shù)的信息，這個(gè)比show status更詳細(xì)一點(diǎn)。

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

這里說明下show code功能，這個(gè)功能必須要在支持debug的MariaDB上才能使用，可以在編譯時(shí)使用選項(xiàng)"--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開始的序列值，表示存儲(chǔ)過程中所執(zhí)行的動(dòng)作先后順序。第二列是mariadb要執(zhí)行的動(dòng)作，這些動(dòng)作是基于存儲(chǔ)過程中的源語句進(jìn)行設(shè)置的。


網(wǎng)頁名稱：MariaDB/MySQL存儲(chǔ)過程和函數(shù)
分享鏈接：http://www.5511xx.com/article/dhdoogd.html