mysql存儲過程怎么理解

這篇文章主要講解了“mysql存儲過程怎么理解”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“mysql存儲過程怎么理解”吧！

1、存儲過程的簡介：

    我們常用的操作數據庫語言SQL語句在執行的時候需要要先編譯，然后執行，而存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯后存儲在數據庫中，用戶通過指定存儲過程的名字并給定參數（如果該存儲過程帶有參數）來調用執行它。

    一個存儲過程是一個可編程的函數，它在數據庫中創建并保存。它可以有SQL語句和一些特殊的控制結構組成。當希望在不同的應用程序或平臺上執行相同的函數，或者封裝特定功能時，存儲過程是非常有用的。數據庫中的存儲過程可以看做是對編程中面向對象方法的模擬。它允許控制數據的訪問方式。

2、存儲過程的有點：

(1).存儲過程增強了SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成復雜的判斷和較復雜的運算。

(2).存儲過程允許標準組件是編程。存儲過程被創建后，可以在程序中被多次調用，而不必重新編寫該存儲過程的SQL語句。而且數據庫專業人員可以隨時對存儲過程進行修改，對應用程序源代碼毫無影響。

(3).存儲過程能實現較快的執行速度。如果某一操作包含大量的Transaction-SQL代碼或分別被多次執行，那么存儲過程要比批處理的執行速度快很多。因為存儲過程是預編譯的。在首次運行一個存儲過程時查詢，優化器對其進行分析優化，并且給出最終被存儲在系統表中的執行計劃。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和優化，速度相對要慢一些。

(4).存儲過程能過減少網絡流量。針對同一個數據庫對象的操作（如查詢、修改），如果這一操作所涉及的Transaction-SQL語句被組織程存儲過程，那么當在客戶計算機上調用該存儲過程時，網絡中傳送的只是該調用語句，從而大大增加了網絡流量并降低了網絡負載。

(5).存儲過程可被作為一種安全機制來充分利用。系統管理員通過執行某一存儲過程的權限進行限制，能夠實現對相應的數據的訪問權限的限制，避免了非授權用戶對數據的訪問，保證了數據的安全。

3、關于mysql的存儲過程：

    存儲過程是數據庫存儲的一個重要的功能，但是MySQL在5.0以前并不支持存儲過程，這使得MySQL在應用上大打折扣。好在MySQL 5.0終于開始已經支持存儲過程，這樣即可以大大提高數據庫的處理速度，同時也可以提高數據庫編程的靈活性。

    同時，要在mysql5.1以上版本創建子程序，必須具有CREATE ROUTINE權限，并且ALTER ROUTINE和EXECUTE權限被自動授予它的創建者；

4、存儲過程的創建：

    （1）語法：

        CREATE PROCEDURE sp_name ([ proc_parameter ]) [ characteristics..] routine_body

         proc_parameter指定存儲過程的參數列表，列表形式如下：[IN|OUT|INOUT] param_name type

        其中in表示輸入參數，out表示輸出參數，inout表示既可以輸入也可以輸出；param_name表示參數名稱；type表示參數的類型該類型可以是MYSQL數據庫中的任意類型有以下取值：

        characteristic: 
            LANGUAGE SQL 
              | [NOT] DETERMINISTIC 
              | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
              | SQL SECURITY { DEFINER | INVOKER } 
              | COMMENT 'string' 
            routine_body: 
           Valid SQL procedure statement or statements

        LANGUAGE SQL ：說明routine_body部分是由SQL語句組成的，當前系統支持的語言為SQL，SQL是LANGUAGE特性的唯一值

        [NOT] DETERMINISTIC ：指明存儲過程執行的結果是否正確。DETERMINISTIC 表示結果是確定的。每次執行存儲過程時，相同的輸入會得到相同的輸出。[NOT] DETERMINISTIC 表示結果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認為[NOT] DETERMINISTIC 

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

            CONTAINS SQL表明子程序包含SQL語句，但是不包含讀寫數據的語句；

            NO SQL表明子程序不包含SQL語句；

            READS SQL DATA：說明子程序包含讀數據的語句；

            MODIFIES SQL DATA表明子程序包含寫數據的語句。

            默認情況下，系統會指定為CONTAINS SQL

        SQL SECURITY { DEFINER | INVOKER } ：指明誰有權限來執行。DEFINER 表示只有定義者才能執行

INVOKER 表示擁有權限的調用者可以執行。默認情況下，系統指定為DEFINER 

        COMMENT 'string' ：注釋信息，可以用來描述存儲過程或函數

        routine_body是SQL代碼的內容，可以用BEGIN...END來表示SQL代碼的開始和結束

   （2） 格式

        MySQL存儲過程創建的格式：CREATE PROCEDURE 過程名 ([過程參數[,...]])
[特性 ...] 過程體

    EG：

    DELIMITER //
    DROP PROCEDURE IF EXISTS simpleproc;
    CREATE PROCEDURE simpleproc (OUT param1 INT)
    BEGIN
    SELECT COUNT(*) INTO param1 FROM t_user;
    END //
    DELIMITER ;

我這是在使用Navicat for MySQL 上敲的，就不打算進入mysql客戶端去敲了，個人覺得這個更方便快捷一點；如果熟悉linux的童鞋估計就不這么認為啦；

好了，廢話不多說，先解釋一下：（1）DELIMITER這個東西的作用就是告訴mysql，我的存儲過程的結束符號是//，所以在你的存儲過程中使用;不會被認為是結束符號；注：當使用delimiter命令時，你應該避免使用反斜杠(‘\’)字符，因為那是MySQL的 轉義字符（2）drop procedure if exists的意思跟我們建表時的語句是一樣的，如果存在這個存儲過程則刪除；（3）create procedure simpleproc (out param1 int) 的意思也很明確，就是創建這個過程，而且帶有一個輸出參數，注：在function中可以有return 語句，但是procedure是沒有的，但是也可以做到將結果傳回去，如上；（4） begin .... end 這中間包裹著的就是procedure的主程序，也就是主體部分；簡而言之，把你要放在一起工作的sql都丟在這里就好；（5）DELIMITER；這個語句作為結束語句。目的是告訴mysql我現在不要使用//作為結束字符了，要換回；了；注：這個不止可是//，還可以是其他符號；

5、變量

        DECLARE語句被用來把不同項目局域到一個 子程序：局部變量，條件和 處理程序 及光標；DECLARE僅被用在BEGIN ... END復合語句里，并且必須在復合語句的開頭，在任何其它語句之前。光標必須在聲明處理程序之前被聲明，并且變量和條件必須在聲明光標或處理程序之前被聲明。

        declare 聲明局部變量：DECLARE var_name[,...] type [DEFAULT value]；這個語句被用來聲明局部變量。要給變量提供一個默認值，請包含一個DEFAULT子句。值可以被指定為一個表達式，不需要為一個常數。如果沒有DEFAULT子句，初始值為NULL。

        set 變量：SET var_name = expr [, var_name = expr] ...； 在存儲程序中的SET語句是一般SET語句的擴展版本。被參考變量可能是子程序內聲明的變量，或者是全局服務器變量。在存儲程序中的SET語句作為預先存在的SET語法的一部分來實現。這允許SET a=x, b=y, ...這樣的擴展語法。其中不同的變量類型（局域 聲明變量及全局和集體變量）可以被混合起來。這也允許把局部變量和一些只對系統變量有意義的選項合并起來。在那種情況下，此選項被識別，但是被忽略了。

6、定義處理程序

    有時候程序是會出錯的，但是你希望你的程序在出錯的情況下繼續執行，declare就可以幫助我們解決這樣的問題；具體定義如下：特定條件需要特定處理。這些條件可以聯系到錯誤，以及子程序中的一般流程控制。定義條件是事先定義程序執行過程中遇到的問題，處理程序定義了在遇到這些問題時候應當采取的處理方式，并且保證存儲過程或函數在遇到警告或錯誤時能繼續執行。這樣可以增強存儲程序處理問題的能力，避免程序異常停止運行

    1、定義條件

           DECLARE condition_name CONDITION FOR[condition_type]

             [condition_type]:

            SQLSTATE[VALUE] sqlstate_value |mysql_error_code

            condition_name：表示條件名稱

            condition_type：表示條件的類型

            sqlstate_value和mysql_error_code都可以表示mysql錯誤

            sqlstate_value為長度5的字符串錯誤代碼

            mysql_error_code為數值類型錯誤代碼，例如：ERROR1142(42000)中，sqlstate_value的值是42000，

            mysql_error_code的值是1142

//方法一：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'

//方法二：使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR SQLSTATE 1148

這個語句指定需要特殊處理條件。他將一個名字和指定的錯誤條件關聯起來。

這個名字隨后被用在定義處理程序的DECLARE HANDLER語句中

    2、定義處理程序

            DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

                handler_type:
                |CONTINUE：對一個CONTINUE處理程序，當前子程序的執行在執行 處理程序語句之后繼續。
                | EXIT :對于EXIT處理程序，當 前BEGIN...END復合語句的執行被終止。
                | UNDO :UNDO 處理程序類型語句還不被支持。 ·
            condition_value:
                SQLSTATE [VALUE] sqlstate_value
                | condition_name
                | SQLWARNING：SQLWARNING是對所有以01開頭的SQLSTATE代碼的速記
                | NOT FOUND：NOT FOUND是對所有以02開頭的SQLSTATE代碼的速記
                | SQLEXCEPTION：SQLEXCEPTION是對所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的速記
                | mysql_error_code

這個語句指定每個可以處理一個或多個條件的處理程序。如果產生一個或多個條件，指定的語句被執行。
 

//方法一：捕獲sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
SET @info='CAN NOT FIND'; 


//方法二：捕獲mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1148SET @info='CAN NOT FIND';

 
//方法三：先定義條件，然后調用 
DECLARE can_not_find CONDITION FOR 1146 ; 
DECLARE CONTINUE HANDLER FOR can_not_find SET 
set @info='CAN NOT FIND'; 


//方法四：使用SQLWARNING 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; 

//方法五：使用NOT FOUND 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';
 
//方法六：使用SQLEXCEPTION 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

eg:

delimiter //
DROP PROCEDURE IF EXISTS sp2;
CREATE PROCEDURE sp2()
BEGIN
		DECLARE CONTINUE HANDLER for SQLSTATE '23000' set @info = 23000;
		SET @info = 1;
		INSERT INTO test.t_user VALUES (1,'test','test','test');
		SET @info = 2;
		INSERT INTO test.t_user VALUES (1,'test','test','test');		
		SET @info = 3;
END //
delimiter ;

-- 執行以下sp2
call sp2();

-- 查詢以下@info 
SELECT @info;

--結果分析：如果程序沒有出錯，按照執行目的，結果@info = 3; 如果被捕獲了 '23000' 則結果是@23000; 

使用continue作為執行handler_type的話，捕獲異常就繼續執行；所以結果為3;
使用exit作為執行handler_type的話，捕獲異常后直接結束，所以結果為23000;

7、光標

        簡單光標在存儲程序和函數內被支持。語法如同在嵌入的SQL中。光標當前是不敏感的，只讀的及不滾動的。 不敏感意為服務器可以活不可以復制它的結果表。 光標必須在聲明處理程序之前被聲明，并且變量和條件必須在聲明光標或處理程序之前被聲明。
    

    1、聲明光標

        DECLARE cursor_name CURSOR FOR select_statement

        這個語句聲明一個光標。也可以在子程序中定義多個光標，但是一個塊中的每一個光標必須有唯一的名字。 SELECT語句不能有INTO子句。

    2、打開光標

        OPEN cursor_name 這個語句打開先前聲明的光標。

    3、使用光標  FETCH

        FETCH cursor_name INTO var_name [, var_name] ... 這個語句用指定的打開光標讀取下一行（如果有下一行的話），并且前進光標指針

    4、關閉光標

        CLOSE cursor_name 這個語句關閉先前打開的光標。 如果未被明確地關閉，光標在它被聲明的復合語句的末尾被關閉。

    2、流程控制構造

1．IF語句

IF語句用來進行條件判斷。根據是否滿足條件，將執行不同的語句。其語法的基本形式如下：

IF search_condition THEN statement_list 
[ELSEIF search_condition THEN statement_list] ... 
[ELSE statement_list] 
END IF

其中，search_condition參數表示條件判斷語句；statement_list參數表示不同條件的執行語句。

注意：MYSQL還有一個IF()函數，他不同于這里描述的IF語句

下面是一個IF語句的示例。代碼如下：

delimiter //
DROP PROCEDURE IF EXISTS sp3;
CREATE PROCEDURE sp3()
BEGIN
	DECLARE age int DEFAULT 19;
	set @age1 = 0;
	SET @age2 = 0;
	set @age3 = 0;
	IF age>20 
		THEN SET @age1=age;  
	ELSEIF age=20 
		THEN SET @age2=age;  
	ELSE 
		SET @age3=-1;  
	END IF; 
END //
delimiter ;
call sp3();

該示例根據age與20的大小關系來執行不同的SET語句。

如果age值大于20，那么將count1的值加1；如果age值等于20，那么將count2的值加1；

其他情況將count3的值加1。IF語句都需要使用END IF來結束。

2．CASE語句

CASE語句也用來進行條件判斷，其可以實現比IF語句更復雜的條件判斷。CASE語句的基本形式如下：

CASE case_value 
WHEN when_value THEN statement_list 
[WHEN when_value THEN statement_list] ... 
[ELSE statement_list] 
END CASE

其中，case_value參數表示條件判斷的變量；

when_value參數表示變量的取值；

statement_list參數表示不同when_value值的執行語句。

CASE語句還有另一種形式。該形式的語法如下：

CASE 
WHEN search_condition THEN statement_list 
[WHEN search_condition THEN statement_list] ... 
[ELSE statement_list] 
END CASE

其中，search_condition參數表示條件判斷語句；

statement_list參數表示不同條件的執行語句。

下面是一個CASE語句的示例。代碼如下：

delimiter //
DROP PROCEDURE IF EXISTS sp4;
CREATE PROCEDURE sp4()
BEGIN
	DECLARE age int DEFAULT 19;
	set @age1 = 0;
	CASE age
		WHEN age > 20 THEN SET @age1 = age;
		WHEN age = 20 THEN SET @age1 = age-1;
		ELSE SET @age1 = -1;
	END CASE;
END //
delimiter ;
call sp4();
select @age1;

注意：這里的CASE語句和“控制流程函數”里描述的SQL CASE表達式的CASE語句有輕微不同。這里的CASE語句不能有ELSE NULL子句

并且用END CASE替代END來終止??！

3．LOOP語句

LOOP語句可以使某些特定的語句重復執行，實現一個簡單的循環。

但是LOOP語句本身沒有停止循環的語句，必須是遇到LEAVE語句等才能停止循環。

LOOP語句的語法的基本形式如下：

[begin_label:] LOOP 
statement_list 
END LOOP [end_label]

其中，begin_label參數和end_label參數分別表示循環開始和結束的標志，這兩個標志必須相同，而且都可以省略；(例子與leave結合一起寫)

4．LEAVE語句

LEAVE語句主要用于跳出循環控制。其語法形式如下：

LEAVE label

其中，label參數表示循環的標志。

下面是一個LEAVE語句的示例。代碼如下：

delimiter //
DROP PROCEDURE IF EXISTS sp5;
CREATE PROCEDURE sp5()
BEGIN
	set @age = 0;
	add_age:LOOP
	  SET @age = @age + 1;
	if @age>1000 
      THEN LEAVE add_age;
	end IF;
	END LOOP add_age;
END //
delimiter ;
-- 調用
call sp5();

-- 查看結果
select @age;

該示例循環執行count加1的操作。當count的值等于100時，則LEAVE語句跳出循環。

5．ITERATE語句

ITERATE語句也是用來跳出循環的語句。但是，ITERATE語句是跳出本次循環，然后直接進入下一次循環。

ITERATE語句只可以出現在LOOP、REPEAT、WHILE語句內。

ITERATE語句的基本語法形式如下：

ITERATE label

其中，label參數表示循環的標志。

下面是一個ITERATE語句的示例。代碼如下：

delimiter //
DROP PROCEDURE IF EXISTS sp6;
CREATE PROCEDURE sp6()
BEGIN
	DECLARE age int DEFAULT 0;
	DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET age = 23000;
	add_fun:LOOP
		SET age = age + 1;
		IF age > 10
			THEN INSERT into t_user value(1, 'lennon', 'lennon', 'lennon');
		ELSEIF age > 5 && age < 10
			THEN ITERATE add_fun;
		ELSE 
			select age;
		END IF;
	END LOOP add_fun;
END //
delimiter ;
call sp6();

說明：LEAVE語句和ITERATE語句都用來跳出循環語句，但兩者的功能是不一樣的。

LEAVE語句是跳出整個循環，然后執行循環后面的程序。而ITERATE語句是跳出本次循環，然后進入下一次循環。

使用這兩個語句時一定要區分清楚。

6．REPEAT語句

REPEAT語句是有條件控制的循環語句。當滿足特定條件時，就會跳出循環語句。REPEAT語句的基本語法形式如下：

[begin_label:] REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT [end_label]

其中，statement_list參數表示循環的執行語句；search_condition參數表示結束循環的條件，滿足該條件時循環結束。

下面是一個REPEAT語句的示例。代碼如下：

delimiter //
DROP PROCEDURE IF EXISTS sp7;
CREATE PROCEDURE sp7()
BEGIN
	set @age = 0;
	add_count:REPEAT
		set @age = @age + 1 ;
	UNTIL @age>100 END REPEAT add_count;
END //
delimiter ;

call sp7();

select @age;

該示例循環執行count加1的操作，count值為100時結束循環。

REPEAT循環都用END REPEAT結束。

7．WHILE語句

WHILE語句也是有條件控制的循環語句。但WHILE語句和REPEAT語句是不一樣的。

WHILE語句是當滿足條件時，執行循環內的語句。

WHILE語句的基本語法形式如下：

[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label]

其中，search_condition參數表示循環執行的條件，滿足該條件時循環執行；

statement_list參數表示循環的執行語句。

下面是一個ITERATE語句的示例。代碼如下：

delimiter //
DROP PROCEDURE IF EXISTS sp8;
CREATE PROCEDURE sp8()
BEGIN
	set @age = 0;
	
	WHILE @age < 100 DO
		set @age = @age + 1 ;
	END WHILE;

END //
delimiter ;

call sp8();

select @age;

該示例循環執行count加1的操作，count值小于100時執行循環。

如果count值等于100了，則跳出循環。WHILE循環需要使用END WHILE來結束。

8、查看存儲過程

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

SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=' sp_name ' ;

eg:

show PROCEDURE STATUS like 'sp6';

show CREATE PROCEDURE sp6;

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp6' ;

感謝各位的閱讀，以上就是“mysql存儲過程怎么理解”的內容了，經過本文的學習后，相信大家對mysql存儲過程怎么理解這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！