MySql存儲過程與函數(shù)詳解

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

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

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

一、存儲過程

1.1、基本語法

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

　　[characteristic ...] routine_body

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

Proc_parameter:存儲過程的參數(shù)列表

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

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

//創(chuàng)建儲存過程.cmd 中運行 
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

因為;分會沖突，所以要加delimiter //。將//設(shè)置為結(jié)束運行符號

如下：

調(diào)用：

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

結(jié)果

（2）、帶OUT的存儲過程

//帶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)計帶林開頭的人數(shù)

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

（3）、帶INOUT的存儲過程

//帶INOUT的存儲過程 
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、存儲過程體

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

1、局部變量

在存儲過程體中可以聲明局部變量，用來存儲存儲過程體中臨時結(jié)果。

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

如：

Declare cid int(10);

使用說明：

局部變量只能在存儲過程體的begin…end語句塊中聲明。
局部變量必須在存儲過程體的開頭處聲明。
局部變量的作用范圍僅限于聲明它的begin..end語句塊，其他語句塊中的語句不可以使用它。
局部變量不同于用戶變量，兩者區(qū)別：局部變量聲明時，在其前面沒有使用@符號，并且它只能在begin..end語句塊中使用；而用戶變量在聲明時，會在其名稱前面使用@符號，同時已聲明的用戶變量存在于整個會話之中。

2、set語句

使用set語句為局部變量賦值

Set var_name=expr
Set cid=910;

3、select … into 語句

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

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

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

4、定義處理程序

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

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

5、流程控制語句

（1）條件判斷語句

If語句

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

Search_condition參數(shù):條件判斷語句
Statement_list參數(shù):不同條件的執(zhí)行語句
多重IF的存儲過程實例
數(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ù)如下：

成績表（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;

然后寫一個存儲過程：返回各個分?jǐn)?shù)等級的人

//帶多重IF的存儲過程 
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)用過程：

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

Case 語句
表達(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)語句

While語句、repeat語句和loop語句。
While語句

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

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

使用范例

//帶while的存儲過程 
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é)果：計算5！

Repeat語句語法格式

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

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

//帶repeat的存儲過程 
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)用存儲過程

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

1.6、修改存儲過程

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

Alter procedure proc_name[characteristic…]

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

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

Drop procedure [if exists] sp_name;

二、函數(shù)

2.1、定義

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

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

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

刪除存儲函數(shù)drop
修改存儲函數(shù)alter 修改存儲函數(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、存儲過程和函數(shù)區(qū)別

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

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

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

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

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

3.1 定義

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

1 聲明光標(biāo)

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

2 打開光標(biāo)

Open cursor_name 
Open cur_employee;

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

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

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

Close cursor_name; 
Close cur_employee;

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

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`) 
)

然后寫光標(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; 
 //打開光標(biāo) 
 OPEN mycur; 
 //開始循環(huán) 
 REPEAT 
 FETCH mycur INTO ID,type,order1;//取出光標(biāo)的內(nèi)容到臨時變量 
 IF NOT done THEN 
 INSERT INTO test_cur2 VALUES (ID,type,order1);//插入到另一張表 
 END IF; 
 UNTIL done END REPEAT;//當(dāng)done=1時結(jié)束循環(huán) 
 //關(guān)閉光標(biāo) 
 CLOSE mycur; 
END

運行：