MYSQL預存程序和函數學習筆記

標籤：io   ar   os   使用   sp   for   strong   資料   on   

學至Tarena金牌講師何山，金色晨曦科技公司技術總監沙利穆課程筆記的綜合。

1. 什麼是預存程序和函數

將SQL語句放入一個集合裡，然後直接調用預存程序和函數來執行已經定義好的SQL語句，通過預存程序和函數，可以避免開發人員重複編寫相同的SQL語句。

MYSQL預存程序和函數是儲存在伺服器中，在伺服器中儲存和執行，可以減少用戶端和伺服器端之間資料轉送的消耗。

預存程序就是一組已經儲存在資料庫中的語句，並且可以隨時地調用。

 

預存程序允許標準組件式編程，預存程序在被建立以後可以在程式中被多次調用而不必重新編寫該預存程序的SQL語句，而且資料庫專業人員可隨時對預存程序進行修改，但對應用程式原始碼毫無影響。因為應用程式原始碼只包含預存程序的調用語句從而極大地提高了程式的可移植性。

 

1.1 什麼時候要用到預存程序（預存程序的特點）：

（1）       預存程序是在伺服器端啟動並執行，它的執行速度比較快。

（2）       預存程序執行一次後，就會駐留在高處緩衝儲存空間中，在以後的操作中，只需要從高處緩衝儲存空間中調用已經編譯好的二進位代碼來執行，這樣就能提高系統的效能和回應時間。

（3）       使用預存程序，可以確保資料庫的安全，因為使用預存程序可以完成資料庫的所有操作，因為可以把想要進行的操作都放入SQL語句中，然後通過編程的方式來控制對資料庫的存取權限。

 

1.2 利用mysql的預存程序比單獨執行mysql的優勢在哪裡？好處有什嗎？

 

電腦上調用Transaction-SQL 編寫的一段程式原因在於預存程序具有以下優點

 

1 預存程序允許標準組件式編程

    預存程序在被建立以後可以在程式中被多次調用，而不必重新編寫該預存程序的SQL語句，而且資料庫專業人員可隨時對預存程序進行修改，但對應用程式原始碼毫無影響，因

 為應用程式原始碼只包含預存程序的調用語句，從而極大地提高了程式的可移植性

 

 

2 預存程序能夠實現較快的執行速度

    如果某一操作包含大量的Transaction-SQL 代碼，或分別被多次執行那麼預存程序要比批處理的執行速度快很多。因為預存程序是先行編譯的，在首次運行一個預存程序時，查詢最佳化工具對其進行分析最佳化，並給出最終被存在系統資料表中的執行計畫，而批處理的Transaction-SQL 陳述式在每次運行時都要進行編譯和最佳化，因此速度相對要慢一些

 

 

3 預存程序能夠減少網路流量

   對於同一個針對資料資料庫物件的操作，如查詢、修改，如果這一操作所涉及到的Transaction-SQL 陳述式被組織成一預存程序，那麼當在客戶電腦上調用該預存程序時，網路中傳送的只是該調用語句，否則將是多條SQL 陳述式從而大大增加了網路流量降低網路負載

 

 

4 預存程序可被作為一種安全機制來充分利用

    系統管理員通過對執行某一預存程序的許可權，進行限制從而能夠實現對相應的資料存取權限的限制，避免非授權使用者對資料的訪問，保證資料的安全。 

 

2. 建立預存程序：

 

CREATE PROCEDURE sp_name

([proc_parameter[,…]])

[characteristic]

Routine_body

 

說明：

Sp_name：預存程序名稱，自訂，盡量起一個有意義的名稱

([proc_parameter[,…]]):需要接收或者輸出的參數

[characteristic]：特性

Routine_body：要執行的代碼，寫在BEGIN和END中，BEGIN和END類似於函數體的{}

 

3.預存程序的參數：

3.1 輸入輸出的參數

輸入輸出的參數 參數的名稱 參數的類型

3.1.1輸入輸出的參數包括：

IN:輸入，把外界的資料傳遞到預存程序當中

OUT:輸出，把預存程序的運算結果傳遞到外界

INOUT:輸入輸出，既可以把外界的資料傳遞到預存程序當中，又可以把預存程序的運算結果傳遞到外界

3.1.2參數的類型

可以是MYSQL資料庫中的任意類型

3.1.3 特性

LANGUAGE SQL：預設的，說明Routine_body部分是由SQL語句註冊，即資料庫預設的語言

DETERMINISTIC：指明預存程序執行的結果是確定的，每次執行預存程序的時候，相同的輸入會得到相同的輸出。

NOT DETERMINISTIC：指明預存程序執行的結果不是確定的，每次執行預存程序的時候，相同的輸入會得到不同的輸出，預設情況下，結果是非確定的。

子查詢使用SQL語句的限制：

CONTAINS SQL：表示子程式中可以包含SQL語句，但不包含讀或寫資料。預設情況下使用該限制。

NO SQL:不包含SQL語句

READS SQL DATA:包含查詢資料的語句

MODIFIES SQL DATA:包含寫資料的語句

--

SQL SECURITY DEFINER / INVOKER：誰有許可權來執行這個預存程序，DEFINER（預設）表示只有定義者自己可以執行，INVOKER表示調用者可以執行。

3.1.4 注釋

COMMENT  ‘string’

‘string’：注釋資訊，可以在建立預存程序的時候指定注釋。

4. 改變預存程序預設的定界符

通過DELIMITER來改變

例子：

DELIMITER //

CREATE PROCEDURE sp_demo1()

BEGIN

SELECT * FROM users2;

END

//

DELIMITER ;.

Query OK, 0 rows affected (0.28 sec)

說明：因為預存程序也包含了很多SQL語句，而這些SQL語句也都是以分號結尾的，為了避免定界符的衝突，所以使用DELINITER來改變定界符

注意：

（1）DELIMITER與定界符之間，一定要有一個空格，否則設定將無效。

（2）要注意每次建立預存程序結束後，要將定界符恢複為分號，這是一個好的習慣。

 

5.建立帶參數的預存程序：

 

DELIMITER //

CREATE PROCEDURE age_from_user2(IN user_id INT,OUT user_age INT)

READS SQL DATA

BEGIN

SELECT age INTO user_age FROM user

WHERE id=user_id;

END

//

DELIMITER ;.

 

說明：

（1） INT表示參數的傳回值，或者說是參數的資料類型

（2）INTO 參數名：表示將SQL語句執行的結果賦給INTO後面的參數中

 

 

建立IN參數的預存程序的例子：

查詢訂貨量大於外界所傳遞進來的參數p_in的訂單資料

DELIMITER //

CREATE PROCEDURE proc2(IN p_in INT)

BEGIN

SELECT * FROM `order` WHERE onum>p_in;

END

//

DELIMITER ;

 

建立帶有OUT輸出參數的例子：

DELIMITER //

CREATE PROCEDURE proc3(OUT p_out INT )

BEGIN

SELECT count(*) INTO p_out FROM custom;

END

//

DELIMITER ;

 

說明：INTO 參數名：表示將count(*)的結果賦給INTO後面的參數中

 

建立帶有INOUT輸入輸出參數的預存程序：

DELIMITER //

CREATE PROCEDURE proc4(INOUT p_io INT)

BEGIN

SET p_io=5;

END

//

DELIMITER ;

 

6.建立儲存函數

 

預存程序與儲存函數本質上是相同的，都屬於儲存程式，也就是儲存在資料庫當中的程式，用的時候，都可以隨時調用。

 

6.1 預存程序與儲存函數的區別

（1）預存程序可以指定IN、OUT參數，儲存函數不需要指定輸入輸出參數，儲存函數所有的參數都屬於IN參數。

（2）儲存函數可以通過RETURN語句將運算的結果返回，但是預存程序不允許調用RETURN語句，預存程序可以通過調用OUT參數，將運算的結果返回給外界。

 

6.2 建立儲存函數

CREATE FUNCTION  func_name ([func_parameter[…]])

RETURNS type

[characteristic…]

Routine_body

 

說明：

RETURN type：表示傳回值的類型

[characteristic…]：表示函數的特性，與預存程序的特性一致

 

例子：

 

DELIMITER //

CREATE FUNCTION username_from_user(user_id INT)

RETURNS VARCHAR(20)

BEGIN

RETURN (SELECT username FROM users2 WHERE id=user_id);

END

//

DELIMITER ;

說明：

RETURNS 是指定傳回值的資料類型

RETURN() 是將返回結果反饋給外界。

 

 

7.調用預存程序

 

CALL sp_name ([parameter[,…]])

 

注意：

（1）       調用的時候，一定要有執行的許可權

（2）       調用之後，系統執行預存程序的語句，然後將輸出結果返回

 

例子：

CALL sp_demo1();

 

7.1調用有參數的預存程序：

 

先建立一個預存程序：

DELIMITER //

CREATE PROCEDURE age_from_user2(IN user_id INT,OUT user_age INT)

READS SQL DATA

BEGIN

SELECT age INTO user_age FROM user

WHERE id=user_id;

END

//

DELIMITER ;

 

然後調用這個預存程序，注意，變數用@變數名稱

CALL age_from_user2(120,@use_age);

 

查看變數的值：

mysql> SELECT @use_age;

+----------+

| @use_age |

+----------+

|       22 |

+----------+

1 row in set (0.00 sec)

 

7.2調用有IN參數的預存程序：

建立IN參數的預存程序的例子：

查詢訂貨量大於外界所傳遞進來的參數p_in的訂單資料

DELIMITER //

CREATE PROCEDURE proc2(IN p_in INT)

BEGIN

SELECT * FROM `order` WHERE onum>p_in;

END

//

DELIMITER ;

調用有IN參數的預存程序：

SET @num=10;

CALL proc2(@num);

 

7.3帶有OUT參數的預存程序的調用

建立帶有OUT輸出參數的例子：

DELIMITER //

CREATE PROCEDURE proc3(OUT p_out INT )

BEGIN

SELECT count(*) INTO p_out FROM custom;

END

//

DELIMITER ;

 

說明：INTO 參數名：表示將count(*)的結果賦給INTO後面的參數中

調用預存程序：

mysql> SET @amount=0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> CALL proc3(@amount);

Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT @amount;

+---------+

| @amount |

+---------+

|       4 |

+---------+

1 row in set (0.00 sec)

 

7.4 調用有INOUT參數的預存程序

建立帶有INOUT輸入輸出參數的預存程序：

DELIMITER //

CREATE PROCEDURE proc4(INOUT p_io INT)

BEGIN

SET p_io=5;

END

//

DELIMITER ;

 

調用有INOUT參數的預存程序：

 

mysql> SET @num=-1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> CALL proc4(@num);

Query OK, 0 rows affected (0.00 sec)

 

mysql> SELECT @num;

+------+

| @num |

+------+

|    5 |

+------+

1 row in set (0.00 sec)

 

8.調用儲存函數

調用儲存函數與調用系統函數的格式是一樣的。

SELECT func_name([parameter[,…]]);

 

例子：

建立儲存函數：

DELIMITER //

CREATE FUNCTION username_from_user(user_id INT)

RETURNS VARCHAR(20)

BEGIN

RETURN (SELECT username FROM users2 WHERE id=user_id);

END

//

DELIMITER ;

 

調用儲存函數：

mysql> SELECT username_from_user(120);

+-------------------------------------+

| username_from_user(120) |

+-------------------------------------+

| nihao                  |

+-------------------------------------+

1 row in set (0.05 sec)

 

調用儲存函數例子：

先建立儲存函數

DELIMITER //

CREATE FUNCTION func1(id CHAR)

RETURNS VARCHAR(10)

BEGIN

RETURN(SELECT cname FROM custom WHERE cid=id);

END

//

DELIMITER ;

 

調用儲存函數：

mysql> SET @id=‘110002‘;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SET @name=‘‘;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SELECT func1(@id) INTO @name;

Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT @name;

+--------------------------------------------+

| @name                    |

+--------------------------------------------+

| 湖北眾合糧油工業有限公司 |

+--------------------------------------------+

1 row in set (0.00 sec)

 

 

 

9. 查看已建立好的預存程序和函數：

 

查看預存程序：SHOW PROCEDURE STATUS LIKE ‘sp_name’;

查看儲存函數：SHOW FUNCTION STATUS LIKE ‘func_name’;

查看全部預存程序：SHOW PROCEDURE STATUS \G

查看全部儲存函數：SHOW FUNCTION STATUS \G

 

注意：‘sp_name’和‘func_name’一定要加引號。

 

例：

SHOW PROCEDURE STATUS LIKE ‘age_from_user3‘;

SHOW PROCEDURE STATUS LIKE ‘age_from_user3‘\G; （按行輸出）

*************************** 1. row ***************************

                  Db: homework （在哪個資料庫下）

                Name: age_from_user3 （預存程序的名字）

                Type: PROCEDURE （預存程序的類型）

             Definer: [email protected] （預存程序的定義者）

            Modified: 2014-11-13 20:48:11 （預存程序的修改時間）

             Created: 2014-11-13 20:48:11  （預存程序的建立時間）

       Security_type: DEFINER （安全類型，誰可以執行這個預存程序）

             Comment: （注釋）

character_set_client: gbk （用戶端的字元集）

collation_connection: gbk_chinese_ci （校正字元集）

  Database Collation: utf8_bin （資料庫的字元集）

1 row in set (0.00 sec)

 

查看儲存函數：

 

SHOW FUNCTION STATUS LIKE ‘username_from_user‘;

SHOW FUNCTION STATUS LIKE ‘username_from_user‘\G;

*************************** 1. row ***************************

                  Db: homework

                Name: username_from_user

                Type: FUNCTION

             Definer: [email protected]

            Modified: 2014-11-13 20:40:10

             Created: 2014-11-13 20:40:10

       Security_type: DEFINER

             Comment:

character_set_client: gbk

collation_connection: gbk_chinese_ci

  Database Collation: utf8_bin

1 row in set (0.00 sec)

 

 

10.查看預存程序和儲存函數的定義

 

SHOW CREATE PROCEDURE sp_name;

SHOW CREATE FUNCTION func_name;

 

例子：

SHOW CREATE PROCEDURE age_from_user3\G;

*************************** 1. row ***************************

           Procedure: age_from_user3

            sql_mode: NO_ENGINE_SUBSTITUTION  (SQL類型)

    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `age_from_user3`(IN user_id INT,OUT user_age INT)

    READS SQL DATA

BEGIN

SELECT age INTO user_age FROM users2

WHERE id=user_id;

END

character_set_client: gbk

collation_connection: gbk_chinese_ci

  Database Collation: utf8_bin

1 row in set (0.00 sec)

 

查看函數的建立例子：

mysql> SHOW CREATE FUNCTION username_from_user\G;

*************************** 1. row ***************************

            Function: username_from_user

            sql_mode: NO_ENGINE_SUBSTITUTION

     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `username_from_user`(user_id INT) RETURNS varchar(20) CHARSET utf8 COLLATE utf8_bin

BEGIN

RETURN (SELECT username FROM users2 WHERE id=user_id);

END

character_set_client: gbk

collation_connection: gbk_chinese_ci

  Database Collation: utf8_bin

1 row in set (0.00 sec)

 

11. 查看資料庫information_schema中的預存程序和函數

資料庫information_schema儲存了所有的預存程序和儲存函數，均儲存在了ROUTINES表中。

（1）       切換資料庫：mysql> USE information_schema;

（2）       查看ROUTINES表：mysql> SELECT * FROM ROUTINES\G

 

或者使用：SELECT * FROM information_schema.routines \G

 

可以根據查詢結果的選項內容，進行條件查詢：

SELECT * FROM information_schema.routines WHERE routine_type=‘FUNCTION‘ \G

 

12.修改預存程序和函數的屬性

我們需要明白這裡的修改不是修改其中的SQL語句，而是修改它的安全性以及資料訪問。我們也可以通過用戶端工具進行查看和修改。

 

ALTER PROCEDURE sp_name  [COMMENT ‘string’];

ALTER FUNCTION func_name  [COMMENT ‘string’];

 

 

修改預存程序：

mysql> ALTER PROCEDURE sp_demo1 COMMENT ‘THIS IS A TEST‘;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SHOW PROCEDURE STATUS LIKE ‘sp_demo1‘\G;

 

修改儲存函數：

ALTER FUNCTION  username_from_user COMMENT‘THIS IS A TEST OF FUCTION‘;

mysql> SHOW FUNCTION STATUS LIKE ‘username_from_user‘\G;

 

12.1 預存程序的安全性

安全類型有兩個：

DEFINER:定義者，定義這個預存程序的人可以執行它，預設

INVOKER:調用者，調用這個預存程序的人可以執行它

SQL 資料訪問選項有4個：

CONTAINS SQL:預存程序或者函數包含SQL語句

NO SQL: 預存程序或者函數不包含SQL語句

READS SQL DATA: 預存程序或者函數的SQL語句是讀資料庫的資料

MODIFIES SQL DATA: 預存程序或者函數的SQL語句是修改資料庫的資料

 

12.2 修改預存程序和函數的安全性

修改之前先查看一下屬性值：

SELECT * FROM information_schema.routines \G

 

然後使用ALTER 語句修改預存程序proc2的安全類型和資料訪問選項：

ALTER PROCEDURE proc2

MODIFIES SQL DATA

SQL SECURITY INVOKER;

 

SHOW PROCEDURE STATUS LIKE ‘proc2‘\G

SELECT * FROM information_schema.routines \G

 

 

13. 刪除預存程序及儲存函數

 

DROP PROCEDURE sp_name;

DROP FUNCTION sp_name;

 

當刪除不存在的預存程序或者儲存函數的時候，會顯示報錯：

mysql> DROP PROCEDURE sp_demo1;

ERROR 1305 (42000): PROCEDURE homework.sp_demo1 does not exist

 

如果想屏蔽錯誤，以警告的形式提示，可以使用：

 

DROP PROCEDURE IF EXISTS  sp_name;

DROP FUNCTION IF EXISTS  sp_name;

 

例子：

mysql> DROP PROCEDURE IF EXISTS  sp_name;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

查看警告的內容：SHOW WARNINGS;

 

 

mysql> SHOW WARNINGS;

+-------+------+-------------------------------------------------------------------------+

| Level | Code | Message                                   |

+-------+------+-------------------------------------------------------------------------+

| Note | 1305 | PROCEDURE homework.sp_name does not exist   |

+-------+------+-------------------------------------------------------------------------+

1 row in set (0.01 sec)

 

刪除預存程序例子：

 

mysql> DROP PROCEDURE sp_demo1;

Query OK, 0 rows affected (0.05 sec)

 

mysql> SHOW CREATE PROCEDURE sp_demo1;

ERROR 1305 (42000): PROCEDURE sp_demo1 does not exist

 

刪除儲存函數例子：

DROP FUNCTION username_from_user;

MYSQL預存程序和函數學習筆記