mysql 預存程序使用說明詳解

MySQL預存程序的優點

先行編譯，相對於直接的SQL效率會高點，同時可以降低SQL語句傳輸過程中消耗的流量；

簡化商務邏輯，可以把需求轉化給專業的DBA(如果有的話)；

更方便的使用MySQL資料庫事物的處理，尤其是購物類網站；

安全、使用者權限更容易管理；

修改預存程序基本上不需要修改程式碼，而直接寫SQL修改SQL一般都要修改相關的程式

mysql儲存過程的建立等語句：

1、CREATE PROCEDURE （建立儲存過程）

   CREATE PROCEDURE 預存程序名 (參數列表)

   BEGIN

SQL語句代碼塊

   END

註：由括弧包圍的參數列必須總是存在。如果沒有參數，也該使用一個空參數列()。每個參數預設都是一個IN參數。要指定為其它參數，可在參數名之前使用關鍵詞 OUT或INOUT在mysql用戶端定義預存程序的時候使用delimiter命令來把語句定界符從;變為//。 當使用delimiter命令時，你應該避免使用反斜線(‘’)字元，因為那是MySQL的逸出字元。

代碼如下

複製代碼

CREATE PROCEDURE proEntpTypeInfo(iid int(11),lvl int)  BEGIN  -- 局部變數定義  declare tid int(11) default -1 ;  declare ttype_name varchar(255) default '' ;  declare tptype_id int(11) default -1 ;  -- 遊標定義  declare cur1 CURSOR FOR select id,type_name,ptype_id from entp_type_info where (ptype_id=iid or id=iid)and type = 20 and is_del = 0;  -- 遊標介紹定義  declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tid = null,ttype_name=null,tptype_id=null;  SET @@max_sp_recursion_depth = 13;  -- 開遊標  OPEN cur1;  FETCH cur1 INTO tid,ttype_name,tptype_id;  WHILE ( tid is not null )  DO  insert into tmp_entp_type_info values(tid,ttype_name,tptype_id,lvl);  -- 樹形結構資料遞迴收集到建立的暫存資料表中  call proEntpTypeInfo(tid,lvl+1);  FETCH cur1 INTO tid,ttype_name,tptype_id ;  END WHILE;  END; drop procedure if exists proEntpTypeInfo;  drop temporary table if exists tmp_entp_type_info;  create temporary table if not exists tmp_entp_type_info(id int(20),type_name varchar(255), fid int(11),lvl int); call proEntpTypeInfo(7,0);  select * from tmp_entp_type_info ;

下面是一個簡單的測試，一個dept表，1-1000個部門，和部門的別名；一個users表，200000個使用者，隨機屬於1000個部門中的一個；假設users表中只有部門名稱，沒有部門名稱別名，在users表中添加此欄位`dept_alias`後根據dept表更新`dept_alias`的值：

代碼如下

複製代碼

//部門資訊表 CREATE TABLE `dept` (   `name` char(255) CHARACTER SET utf8 NOT NULL DEFAULT NULL,   `alias` char(255) CHARACTER SET utf8 DEFAULT NULL,   PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;     //使用者資料表 CREATE TABLE `users` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `username` char(255) CHARACTER SET utf8 DEFAULT NULL,   `gender` enum('男','女') CHARACTER SET utf8 DEFAULT '男',   `dept` char(255) CHARACTER SET utf8 DEFAULT NULL,   `dept_alias` char(255) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `index_dept` (`dept`) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8;     //測試預存程序 DROP PROCEDURE IF EXISTS testProcedure; CREATE PROCEDURE testProcedure() BEGIN     DECLARE flag INT DEFAULT 0;     DECLARE tID INT;     DECLARE tDept CHAR(255);     DECLARE tAlias CHAR(20);     DECLARE cur CURSOR FOR SELECT id,dept FROM users;     DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;     OPEN cur;     FETCH cur INTO tID,tDept;     WHILE flag<>1 DO         SELECT alias FROM dept WHERE name = tDept INTO tAlias;         UPDATE users SET dept_alias=tAlias WHERE id=tID;         FETCH cur INTO tID,tDept;     END WHILE;     CLOSE cur; END

首先，這個需要使用下面的一條SQL語句就可以實現。

代碼如下	複製代碼
-- 4.25 s UPDATE users AS u SET u.dept_alias=(SELECT alias FROM dept WHERE name=u.dept);

不過，為了測試，先將users中的資料逐一讀出，然後一一查詢更新，使用預存程序和使用通常的查詢做法分別如下所示：

代碼如下

複製代碼

//time: 17.667736053467 s //memory: 55128 bytes (不包含MySQL記憶體，僅供參考) mysql_connect('127.0.0.1','root','develop') OR die('Connect Failure'); mysql_select_db('test') OR die('SELECT DB Error!'); mysql_query('SET NAMES utf8;'); $t1 = getMicrotime(); mysql_query('CALL testProcedure();'); $t2 = getMicrotime(); var_dump( $t2-$t1,memory_get_usage() ); mysql_close();     function getMicrotime() {     list( $usec, $sec ) = explode(" ", microtime());     return ((float)$usec + (float)$sec); }