MySQL的存储过程解析

MySQL的存储过程解析1、存储过程简介我们使用的sql语言，需要先编译然后才会执行，然而存储过程（procedure）是为了万恒特定功能的sql语句集合，经编译后存储在数据库中，用户通过存储过程的名字并给定参数或传值的方式来调用执行他。一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有控制语句和sql语句组成。适用于不同的平台和应用程序执行相同的函数。存储过程具有以下优点：1)、存储过程增强了sql语言的功能和灵活性。存储过程可以用流控制语句的编写，具有很强的灵活性，可以完成复杂的判断和较为复杂的运算。2)、存储过程允许的标准组件是编程。存储过程被创建以后，在程序中多次被调用，而不必进行重新编写。对存储过程的修改不会影响程序源代码的执行。3)、存储过程可以执行较快的执行速度。若某一操作中包含大量的事物处理代码或分别被多次执行，那么储存过程要比批处理的执行速度快很多。因为存储过程是预编程的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理需要每次进行编译和优化，速度相对要慢。（总结：存储过程比批处理要快很对，原因是：前者进行预编译。）4)、存储过程可以减少网络流量。针对数据库操作的增删改查，存储过程在网路中只是传递该调用的语句。5)、存储过程可看做是一种安全机制来充分利用。可以对存储过程的权限进行设置，进而事项对响应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证数据的安全。（总结：个人感觉，不太实用）2、MySQL存储过程的创建1)、格式格式：createprocedure过程名（[过程参数[,…]]）[特性…]过程体。需要注意的是：delimiter//和delimiter;两句，delimiter是分隔符的意思，因为MySQL默认以“;”为分隔符，如果没有生命分隔符，那么编译器会把编译器存储过程当成sql语句进行处理，则存储过程的编译过程会报错，所以要实现用delimiter关键字申明当前段分隔符，这样MySQL才会将”;”当做存储过程的代码，而不会执行这些代码，当然用完之后要用delimiter;进行分割符的还原。存储过程根据需要可能有输入、输出或者是输入输出；过程体的开始和结束是以begin和end进行标示的，后边不加”;”2)、参数MySQL的储存过程共存在三种类型的参数分别是：IN、OUT和INOUT格式为：createprocedure过程名([IN|OUT|INOUT[参数名数据类型…]])IN输入参数：标示该参数必须在调用存储过程时制定，存储过程中进行参数的修改不能被返回，为默认值。OUT输出参数：该值在存储过程中可以被改变，返回修改后的值。INOUT输入输出参数：调用时候可以进行修改和返回。IN参数输入例子：创建一个名为demo_in的存储过程执行结果：输入一个参数后，可以获取新输入的值。OUT输出参数例子：创建一个demo_out的过程执行结果：可以看出OUT无法向过程里进行传值，过程中的值可以正确的返回。INOUT参数的例子：创建一个名为demo_inoutde过程名执行结果：可以看出既可以输入参数也可以返回参数。3)、变量i.变量定义Declarevariable_name[,variable_name…]datatype[defaultvalue]其中datatype为MySQL的数据类型，如intfloatdatevarchar例如：declarel_intintdefault4000;declarel_numericnumber(8,2)default10.99;declarel_datedatedefault‘1990-09-11’;declarel_datetimedatetimedefault‘1990-09-1124:00:00’;declarel_varcharvarchar(20)default‘youareverygood’;ii.变量赋值Set变量名=表达式值iii.用户变量客户端使用用户变量例子：使用@开始定义变量存储过程是使用用户变量案例：跨存储过程的用户变量间的传递：4)、注释MySQL存储过程的注释方式是：--例子：3、MySQL存储过程的查询我们可以用selectnamefrommysql.procwheredb=’数据库名’;或者selectroutine_namefrominformation_schema.routineswhereroutine_schema='数据库名';或者showprocedurestatuswheredb='数据库名';进行查询。SHOWCREATEPROCEDURE数据库.存储过程名;就可以查看当前存储过程的详细。4、MySQL存储过程的修改alterprocedure存储过程的名称createorreplaceprocedure存储过程名称以上两者实测在再MySQL中均无法采用命令方式进行修改，但是可以使用编辑器修改。5、MySQL存储过程的删除Dropprocedure名称；6、MySQL存储过程的控制语句1.变量作用域内部变量在其内部享有更高的优先权，当执行end时，内部变量作用消失，此时进入到外部的作用域中，变量不在可见了。我们可以将变量指派到外部进行变量数值的传递。注意：在进行嵌套begin…end时候，end后边必须进行添加‘；’分隔符，否则创建过程会出现错误。2.条件语句If…thenelse..endif;Casewhen…then..endcase;3.循环语句While…do…endwhile;Repeat…until…endrepeat;Loop…if…then..leaveLOOP_LABLEendif;endloop;7、MySQL存储过程的基本函数(1)字符串类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,string[,start_position])同INSTR,但可指定开始位置LPAD(string2,length,pad)//重复用pad加在string开头,直到字串长度为lengthLTRIM(string2)//去除前端空格RTRIM(string2)//去除后端空格RPAD(string2,length,pad)//在str后用pad补充,直到长度为lengthREPEAT(string2,count)//重复count次REPLACE(str,search_str,replace_str)//在str中用replace_str替换search_strSTRCMP(string1,string2)//逐字符比较两字串大小,返回不同个数，前者大为正数，后者大为负数SUBSTRING(str,position[,length])//从str的position开始,取length个字符,注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1TRIM([[BOTH|LEADING|TRAILING][padding]FROM]string2)//去除指定位置的指定字符UCASE(string2)//转换成大写RIGHT(string2,length)//取string2最后length个字符SPACE(count)//生成count个空格(2)数学类ABS(number2)//绝对值BIN(decimal_number)//十进制转二进制CEILING(number2)//向上取整FLOOR(number2)//向下取整CONV(number2,from_base,to_base)//进制转换FORMAT(number,decimal_places)//保留小数位数HEX(DecimalNumber)//转十六进制注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19LEAST(number,number2[,..])//求最小值MOD(numerator,denominator)//求余POWER(number,power)//求指数RAND([seed])//随机数ROUND(number[,decimals])//四舍五入,decimals为小数位数](3)日期时间类ADDTIME(date2,time_interval)//将time_interval加到date2CONVERT_TZ(datetime2,fromTZ,toTZ)//转换时区CURRENT_DATE()//当前日期CURRENT_TIME()//当前时间CURRENT_TIMESTAMP()//当前时间戳DATE(datetime)//返回datetime的日期部分DATE_ADD(date2,INTERVALd_valued_type)//在date2中加上日期或时间DATE_FORMAT(datetime,FormatCodes)//使用formatcodes格式显示datetimeDATE_SUB(date2,INTERVALd_valued_type)//在date2上减去一个时间DATEDIFF(date1,date2)//两个日期差DAY(date)//返回日期的天DAYNAME(date)//英文星期DAYOFWEEK(date)//星期(1-7),1为星期天DAYOFYEAR(date)//一年中的第几天EXTRACT(interval_nameFROMdate)//从date中提取日期的指定部分MAKEDATE(year,day)//给出年及年中的第几天,生成日期串MAKETIME(hour,minute,second)//生成时间串MONTHNAME(date)//英文月份名NOW()//当前时间SEC_TO_TIME(seconds)//秒数转成时间TIME_TO_SEC(time)//时间转秒数]STR_TO_DATE(string,format)//字串转成时间,以format格式显示TIMEDIFF(datetime1,datetime2)//两个时间差WEEK(date_time[,start_of_week])//第几周YEAR(datetime)//年份DAYOFMONTH(datetime)//月的第几天HOUR(datetime)//小时LAST_DAY(date)//date的月的最后日期MICROSECOND(datetime)//微秒MONTH(datetime)//月MINUTE(datetime)//分返回符号,正负或0SQRT(number2)//开平方