MySql存儲(chǔ)過(guò)程與函數(shù)詳解

更新時(shí)間：2020年04月17日 11:30:20 作者：林炳文

這篇文章主要為大家詳細(xì)介紹了MySql存儲(chǔ)過(guò)程與函數(shù)，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

存儲(chǔ)過(guò)程和函數(shù)是在數(shù)據(jù)庫(kù)中定義一些SQL語(yǔ)句的集合，然后直接調(diào)用這些存儲(chǔ)過(guò)程和函數(shù)來(lái)執(zhí)行已經(jīng)定義好的SQL語(yǔ)句。存儲(chǔ)過(guò)程和函數(shù)可以避免開(kāi)發(fā)人員重復(fù)的編寫(xiě)相同的SQL語(yǔ)句。而且，存儲(chǔ)過(guò)程和函數(shù)是在MySQL服務(wù)器中存儲(chǔ)和執(zhí)行的，可以減少客戶(hù)端和服務(wù)器端的數(shù)據(jù)傳輸。

一、存儲(chǔ)過(guò)程

1.1、基本語(yǔ)法

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

　　[characteristic ...] routine_body

Sp_name:存儲(chǔ)過(guò)程的名稱(chēng)，默認(rèn)在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建。這個(gè)名稱(chēng)應(yīng)當(dāng)盡量避免與MySQL的內(nèi)置函數(shù)相同的名稱(chēng)

Proc_parameter:存儲(chǔ)過(guò)程的參數(shù)列表

格式[IN|OUT|INOUT]param_name type
Param_name為參數(shù)名，type為參數(shù)的數(shù)據(jù)類(lèi)型。多個(gè)參數(shù)彼此間用逗號(hào)分隔。輸入?yún)?shù)、輸出參數(shù)和輸入/輸出參數(shù)，分別用in/out/inout標(biāo)識(shí)。參數(shù)的取名不要與數(shù) 據(jù)表的列名相同。
Characteristic:存儲(chǔ)過(guò)程的某些特征設(shè)定，分別介紹

        1 COMMENT'string':用于對(duì)存儲(chǔ)過(guò)程的描述，其中string為描述內(nèi)容,comment為關(guān)鍵字。
        2 LANGUAGE SQL:指明編寫(xiě)這個(gè)存儲(chǔ)過(guò)程的語(yǔ)言為SQL語(yǔ)言。這個(gè)選項(xiàng)可以不指定。
        3 DETERMINISTIC:表示存儲(chǔ)過(guò)程對(duì)同樣的輸入?yún)?shù)產(chǎn)生相同的結(jié)果;NOT DETERMINISTIC，則表示會(huì)產(chǎn)生不確定的結(jié)果（默認(rèn)）。
        4 contains sql | no sql | reads sql data | modifies sql data Contains sql表示存儲(chǔ)過(guò)程包含讀或?qū)憯?shù)據(jù)的語(yǔ)句（默認(rèn)）
        No sql表示不包含sql語(yǔ)句
        Reads sql data表示存儲(chǔ)過(guò)程只包含讀數(shù)據(jù)的語(yǔ)句
        Modifies sql data 表示存儲(chǔ)過(guò)程只包含寫(xiě)數(shù)據(jù)的語(yǔ)句
        5 sql security:這個(gè)特征用來(lái)指定存儲(chǔ)過(guò)程使用創(chuàng)建該存儲(chǔ)過(guò)程的用戶(hù)(definer)的許可來(lái)執(zhí)行，還是使用調(diào)用者(invoker)的許可來(lái)執(zhí)行。默認(rèn)是definer
    Routine_body:存儲(chǔ)過(guò)程的主體部分，包含了在過(guò)程調(diào)用的時(shí)候必須執(zhí)行的sql語(yǔ)句。以begin開(kāi)始，以end結(jié)束。如果存儲(chǔ)過(guò)程體中只有一條sql語(yǔ)句,可以省略begin-end標(biāo)志。

1.2、數(shù)據(jù)準(zhǔn)備

CREATE TABLE 
 t_user 
 ( 
 USER_ID INT NOT NULL AUTO_INCREMENT, 
 USER_NAME CHAR(30) NOT NULL, 
 USER_PASSWORD CHAR(10) NOT NULL, 
 USER_EMAIL CHAR(30) NOT NULL, 
 PRIMARY KEY (USER_ID), 
 INDEX IDX_NAME (USER_NAME) 
 ) 
 ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后這是插入的一些數(shù)據(jù)：

1.3 IN、OUT、INOUT參數(shù)

（1）、帶IN的存儲(chǔ)過(guò)程

//創(chuàng)建儲(chǔ)存過(guò)程.cmd 中運(yùn)行 
CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) 
BEGIN 
IF p_name is null or p_name='' THEN 
SELECT * FROM t_user; 
ELSE 
SELECT * FROM t_user WHERE USER_NAME LIKE p_name; 
END IF; 
END

因?yàn)?分會(huì)沖突，所以要加delimiter //。將//設(shè)置為結(jié)束運(yùn)行符號(hào)

如下：

調(diào)用：

//調(diào)用并輸出結(jié)果 
CALL SP_SEARCH('林炳文')

結(jié)果

（2）、帶OUT的存儲(chǔ)過(guò)程

//帶OUT返回的 
CREATE PROCEDURE SP_SEARCH2(IN p_name CHAR(20),OUT p_int INT) 
BEGIN 
IF p_name is null or p_name='' THEN 
SELECT * FROM t_user; 
ELSE 
SELECT * FROM t_user WHERE USER_NAME LIKE p_name; 
END IF; 
SELECT FOUND_ROWS() INTO p_int; 
END

調(diào)用輸出：統(tǒng)計(jì)帶林開(kāi)頭的人數(shù)

//調(diào)用并輸出結(jié)果 
CALL SP_SEARCH2('林%',@p_num); 
SELECT @p_num;

（3）、帶INOUT的存儲(chǔ)過(guò)程

//帶INOUT的存儲(chǔ)過(guò)程 
CREATE PROCEDURE sp_inout(INOUT p_num INT) 
BEGIN 
SET p_num=p_num*10; 
END 
//調(diào)用并輸出結(jié)果 
SET @p_num=2; 
call sp_inout(@p_num); 
SELECT @p_num;

輸出結(jié)果：

1.4、存儲(chǔ)過(guò)程體

存儲(chǔ)過(guò)程體中可以使用各種sql語(yǔ)句和過(guò)程式語(yǔ)句的組合，來(lái)封裝數(shù)據(jù)庫(kù)應(yīng)用中復(fù)雜的業(yè)務(wù)邏輯和處理規(guī)則，以實(shí)現(xiàn)數(shù)據(jù)庫(kù)應(yīng)用的靈活編程。下面主要介紹幾個(gè)用于構(gòu)造存儲(chǔ)過(guò)程體的常用語(yǔ)法元素。

1、局部變量

在存儲(chǔ)過(guò)程體中可以聲明局部變量，用來(lái)存儲(chǔ)存儲(chǔ)過(guò)程體中臨時(shí)結(jié)果。

DECLARE var_name[,…] type [DEFAULT value] 
Var_name:指定局部變量的名稱(chēng) 
Type:用于聲明局部變量的數(shù)據(jù)類(lèi)型 
default子句:用于為局部變量指定一個(gè)默認(rèn)值。若沒(méi)有指定，默認(rèn)為null.

如：

Declare cid int(10);

使用說(shuō)明：

局部變量只能在存儲(chǔ)過(guò)程體的begin…end語(yǔ)句塊中聲明。
局部變量必須在存儲(chǔ)過(guò)程體的開(kāi)頭處聲明。
局部變量的作用范圍僅限于聲明它的begin..end語(yǔ)句塊，其他語(yǔ)句塊中的語(yǔ)句不可以使用它。
局部變量不同于用戶(hù)變量，兩者區(qū)別：局部變量聲明時(shí)，在其前面沒(méi)有使用@符號(hào)，并且它只能在begin..end語(yǔ)句塊中使用；而用戶(hù)變量在聲明時(shí)，會(huì)在其名稱(chēng)前面使用@符號(hào)，同時(shí)已聲明的用戶(hù)變量存在于整個(gè)會(huì)話(huà)之中。

2、set語(yǔ)句

使用set語(yǔ)句為局部變量賦值

Set var_name=expr
Set cid=910;

3、select … into 語(yǔ)句

把選定列的值直接存儲(chǔ)到局部變量中，語(yǔ)法格式

Select col_name[,…] into var_name[,…] table_expr 
Col_name:用于指定列名 
Var_name:用于指定要賦值的變量名 
Table_expr:表示select語(yǔ)句中的from字句及后面的語(yǔ)法部分

說(shuō)明:存儲(chǔ)過(guò)程體中的select…into語(yǔ)句返回的結(jié)果集只能有一行數(shù)據(jù)。

4、定義處理程序

是事先定義程序執(zhí)行過(guò)程中可能遇到的問(wèn)題。并且可以在處理程序中定義解決這些問(wèn)題的辦法。這種方式可以提前預(yù)測(cè)可能出現(xiàn)的問(wèn)題，并提出解決方法。

DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement 
handler_type:CONTINUE | EXIT | UNDO 
Condition_value:Sqlwarning | not found | sqlexception

5、流程控制語(yǔ)句

（1）條件判斷語(yǔ)句

If語(yǔ)句

If search_condition then statement_list 
[elseif search_condition then statement_list]… 
[else statement_list] 
End if

Search_condition參數(shù):條件判斷語(yǔ)句
Statement_list參數(shù):不同條件的執(zhí)行語(yǔ)句
多重IF的存儲(chǔ)過(guò)程實(shí)例
數(shù)據(jù)準(zhǔn)備

學(xué)生表：

CREATE TABLE 
 t_student 
 ( 
 STU_ID INT NOT NULL, 
 STU_NAME CHAR(10) NOT NULL, 
 STU_CLASS INT NOT NULL, 
 STU_SEX CHAR(2) NOT NULL, 
 STU_AGE INT NOT NULL, 
 PRIMARY KEY (STU_ID) 
 ) 
 ENGINE=InnoDB DEFAULT CHARSET=utf8;

數(shù)據(jù)如下：

成績(jī)表（STU_ID是學(xué)生表是外鍵關(guān)系）：

CREATE TABLE 
 t_grade 
 ( 
 STU_ID INT NOT NULL, 
 STU_SCORE INT NOT NULL, 
 FOREIGN KEY (STU_ID) REFERENCES t_student (STU_ID), 
 INDEX STU_ID (STU_ID) 
 ) 
 ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后寫(xiě)一個(gè)存儲(chǔ)過(guò)程：返回各個(gè)分?jǐn)?shù)等級(jí)的人

//帶多重IF的存儲(chǔ)過(guò)程 
CREATE PROCEDURE SP_SCHOLARSHIP_LEVEL(IN p_level char(1)) 
BEGIN 
IF p_level ='A' THEN 
SELECT * FROM t_grade WHERE STU_SCORE >=90; 
ELSEIF p_level ='B' THEN 
SELECT * FROM t_grade WHERE STU_SCORE <90 AND STU_SCORE>=80; 
ELSEIF p_level ='C' THEN 
SELECT * FROM t_grade WHERE STU_SCORE <80 AND STU_SCORE>=70; 
ELSEIF p_level ='D' THEN 
SELECT * FROM t_grade WHERE STU_SCORE <60; 
ELSE 
SELECT * FROM t_grade; 
END IF; 
END

調(diào)用過(guò)程：

//調(diào)用并輸出結(jié)果 
CALL SP_SCHOLARSHIP_LEVEL('A');

Case 語(yǔ)句
表達(dá)形式1

Case case_value 
When when_value then statement_list 
[When when_value then statement_list]… 
[else statement_list] 
End case

表達(dá)形式2

Case 
When search_condition then statement_list 
End case

使用范例

CREATE PROCEDURE SP_SCHOLARSHIP_LEVEL3(IN p_level char(1)) 
BEGIN 
DECLARE p_num int DEFAULT 0; 
CASE p_level 
WHEN 'A' THEN 
SET p_num=90; 
WHEN 'B' THEN 
SET p_num=80; 
WHEN 'C' THEN 
SET p_num=70; 
WHEN 'D' THEN 
SET p_num=60; 
ELSE 
SET p_num=0; 
END CASE; 
SELECT * FROM t_grade g, t_student s WHERE g.STU_ID=s.STU_ID AND g.STU_SCORE >= p_num ; 
END

調(diào)用：

//調(diào)用并輸出結(jié)果 
CALL SP_SCHOLARSHIP_LEVEL3('d');

(2)循環(huán)語(yǔ)句

While語(yǔ)句、repeat語(yǔ)句和loop語(yǔ)句。
While語(yǔ)句

[begin_label:] 
while search_condition do 
Statement_list 
End while 
[end_label]

判斷條件search_condition是否為真,若為真,則執(zhí)行statement_list中的語(yǔ)句，然后再進(jìn)行判斷，如若仍然為真則繼續(xù)循環(huán)，直至條件判斷不為真時(shí)循環(huán)結(jié)束。

使用范例

//帶while的存儲(chǔ)過(guò)程 
CREATE PROCEDURE sp_cal(IN p_num INT,OUT p_result INT) 
BEGIN 
 SET p_result=1; 
 WHILE p_num > 1 DO 
 SET p_result = p_num * p_result; 
 SET p_num = p_num-1; 
 END WHILE; 
END 
//調(diào)用并輸出結(jié)果 
CALL sp_cal(5,@result); 
SELECT @result;

輸出結(jié)果：計(jì)算5！

Repeat語(yǔ)句語(yǔ)法格式

[begin_label:] 
repeat 
Statement_list 
Until search_condition 
End repeat 
[end_label]

Repeat語(yǔ)句首先執(zhí)行statement_list中的語(yǔ)句，然后判斷條件search_condition是否為真，倘若為真，則結(jié)束循環(huán)，若不為真，繼續(xù)循環(huán)。
Repeat先執(zhí)行后判斷，while先判斷后執(zhí)行。
使用范例：

//帶repeat的存儲(chǔ)過(guò)程 
CREATE PROCEDURE sp_cal2(IN p_num INT,OUT p_result INT) 
BEGIN 
 SET p_result=1; 
 REPEAT 
 SET p_result = p_num * p_result; 
 SET p_num = p_num-1; 
 UNTIL p_num<=1 
 END REPEAT; 
END 
//調(diào)用并輸出結(jié)果 
CALL sp_cal2(5,@result); 
SELECT @result;

1.5、調(diào)用存儲(chǔ)過(guò)程

Call sp_name([parameter[,…]]); 
Sp_name被調(diào)用存儲(chǔ)過(guò)程的名稱(chēng) 
Parameter:指定調(diào)用存儲(chǔ)過(guò)程所要使用的參數(shù)。

1.6、修改存儲(chǔ)過(guò)程

復(fù)制代碼代碼如下:

Alter procedure proc_name[characteristic…]

只能修改存儲(chǔ)過(guò)程的特征，如果要修改存儲(chǔ)過(guò)程的內(nèi)容，可以先刪除該存儲(chǔ)過(guò)程，然后再重新創(chuàng)建
1.7、刪除存儲(chǔ)過(guò)程

復(fù)制代碼代碼如下:

Drop procedure [if exists] sp_name;

二、函數(shù)

2.1、定義

MySQL中，創(chuàng)建存儲(chǔ)函數(shù)的基本形式如下：
CREATE FUNCTION sp_name([func_parameter[,...]]) 
RETURNS type 
[characteristic ...] routine_body 
Return

子句用于聲明存儲(chǔ)函數(shù)返回值的數(shù)據(jù)類(lèi)型。存儲(chǔ)過(guò)程是用戶(hù)定義的一系列sql語(yǔ)句的集合，涉及特定表或其它對(duì)象的任務(wù)，用戶(hù)可以調(diào)用存儲(chǔ)過(guò)程，而函數(shù)通常是數(shù)據(jù)庫(kù)已定義的方法，它接收參數(shù)并返回某種類(lèi)型的值并且不涉及特定用戶(hù)表。
調(diào)用存儲(chǔ)函數(shù)

Select sp_name([func_parameter…]) 
Select fn_search(2);

刪除存儲(chǔ)函數(shù)drop
修改存儲(chǔ)函數(shù)alter 修改存儲(chǔ)函數(shù)的某些相關(guān)特征。

2.2、函數(shù)使用例子

（比較大小，返回大的數(shù)）

/**函數(shù)使用**/ 
CREATE FUNCTION sp_cal_max(p_num1 INT,p_num2 INT) 
RETURNS INT 
BEGIN 
IF p_num1 >= p_num2 THEN 
RETURN p_num1; 
ELSE 
RETURN p_num2; 
END IF; 
END

調(diào)用：

SET @p_num1=2; 
SET @p_num2=34; 
SELECT sp_cal_max(@p_num1,@p_num2);

2.3、存儲(chǔ)過(guò)程和函數(shù)區(qū)別

1）一般來(lái)說(shuō)，存儲(chǔ)過(guò)程實(shí)現(xiàn)的功能要復(fù)雜一點(diǎn)，而函數(shù)的實(shí)現(xiàn)的功能針對(duì)性比較強(qiáng)。存儲(chǔ)過(guò)程，功能強(qiáng)大，可以執(zhí)行包括修改表等一系列數(shù)據(jù)庫(kù)操作；用戶(hù)定義函數(shù)不能用于執(zhí)行一組修改全局?jǐn)?shù)據(jù)庫(kù)狀態(tài)的操作。

2）對(duì)于存儲(chǔ)過(guò)程來(lái)說(shuō)可以返回參數(shù)，如記錄集，而函數(shù)只能返回值或者表對(duì)象。函數(shù)只能返回一個(gè)變量；而存儲(chǔ)過(guò)程可以返回多個(gè)。存儲(chǔ)過(guò)程的參數(shù)可以有IN,OUT,INOUT三種類(lèi)型，而函數(shù)只能有IN類(lèi)~~存儲(chǔ)過(guò)程聲明時(shí)不需要返回類(lèi)型，而函數(shù)聲明時(shí)需要描述返回類(lèi)型，且函數(shù)體中必須包含一個(gè)有效的RETURN語(yǔ)句。

3）存儲(chǔ)過(guò)程，可以使用非確定函數(shù)，不允許在用戶(hù)定義函數(shù)主體中內(nèi)置非確定函數(shù)。

4）存儲(chǔ)過(guò)程一般是作為一個(gè)獨(dú)立的部分來(lái)執(zhí)行（ EXECUTE 語(yǔ)句執(zhí)行），而函數(shù)可以作為查詢(xún)語(yǔ)句的一個(gè)部分來(lái)調(diào)用（SELECT調(diào)用），由于函數(shù)可以返回一個(gè)表對(duì)象，因此它可以在查詢(xún)語(yǔ)句中位于FROM關(guān)鍵字的后面。 SQL語(yǔ)句中不可用存儲(chǔ)過(guò)程，而可以使用函數(shù)。

三、光標(biāo)（游標(biāo)）

3.1 定義

查詢(xún)語(yǔ)句可能查詢(xún)出多條記錄，在存儲(chǔ)過(guò)程和函數(shù)中使用光標(biāo)標(biāo)來(lái)逐條讀取查詢(xún)結(jié)果集中的記錄。光標(biāo)的使用包括聲明光標(biāo)、打開(kāi)光標(biāo)、使用光標(biāo)和關(guān)閉光標(biāo)。光標(biāo)必須聲明光標(biāo)、打開(kāi)光標(biāo)、使用光標(biāo)和關(guān)閉光標(biāo)。光標(biāo)必須聲明在處理程序之前，并且聲明在變量和條件之后。

1 聲明光標(biāo)

Declare cursor_name cursor forselect_statement; 
Cursor_name:光標(biāo)名稱(chēng) 
Select_statement:select語(yǔ)句的內(nèi)容 
Declare cur_employee cursor forselect name,age from employee;

2 打開(kāi)光標(biāo)

Open cursor_name 
Open cur_employee;

3 使用光標(biāo)
Mysql中使用fetch關(guān)鍵字來(lái)使用光標(biāo)，語(yǔ)法形式

Fetch cur_name intovar_name[,var_name…]; 
Cur_name表示光標(biāo)的名稱(chēng) 
Var_name表示將光標(biāo)中的select語(yǔ)句查詢(xún)出來(lái)的信息存入該參數(shù)。Var_name必須在聲明光標(biāo)前就定義好。 
Fetch cur_employee intoemp_name,emp_age;

4 關(guān)閉光標(biāo)

Close cursor_name; 
Close cur_employee;

每個(gè)光標(biāo)不再需要時(shí)都應(yīng)該被關(guān)閉，使用close語(yǔ)句將會(huì)釋放光標(biāo)所使用的全部資源。在一個(gè)光標(biāo)被關(guān)閉后，如果沒(méi)有重新被打開(kāi)，則不能被使用。對(duì)于聲明過(guò)的光標(biāo)，則不需要再次聲明，可直接使用open語(yǔ)句打開(kāi)。

3.2、使用范例

（將表test_cur1數(shù)據(jù)復(fù)制到test_cur2）

CREATE TABLE `test_cur1` ( 
 `id` int(11) NOT NULL auto_increment, 
 `type` char(11) default NULL, 
 `order1` char(11) default NULL, 
 PRIMARY KEY (`id`) 
) 
INSERT INTO `test_cur1` VALUES (1, '145', 'd1'); 
INSERT INTO `test_cur1` VALUES (2, '134', '1d'); 
INSERT INTO `test_cur1` VALUES (3, '123', '1ad'); 
INSERT INTO `test_cur1` VALUES (4, '121', '1as'); 
 
CREATE TABLE `test_cur2` ( 
 `id` int(11) NOT NULL auto_increment, 
 `type` char(11) default NULL, 
 `order1` char(11) default NULL, 
 PRIMARY KEY (`id`) 
)

然后寫(xiě)光標(biāo)了：

create procedure get_cur () 
BEGIN 
 DECLARE done INT DEFAULT 0; 
 DECLARE ID int(11); 
 DECLARE type char(11); 
 DECLARE order1 char(11); 
 DECLARE mycur CURSOR FOR SELECT * FROM test_cur1;//定義光標(biāo) 
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
 //打開(kāi)光標(biāo) 
 OPEN mycur; 
 //開(kāi)始循環(huán) 
 REPEAT 
 FETCH mycur INTO ID,type,order1;//取出光標(biāo)的內(nèi)容到臨時(shí)變量 
 IF NOT done THEN 
 INSERT INTO test_cur2 VALUES (ID,type,order1);//插入到另一張表 
 END IF; 
 UNTIL done END REPEAT;//當(dāng)done=1時(shí)結(jié)束循環(huán) 
 //關(guān)閉光標(biāo) 
 CLOSE mycur; 
END

運(yùn)行：