MySQL預存程序和暫存資料表

標籤：

MySQL建立預存程序

MySQL中，建立預存程序的基本形式如下：

CREATE PROCEDURE sp_name ([proc_parameter[,...]])          [characteristic ...] routine_body 

其中，sp_name參數是預存程序的名稱；proc_parameter表示預存程序的參數列表； characteristic參數指定預存程序的特性；routine_body參數是SQL代碼的內容，可以用BEGIN…END來標誌SQL代碼的開始和結束。

• proc_parameter

proc_parameter中的每個參數由3部分組成。這3部分分別是輸入輸出類型、參數名稱和參數類型。其形式如下：

[ IN | OUT | INOUT ] param_name type 

其中，IN表示輸入參數；OUT表示輸出參數； INOUT表示既可以是輸入，也可以是輸出； param_name參數是預存程序的參數名稱；type參數指定預存程序的參數類型，該類型可以是MySQL資料庫的任意資料類型。

• characteristic

characteristic參數有多個取值。其取值說明如下：

1. LANGUAGE SQL：說明routine_body部分是由SQL語言的語句組成，這也是資料庫系統預設的語言。
2. [NOT] DETERMINISTIC：指明預存程序的執行結果是否是確定的。DETERMINISTIC表示結果是確定的。每次執行預存程序時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結果是非確定的，相同的輸入可能得到不同的輸出。預設情況下，結果是非確定的。
3. { 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。
4. SQL SECURITY { DEFINER | INVOKER }：指明誰有許可權來執行。DEFINER表示只有定義者自己才能夠執行；INVOKER表示調用者可以執行。預設情況下，系統指定的許可權是DEFINER。
5. COMMENT ‘string‘：注釋資訊。

【樣本14-1】 下面建立一個名為num_from_employee的預存程序。代碼如下：

DROP PROCEDURE IF EXISTS num_from_employee ;DELIMITER //CREATE  PROCEDURE  num_from_employee (    IN emp_id INT,     OUT count_num INT)   READS SQL DATA  BEGIN      SELECT  COUNT(*)  INTO  count_num      FROM  employee      WHERE  d_id=emp_id ;  END //DELIMITER ;

說明：MySQL中預設的語句結束符為分號（;）。預存程序中的SQL語句需要分號來結束。為了避免衝突，首先用"DELIMITER //"將MySQL的結束符設定為//。最後再用"DELIMITER ;"來將結束符恢複成分號。這與建立觸發器時是一樣的。

 

MySQL 建立儲存函數

在MySQL中，建立儲存函數的基本形式如下：

CREATE FUNCTION sp_name ([func_parameter[,...]])          RETURNS type          [characteristic ...] routine_body 

其中，sp_name參數是儲存函數的名稱；func_parameter表示儲存函數的參數列表；RETURNS type指定傳回值的類型；characteristic參數指定儲存函數的特性，該參數的取值與預存程序中的取值是一樣的；routine_body參數是SQL代碼的內容，可以用BEGIN…END來標誌SQL代碼的開始和結束。

func_parameter

func_parameter可以由多個參數組成，其中每個參數由參數名稱和參數類型組成，其形式如下：param_name type 

其中，param_name參數是儲存函數的參數名稱；type參數指定儲存函數的參數類型，該類型可以是MySQL資料庫的任意資料類型。

【樣本14-2】 下面建立一個名為name_from_employee的儲存函數。代碼如下：

DELIMITER //

CREATE  FUNCTION  name_from_employee (emp_id INT )  　　RETURNS VARCHAR(20)  BEGIN  　　RETURN  (SELECT  name  FROM  employee  WHERE  num=emp_id );  END //

DELIMITER ;

MySQL暫存資料表

首先，暫存資料表只在當前串連可見，當關閉串連時，Mysql會自動刪除表並釋放所有空間。因此在不同的串連中可以建立同名的暫存資料表，並且操作屬於本串連的暫存資料表。

建立暫存資料表的文法與建立表文法類似，不同之處是增加關鍵字TEMPORARY，如：

CREATE TEMPORARY TABLE 表名 (…. )

暫存資料表使用有一些限制條件：

　　*  暫存資料表在 memory、myisam、merge或者innodb上使用，並且不支援mysql cluster簇)；

               show tables語句不會列出暫存資料表，在information_schema中也不存在暫存資料表資訊；show create table可以查看暫存資料表；

　　*  不能使用rename來重新命名暫存資料表。但是可以alter table rename代替：

            　mysql>ALTER TABLE orig_name RENAME new_name;

　　*  可以複製暫存資料表得到一個新的暫存資料表，如：

                mysql>create temporary table new_table select * from old_table;

　　*  但在同一個query語句中，相同的暫存資料表只能出現一次。如：

               可以使用：mysql> select * from temp_tb;

               但不能使用：mysql> select * from temp_tb, temp_tb as t;

               錯誤資訊：   ERROR 1137 (HY000): Can‘t reopen table: ‘temp_tb‘

        　　同樣相同暫存資料表不能在儲存函數中出現多次，如果在一個儲存函數裡，用不同的別名尋找一個暫存資料表多次，或者在這個儲存函數裡用不同的語句尋找，都會出現這個錯誤。

　　*  但不同的暫存資料表可以出現在同一個query語句中，如暫存資料表temp_tb1, temp_tb2：

                 Mysql> select * from temp_tb1, temp_tb2;

      *  暫存資料表可以手動刪除：

                 DROP TEMPORARY TABLE IF EXISTS temp_tb;

在建立暫存資料表時宣告類型為HEAP，則Mysql會在記憶體中建立該暫存資料表，即記憶體表：如：

CREATE TEMPORARY TABLE 表名 (。。。。) TYPE = HEAP

因為HEAP表格儲存體在記憶體中，你對它啟動並執行查詢可能比磁碟上的暫存資料表快些。如：

mysql> create temporary table temp_tb type=‘heap‘ select * from temptb;

CREATE TEMPORARY TABLE `temp_tb` (  `id` int(10) unsigned NOT NULL DEFAULT ‘0‘,  `Name` char(20) NOT NULL,  `Age` tinyint(4) NOT NULL) ENGINE=MEMORY DEFAULT CHARSET=gbk

暫存資料表和記憶體表

        暫存資料表主要是為了放一些中間大結果集的一些子集，記憶體表可以放一些經常頻繁使用的資料。

        *  暫存資料表：表建在記憶體裡，資料在記憶體裡
        *  記憶體表：表建在磁碟裡，資料在記憶體裡

        暫存資料表和記憶體表所使用記憶體大小可以通過My.cnf中的max_heap_table_size、tmp_table_size指定：
              [mysqld]
              max_heap_table_size=1024M   #記憶體表容量
              tmp_table_size=1024M              #暫存資料表容量

        當資料超過暫存資料表的最大值設定時，自動轉為磁碟表，此時因需要進行IO操作，效能會大大下降，而記憶體表不會，記憶體表滿後，則會提示資料滿錯誤。

       show tables 命令不會顯示暫存資料表。

        以下是對記憶體表和暫存資料表之間區別的總結：

   記憶體表：

        1．預設儲存引擎為MEMORY
        2．可以通過參數max_heap_table_size來設定記憶體表大小
        3．到達max_heap_table_size設定的記憶體上限後將報錯
        4．表定義儲存在磁碟上，資料和索引儲存在記憶體中
        5．不能包含TEXT、BLOB等欄位
   暫存資料表：

        1．預設儲存引擎為MySQL伺服器預設引擎，引擎類型只能是：memory（heap）、myisam、merge、innodb（memory暫存資料表由於表的增大可能會轉變為myisam暫存資料表）
        2．可以通過參數 tmp_table_size 來設定暫存資料表大小。
        3．到達tmp_table_size設定的記憶體上限後將在磁碟上建立臨時檔案
        4．表定義和資料都儲存在記憶體中
        5．可以包含TEXT, BLOB等欄位

        暫存資料表一般比較少用，通常是在應用程式中動態建立或者由MySQL內部根據SQL執行計畫需要自己建立。

        記憶體表則大多作為Cache來使用，特別在沒有第三方cache使用時。如今隨著memcache、NoSQL的流行，越來越少選擇使用記憶體表。

 

MySQL預存程序和暫存資料表