C-15.存儲(chǔ)過程和函數(shù)

MySQL從5.0版本開始支持存儲(chǔ)過程和函數(shù)。存儲(chǔ)過程和函數(shù)能夠?qū)?fù)雜的SQL邏輯封裝在一起，應(yīng)用程序無須關(guān)注存儲(chǔ)過程和函數(shù)內(nèi)部復(fù)雜的SQL邏輯，而只需要簡單地調(diào)用存儲(chǔ)過程和函數(shù)即可。

1.存儲(chǔ)過程概述

1.1 理解

含義:存儲(chǔ)過程的英文是Stored Procedure。它的思想很簡單，就是一組經(jīng)過預(yù)先編譯的SQL語句的封裝。

執(zhí)行過程：存儲(chǔ)過程預(yù)先存儲(chǔ)在MySQL服務(wù)器上，需要執(zhí)行的時(shí)候，客戶端只需要向服務(wù)器端發(fā)出調(diào)用存儲(chǔ)過程的命令，服務(wù)器端就可以把預(yù)先存儲(chǔ)好的這一系列SQL語句全部執(zhí)行。

好處：

1.簡化操作，提高了sql語句的重用性，減少了開發(fā)程序員的壓里。

2.減少操作過程中的失誤，提高效率。

3.減少網(wǎng)絡(luò)傳輸量(客戶端不需要把所有的SQL語句通過網(wǎng)絡(luò)發(fā)給服務(wù)器)。

4.減少了SQL語句暴露在網(wǎng)上的風(fēng)險(xiǎn)，也提高了數(shù)據(jù)查詢的安全性。

和視圖，函數(shù)的對比：

它和視圖有著同樣的優(yōu)點(diǎn)，清晰，安全，還可以減少網(wǎng)絡(luò)傳輸量。不過它和視圖不同，視圖是虛擬表，通常不對底層數(shù)據(jù)表直接操作，而存儲(chǔ)過程是程序化的SQL，可以直接操作底層數(shù)據(jù)表，相比于面向集合的操作方式，能夠?qū)崿F(xiàn)一些更復(fù)雜的數(shù)據(jù)處理。

一旦存儲(chǔ)過程被創(chuàng)建出來，使用它就像使用函數(shù)一樣簡單，我們直接通過調(diào)用存儲(chǔ)過程名即可。相較于函數(shù)，存儲(chǔ)過程是沒有返回值的。

1.2 分類

存儲(chǔ)過程的參數(shù)類型可以是IN、OUT和INOUT。根據(jù)這點(diǎn)分類如下：

• 1、沒有參數(shù)（無參數(shù)無返回）

• 2、僅僅帶 IN 類型（有參數(shù)無返回）

• 3、僅僅帶 OUT 類型（無參數(shù)有返回）

• 4、既帶 IN 又帶 OUT（有參數(shù)有返回）

• 5、帶 INOUT（有參數(shù)有返回）

注意：IN、OUT、INOUT 都可以在一個(gè)存儲(chǔ)過程中帶多個(gè)。

2.創(chuàng)建存儲(chǔ)過程

2.1 語法分析

語法

DELIMITER //

CREATE PROCEDURE 存儲(chǔ)過程名(IN|OUT|INOUT 參數(shù)名 參數(shù)類型,...)

[characteristics ...]
BEGIN 
	存儲(chǔ)過程體
END //

DELIMITER ;

類型Java中的方法

// 修飾符 返回值 方法名(參數(shù)類型 參數(shù)名,...) 
public static void main(String[] args){
    //方法體;
}

說明:

1、參數(shù)前面的符號(hào)的意思

• IN：當(dāng)前參數(shù)為輸入?yún)?shù)，也就是表示入?yún)?。存?chǔ)過程只是讀取這個(gè)參數(shù)的值。如果沒有定義參數(shù)種類，默認(rèn)就是IN，表示輸入?yún)?shù)。
• OUT：當(dāng)前參數(shù)為輸出參數(shù)，也就是表示出參。執(zhí)行完成之后，調(diào)用這個(gè)存儲(chǔ)過程的客戶端或者應(yīng)用程序可以讀取這個(gè)參數(shù)的返回值。
• INOUT：當(dāng)前參數(shù)既可以是輸入?yún)?shù)，也可以是輸出參數(shù)。

2、形參類型可以是MySQL數(shù)據(jù)庫中的任意類型

2.1.1 存儲(chǔ)過程和函數(shù)約束類型

3、characteristrics表示創(chuàng)建存儲(chǔ)過程時(shí)指定的對存儲(chǔ)過程的約束條件，其取值信息如下：

LANGUAGE SQL
# DETERMINISTIC a.確定性的
| [NOT] DETERMINISTIC
# CONTAINS 包含 MODIFIES 修改
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
# DEFINER 定義者 INVOKER 調(diào)用者
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT '注釋'

• LANGUAGE SQL ：說明存儲(chǔ)過程體是由SQL語句組成的，當(dāng)前系統(tǒng)支持的語言為SQL。

• [NOT] DETERMINISTIC：指明存儲(chǔ)過程執(zhí)行的結(jié)果是否確定。DETERMINISTIC表示結(jié)果就是確定的。每次執(zhí)行存儲(chǔ)過程時(shí)，相同的輸入會(huì)得到相同的輸出。NOT DETERMINISTIC 表示結(jié)果時(shí)不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個(gè)值，默認(rèn)為NOT DETERMINISTIC。

• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL語句的限制。

  • CONTAINS SQL表示當(dāng)前存儲(chǔ)過程的子程序包含SQL語句，但不包含讀寫數(shù)據(jù)的SQL語句；

  • NO SQL表示當(dāng)前存儲(chǔ)過程的子查詢中不包含任何SQL語句；

  • READS SQL DATA表示當(dāng)前存儲(chǔ)過程的子查詢中包含讀數(shù)據(jù)的SQL語句;

  • MODIFIES SQL DATA表示當(dāng)前存儲(chǔ)過程的子查詢中包含寫數(shù)據(jù)的SQL語句；

  • 默認(rèn)情況下，系統(tǒng)會(huì)制定為CONTAINS SQL。

• SQL SECURITY { DEFINER | INVOKER }：調(diào)用當(dāng)前存儲(chǔ)過程的權(quán)限，即指明那些用戶能夠執(zhí)行當(dāng)前存儲(chǔ)過程。

  • DEFINER表示只有當(dāng)前存儲(chǔ)過程的創(chuàng)建者或定義者才能執(zhí)行當(dāng)前存儲(chǔ)過程。

  • INVOKER表示擁有當(dāng)前存儲(chǔ)過程的訪問權(quán)限的用戶能夠執(zhí)行當(dāng)前存儲(chǔ)工廠。

  • 默認(rèn)情況下，MySQL默認(rèn)指定值為DEFINER。

• COMMENT '注釋'：注釋信息，可以用來描述存儲(chǔ)過程。

4、存儲(chǔ)過程體中可以由多條SQL語句，如果僅僅一條SQL語句，則可以省略BEGIN 和END。

編寫存儲(chǔ)過程并不是一件簡單的事情，可能存儲(chǔ)過程中需要復(fù)雜的 SQL 語句。

1. BEGIN…END：BEGIN…END 中間包含了多個(gè)語句，每個(gè)語句都以（;）號(hào)為結(jié)束符。
2. DECLARE：DECLARE 用來聲明變量，使用的位置在于 BEGIN…END 語句中間，而且需要在其他語句使用之前進(jìn)行變量的聲明。
3. SET：賦值語句，用于對變量進(jìn)行賦值。
4. SELECT… INTO：把從數(shù)據(jù)表中查詢的結(jié)果存放到變量中，也就是為變量賦值。

5、需要設(shè)置新的結(jié)束標(biāo)記

DELIMITER 新的結(jié)束標(biāo)記

因?yàn)镸ySQL默認(rèn)的語句結(jié)束符號(hào)為分號(hào)';'。為了避免與存儲(chǔ)過程中SQL語句結(jié)束符相沖突，需要使用DELIMITER改變存儲(chǔ)過程的結(jié)束符。

比如：“DELIMITER //”語句的作用是將MySQL的結(jié)束符設(shè)置為//，并以“END //”結(jié)束存儲(chǔ)過程。存儲(chǔ)過程定義完畢之后再使用“DELIMITER ;”恢復(fù)默認(rèn)結(jié)束符。DELIMITER也可以指定其他符號(hào)作為結(jié)束符。

注意：當(dāng)使用DELIMITER命令時(shí)，應(yīng)該避免使用反斜杠（‘\’）字符，因?yàn)榉葱本€是MySQL的轉(zhuǎn)義字符。

實(shí)例：

DELIMITER $

CREATE PROCEDURE 存儲(chǔ)過程名(IN|OUT|INOUT 參數(shù)名 參數(shù)類型,...)
[characteristics ...]
BEGIN

    sql語句1;
    sql語句2;

END $

DELIMITER ;

2.2 舉例

#無參類型
#舉例1：創(chuàng)建存儲(chǔ)過程select_all_data()，查看 emps 表的所有數(shù)據(jù)
DELIMITER $

CREATE PROCEDURE select_all_date()
BEGIN 
    SELECT * FROM emps;
END $

DELIMITER ;

-- 存儲(chǔ)過程的調(diào)用
CALL select_all_date();

#無參類型
#舉例2：創(chuàng)建存儲(chǔ)過程avg_employee_salary()，返回所有員工的平均工資
DELIMITER $

CREATE PROCEDURE avg_emps_salary()
BEGIN
    SELECT AVG(salary) FROM emps;
END $

DELIMITER ;

CALL avg_emps_salary();

#無參類型
#舉例3：創(chuàng)建存儲(chǔ)過程show_max_salary()，用來查看“emps”表的最高薪資值。
DELIMITER //

CREATE PROCEDURE show_max_salary()
BEGIN
    SELECT MAX(salary) FROM emps;
END //

DELIMITER ;

CALL show_max_salary();

#OUT類型
#舉例4：創(chuàng)建存儲(chǔ)過程show_min_salary()，查看“emps”表的最低薪資值。并將最低薪資通過OUT參數(shù)“ms”輸出
DELIMITER $

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN 
    SELECT MIN(salary) INTO ms
    FROM emps;
END $

DELIMITER ;

-- 有輸出參數(shù)的存儲(chǔ)過程調(diào)用
CALL show_min_salary(@ms);
SELECT @ms;

#IN類型
#舉例5：創(chuàng)建存儲(chǔ)過程show_someone_salary()，查看“emps”表的某個(gè)員工的薪資，并用IN參數(shù)empname輸入員工姓名。
DELIMITER $

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
    SELECT salary FROM emps WHERE last_name = empname;
END $

DELIMITER ;

-- 輸入?yún)?shù)的存儲(chǔ)過程調(diào)用
-- 方式1
CALL show_someone_salary('Abel');
-- 方式2
-- 這種方式，需要變量和使用變量的sql語句一起執(zhí)行
SET @empname = 'Abel';
CALL show_someone_salary(@empname);

# 有IN,有OUT類型
#舉例6：創(chuàng)建存儲(chǔ)過程show_someone_salary2()，查看“emps”表的某個(gè)員工的薪資，并用IN參數(shù)empname
#輸入員工姓名，用OUT參數(shù)empsalary輸出員工薪資。
DELIMITER $

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20), OUT empsalary DOUBLE)
BEGIN 
    SELECT salary INTO empsalary FROM emps WHERE last_name = empname;
END $

DELIMITER ;

-- 調(diào)用方式
CALL show_someone_salary2('Abel',@empsalary);
SELECT @empsalary;

#INOUT類型
#舉例7：創(chuàng)建存儲(chǔ)過程show_mgr_name()，查詢某個(gè)員工領(lǐng)導(dǎo)的姓名，并用INOUT參數(shù)“empname”輸入員工姓名，輸出領(lǐng)導(dǎo)的姓名。
DELIMITER $

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
BEGIN 
    SELECT m.last_name INTO empname 
    FROM emps w,emps m 
    WHERE w.manager_id = m.employee_id 
    AND w.last_name = empname;
END $

DELIMITER ;

-- inout類型參數(shù)的存儲(chǔ)過程的調(diào)用方式
SET @empname = 'Hunold';
CALL show_mgr_name(@empname);
SELECT @empname;

3.調(diào)用存儲(chǔ)過程

3.1 調(diào)用格式

存儲(chǔ)過程有多種調(diào)用方法。存儲(chǔ)過程必須使用CALL語句調(diào)用，并且存儲(chǔ)過程和數(shù)據(jù)庫相關(guān)，如果要執(zhí)行其他數(shù)據(jù)庫中的存儲(chǔ)過程，需要指定數(shù)據(jù)庫名稱，例如CALL dbname.procname。

CALL 存儲(chǔ)過程名(實(shí)參列表);

格式：

1.調(diào)用IN模式

call sq1('值');

2.調(diào)用OUT模式

SET @name;#聲明空變量可以省略，MySQL系統(tǒng)會(huì)自動(dòng)創(chuàng)建，并根據(jù)存儲(chǔ)過程結(jié)果(結(jié)果，不是返回值)賦值給變量
CALL sp1(@name);
SELECT @name;

3.調(diào)用INOUT模式

SET @name=值;
CALL sp1(@name);
SELECT @name;

3.2 舉例

INT類型：創(chuàng)建存儲(chǔ)過程，實(shí)現(xiàn)累加運(yùn)算，計(jì)算 1+2+…+n 等于多少。具體的代碼如下：

DELIMITER //

CREATE PROCEDURE `add_num`(IN n INT)

BEGIN
    DECLARE i INT;
    DECLARE sum INT;
    
    SET i = 1; 
    SET sum = 0;
    
    WHILE i <= n DO
        SET sum = sum + i;
        SET i = i +1;
    END WHILE;
    
    SELECT sum;
END //

DELIMITER ;

如果你用的是 Navicat 工具，那么在編寫存儲(chǔ)過程的時(shí)候，Navicat 會(huì)自動(dòng)設(shè)置 DELIMITER 為其他符號(hào)，我們不需要再進(jìn)行 DELIMITER 的操作。

直接使用 CALL add_num(50); 即可。這里我傳入的參數(shù)為 50，也就是統(tǒng)計(jì) 1+2+…+50 的積累之和。

其他類型見上 2.2 舉例。

3.3 如何調(diào)試

在 MySQL 中，存儲(chǔ)過程不像普通的編程語言（比如 VC++、Java 等）那樣有專門的集成開發(fā)環(huán)境。因此，你可以通過 SELECT 語句，把程序執(zhí)行的中間結(jié)果查詢出來，來調(diào)試一個(gè) SQL 語句的正確性。調(diào)試成功之后，把 SELECT 語句后移到下一個(gè) SQL 語句之后，再調(diào)試下一個(gè) SQL 語句。這樣 逐步推進(jìn) ，就可以完成對存儲(chǔ)過程中所有操作的調(diào)試了。當(dāng)然，你也可以把存儲(chǔ)過程中的 SQL 語句復(fù)制出來，逐段單獨(dú)調(diào)試。存儲(chǔ)過程動(dòng)輒成百上千行，不好調(diào)式。

4.存儲(chǔ)函數(shù)的使用

前面學(xué)習(xí)了很多函數(shù)，使用這些函數(shù)可以對數(shù)據(jù)進(jìn)行的各種處理操作，極大地提高用戶對數(shù)據(jù)庫的管理效率。MySQL支持自定義函數(shù)，定義好之后，調(diào)用方式與調(diào)用MySQL預(yù)定義的系統(tǒng)函數(shù)一樣。

4.1 語法分析

學(xué)過的函數(shù)：LENGTH、SUBSTR、CONCAT等

語法格式：

CREATE FUNCTION 函數(shù)名(參數(shù)名 參數(shù)類型,...)
RETURNS 返回值類型
[characteristics ...]
BEGIN 
	函數(shù)體 #函數(shù)體中一定有return語句
END

說明：

1、參數(shù)列表：指定參數(shù)為IN、OUT或INOUT只對PROCEDURE是合法的，F(xiàn)UNCTION中總是默認(rèn)為IN參數(shù)。

2、RETURNS type 語句表示函數(shù)返回?cái)?shù)據(jù)的類型；RETURNS子句只能對FUNCTION做指定，對函數(shù)而言這是強(qiáng)制的。它用來指定函數(shù)的返回類型，而且函數(shù)體必須包含一個(gè)RETURN value語句。

3、characteristic 創(chuàng)建函數(shù)時(shí)指定的對函數(shù)的約束。取值與創(chuàng)建存儲(chǔ)過程時(shí)相同，這里不再贅述。

4、函數(shù)體也可以用BEGIN…END來表示SQL代碼的開始和結(jié)束。如果函數(shù)體只有一條語句，也可以省略BEGIN…END。

4.2 調(diào)用存儲(chǔ)函數(shù)

在MySQL中，存儲(chǔ)函數(shù)的使用方法與MySQL內(nèi)部函數(shù)的使用方法是一樣的。換言之，用戶自己定義的存儲(chǔ)函數(shù)與MySQL內(nèi)部函數(shù)是一個(gè)性質(zhì)的。區(qū)別在于，存儲(chǔ)函數(shù)是用戶自己定義的，而內(nèi)部函數(shù)是MySQL的開發(fā)者定義的。

SELECT 函數(shù)名(實(shí)參列表)

4.3 舉例

-- 舉例1，創(chuàng)建存儲(chǔ)函數(shù)，名稱為email_by_name()，參數(shù)定義為空
-- 該函數(shù)查詢Abel的email，并返回，數(shù)據(jù)類型為字符串型
DELIMITER //

CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
    DETERMINISTIC
    CONTAINS SQL
    READS SQL DATA
BEGIN
    RETURN(SELECT email FROM emps WHERE last_name = 'Abel');
END //

DELIMITER ;
-- 調(diào)用
SELECT email_by_name();

-- 舉例2 創(chuàng)建存儲(chǔ)函數(shù)，名稱為email_by_id(),參數(shù)傳入emp_id，該函數(shù)
-- 查詢emp_id的email，并返回，數(shù)據(jù)類型為字符串類型
DELIMITER //

CREATE FUNCTION email_by_id(empId INT)
RETURNS VARCHAR(25)
    DETERMINISTIC
    CONTAINS SQL
    READS SQL DATA
BEGIN
    RETURN(SELECT email FROM emps WHERE employee_id = empId);
END //

DELIMITER ;

SELECT email_by_id(101);

SET @emp_id = 102;
SELECT email_by_id(@emp_id);

# 舉例3，創(chuàng)建存儲(chǔ)函數(shù)count_by_id()，參數(shù)傳入dept_id，該函數(shù)查詢dept_id部門的
# 員工人數(shù)，并返回，數(shù)據(jù)類型為整形
DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
    DETERMINISTIC
    CONTAINS SQL
    READS SQL DATA
BEGIN   
    RETURN(SELECT COUNT(*) FROM emps WHERE department_id = dept_id);
END //

DELIMITER ;

-- 常量調(diào)用存儲(chǔ)函數(shù)
SELECT count_by_id(30);

-- 變量調(diào)用存儲(chǔ)函數(shù)
SET @dept_id = 30;
SELECT count_by_id(@dept_id);

注意：

若在創(chuàng)建存儲(chǔ)函數(shù)中報(bào)錯(cuò)you might want to use the less safelog_bin_trust_function_creators variable，有兩種處理方法

• 方式1，加上必要的函數(shù)特性“[NOT] DETERMINISTIC”和“

• 方式2：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

方式2說明，log_bin_trust_function_creators = 0時(shí)，對于用戶在創(chuàng)建存儲(chǔ)函數(shù)時(shí)， MySQL系統(tǒng)，會(huì)進(jìn)行檢查，存儲(chǔ)函數(shù)的創(chuàng)建語句。= 1，后MySQL不會(huì)進(jìn)行檢查。

4.4 存儲(chǔ)函數(shù)和過程對比

此外，存儲(chǔ)函數(shù)可以放在查詢語句中使用，存儲(chǔ)過程不行。反之，存儲(chǔ)過程的功能更加強(qiáng)大，包括能夠執(zhí)行對表的操作（比如創(chuàng)建表，刪除表等）和事務(wù)操作，這些功能是存儲(chǔ)函數(shù)不具備的。

5.存儲(chǔ)過程和函數(shù)的查看，修改，刪除

5.1 查看

創(chuàng)建完之后，怎么知道我們創(chuàng)建的存儲(chǔ)過程、存儲(chǔ)函數(shù)是否成功了呢？

MySQL存儲(chǔ)了存儲(chǔ)過程和函數(shù)的狀態(tài)信息，用戶可以使用SHOW STATUS語句或SHOW CREATE語句來查看，也可直接從系統(tǒng)的information_schema數(shù)據(jù)庫中查詢。這里介紹3種方法。

1.使用SHOW CREATE語句查看存儲(chǔ)過程和函數(shù)的創(chuàng)建信息

語法：

SHOW CREATE {PROCEDURE | FUNCTION} 存儲(chǔ)過程名或函數(shù)名

舉例

#(1).使用show create 查看存儲(chǔ)過程或函數(shù)
SHOW CREATE PROCEDURE show_mgr_name;

SHOW CREATE FUNCTION count_by_id;

2.使用SHOW STATUS語句查看存儲(chǔ)過程和函數(shù)的狀態(tài)信息

語法

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

這個(gè)語句返回子程序的特征，如數(shù)據(jù)庫、名字、類型、創(chuàng)建者及創(chuàng)建和修改日期。

[LIKE 'pattern']：匹配存儲(chǔ)過程或函數(shù)的名稱，可以省略。當(dāng)省略不寫時(shí)，會(huì)列出MySQL數(shù)據(jù)庫中存在的所有存儲(chǔ)過程或函數(shù)的信息。 舉例：SHOW STATUS語句示例，代碼如下：

mysql> SHOW PROCEDURE STATUS LIKE 'show_min_salary'\G
*************************** 1. row ***************************
                  Db: test15
                Name: show_min_salary
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2024-02-21 17:02:17
             Created: 2024-02-21 17:02:17
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> SHOW FUNCTION STATUS LIKE 'count_by_id'\G
*************************** 1. row ***************************
                  Db: test15
                Name: count_by_id
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2024-02-20 20:40:00
             Created: 2024-02-20 20:40:00
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

3. 從information_schema.Routines表中查看存儲(chǔ)過程和函數(shù)的信息

MySQL中存儲(chǔ)過程和函數(shù)的信息存儲(chǔ)在information_schema數(shù)據(jù)庫下的Routines表中。可以通過查詢該表的記錄來查詢存儲(chǔ)過程和函數(shù)的信息。其基本語法形式如下：

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存儲(chǔ)過程或函數(shù)的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

說明：如果在MySQL數(shù)據(jù)庫中存在存儲(chǔ)過程和函數(shù)名稱相同的情況，最好指定ROUTINE_TYPE查詢條件來指明查詢的是存儲(chǔ)過程還是函數(shù)。

舉例

-- (3).從information_schema.Routines表中查看存儲(chǔ)過程和函數(shù)的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='count_by_id' AND ROUTINE_TYPE = 'FUNCTION';

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='show_mgr_name' AND ROUTINE_TYPE = 'PROCEDURE';

5.2 修改

修改存儲(chǔ)過程或函數(shù)，不影響存儲(chǔ)過程或函數(shù)功能，只是修改相關(guān)特性。使用ALTER語句實(shí)現(xiàn)。

ALTER {PROCEDURE | FUNCTION} 存儲(chǔ)過程或函數(shù)的名 [characteristic ...]

其中，characteristic指定存儲(chǔ)過程或函數(shù)的特性，其取值信息與創(chuàng)建存儲(chǔ)過程、函數(shù)時(shí)的取值信息略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• CONTAINS SQL ，表示子程序包含SQL語句，但不包含讀或?qū)憯?shù)據(jù)的語句。

• NO SQL ，表示子程序中不包含SQL語句。

• READS SQL DATA ，表示子程序中包含讀數(shù)據(jù)的語句。

• MODIFIES SQL DATA ，表示子程序中包含寫數(shù)據(jù)的語句。

• SQL SECURITY { DEFINER | INVOKER } ，指明誰有權(quán)限來執(zhí)行。

  • DEFINER ，表示只有定義者自己才能夠執(zhí)行。

  • INVOKER ，表示調(diào)用者可以執(zhí)行。

• COMMENT 'string' ，表示注釋信息。

修改存儲(chǔ)過程使用ALTER PROCEDURE語句，修改存儲(chǔ)函數(shù)使用ALTER FUNCTION語句。但是，這兩個(gè)語句的結(jié)構(gòu)是一樣的，語句中的所有參數(shù)也是一樣的。

注意，修改存儲(chǔ)過程或函數(shù)時(shí)，與創(chuàng)建存儲(chǔ)過程或函數(shù)時(shí)的約束少了，LANGUAGE SQL 和 [NOT] DETERMINISTIC約束。

舉例

#(4).使用alter修改存儲(chǔ)過程或函數(shù)的特性，不能修改存儲(chǔ)過程或存儲(chǔ)函數(shù)體
ALTER PROCEDURE show_max_salary
    SQL SECURITY INVOKER
    COMMENT '查看薪水最大值';
-- 修改存儲(chǔ)函數(shù)略
ALTER FUNCTION count_by_id
    SQL SECURITY INVOKER
    COMMENT '根據(jù)id數(shù)量';

5.3 刪除

刪除存儲(chǔ)過程和函數(shù)，可以使用DROP語句，其語法結(jié)構(gòu)如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存儲(chǔ)過程或函數(shù)的名

IF EXISTS：如果程序或函數(shù)不存儲(chǔ)，它可以防止發(fā)生錯(cuò)誤，產(chǎn)生一個(gè)用SHOW WARNINGS查看的警告。

舉例

#(5).刪除存儲(chǔ)函數(shù)或存儲(chǔ)過程
DROP PROCEDURE show_min_salary;

DROP FUNCTION email_by_name;

6. 關(guān)于存儲(chǔ)過程使用的爭議

盡管存儲(chǔ)過程有諸多優(yōu)點(diǎn)，但是對于存儲(chǔ)過程的使用，一直都存在著很多爭議，比如有些公司對于大型項(xiàng)目要求使用存儲(chǔ)過程，而有些公司在手冊中明確禁止使用存儲(chǔ)過程，為什么這些公司對存儲(chǔ)過程的使用需求差別這么大呢？

6.1 優(yōu)點(diǎn)

1、存儲(chǔ)過程可以一次編譯多次使用。存儲(chǔ)過程只在創(chuàng)建時(shí)進(jìn)行編譯，之后的使用都不需要重新編譯，這就提升了 SQL 的執(zhí)行效率。

2、可以減少開發(fā)工作量。將代碼封裝成模塊，實(shí)際上是編程的核心思想之一，這樣可以把復(fù)雜的問題拆解成不同的模塊，然后模塊之間可以重復(fù)使用，在減少開發(fā)工作量的同時(shí)，還能保證代碼的結(jié)構(gòu)清晰。

3、存儲(chǔ)過程的安全性強(qiáng)。我們在設(shè)定存儲(chǔ)過程的時(shí)候可以設(shè)置對用戶的使用權(quán)限，這樣就和視圖一樣具有較強(qiáng)的安全性。

4、可以減少網(wǎng)絡(luò)傳輸量。因?yàn)榇a封裝到存儲(chǔ)過程中，每次使用只需要調(diào)用存儲(chǔ)過程即可，這樣就減少了網(wǎng)絡(luò)傳輸量。

5、良好的封裝性。在進(jìn)行相對復(fù)雜的數(shù)據(jù)庫操作時(shí)，原本需要使用一條一條的 SQL 語句，可能要連接多次數(shù)據(jù)庫才能完成的操作，現(xiàn)在變成了一次存儲(chǔ)過程，只需要連接一次即可。

6.2 缺點(diǎn)

基于上面這些優(yōu)點(diǎn)，不少大公司都要求大型項(xiàng)目使用存儲(chǔ)過程，比如微軟、IBM 等公司。但是國內(nèi)的阿里并不推薦開發(fā)人員使用存儲(chǔ)過程，這是為什么呢？

阿里開發(fā)規(guī)范

【強(qiáng)制】禁止使用存儲(chǔ)過程，存儲(chǔ)過程難以調(diào)試和擴(kuò)展，更沒有移植性。

存儲(chǔ)過程雖然有諸如上面的好處，但缺點(diǎn)也是很明顯的。

1、可移植性差。存儲(chǔ)過程不能跨數(shù)據(jù)庫移植，比如在 MySQL、Oracle 和 SQL Server 里編寫的存儲(chǔ)過程，在換成其他數(shù)據(jù)庫時(shí)都需要重新編寫。

2、調(diào)試?yán)щy。只有少數(shù) DBMS 支持存儲(chǔ)過程的調(diào)試。對于復(fù)雜的存儲(chǔ)過程來說，開發(fā)和維護(hù)都不容易。雖然也有一些第三方工具可以對存儲(chǔ)過程進(jìn)行調(diào)試，但要收費(fèi)。

3、存儲(chǔ)過程的版本管理很困難。比如數(shù)據(jù)表索引發(fā)生變化了，可能會(huì)導(dǎo)致存儲(chǔ)過程失效。我們在開發(fā)軟件的時(shí)候往往需要進(jìn)行版本管理，但是存儲(chǔ)過程本身沒有版本控制，版本迭代更新的時(shí)候很麻煩。

4、它不適合高并發(fā)的場景。高并發(fā)的場景需要減少數(shù)據(jù)庫的壓力，有時(shí)數(shù)據(jù)庫會(huì)采用分庫分表的方式，而且對可擴(kuò)展性要求很高，在這種情況下，存儲(chǔ)過程會(huì)變得難以維護(hù)， 增加數(shù)據(jù)庫的壓力 ，顯然就不適用了。

6.3 小結(jié)

存儲(chǔ)過程既方便，又有局限性。盡管不同的公司對存儲(chǔ)過程的態(tài)度不一，但是對于我們開發(fā)人員來說，不論怎樣，掌握存儲(chǔ)過程都是必備的技能之一。

只是為了記錄自己的學(xué)習(xí)歷程，且本人水平有限，不對之處，請指正。文章來源地址http://www.zghlxwxcb.cn/news/detail-834470.html

到了這里，關(guān)于MySQL-15.存儲(chǔ)過程和函數(shù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！