mysql8.0存儲過程詳解

1、存儲過程概述

1.1、理解

含義：
存儲過程的英文是 Stored Procedure 。它的思想很簡單，就是一組經(jīng)過 預(yù)先編譯 的 SQL 語句的封裝。
執(zhí)行過程：存儲過程預(yù)先存儲在 MySQL 服務(wù)器上，需要執(zhí)行的時候，客戶端只需要向服務(wù)器端發(fā)出調(diào)用存儲過程的命令，服務(wù)器端就可以把預(yù)先存儲好的這一系列SQL語句全部執(zhí)行


好處：
1、簡化操作，提高了sql語句的重用性，減少了開發(fā)程序員的壓力 
2、減少操作過程中的失誤，提高效率
3、減少網(wǎng)絡(luò)傳輸量（客戶端不需要把所有的 SQL 語句通過網(wǎng)絡(luò)發(fā)給服務(wù)器） 
4、減少了 SQL 語句暴露在網(wǎng)上的風(fēng)險，也提高了數(shù)據(jù)查詢的安全性


和視圖、函數(shù)的對比：
它和視圖有著同樣的優(yōu)點，清晰、安全，還可以減少網(wǎng)絡(luò)傳輸量。
不過它和視圖不同，視圖是 虛擬表 ，通常不對底層數(shù)據(jù)表直接操作，而存儲過程是程序化的SQL，可以直接操作底層數(shù)據(jù)表 ，相比于面向集合的操作方式，能夠?qū)崿F(xiàn)一些更復(fù)雜的數(shù)據(jù)處理。
一旦存儲過程被創(chuàng)建出來，使用它就像使用函數(shù)一樣簡單，我們直接通過調(diào)用存儲過程名即可。相較于函數(shù)，存儲過程是 沒有返回值 的。

1.2、分類

存儲過程的參數(shù)類型可以是IN、OUT和INOUT。根據(jù)這點分類如下：
1、沒有參數(shù)（無參數(shù)無返回）
2、僅僅帶 IN 類型（有參數(shù)無返回） 
3、僅僅帶 OUT 類型（無參數(shù)有返回） 
4、既帶 IN 又帶 OUT（有參數(shù)有返回） 
5、帶 INOUT（有參數(shù)有返回）
注意：IN、OUT、INOUT 都可以在一個存儲過程中帶多個

2、創(chuàng)建存儲過程

2.1、語法分析

語法：
CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數(shù)名 參數(shù)類型,...)
[characteristics ...]
BEGIN
存儲過程體
END


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


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


3、 characteristics 表示創(chuàng)建存儲過程時指定的對存儲過程的約束條件，其取值信息如下：
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'


LANGUAGE SQL ：說明存儲過程執(zhí)行體是由SQL語句組成的，當(dāng)前系統(tǒng)支持的語言為SQL。
[NOT] DETERMINISTIC ：指明存儲過程執(zhí)行的結(jié)果是否確定。DETERMINISTIC表示結(jié)果是確定的。每次執(zhí)行存儲過程時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結(jié)果是不確定
的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認(rèn)為NOT DETERMINISTIC。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL語句的限制。
    CONTAINS SQL表示當(dāng)前存儲過程的子程序包含SQL語句，但是并不包含讀寫數(shù)據(jù)的SQL語句；
    NO SQL表示當(dāng)前存儲過程的子程序中不包含任何SQL語句；
    READS SQL DATA表示當(dāng)前存儲過程的子程序中包含讀數(shù)據(jù)的SQL語句；
    MODIFIES SQL DATA表示當(dāng)前存儲過程的子程序中包含寫數(shù)據(jù)的SQL語句。
    默認(rèn)情況下，系統(tǒng)會指定為CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER } ：執(zhí)行當(dāng)前存儲過程的權(quán)限，即指明哪些用戶能夠執(zhí)行當(dāng)前存儲過程。
    DEFINER 表示只有當(dāng)前存儲過程的創(chuàng)建者或者定義者才能執(zhí)行當(dāng)前存儲過程；
    INVOKER 表示擁有當(dāng)前存儲過程的訪問權(quán)限的用戶能夠執(zhí)行當(dāng)前存儲過程。


4、存儲過程體中可以有多條 SQL 語句，如果僅僅一條SQL 語句，則可以省略BEGIN和END編寫存儲過程并不是一件簡單的事情，可能存儲過程中需要復(fù)雜的 SQL 語句。
4.1. BEGIN…END：BEGIN…END 中間包含了多個語句，每個語句都以（;）號為結(jié)束符。
4.2. DECLARE：DECLARE 用來聲明變量，使用的位置在于 BEGIN…END 語句中間，而且需要在其他語句使用之前進行變量的聲明。
4.3. SET：賦值語句，用于對變量進行賦值。
4.4. SELECT… INTO：把從數(shù)據(jù)表中查詢的結(jié)果存放到變量中，也就是為變量賦值。


5、需要設(shè)置新的結(jié)束標(biāo)記
DELIMITER 新的結(jié)束標(biāo)記
因為MySQL默認(rèn)的語句結(jié)束符號為分號‘;’。為了避免與存儲過程中SQL語句結(jié)束符相沖突，需要使用DELIMITER改變存儲過程的結(jié)束符。
比如：“DELIMITER //”語句的作用是將MySQL的結(jié)束符設(shè)置為//，并以“END //”結(jié)束存儲過程。存儲過程定義完畢之后再使用“DELIMITER ;
”恢復(fù)默認(rèn)結(jié)束符。DELIMITER也可以指定其他符號作為結(jié)束符。
當(dāng)使用DELIMITER命令時，應(yīng)該避免使用反斜杠（‘’）字符，因為反斜線是MySQL的轉(zhuǎn)義字符。


示例：
DELIMITER $
CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數(shù)名 參數(shù)類型,...)
[characteristics ...]
BEGIN
    sql語句1;
    sql語句2;
END $

2.2、代碼舉例

舉例1：創(chuàng)建存儲過程select_all_data()，查看 emps 表的所有數(shù)據(jù)
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
    SELECT * FROM emps;
END $
DELIMITER ;


舉例2：創(chuàng)建存儲過程avg_employee_salary()，返回所有員工的平均工資
DELIMITER //
CREATE PROCEDURE avg_employee_salary ()
BEGIN
    SELECT AVG(salary) AS avg_salary FROM emps;
END //
DELIMITER ;


舉例3：創(chuàng)建存儲過程show_max_salary()，用來查看“emps”表的最高薪資值
DELIMITER //
CREATE PROCEDURE show_max_salary()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT '查看最高薪資'
    BEGIN
        SELECT MAX(salary) FROM emps;
    END //
DELIMITER ;


舉例4：創(chuàng)建存儲過程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 ;


舉例5：創(chuàng)建存儲過程show_someone_salary()，查看“emps”表的某個員工的薪資，并用IN參數(shù)empname輸入員工姓名。
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
    SELECT salary FROM emps WHERE ename = empname;
END //
DELIMITER ;


舉例6：創(chuàng)建存儲過程show_someone_salary2()，查看“emps”表的某個員工的薪資，并用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 ename = empname;
END //
DELIMITER ;


舉例7：創(chuàng)建存儲過程show_mgr_name()，查詢某個員工領(lǐng)導(dǎo)的姓名，并用INOUT參數(shù)“empname”輸入員工姓名，輸出領(lǐng)導(dǎo)的姓名。
DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
BEGIN
    SELECT ename INTO empname FROM emps
    WHERE eid = (SELECT MID FROM emps WHERE ename=empname);
END //
DELIMITER ;

3、調(diào)用存儲過程

3.1、調(diào)用格式

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


格式：
1、調(diào)用in模式的參數(shù)：
CALL sp1('值');


2、調(diào)用out模式的參數(shù)：
SET @name;
CALL sp1(@name);
SELECT @name;


3、調(diào)用inout模式的參數(shù)：
SET @name=值;
CALL sp1(@name);
SELECT @name;

3.2、代碼舉例

舉例1：
DELIMITER //
CREATE PROCEDURE CountProc(IN sid INT,OUT num INT)
BEGIN
    SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
END //
DELIMITER ;
調(diào)用存儲過程：
CALL CountProc (101, @num);
查看返回結(jié)果：
SELECT @num;
該存儲過程返回了指定 s_id=101 的水果商提供的水果種類，返回值存儲在num變量中，使用SELECT查看，返回結(jié)果為3。


舉例2：創(chuàng)建存儲過程，實現(xiàn)累加運算，計算 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 工具，那么在編寫存儲過程的時候，Navicat 會自動設(shè)置 DELIMITER 為其他符號，我們不需要再進行DELIMITER 的操作。
直接使用 CALL add_num(50); 即可。這里我傳入的參數(shù)為 50，也就是統(tǒng)計 1+2+…+50 的積累之和。

3.3、如何調(diào)試

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

練習(xí)

#0.準(zhǔn)備工作
CREATE DATABASE test15_pro_func;
USE test15_pro_func;
#1.創(chuàng)建存儲過程insert_user(),實現(xiàn)傳入用戶名和密碼，插入到admin表中
CREATE TABLE admin(
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(15) NOT NULL,
    pwd VARCHAR(25) NOT NULL
);
#2.創(chuàng)建存儲過程get_phone(),實現(xiàn)傳入女神編號，返回女神姓名和女神電話
CREATE TABLE beauty(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(15) NOT NULL,
    phone VARCHAR(15) UNIQUE,
    birth DATE
);
INSERT INTO beauty(NAME,phone,birth)
VALUES
('朱茵','13201233453','1982-02-12'),
('孫燕姿','13501233653','1980-12-09'),
('田馥甄','13651238755','1983-08-21'),
('鄧紫棋','17843283452','1991-11-12'),
('劉若英','18635575464','1989-05-18'),
('楊超越','13761238755','1994-05-11');
SELECT * FROM beauty;


#1.創(chuàng)建存儲過程insert_user(),實現(xiàn)傳入用戶名和密碼，插入到admin表中
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(15) NOT NULL,
pwd VARCHAR(25) NOT NULL
);
DELIMITER//
CREATE PROCEDURE insert_user(IN username VARCHAR(20),IN loginPwd VARCHAR(20))
BEGIN
    INSERT INTO admin(user_name,pwd) VALUES(username,loginpwd);
END//
DELIMITER;


#2.創(chuàng)建存儲過程get_phone(),實現(xiàn)傳入女神編號，返回女神姓名和女神電話
DELIMITER//
CREATE PROCEDURE get_phone(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))
BEGIN
    SELECT b.name,b.phone INTO NAME,phone FROM beauty b WHEREb.id=id;
END//
DELIMITER;
#調(diào)用
CALL get_phone(1,@name,@phone);
SELECT @name,@phone;


#3.創(chuàng)建存儲過程date_diff()，實現(xiàn)傳入兩個女神生日，返回日期間隔大小
DELIMITER//
CREATE PROCEDURE date_diff(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)
BEGIN
    SELECT DATEDIFF(birth1,birth2) INTO result;
END//
DELIMITER;
#調(diào)用
SET @birth1='1992-09-08';
SET @birth2='1989-01-03';
CALL date_diff(@birth1,@birth2,@result);
SELECT @result;


#4.創(chuàng)建存儲過程format_date(),實現(xiàn)傳入一個日期，格式化成xx年xx月xx日并返回
DELIMITER//
CREATE PROCEDURE format_date(IN mydate DATETIME,OUT strdate VARCHAR(50))
BEGIN
    SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END//
DELIMITER;
#調(diào)用
SET @mydate='1992-09-08';
CALL format_date(@mydate,@strdate);
SELECT @strdate;


#5.創(chuàng)建存儲過程beauty_limit()，根據(jù)傳入的起始索引和條目數(shù)，查詢女神表的記錄
DELIMITER//
CREATE PROCEDURE beauty_limit(IN startIndex INT,IN size INT)
BEGIN
    SELECT * FROM beauty LIMIT startIndex,size;
END//
DELIMITER;
#調(diào)用
CALL beauty_limit(1,3);


#創(chuàng)建帶inout模式參數(shù)的存儲過程
#6.傳入a和b兩個值，最終a和b都翻倍并返回
DELIMITER//
CREATE PROCEDURE add_double(INOUT a INT,INOUT b INT)
BEGIN
    SET a=a*2;
    SET b=b*2;
END//
DELIMITER;
#調(diào)用
SET @a=3,@b=5;
CALL add_double(@a,@b);
SELECT @a,@b;


#7.刪除題目5的存儲過程
DROP PROCEDURE beauty_limit;


#8.查看題目6中存儲過程的信息
SHOW CREATE PROCEDURE add_double;
SHOW PROCEDURE STATUS LIKE'add_double';

鏈接：https://blog.51cto.com/u_13236892/9073404

審核編輯：劉清