初識存儲過程

• 理解:
  • 含義: 存儲過程（Stored Procedure）是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL 語句集(這些SQL語句已經(jīng)編譯過了)，它存儲在數(shù)據(jù)庫中，一次編譯后永久有效，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。
  • 執(zhí)行過程:存儲過程預(yù)先存儲在MySQL服務(wù)器，當(dāng)需要執(zhí)行的時(shí)候，客戶端向服務(wù)器端發(fā)送執(zhí)行的命令，服務(wù)器端就可以把預(yù)先存儲好的這一系列sql語句全部執(zhí)行。
• 好處
  • 簡化操作，提高sql語句復(fù)用性
  • 減少網(wǎng)絡(luò)傳輸量(客戶端不需要把所有的SQL語句通過網(wǎng)絡(luò)發(fā)送給服務(wù)器端)
  • 減少sql語句暴露在網(wǎng)上的風(fēng)險(xiǎn)，提高數(shù)據(jù)查詢的安全性

存儲過程和視圖與函數(shù)的對比

存儲過程可以直接操作底層數(shù)據(jù)表，視圖是虛擬表，存儲過程一旦創(chuàng)建出來，我們直接通過存儲過程名調(diào)用就可以了，就像用函數(shù)一樣，相對于函數(shù)，存儲過程是沒有返回值的

接下來看看存儲過程的分類，現(xiàn)在不懂沒關(guān)系，等文章后面內(nèi)容看完再回過來看，其實(shí)分類和函數(shù)的分類差不多，就是根據(jù)有無參數(shù)和有無返回值來劃分。

• 存儲過程的分類
  存儲過程的參數(shù)類型可以是IN,OUT,INOUT，根據(jù)這樣可以分類
  • ①沒有參數(shù)(無參無返回)
  • ②僅僅帶IN類型(有參數(shù)無返回)
  • ③僅僅帶OUT類型(無參數(shù)有返回)
  • ④既有IN，又有OUT(有參有返回)
  • ⑤帶INOUT(有參有返回)

IN,OUT,INOUT都可以在一個(gè)存儲過程中帶多個(gè)

存儲過程語法

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

類似于Java的語法

修飾符 返回類型 方法名(參數(shù)類型 參數(shù)名,...){
	方法體;
}

接下來對參數(shù)類型前面的IN,OUT,INOUT進(jìn)行說明

• IN:當(dāng)前參數(shù)是輸入?yún)?shù)，如果沒有定義參數(shù)的話，默認(rèn)就是參數(shù)類型是IN
• OUT:輸出參數(shù)
• INOUT:可以表示為輸入?yún)?shù)，也可以是輸出參數(shù)

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

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í)行存儲過程時(shí)，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個(gè)值，默認(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)前存儲過程。
  • 如果沒有設(shè)置相關(guān)的值，則MySQL默認(rèn)指定值為DEFINER。
• COMMENT 'string'：注釋信息，可以用來描述存儲過程。

需要注意的地方:

• mysql默認(rèn)以；作為語句結(jié)束的標(biāo)識
• 為了避免與存儲過程中SQL語句結(jié)束符相沖突，需要使用DELIMITER改變存儲過程的結(jié)束符。
• 存儲過程定義完畢之后再使用“DELIMITER ;”恢復(fù)默認(rèn)結(jié)束符。DELIMITER也可以指定其他符號作為結(jié)束符。

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

存儲過程調(diào)用

準(zhǔn)備工作

 -- 準(zhǔn)備工作，創(chuàng)建新數(shù)據(jù)庫
 create database  db15;
use db15;
create table emps
as select * from atguigudb.employees;
create table departments
as
    select * from atguigudb.departments;
select * from emps;

無參無返回

存儲過程的調(diào)用，用call+存儲過程名

OUT類型

IN類型

帶IN和OUT

帶INOUT

存儲函數(shù)的使用

語法

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)建存儲過程時(shí)相同，這里不再贅述。

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

函數(shù)的調(diào)用

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

注意：

若在創(chuàng)建存儲函數(shù)中報(bào)錯“you might want to use the less safe log_bin_trust_function_creators variable”，有兩種處理方法：

• 方式1：加上必要的函數(shù)特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”

• 方式2：

SET GLOBAL log_bin_trust_function_creators = 1;

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

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

到此這篇關(guān)于關(guān)于MySQL的存儲過程與存儲函數(shù)的文章就介紹到這了,更多相關(guān)MySQL存儲過程與存儲函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論