用户名:
密 码: 记住
您当前的位置:首页 > 网络编程 > mysql教程

mysql存储过程使用说明详解

时间:2015-01-23  来源:西部数据  作者:西部数据

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

MySQL存储过程的优点

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

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

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

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

  1. CREATE PROCEDURE (创建储存过程) 
  2.    CREATE PROCEDURE 存储过程名 (参数列表) 
  3.    BEGIN 
  4. SQL语句代码块 
  5.    END 

注:由括号包围的参数列必须总是存在,如果没有参数,也该使用一个空参数列(),每个参数默认都是一个IN参数,要指定为其它参数,可在参数名之前使用关键词 OUT或INOUT在mysql客户端定义存储过程的时候使用delimiter命令来把语句定界符从;变为//, 当使用delimiter命令时,你应该避免使用反斜杠(‘’)字符,因为那是MySQL的转义字符,代码如下:

  1. CREATE PROCEDURE proEntpTypeInfo(iid int(11),lvl int)  
  2. BEGIN  
  3. -- 局部变量定义  
  4. declare tid int(11) default -1 ;  
  5. declare ttype_name varchar(255) default '' ;  
  6. declare tptype_id int(11) default -1 ;  
  7. -- 游标定义  
  8. 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;  
  9. -- 游标介绍定义  
  10. declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tid = null,ttype_name=null,tptype_id=null;  
  11. SET @@max_sp_recursion_depth = 13;  
  12.  
  13. -- 开游标  
  14. OPEN cur1;  
  15. FETCH cur1 INTO tid,ttype_name,tptype_id;  
  16.  
  17. WHILE ( tid is not null )  
  18. DO  
  19. insert into tmp_entp_type_info values(tid,ttype_name,tptype_id,lvl);  
  20. -- 树形结构数据递归收集到建立的临时表中  
  21. call proEntpTypeInfo(tid,lvl+1);  
  22. FETCH cur1 INTO tid,ttype_name,tptype_id ;  
  23. END WHILE;  
  24. END
  25.  
  26. drop procedure if exists proEntpTypeInfo;  
  27. drop temporary table if exists tmp_entp_type_info;  
  28. create temporary table if not exists tmp_entp_type_info(id int(20),type_name varchar(255), fid int(11),lvl int); 
  29. call proEntpTypeInfo(7,0);  
  30. select * from tmp_entp_type_info ;  

下面是一个简单的测试,一个dept表,1-1000个部门,和部门的别名,一个users表,200000个用户,随机属于1000个部门中的一个,假设users表中只有部门名称,没有部门名称别名,在users表中添加此字段`dept_alias`后根据dept表更新`dept_alias`的值,代码如下:

  1. //部门信息表 
  2. CREATE TABLE `dept` ( 
  3.   `namechar(255) CHARACTER SET utf8 NOT NULL DEFAULT NULL
  4.   `alias` char(255) CHARACTER SET utf8 DEFAULT NULL
  5.   PRIMARY KEY (`name`) 
  6. ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 
  7.      
  8. //用户数据表 
  9. CREATE TABLE `users` ( 
  10.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  11.   `username` char(255) CHARACTER SET utf8 DEFAULT NULL
  12.   `gender` enum('男','女'CHARACTER SET utf8 DEFAULT '男'
  13.   `dept` char(255) CHARACTER SET utf8 DEFAULT NULL
  14.   `dept_alias` char(255) DEFAULT NULL
  15.   PRIMARY KEY (`id`), 
  16.   KEY `index_dept` (`dept`) USING BTREE 
  17. ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 
  18.      
  19. //测试存储过程 
  20. DROP PROCEDURE IF EXISTS testProcedure; 
  21. CREATE PROCEDURE testProcedure() 
  22. BEGIN 
  23.     DECLARE flag INT DEFAULT 0; 
  24.     DECLARE tID INT
  25.     DECLARE tDept CHAR(255); 
  26.     DECLARE tAlias CHAR(20); 
  27.     DECLARE cur CURSOR FOR SELECT id,dept FROM users; 
  28.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1; 
  29.     OPEN cur; 
  30.     FETCH cur INTO tID,tDept; 
  31.     WHILE flag<>1 DO 
  32.         SELECT alias FROM dept WHERE name = tDept INTO tAlias; 
  33.         UPDATE users SET dept_alias=tAlias WHERE id=tID; 
  34.         FETCH cur INTO tID,tDept; 
  35.     END WHILE; 
  36.     CLOSE cur; 
  37. END 

首先,这个需要使用下面的一条SQL语句就可以实现,代码如下:

  1. -- 4.25 s 
  2. UPDATE users AS u SET u.dept_alias=(SELECT alias FROM dept WHERE name=u.dept); 

不过,为了测试,先将users中的数据逐一读出,然后一一查询更新,使用存储过程和使用通常的查询做法分别如下所示:

  1. //time: 17.667736053467 s 
  2. //memory: 55128 bytes (不包含MySQL内存,仅供参考) 
  3. mysql_connect('127.0.0.1','root','develop'OR die('Connect Failure'); 
  4. mysql_select_db('test'OR die('SELECT DB Error!'); 
  5. mysql_query('SET NAMES utf8;'); 
  6. $t1 = getMicrotime(); 
  7. mysql_query('CALL testProcedure();'); 
  8. $t2 = getMicrotime(); 
  9. var_dump( $t2-$t1,memory_get_usage() ); 
  10. mysql_close(); 
  11. --phpfensi.com 
  12. function getMicrotime() { 
  13.     list( $usec, $sec ) = explode(" ", microtime()); 
  14.     return ((float)$usec + (float)$sec); 
  15. }
来顶一下
返回首页
返回首页
推荐资讯
WiFi太不安全:7岁女孩11分钟内入侵公共网络 WiFi太不安全:7岁女孩11分钟内入侵近期刚刚发布研究说WiFi网络能获得人们手机里多少私人信息,
不服跑个分?人工智能也出现“刷分”乱象 不服跑个分?人工智能也出现“刷分2014年,人工智能领域突然爆发,成为了科研和科技创业的热门
相关文章
栏目更新
栏目热门