MySQL存储过程实例教程详解.docx

MySQL存储过程实例教程详解.docx

《MySQL存储过程实例教程详解.docx》由会员分享，可在线阅读，更多相关《MySQL存储过程实例教程详解.docx（13页珍藏版）》请在冰豆网上搜索。

MySQL存储过程实例教程详解

MySQL存储过程实例教程

MySQL5.0以后的版本开始支持存储过程，存储过程具有一致性、高效性、安全性和体系结构等特点，本节将通过具体的实例讲解PHP是如何操纵MySQL存储过程的。

实例261：

存储过程的创建

这是一个创建存储过程的实例

录像位置：

光盘mingrisoft9?

lt;/p>

实例说明

为了保证数据的完整性、一致性，提高应用的性能，常采用存储过程技术。

MySQL5.0之前的版本并不支持存储过程，随着MySQL技术的日趋完善，存储过程将在以后的项目中得到广泛的应用。

本实例将介绍在MySQL5.0以后的版本中创建存储过程。

技术要点

一个存储过程包括名字、参数列表，以及可以包括很多SQL语句的SQL语句集。

下面为一个存储过程的定义过程：

createprocedureproc_name（inparameterinteger）begindeclarevariablevarchar（20）;ifparameter=1thensetvariable='MySQL';elsesetvariable='PHP';endif;insertintotb（name）values（variable）;end;  

MySQL中存储过程的建立以关键字createprocedure开始，后面紧跟存储过程的名称和参数。

MySQL的存储过程名称不区分大小写，例如PROCE1（）和proce1（）代表同一个存储过程名。

存储过程名不能与MySQL数据库中的内建函数重名。

存储过程的参数一般由3部分组成。

第一部分可以是in、out或inout。

in表示向存储过程中传入参数；out表示向外传出参数；inout表示定义的参数可传入存储过程，并可以被存储过程修改后传出存储过程，存储过程默认为传入参数，所以参数in可以省略。

第二部分为参数名。

第三部分为参数的类型，该类型为MySQL数据库中所有可用的字段类型，如果有多个参数，参数之间可以用逗号进行分割。

MySQL存储过程的语句块以begin开始，以end结束。

语句体中可以包含变量的声明、控制语句、SQL查询语句等。

由于存储过程内部语句要以分号结束，所以在定义存储过程前应将语句结束标志“;”更改为其他字符，并且该字符在存储过程中出现的几率也应该较低，可以用关键字delimiter更改。

例如：

mysql>delimiter//

存储过程创建之后，可用如下语句进行删除，参数proc_name指存储过程名。

dropprocedureproc_name 

实现过程

（1）MySQL存储过程是在“命令提示符”下创建的，所以首先应该打开“命令提示符”窗口。

（2）进入“命令提示符”窗口后，首先应该登录MySQL数据库服务器，在“命令提示符”下输入如下命令：

mysql–u用户名–p用户密码 

（3）更改语句结束符号，本实例将语句结束符更改为“//”。

代码如下：

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,address）;end;//    

“delimiter//”的作用是将语句结束符更改为“//”。

“inncvarchar（50）……inaddressvarchar（50）”表示要向存储过程中传入的参数。

实现过程

（1）通过PHP预定义类mysqli，实现与MySQL数据库的连接。

代码如下：

$conn=newmysqli（"localhost","root","root","db_database09"）;$conn->query（"setnamesgb2312"）;   

（2）调用存储过程pro_reg，实现将用户录入的注册信息保存到数据库。

代码如下：

if（$sql=$conn->query（"callpro_reg（'".$nc."','".$pwd."','".$email."','".$address."'）"））{echo"";}else{echo"";

MySQL5.0参考手册中关于创建存储过程的语法说明：

CREATE

   [DEFINER={user|CURRENT_USER}]

   PROCEDUREsp_name（[proc_parameter[,...]]）

   [characteristic...]routine_body

proc_parameter:

   [IN|OUT|INOUT]param_nametype

type:

   AnyvalidMySQLdatatype

characteristic:

   LANGUAGESQL

 |[NOT]DETERMINISTIC

 |{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}

 |SQLSECURITY{DEFINER|INVOKER}

 |COMMENT'string'

routine_body:

   ValidSQLprocedurestatement

如果你对MySQL还不太熟悉的话，单单看这个语法结构当然不足以进行MySQL存储过程编程。

我之前基本都是使用MSSQLSERVER，所以以下记录我熟悉MySQL存储过程的过程，也是重点介绍MSSQLSERVER与MySQL区别较大的地方。

第一步，当然是写个HelloWord的存储过程，如下：

CREATEPROCEDUREphelloword（）

BEGIN

 SELECT'HelloWord!

'ASF;

END;

将上面创建phelloword存储过程的语句拷到phpMyAdmin中执行，报如下错误：

#1064-YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear''atline3

在这个问题上我纠缠了很久，在MySQL的命令行工具中执行同样不成功，但是根据提示信息可以知道执行在SELECT'HelloWord!

'ASF;处结束，后面的END;没有执行，这显然会导致错误。

这里需要选择以个分隔符，语法如下：

DELIMITER//

分隔符是通知MySQL客户端已经输入完成的符号。

一直都是用“;”，但是在存储过程中不行，因为存储过程中很多语句都需要用到分号。

因此上面的存储过程改为：

CREATEPROCEDUREptest（）

BEGIN

 SELECT'HelloWord!

'ASF;

END//

另外在phpMyAdmin中执行时，在Delimiter文本框中填写//，这次存储过程即可创建成功。

第二步，写一个包括参数，变量，变量赋值，条件判断，UPDATE语句，SELECT返回结果集的完整的一个存储过程，如下：

CREATEPROCEDUREplogin

（

   p_usernamechar（15）,

   p_passwordchar（32）,

   p_ipchar（18）,

   p_logintimedatetime

）

LABEL_PROC:

BEGIN   

   DECLAREv_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_oltime

   FROMcdb_membersuINNERJOINcdb_memberfieldsfONf.uid=u.uidWHEREu.username=p_username;   

   IF（v_uidISNULL）THEN

       SELECT2ASErrorCode;

       LEAVELABEL_PROC;

   ENDIF;

   IF（p_password<>v_realpassword）THEN

       SELECT3ASErrorCode;

       LEAVELABEL_PROC;

   ENDIF;

   UPDATEipsp_userexpandsSETlastloginip=p_ip,lastlogintime=p_logintimeWHEREuid=v_uid;

   SELECT0ASErrorCode,v_uidASuid,v_nicknameASnickname,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

   ...;

ELSE

   IF...THEN

     ...;

   ELSEIF

     ...;

   ELSE

     ...;

   ENDIF;

ENDIF;

最后说说LEAVE语法的使用。

当满足某种条件，不继续执行下面的SQL时，在MSSQLSERVER中使用RETURN语法，在MySQL中我没有找到对应的关键字，但是这里可以利用LEAVE语法来满足要求，在存储过程的BEGIN前定义一个标签，如：

“LABEL_PROC:

”然后再需要用到RETURN中断执行的地方执行“LEAVELABEL_PROC;”即可。

第三步，创建一个执行动态SQL的存储过程。

CREATEPROCEDUREipsp_getresourcedir

（

   p_hashcodechar（40）

）

LABEL_PROC:

BEGIN

   DECLAREv_sqlvarchar（200）;

   SETv_sql=CONCAT（'SELECTfiledirFROMipsp_resourcesWHEREhashcode='',p_hashcode,''LIMIT0,1'）;

   SET@sql=v_sql;

   PREPAREslFROM@sql;

   EXECUTEsl;

   DEALLOCATEPREPAREsl;

ENDLABEL_PROC//

mysql存储过程的创建，删除，调用及其他常用命令

mysql5.0存储过程学习总结

一.创建存储过程

1.基本语法：

createproceduresp_name（）

begin

………

end

2.参数传递

二.调用存储过程

1.基本语法：

callsp_name（）

注意：

存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

三.删除存储过程

1.基本语法：

dropproceduresp_name//

2.注意事项

（1）不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

四.区块，条件，循环

1.区块定义，常用

begin

……

end;

也可以给区块起别名，如：

lable:

begin

………..

endlable;

可以用leavelable;跳出区块，执行区块以后的代码

2.条件语句

if条件then

statement

else

statement

endif;

3.循环语句

（1）.while循环

[label:

]WHILEexpressionDO

statements

ENDWHILE[label];

（2）.loop循环

[label:

]LOOP

statements

ENDLOOP[label];

（3）.repeatuntil循环

[label:

]REPEAT

statements

UNTILexpression

ENDREPEAT[label];

五.其他常用命令

1.showprocedurestatus

显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

2.showcreateproceduresp_name

显示某一个存储过程的详细信息

mysql存储过程中要用到的运算符

mysql存储过程学习总结－操作符

算术运算符

+    加  SETvar1=2+2;      4

-    减  SETvar2=3-2;      1

*    乘  SETvar3=3*2;      6

/    除  SETvar4=10/3;     3.3333

DIV  整除SETvar5=10DIV3; 3

%    取模SETvar6=10%3;    1

比较运算符

>           大于1>2False

<           小于2<1False

<=          小于等于2<=2True

>=          大于等于3>=2True

BETWEEN     在两值之间5BETWEEN1AND10True

NOTBETWEEN 不在两值之间5NOTBETWEEN1AND10False

IN          在集合中5IN（1,2,3,4）False

NOTIN      不在集合中5NOTIN（1,2,3,4）True

=           等于2=3False

<>,!

=      不等于2<>3False

<=>         严格比较两个NULL值是否相等NULL<=>NULLTrue

LIKE        简单模式匹配"GuyHarrison"LIKE"Guy%"True

REGEXP      正则式匹配"GuyHarrison"REGEXP"[Gg]reg"False

ISNULL     为空0ISNULLFalse

ISNOTNULL 不为空0ISNOTNULLTrue

逻辑运算符

与（AND）

AND

 TRUE

 FALSE

 NULL

TRUE

 TRUE

 FALSE

 NULL

FALSE

 FALSE

 FALSE

 NULL

NULL

 NULL

 NULL

 NULL

或（OR）

OR

 TRUE

 FALSE

 NULL

TRUE

 TRUE

 TRUE

 TRUE

FALSE

 TRUE

 FALSE

 NULL

NULL

 TRUE

 NULL

 NULL

异或（XOR）

XOR

 TRUE

 FALSE

 NULL

TRUE

 FALSE

 TRUE

 NULL

FALSE

 TRUE

 FALSE

 NULL

NULL

 NULL

 NULL

 NULL

位运算符

|  位或

&  位与

<< 左移位

>> 右移位

~  位非（单目运算，按位取反）

mysq存储过程中常用的函数，字符串类型操作，数学类，日期时间类。

mysql存储过程基本函数

一.字符串类

CHARSET（str）//返回字串字符集

CONCAT（string2 [,...]）//连接字串

INSTR（string,substring）//返回substring首次在string中出现的位置,不存在返回0

LCASE（string2）//转换成小写

LEFT（string2,length）//从string2中的左边起取length个字符

LENGTH（string）//string长度

LOAD_FILE（file_name）//从文件读取内容

LOCATE（substring,string [,start_position]）同INSTR,但可指定开始位置

LPAD（string2,length,pad）//重复用pad加在string开头,直到字串长度为length

LTRIM（string2）//去除前端空格

REPEAT（string2,count）//重复count次

REPLACE（str,search_str,replace_str）//在str中用replace_str替换search_str

RPAD（string2,length,pad）//在str后用pad补充,直到长度为length

RTRIM（string2）//去除后端空格

STRCMP（string1,string2）//逐字符比较两字串大小,

SUBSTRING（str,position [,length]）//从str的position开始,取length个字符,

注：

mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

mysql>selectsubstring（’abcd’,0,2）;

+———————–+

|substring（’abcd’,0,2）|

+———————–+

|                      |

+———————–+

1rowinset（0.00sec）

mysql>selectsubstring（’abcd’,1,2）;

+———————–+

|substring（’abcd’,1,2）|

+———————–+

|ab                   |

+———————–+

1rowinset（0.02sec）

TRIM（[[BOTH|LEADING|TRAILING][padding]FROM]string2）//去除指定位置的指定字符

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）//保留小数位数

HEX（DecimalNumber）//转十六进制

注：

HEX（）中可传入字符串，则返回其ASC-11码，如HEX（’DEF’）返回4142143

也可以传入十进制整数，返回其十六进制编码，如HEX（25）返回19

LEAST（number,number2 [,..]）//求最小值

MOD（numerator,denominator）//求余

POWER（number,power）//求指数

RAND（[seed]）//随机数

ROUND（number [,decimals]）//四舍五入,decimals为小数位数]

注：

返回类型并非均为整数，如：

（1）默认变为整形值

mysql>selectround（1.23）;

+————-+

|round（1.23）|

+————-+

|          1|

+————-+

1rowinset（0.00sec）

mysql>selectround（1.56）;

+————-+

|round（1.56）|

+————-+

|          2|

+————-+

1rowinset（0.00sec）

（2）可以设定小数位数，返回浮点型数据

mysql>selectround（1.567,2）;

+—————-+

|round（1.567,2）|

+—————-+

|          1.57|

+—————-+

1rowinset（0.00sec）

SIGN（number2）//返回符号,正负或0

SQRT（number2）//开平方

三.日期时间类

ADDTIME（date2,time_interval）//将time_interval加到date2

CONVERT_TZ（datetime2,fromTZ,toTZ）//转换时区

CURRENT_DATE（ ）//当前日期

CURRENT_TIME（ ）//当前时间

CURRENT_TIMESTAMP（ ）//当前时间戳

DATE（datetime）//返回datetime的日期部分

DATE_ADD（date2,INTERVALd_va