mysql 存儲過程使用說明詳解

本文章來給各位同學介紹一下mysql 存儲過程一些使用方法與入門基本教程，有需要了解mysql 存儲過程的朋友可參考，但只有在mysql5才支持存儲過程哦。

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);
}