MySQL中的預存程序

標籤：mysql資料庫   觸發器   code   int   bsp   語句   結果   表示   nbsp   

什麼是預存程序

簡單的說，就是一組SQL語句集，功能強大，可以實現一些比較複雜的邏輯功能，類似於JAVA語言中的方法；

ps:預存程序跟觸發器有點類似，都是一組SQL集，但是預存程序是主動調用的，且功能比觸發器更加強大，觸發器是某件事觸發後自動調用；

有哪些特性

有輸入輸出參數，可以聲明變數，有if/else, case,while等控制語句，通過編寫預存程序，可以實現複雜的邏輯功能；

函數的普遍特性：模組化，封裝，代碼複用；

速度快，只有首次執行需經過編譯和最佳化步驟，後續被調用可以直接執行，省去以上步驟；

 

預存程序的基本格式如下：

-- 聲明結束符-- 建立預存程序DELIMITER $  -- 聲明預存程序的結束符CREATE PROCEDURE pro_test()    --預存程序名稱(參數列表)BEGIN    -- 可以寫多個sql語句;      -- sql語句+流程式控制制    SELECT * FROM employee;END $  -- 結束 結束符-- 執行預存程序CALL pro_test();   -- CALL 預存程序名稱(參數);-- 刪除預存程序DROP PROCEDURE pro_test;參數：IN：   表示輸入參數，可以攜帶資料帶預存程序中OUT： 表示輸出參數，可以從預存程序中返回結果INOUT： 表示輸入輸出參數，既可以輸入功能，也可以輸出功能

 

1. 帶有輸入參數的預存程序

    需求：傳入一個員工的id，查詢員工資訊

DELIMITER $CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 輸入參數BEGIN    SELECT * FROM employee WHERE id=eid;END $ -- 調用CALL pro_findById(4);

 

2. 帶有輸出參數的預存程序

DELIMITER $CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：輸出參數BEGIN        -- 給參數賦值    SET str=‘hellojava‘;END $

如何接受返回參數的值呢？這裡涉及到MySQL的變數

 

MySQL變數一共有三種：

全域變數

全域變數又叫內建變數，是mysql資料庫內建的變數 ，對所有串連都起作用。

查看所有全域變數： show variables

查看某個全域變數： select @@變數名

修改全域變數： set 變數名=新值

character_set_client: mysql伺服器的接收資料的編碼

character_set_results：mysql伺服器輸出資料的編碼

 

會話變數

只存在於當前用戶端與資料庫伺服器端的一次串連當中。如果串連斷開，那麼會話變數全部丟失！

定義會話變數: set @變數=值

查看會話變數： select @變數

局部變數

在預存程序中使用的變數就叫局部變數。只要預存程序執行完畢，局部變數就丟失。

 

回到上面這個預存程序，如何接受返回參數的值呢？

定義一個會話變數name, 使用name會話變數接收預存程序的傳回值

CALL pro_testOut(@NAME);

查看變數值

SELECT @NAME;

 

3. 帶有輸入輸出參數的預存程序

DELIMITER $CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT： 輸入輸出參數BEGIN   -- 查看變數   SELECT n;   SET n =500;END $-- 調用SET @n=10;CALL pro_testInOut(@n);SELECT @n;

 

4. 帶有條件判斷的預存程序

需求：輸入一個整數，如果1，則返回“星期一”,如果2，返回“星期二”,如果3，返回“星期三”。其他數字，返回“錯誤輸入”;

DELIMITER $CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))BEGIN    IF num=1 THEN        SET str=‘星期一‘;    ELSEIF num=2 THEN        SET str=‘星期二‘;    ELSEIF num=3 THEN        SET str=‘星期三‘;    ELSE        SET str=‘輸入錯誤‘;    END IF;END $--調用CALL pro_testIf(4,@str); SELECT @str;

 

5. 帶有迴圈功能的預存程序

需求： 輸入一個整數，求和。例如，輸入100，統計1-100的和

DELIMITER $CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)BEGIN    -- 定義一個局部變數    DECLARE i INT DEFAULT 1;    DECLARE vsum INT DEFAULT 0;    WHILE i<=num DO          SET vsum = vsum+i;          SET i=i+1;    END WHILE;    SET result=vsum;END $--調用CALL pro_testWhile(100,@result);SELECT @result;

 

6. 使用查詢的結果賦值給變數（INTO）

DELIMITER $CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )BEGIN    SELECT empName INTO vname FROM employee WHERE id=eid;END $--調用CALL pro_findById2(1,@NAME);SELECT @NAME;

 

預存程序弊端

不同資料庫，文法差別很大，移植困難，換了資料庫，需要重新編寫；

不好管理，把過多商務邏輯寫在預存程序不好維護，不利於分層管理，容易混亂，一般預存程序適用於個別對效能要求較高的業務，其它的必要性不是很大；

...

MySQL中的預存程序