1、存儲過程概述

1.1、理解

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


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


和視圖、函數的對比：
它和視圖有著同樣的優點，清晰、安全，還可以減少網絡傳輸量。
不過它和視圖不同，視圖是 虛擬表 ，通常不對底層數據表直接操作，而存儲過程是程序化的SQL，可以直接操作底層數據表 ，相比于面向集合的操作方式，能夠實現一些更復雜的數據處理。
一旦存儲過程被創建出來，使用它就像使用函數一樣簡單，我們直接通過調用存儲過程名即可。相較于函數，存儲過程是 沒有返回值 的。

1.2、分類

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

2、創建存儲過程

2.1、語法分析

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


說明：
1、參數前面的符號的意思
IN ：當前參數為輸入參數，也就是表示入參；存儲過程只是讀取這個參數的值。如果沒有定義參數種類， 默認就是 IN ，表示輸入參數。
OUT ：當前參數為輸出參數，也就是表示出參；執行完成之后，調用這個存儲過程的客戶端或者應用程序就可以讀取這個參數返回的值了。
INOUT ：當前參數既可以為輸入參數，也可以為輸出參數。


2、形參類型可以是 MySQL數據庫中的任意類型。


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


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


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


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


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

2.2、代碼舉例

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


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


舉例3：創建存儲過程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：創建存儲過程show_min_salary()，查看“emps”表的最低薪資值。并將最低薪資通過OUT參數“ms”輸出
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
    SELECT MIN(salary) INTO ms FROM emps;
END //
DELIMITER ;


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


舉例6：創建存儲過程show_someone_salary2()，查看“emps”表的某個員工的薪資，并用IN參數empname輸入員工姓名，用OUT參數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：創建存儲過程show_mgr_name()，查詢某個員工領導的姓名，并用INOUT參數“empname”輸入員工姓名，輸出領導的姓名。
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、調用存儲過程

3.1、調用格式

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


格式：
1、調用in模式的參數：
CALL sp1('值');


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


3、調用inout模式的參數：
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 ;
調用存儲過程：
CALL CountProc (101, @num);
查看返回結果：
SELECT @num;
該存儲過程返回了指定 s_id=101 的水果商提供的水果種類，返回值存儲在num變量中，使用SELECT查看，返回結果為3。


舉例2：創建存儲過程，實現累加運算，計算 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 會自動設置 DELIMITER 為其他符號，我們不需要再進行DELIMITER 的操作。
直接使用 CALL add_num(50); 即可。這里我傳入的參數為 50，也就是統計 1+2+…+50 的積累之和。

3.3、如何調試

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

練習

#0.準備工作
CREATE DATABASE test15_pro_func;
USE test15_pro_func;
#1.創建存儲過程insert_user(),實現傳入用戶名和密碼，插入到admin表中
CREATE TABLE admin(
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(15) NOT NULL,
    pwd VARCHAR(25) NOT NULL
);
#2.創建存儲過程get_phone(),實現傳入女神編號，返回女神姓名和女神電話
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.創建存儲過程insert_user(),實現傳入用戶名和密碼，插入到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.創建存儲過程get_phone(),實現傳入女神編號，返回女神姓名和女神電話
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;
#調用
CALL get_phone(1,@name,@phone);
SELECT @name,@phone;


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


#4.創建存儲過程format_date(),實現傳入一個日期，格式化成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;
#調用
SET @mydate='1992-09-08';
CALL format_date(@mydate,@strdate);
SELECT @strdate;


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


#創建帶inout模式參數的存儲過程
#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;
#調用
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

審核編輯：劉清