MySQL存储过程实例教程

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

2、很多 SQL 语句的 SQL 语句集。下面为一个存储过程的定义过程：createprocedureproc_name(inparameterinteger)begindeclarevariablevarchar(20);ifparameter=1thensetvariable=MySQL;elsesetvariable=PHP;endif;insertintotb(name)values(variable);end;MySQL 中存储过程的建立以关键字 createprocedure 开始，后面紧跟存储过程的名称和参数。MySQL 勺存储过程名称不区分大小写，例如 PROCE1(和 proce

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

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

5、实例将语句结束符更改为“”。代码如下：delimiter/(4)创建存储过程前应首先选择某个数据库。代码如下：use 数据库名(5)创建存储过程。(6)通过 call 语句调用存储过程。实例的关键技术是如何创建传入参数的存储过程，具体实现代码如下：delimiter/createprocedurepro_reg(inncvarchar(50),inpwdvarchar(50),inemailvarchar(50),inaddressvarchar(50)begininsertintotb_reg(name,pwd,email,address)values(nc,pwd,email,addres

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

7、,.$email.,.$address.)echoalert(用户注册成功！);elseechoalert(用户注册失败!);MySQL5.0 参考手册中关于创建存储过程的语法说明:CREATEDEFINER=user|CURRENT_USERPROCEDUREsp_name(proc_parameter,.)characteristic.routine_bodyproc_parameter:IN|OUT|INOUTparam_nametypetype:AnyvalidMySQLdatatypecharacteristic:LANGUAGESQL|NOTDETERMINISTIC|CONTAI

8、NSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA|SQLSECURITYDEFINER|INVOKER|COMMENTstringroutine_body:ValidSQLprocedurestatement如果你对 MySQ 好不太熟悉的话，单单看这个语法结构当然不足以进行 MySQLMW 过程编程。我之前基本都是使用 MSSQLSERVER 所以以下记录我熟悉 MySQW 储过程的过程，也是重点介绍MSSQLSERVER!MySQLM 别较大的地方。第一步，当然是写个 HelloWord 的存储过程，如下：CREATEPROCEDUREphelloword(

9、)BEGINSELECTHelloWord!ASF;END;将上面创建 phelloword 存储过程的语句拷到 phpMyAdmin 中执行，报如下错误：#1064-YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenearatline3在这个问题上我纠缠了很久，在 MySQL 的命令行工具中执行同样不成功，但是根据提示信息可以知道执行在 SELECTHelloWord!ASF;处结束，后面的 END;没有执行，这显然会导致错误。这里

10、需要选择以个分隔符，语法如下：DELIMITER/分隔符是通知 MySQL 客户端已经输入完成的符号。一直都是用“，但是在存储过程中不行，因为存储过程中很多语句都需要用到分号。因此上面的存储过程改为：CREATEPROCEDUREptest()BEGINSELECTHelloWord!ASF;END/另外在 phpMyAdmin 中执行时，在 Delimiter 文本框中填写，这次存储过程即可创建成功。第二步，写一个包括参数，变量，变量赋值，条件判断，UPDATED 句，SELECTS 回结果集的完整的一个存储过程，如下：CREATEPROCEDUREploginp_usernamechar(

11、15),p_passwordchar(32),p_ipchar(18),p_logintimedatetime)LABEL_PROC:BEGINDECLAREv_uidmediumint(8);DECLAREv_realpasswordchar(32);DECLAREv_nicknamevarchar(30);DECLAREv_oltimesmallint(6);SELECTu.uid,u.password,f.nickname,u.oltimeINTOv_uid,v_realpassword,v_nickname,v_oltimeFROMcdb_membersuINNERJOINcdb_me

12、mberfieldsfONf.uid=u.uidWHEREu.username=p_username;IF(v_uidISNULL)THENSELECT2ASErrorCode;LEAVELABEL_PROC;ENDIF;IF(p_passwordv_realpassword)THENSELECT3ASErrorCode;LEAVELABEL_PROC;ENDIF;UPDATEipsp_userexpandsSETlastloginip=p_ip,lastlogintime=p_logintimeWHEREuid=v_uid;SELECT0ASErrorCode,v_uidASuid,v_ni

13、cknameASnickname,v_oltimeASoltime;ENDLABEL_PROC/首先要说的是给变量赋值的语法，MySQL 中使用 SELECTu.uid,u.password,f.nickname,u.oltimeINTOv_uid,v_realpassword,v_nickname,v_oltimeFROMcdb_membersuINNERJOINcdb_memberfieldsfONf.uid=u.uidWHEREu.username=p_username;这种方式给变量赋值。其次是条件判断的语法结构，如下所示：IF.THEN.;ELSEIF.THEN，ELSEIFELSE

14、ENDIF;ENDIF;最后说说 LEAVE 语法的使用。当满足某种条件，不继续执行下面的 SQL 时，在 MSSQLSERVER 中使用 RETURNS 法，在 MySQ 冲我没有找到对应的关键字，但是这里可以利用 LEAVE 语法来满足要求，在存储过程的 BEGIN 前定义一个标签，如：“LABEL_PROC:然后再需要用到 RETUR 曲断执行的地方执行LEAVELABEL_PROC 即可。第三步，创建一个执行动态 SQL 的存储过程。CREATEPROCEDUREipsp_getresourcedir(p_hashcodechar(40)LABEL_PROC:BEGINDECLAREv

15、_sqlvarchar(200);SETv_sql=CONCAT(SELECTfiledirFROMipsp_resourcesWHEREhashcode=,p_hashcode,LIMIT0,1);SETsql=v_sql;PREPAREslFROMsql;EXECUTEsl;DEALLOCATEPREPAREsl;ENDLABEL_PROC/mysql 存储过程的创建，删除，调用及其他常用命令mysql5.0 存储过程学习总结一.创建存储过程1 .基本语法：createproceduresp_name()begin,end2 .参数传递.调用存储过程1.基本语法：callsp_name()

16、注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递三.删除存储过程1 .基本语法：dropproceduresp_name/2 .注意事项(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程四.区块，条件，循环1 .区块定义，常用begin,end;也可以给区块起别名，如：lable:begin,.endlable;可以用 leavelable;跳出区块，执行区块以后的代码2 .条件语句if 条件 thenstatementelsestatementendif;3 .循环语句(1) .while 循环label:WHILEexpressionDOstatementsE

17、NDWHILElabel;(2) .loop 循环label:LOOPstatementsENDLOOPlabel;(3) .repeatuntil 循环label:REPEATstatementsUNTILexpressionENDREPEATlabel;五.其他常用命令1.showprocedurestatus显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称,2.showcreateproceduresp_name创建时间等显示某一个存储过程的详细信息mysql 存储过程中要用到的运算符mysql 存储过程学习总结一操作符算术运算符+力口 SETvar1=2+2;4减

18、SETvar2=3-2;12False小于 21False=小于等于 2=2True=2TrueBETWEEN 在两值之间 5BETWEEN1AND10TrueNOTBETWEEN 不在两值之间 5NOTBETWEEN1AND10FalseIN 在集合中 5IN(1,2,3,4)FalseNOTIN 不在集合中 5NOTIN(1,2,3,4)True=等于 2=3False,!=不等于 23False严格比较两个 NULL 值是否相等 NULLNULLTrueLIKE 简单模式匹配GuyHarrisonLIKEGuy%TrueREGEXP 正则式匹配GuyHarrisonREGEXP”Ggre

19、gFalseISNULL 为空 0ISNULLFalseISNOTNULL 不为空 0ISNOTNULLTrue 逻辑运算符与(AND)ANDTRUEFALSENULLTRUETRUEFALSEFALSEFALSEFALSENULLNULLNULLNULLNULL或(OR)ORTRUEFALSENULLTRUETRUETRUETRUEFALSETRUEFALSENULLNULLTRUENULL异或(XOR)XORTRUEFALSENULLTRUEFALSETRUENULLFALSETRUEFALSENULLNULLNULLNULLNULL位运算符|位或&位与右移位位非（单目运算，按位取

20、反）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(substring,str

21、ing,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(string2)/去除后端空格STRCMP(s

22、tring1,string2)/逐字符比较两字串大小，SUBSTRING(str,position,length)/从 str 的 position 开始，取 length 个字符，注：mysql 中处理字符串时，默认第一个字符下标为 1,即参数 position 必须大于等于 1mysqlselectsubstring(abcd,0,2);+|substring(abcd,0,2)|+|+1rowinset(0.00sec)mysqlselectsubstring(abcd,1,2);+|substring(abcd,1,2)|+|ab|1rowinset(0.02sec)TRIM(BOTH

23、|LEADING|TRAILINGpaddingFROMstring2)/去除指定位置的指定字符UCASE(string2)/转换成大写RIGHT(string2,length)/取 string2 最后 length 个字符SPACE(count)/生成 count 个空格二.数学类ABS(number2)/绝对值BIN(decimal_number)/十进制转二进制CEILING(number2)/向上取整CONV(number2,from_base,to_base)/进制转换FLOOR(number2)/向下取整FORMAT(number,decimal_places)/保留小数位数HE

24、X(DecimalNumber)/转十六进制注：HEX()中可传入字符串，则返回其 ASC-11 码，如 HEX(DEF)返回 4142143也可以传入十进制整数，返回其十六进制编码，如 HEX(25)返回 19LEAST(number,number2,.)/求最小值MOD(numerator,denominator)/求余POWER(number,power)/求指数RAND(seed)/随机数ROUND(number,decimals)/四舍五入,decimals 为小数位数注：返回类型并非均为整数，如：(1)默认变为整形值mysqlselectround(1.23);+-+|round(

25、1.23)|+-+|1|+-+1rowinset(0.00sec)mysqlselectround(1.56);+-+|round(1.56)|+-+1rowinset(0.00sec)(2)可以设定小数位数，返回浮点型数据 mysqlselectround(1.567,2);+-+|round(1.567,2)|+-+|1.57|+-+1rowinset(0.00sec)SIGN(number2)/返回符号，正负或 0SQRT(number2)开平方三.日期时间类ADDTIME(date2,time_interval)/将 time_interval 力口至Udate2CONVERT_TZ(datetime2,fromTZ,toTZ)/转换时区CURRENT_DATE()/当前日期 CURRENT_TIME()/当前时间 CURRENT_TIMESTAMP()/当前时间戳DATE(datetime