MySQL存储过程实例教程.docx

1、MySQL存储过程实例教程MySQL存储过程实例教程MySQL 5.0以后的版本开始支持存储过程，存储过程具有一致性、高效性、安全性和体系结构等特点，本节将通过具体的实例讲解PHP是如何操纵MySQL存储过程的。实例261：存储过程的创建这是一个创建存储过程的实例录像位置：光盘mingrisoft9?lt;/p实例说明为了保证数据的完整性、一致性，提高应用的性能，常采用存储过程技术。MySQL 5.0之前的版本并不支持存储过程，随着MySQL技术的日趋完善，存储过程将在以后的项目中得到广泛的应用。本实例将介绍在MySQL 5.0以后的版本中创建存储过程。技术要点一个存储过程包括名字、参数列表，

2、以及可以包括很多SQL语句的SQL语句集。下面为一个存储过程的定义过程：create procedure proc_name (in parameterinteger)begindeclare variable varchar(20);if parameter=1 thensetvariable=MySQL;elset variable=PHP;end if;insert into tb (name) values(variable);end;MySQL中存储过程的建立以关键字create procedure开始，后面紧跟存储过程的名称和参数。MySQL的存储过程名称不区分大小写，例如PROC

3、E1()和proce1()代表同一个存储过程名。存储过程名不能与MySQL数据库中的内建函数重名。存储过程的参数一般由3部分组成。第一部分可以是in、out或inout。in表示向存储过程中传入参数；out表示向外传出参数；inout表示定义的参数可传入存储过程，并可以被存储过程修改后传出存储过程，存储过程默认为传入参数，所以参数in可以省略。第二部分为参数名。第三部分为参数的类型，该类型为MySQL数据库中所有可用的字段类型，如果有多个参数，参数之间可以用逗号进行分割。MySQL存储过程的语句块以begin开始，以end结束。语句体中可以包含变量的声明、控制语句、SQL查询语句等。由于存储过

4、程内部语句要以分号结束，所以在定义存储过程前应将语句结束标志“;”更改为其他字符，并且该字符在存储过程中出现的几率也应该较低，可以用关键字delimiter更改。例如：mysqldelimiter /存储过程创建之后，可用如下语句进行删除，参数proc_name指存储过程名。dropprocedureproc_name实现过程（1）MySQL存储过程是在“命令提示符”下创建的，所以首先应该打开“命令提示符”窗口。（2）进入“命令提示符”窗口后，首先应该登录MySQL数据库服务器，在“命令提示符”下输入如下命令：mysqlu用户名p用户密码（3）更改语句结束符号，本实例将语句结束符更改为“/”。

5、代码如下：delimiter /（4）创建存储过程前应首先选择某个数据库。代码如下：use数据库名（5）创建存储过程。（6）通过call语句调用存储过程。实例的关键技术是如何创建传入参数的存储过程，具体实现代码如下：delimiter/create procedure pro_reg (in nc varchar(50), in pwd varchar(50), in emailvarchar(50),in address varchar(50)beginsert into tb_reg (name,pwd ,email ,address) values (nc, pwd, email, ad

6、dress);end;/“delimiter /”的作用是将语句结束符更改为“/”。“in nc varchar(50)in address varchar(50)”表示要向存储过程中传入的参数。实现过程（1）通过PHP预定义类mysqli，实现与MySQL数据库的连接。代码如下：$conn=newmysqli(localhost,root,root,db_database09);$conn-query(set namesgb2312);（2）调用存储过程pro_reg，实现将用户录入的注册信息保存到数据库。代码如下：if($sql=$conn-query(callpro_reg(.$nc.,

7、.$pwd.,.$email.,.$address.)echoalert(用户注册成功!);elseecho alert(用户注册失败!);MySQL 5.0参考手册中关于创建存储过程的语法说明：CREATEDEFINER = user | CURRENT_USER PROCEDURE sp_name (proc_parameter,.)characteristic . routine_bodyproc_parameter: IN | OUT | INOUT param_name typetype:Any valid MySQL data typecharacteristic:LANGUAGE

8、 SQL| NOT DETERMINISTIC| CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY DEFINER | INVOKER | COMMENT stringroutine_body:Valid SQL procedure statement如果你对MySQL还不太熟悉的话，单单看这个语法结构当然不足以进行MySQL存储过程编程。我之前基本都是使用MS SQL SERVER，所以以下记录我熟悉MySQL存储过程的过程，也是重点介绍MS SQL SERVER与MySQL区别较大的地方。第一

9、步，当然是写个Hello Word的存储过程，如下：CREATE PROCEDURE phelloword()BEGINSELECT Hello Word! AS F;END;将上面创建phelloword存储过程的语句拷到phpMyAdmin中执行，报如下错误：#1064 - You have an error in your SQL syntax; check the manual that correspondsto your MySQL server version for the right syntax to use near at line 3在这个问题上我纠缠了很久，在MySQ

10、L的命令行工具中执行同样不成功，但是根据提示信息可以知道执行在SELECT Hello Word! AS F;处结束，后面的END;没有执行，这显然会导致错误。这里需要选择以个分隔符，语法如下：DELIMITER /分隔符是通知MySQL客户端已经输入完成的符号。一直都是用“;”，但是在存储过程中不行，因为存储过程中很多语句都需要用到分号。因此上面的存储过程改为：CREATE PROCEDURE ptest()BEGINSELECT Hello Word! AS F;END /另外在phpMyAdmin中执行时，在Delimiter文本框中填写/，这次存储过程即可创建成功。第二步，写一个包括参

11、数，变量，变量赋值，条件判断，UPDATE语句，SELECT返回结果集的完整的一个存储过程，如下：CREATE PROCEDURE plogin(p_username char(15),p_password char(32),p_ip char(18),p_logintime datetime)LABEL_PROC:BEGINDECLARE v_uid mediumint(8);DECLARE v_realpassword char(32);DECLARE v_nickname varchar(30);DECLARE v_oltime smallint(6);SELECT u.uid, u.p

12、assword, f.nickname, u.oltime INTO v_uid, v_realpassword,v_nickname, v_oltimeFROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHEREu.username = p_username;IF (v_uid IS NULL) THENSELECT 2 AS ErrorCode;LEAVE LABEL_PROC;END IF;IF (p_password v_realpassword) THENSELECT 3 AS ErrorCode;LE

13、AVE LABEL_PROC;END IF;UPDATE ipsp_userexpands SET lastloginip = p_ip, lastlogintime = p_logintimeWHERE uid = v_uid;SELECT 0 AS ErrorCode, v_uid AS uid, v_nickname AS nickname, v_oltime ASoltime;END LABEL_PROC /首先要说的是给变量赋值的语法，MySQL中使用SELECTu.uid,u.password,f.nickname,u.oltimeINTOv_uid,v_realpassword,

14、v_nickname,v_oltimeFROMcdb_membersuINNERJOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;这种方式给变量赋值。其次是条件判断的语法结构，如下所示：IF . THEN.;ELSEIF . THEN.;ELSEIF.;ELSE.;END IF;END IF;最后说说LEAVE语法的使用。当满足某种条件，不继续执行下面的SQL时，在MSSQLSERVER中使用RETURN语法，在MySQL中我没有找到对应的关键字，但是这里可以利用LEAVE语法来满足要求，在存储过程的B

15、EGIN前定义一个标签，如：“LABEL_PROC:” 然后再需要用到RETURN中断执行的地方执行“LEAVE LABEL_PROC;”即可。第三步，创建一个执行动态SQL的存储过程。CREATE PROCEDURE ipsp_getresourcedir(p_hashcode char(40)LABEL_PROC:BEGINDECLARE v_sql varchar(200);SET v_sql = CONCAT(SELECT filedir FROM ipsp_resources WHERE hashcode =,p_hashcode, LIMIT 0, 1);SET sql = v_s

16、ql;PREPARE sl FROM sql;EXECUTE sl;DEALLOCATE PREPARE sl;END LABEL_PROC /mysql存储过程的创建，删除，调用及其他常用命令mysql 5.0存储过程学习总结一.创建存储过程1.基本语法：create procedure sp_name()beginend2.参数传递二.调用存储过程1.基本语法：call sp_name()注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递三.删除存储过程1.基本语法：drop procedure sp_name/2.注意事项(1)不能在一个存储过程中删除另一个存储过程，只能调用另

17、一个存储过程四.区块，条件，循环1.区块定义，常用beginend;也可以给区块起别名，如：lable:begin.end lable;可以用leave lable;跳出区块，执行区块以后的代码2.条件语句if条件thenstatementelsestatementend if;3.循环语句(1).while循环label: WHILE expression DOstatementsEND WHILE label ;(2).loop循环label: LOOPstatementsEND LOOP label;(3).repeat until循环label: REPEATstatementsUNT

18、IL expressionEND REPEAT label ;五.其他常用命令1.show procedure status显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等2.show create procedure sp_name显示某一个存储过程的详细信息mysql存储过程中要用到的运算符mysql存储过程学习总结xx算术运算符+加SET var1=2+2; 4-减SET var2=3-2; 1*乘SET var3=3*2; 6/除DIV整除SET var5=10 DIV 3; 3%取模SET var6=10%3 ; 1比较运算符大于12 False小于2

19、1 False=小于等于2=大于等于3=2 TrueBETWEEN在两值之间5 BETWEEN 1 AND 10 TrueNOT BETWEEN不在两值之间5 NOT BETWEEN 1 AND 10 FalseIN在集合中5 IN (1,2,3,4) FalseNOT IN不在集合中5 NOT IN (1,2,3,4) True=等于2=3 False, !=不等于23 FalseLIKEREGEXPIS NULLIS NOT NULL逻辑运算符与(AND)ANDTRUEFALSENULLTRUETRUEFALSENULLFALSE严格比较两个NULL值是否相等NULLNULL True简单

20、模式匹配Guy Harrison LIKE Guy% True正则式匹配Guy Harrison REGEXP Ggreg False为空0 IS NULL False不为空0 IS NOT NULL TrueFALSEFALSENULLNULLNULLNULLNULL或(OR)ORTRUEFALSENULLTRUETRUETRUETRUEFALSETRUEFALSENULLNULLTRUENULLNULL异或(XOR)XORTRUEFALSENULLTRUEFALSETRUENULLFALSETRUEFALSENULLNULLNULLNULLNULL位运算符|位或&位与右移位位非(单目运算，

21、按位取反)mysq存储过程中常用的函数，字符串类型操作，数学类，日期时间类。mysql存储过程基本函数一.字符串类CHARSET(str) /返回字串字符集CONCAT (string2 ,. ) /连接字串INSTR (string ,substring ) /返回substring首次在string中出现的位置,不存在返回0LCASE (string2 ) /转换成小写LEFT (string2 ,length ) /从string2中的左边起取length个字符LENGTH (string ) /string长度LOAD_FILE (file_name ) /从文件读取内容LOCATE

22、(substring , string ,start_position )同INSTR,但可指定开始位置LPAD (string2 ,length ,pad ) /重复用pad加在string开头,直到字串长度为lengthLTRIM (string2 ) /去除前端空格REPEAT (string2 ,count ) /重复count次REPLACE (str ,search_str ,replace_str ) /在str中用replace_str替换search_strRPAD (string2 ,length ,pad) /在str后用pad补充,直到长度为lengthRTRIM (s

23、tring2 ) /去除后端空格STRCMP (string1 ,string2 ) /逐字符比较两字串大小,SUBSTRING (str , position ,length ) /从str的position开始,取length个字符,注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1mysql select substring(abcd,0,2);+| substring(abcd,0,2) |+| |+1 row in set (0.00 sec)mysql select substring(abcd,1,2);+| substring(abcd,

24、1,2) |+| ab |+1 row in set (0.02 sec)TRIM(BOTH|LEADING|TRAILING padding FROMstring2) /去除指定位置的指定字符UCASE (string2 ) /转换成大写RIGHT(string2,length) /取string2最后length个字符SPACE(count) /生成count个空格二.数学类ABS (number2 ) /绝对值BIN (decimal_number ) /十进制转二进制CEILING (number2 ) /向上取整CONV(number2,from_base,to_base) /进制转

25、换FLOOR (number2 ) /向下取整FORMAT (number,decimal_places ) /保留小数位数HEX (DecimalNumber ) /转十六进制注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(DEF)返回也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19LEAST (number , number2 ,.) /求最小值MOD (numerator ,denominator ) /求余POWER (number ,power ) /求指数RAND(seed) /随机数ROUND (number ,decimals ) /四舍五入,

26、decimals为小数位数注：返回类型并非均为整数，如：(1)默认变为整形值mysql select round(1.23);+-+| round(1.23) |+-+| 1 |+-+1 row in set (0.00 sec)mysql select round(1.56);+-+| round(1.56) |+-+| 2 |+-+1 row in set (0.00 sec)(2)可以设定小数位数，返回浮点型数据mysql select round(1.567,2);+-+| round(1.567,2) |+-+| 1.57 |+-+1 row in set (0.00 sec)SIG

27、N (number2 ) /返回符号,正负或0SQRT(number2) /开平方三.日期时间类ADDTIME (date2 ,time_interval ) /将time_interval加到date2CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) /转换时区CURRENT_DATE ( ) /当前日期CURRENT_TIME ( ) /当前时间CURRENT_TIMESTAMP ( ) /当前时间戳DATE (datetime ) /返回datetime的日期部分DATE_ADD (date2 , INTERVAL d_value d_type ) /在date

28、2中加上日期或时间DATE_FORMAT (datetime ,FormatCodes ) /使用formatcodes格式显示datetimeDATE_SUB (date2 , INTERVAL d_value d_type ) /在date2上减去一个时间DATEDIFF (date1 ,date2 ) /两个日期差DAY (date ) /返回日期的天DAYNAME (date ) /英文星期DAYOFWEEK (date ) /星期(1-7) ,1为星期天DAYOFYEAR (date ) /一年中的第几天EXTRACT (interval_name FROM date ) /从date中提取日期的指定部分MAKEDATE (year ,day ) /给出年及年中的第几天,生成日期串MAKETIME (hour ,minute ,second ) /生成时间串MONTHNAME (date ) /英文月份名NOW ( ) /当前时间