MySQL學習21：初始預存程序

標籤：

       一預存程序簡介

       在學習預存程序之前我們先來看看MySQL資料庫中的命令執行流程：

       (1)預存程序

       預存程序是SQL語句和控制語句的先行編譯集合，以一個名稱儲存並作為一個單元處理。

       (2)預存程序的優點

       1)增強SQL語句的功能和靈活性。

       2)實現較快的執行速度。

       3)減少網路流量。

       (3)預存程序文法結構解析       MySQL資料庫建立預存程序的文法結構為：

       CREATE [DEFINER = { user | CURRENT_USER} ] PROCEDURE sp_name ([proc_parameter[,...]]) 

[characteristic ...] routine_body;

       proc_parameter代表的含義：

       [ IN | OUT | INOUT ] param_name type

       參數含義：

       1)IN，表示該參數的值必須在調用預存程序時指定。

       2)OUT，表示該參數的值可以被預存程序改變，並且可以返回。

       3)INOUT，表示該參數的值在調用時指定，並且可以被改變和返回。

       (4)預存程序特性

       COMMENT ‘string‘

       | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} 
       | SQL SECURITY { DEFINER | INVOKER }

       特性釋義：

       1)COMMENT：注釋。

       2)CONTAINS SQL：包含SQL語句，但不包含讀或寫資料的語句。

       3)NO SQL：不包含SQL語句。

       4)READS SQL DATA ：包含讀資料的語句。

       5)MODIFIES SQL DATA：包含寫資料的語句。

       6)SQL SECURITY { DEFINER | INVOKER }：指明誰有許可權來執行。
       (5)過程體

       1)過程體由合法的SQL語句構成。

       2)過程體可以是任意SQL語句。

       3)過程體如果為複合結構則使用BEGIN...AND語句。

       4)複合結構也可以包含聲明、迴圈和控制結構。

       (6)調用預存程序

       MySQL資料庫調用預存程序的文法為：

       1)調用有參數的預存程序

       CALL sp_name([parameter[,...]]);

       2)調用無參數的預存程序

       CALL sp_name[()];

       二建立預存程序       (1)建立不帶參數的預存程序

       建立查詢MySQL資料庫版本號碼的預存程序

       CREATE PROCEDURE sp1() SELECT VERSION();

       調用預存程序

       CALL sp1;

       CALL sp1();

       (2)建立帶有IN型別參數的預存程序

       DESC users3;

       SELECT * FROM users3;

       建立一個刪除資料表中全部記錄的預存程序

       修改MySQL句的結束符：

       DELIMITER //

       CREATE PROCEDURE removeUserByID(IN id INT UNSIGNED) BEGIN DELETE FROM users3WHERE

 id=id; END//

       DELIMITER ;

       調用預存程序

       CALL removeUserByID(3);

       SELECT * FROM users3;

       修改預存程序的文法結構：

       ALTER PROCEDURE sp_name [chatacteristic ...] COMMENT ‘string‘ | { CONTAINS SQL | NO SQL | READS 

SQL DATA | MODIFIES SQL DATA} | SQL SECURITY { DEFINER | INVOKER };

       刪除預存程序的文法結構：       DROP PROCEDURE [IF EXISTS] sp_name;

       刪除預存程序例子：

       DROP PROCEDURE removeUserByID;

       重新向users3資料表中插入記錄

       INSERT users3 VALUES(NULL,‘Tom‘,‘123‘,25,1);

       INSERT users3 VALUES(NULL,‘John‘,‘223‘,DEFAULT,0);

       INSERT users3 VALUES(DEFAULT,‘Rose‘,‘323‘,25,1);

       INSERT users3 VALUES(DEFAULT,‘Paul‘,‘123‘,23,1);

       INSERT users3 VALUES(DEFAULT,‘Jord‘,‘123‘,23,1);

       INSERT users3 VALUES(DEFAULT,‘Lee‘,‘123‘,23,1);

       INSERT users3 VALUES(DEFAULT,‘Jams‘,‘123‘,23,1);

       INSERT users3 VALUES(NULL,‘Dave‘,‘456‘,23,0);

       INSERT users3 VALUES(NULL,‘Jack‘,‘456‘,24,1);

       建立一個刪除資料表中一條記錄的預存程序        DELIMITER //

        CREATE PROCEDURE removeUserByID(IN p_id INT UNSIGNED) BEGIN DELETE FROM users3WHERE 

id=p_id; END//

       DELIMITER ;

       SELECT * FROM users3;

       調用預存程序

       CALL removeUserByID(12);

       SELECT * FROM users3;

       (3)建立帶有IN和OUT型別參數的預存程序       建立一個刪除一條記錄並且返回剩餘記錄總數的預存程序

       DELIMITER //

       CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT 

UNSIGNED) BEGIN DELETE FROM users3 WHERE id=p_id; SELECT count(id) FROM users3 INTO userNums;

 END//

       DELIMITER ;

       SELECT count(id) FROM users3;

       調用預存程序

       CALL removeUserAndReturnUserNums(14,@nums);

       SELECT @nums;

       以@符號開頭的字元序列是指使用者變數，也就是使用者在MySQL用戶端定義的變數，一般用在BEGIN AND塊中，

對預存程序傳入的參數進行聲明、定義等。

       (3)建立帶有多個OUT型別參數的預存程序

       得到被影響的行數的命令是：SELECT ROW_COUNT():

       建立通過age欄位刪除記錄並且返回刪除的記錄數和剩餘記錄數的預存程序

       DELIMITER //

       CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age INT UNSIGNED,OUT deleteUsers 

SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED) BEGIN DELETE FROM users3 WHERE

 age=p_age; SELECT ROW_COUNT()  INTO deleteUsers; SELECT COUNT(id) FROM users3 INTO userCounts;

 END//

       DELIMITER ;

       SELECT * FROM users3;

       SELECT COUNT(id) FROM users3 WHERE age=23;

       調用預存程序(刪除age=23的記錄)

       CALL removeUserByAgeAndReturnInfos(23,@a,@b);

       SELECT @a;

       SELECT @b;

       SELECT * FROM users3;

       SELECT @a,@b;

       三預存程序與自訂函數的區別        預存程序和自訂函數的區別：

       1)預存程序實現的功能要複雜一些；而函數的針對性更強。

       2)預存程序可以返回多個值；函數只能有一個返回值。

       3)預存程序一般獨立的來執行；而函數可以作為其他SQL語句的組成部分來出現。
       使用預存程序注意事項

       1)建立預存程序或者自訂函數時需要通過DELIMITER語句修改定界符。

       2)如果函數體或過程體有多個語句，需要包含在BEIGIN...AND語句塊中。

       3)預存程序通過CALL關鍵字來調用。

MySQL學習21：初始預存程序