mysql存储过程使用说明详解

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

MySQL存储过程的优点

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

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

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

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

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

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

1. CREATEPROCEDUREproEntpTypeInfo(iidint(11),lvlint)
2. BEGIN
3. --局部变量定义
4. declaretidint(11)default-1;
5. declarettype_namevarchar(255)default'';
6. declaretptype_idint(11)default-1;
7. --游标定义
8. declarecur1CURSORFORselectid,type_name,ptype_idfromentp_type_infowhere(ptype_id=iidorid=iid)andtype=20andis_del=0;
9. --游标介绍定义
10. declareCONTINUEHANDLERFORSQLSTATE'02000'SETtid=null,ttype_name=null,tptype_id=null;
11. SET@@max_sp_recursion_depth=13;
13. --开游标
14. OPENcur1;
15. FETCHcur1INTOtid,ttype_name,tptype_id;
17. WHILE(tidisnotnull)
18. DO
19. insertintotmp_entp_type_infovalues(tid,ttype_name,tptype_id,lvl);
20. --树形结构数据递归收集到建立的临时表中
21. callproEntpTypeInfo(tid,lvl+1);
22. FETCHcur1INTOtid,ttype_name,tptype_id;
23. ENDWHILE;
24. END;
26. dropprocedureifexistsproEntpTypeInfo;
27. droptemporarytableifexiststmp_entp_type_info;
28. createtemporarytableifnotexiststmp_entp_type_info(idint(20),type_namevarchar(255),fidint(11),lvlint);
29. callproEntpTypeInfo(7,0);
30. select*fromtmp_entp_type_info;

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

1. //部门信息表
2. CREATETABLE`dept`(
3. `name`char(255)CHARACTERSETutf8NOTNULLDEFAULTNULL,
4. `alias`char(255)CHARACTERSETutf8DEFAULTNULL,
5. PRIMARYKEY(`name`)
6. )ENGINE=MyISAMDEFAULTCHARSET=utf8;
8. //用户数据表
9. CREATETABLE`users`(
10. `id`int(10)unsignedNOTNULLAUTO_INCREMENT,
11. `username`char(255)CHARACTERSETutf8DEFAULTNULL,
12. `gender`enum('男','女')CHARACTERSETutf8DEFAULT'男',
13. `dept`char(255)CHARACTERSETutf8DEFAULTNULL,
14. `dept_alias`char(255)DEFAULTNULL,
15. PRIMARYKEY(`id`),
16. KEY`index_dept`(`dept`)USINGBTREE
17. )ENGINE=MyISAMDEFAULTCHARSET=utf8;
19. //测试存储过程
20. DROPPROCEDUREIFEXISTStestProcedure;
21. CREATEPROCEDUREtestProcedure()
22. BEGIN
23. DECLAREflagINTDEFAULT0;
24. DECLAREtIDINT;
25. DECLAREtDeptCHAR(255);
26. DECLAREtAliasCHAR(20);
27. DECLAREcurCURSORFORSELECTid,deptFROMusers;
28. DECLARECONTINUEHANDLERFORNOTFOUNDSETflag=1;
29. OPENcur;
30. FETCHcurINTOtID,tDept;
31. WHILEflag<>1DO
32. SELECTaliasFROMdeptWHEREname=tDeptINTOtAlias;
33. UPDATEusersSETdept_alias=tAliasWHEREid=tID;
34. FETCHcurINTOtID,tDept;
35. ENDWHILE;
36. CLOSEcur;
37. END

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

1. --4.25s
2. UPDATEusersASuSETu.dept_alias=(SELECTaliasFROMdeptWHEREname=u.dept);

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

1. //time:17.667736053467s
2. //memory:55128bytes(不包含MySQL内存，仅供参考)
3. mysql_connect('127.0.0.1','root','develop')ORdie('ConnectFailure');
4. mysql_select_db('test')ORdie('SELECTDBError!');
5. mysql_query('SETNAMESutf8;');
6. $t1=getMicrotime();
7. mysql_query('CALLtestProcedure();');
8. $t2=getMicrotime();
9. var_dump($t2-$t1,memory_get_usage());
10. mysql_close();
11. --phpfensi.com
12. functiongetMicrotime(){
13. list($usec,$sec)=explode("",microtime());
14. return((float)$usec+(float)$sec);
15. }