mysql存储过程使用说明详解

发布:smiling　来源: PHP粉丝网　添加日期：2014-10-04 11:44:51　浏览: 评论：0　

本文章来给各位同学介绍一下mysql 存储过程一些使用方法与入门基本教程,有需要了解mysql 存储过程的朋友可参考,但只有在mysql5才支持存储过程.

MySQL存储过程的优点

预编译,相对于直接的SQL效率会高点,同时可以降低SQL语句传输过程中消耗的流量；

简化业务逻辑,可以把需求转化给专业的DBA(如果有的话)；更方便的使用MySQL数据库事物的处理,尤其是购物类网站；

安全、用户权限更容易管理；修改存储过程基本上不需要修改程序代码，而直接写SQL修改SQL一般都要修改相关的程序.

mysql储存过程的创建等语句:

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

--phpfensi.com

function getMicrotime() {

list( $usec, $sec ) = explode(" ", microtime());

return ((float)$usec + (float)$sec);

}