MySQL的存储过程解析.docx
《MySQL的存储过程解析.docx》由会员分享，可在线阅读，更多相关《MySQL的存储过程解析.docx（26页珍藏版）》请在冰豆网上搜索。
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中出现的位置,不存在返回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）//去除前端空格
RTRIM（string2）//去除后端空格
RPAD（string2,length,pad）//在str后用pad补充,直到长度为length
REPEAT（string2,count）//重复count次
REPLACE（str,search_str,replace_str）//在str中用replace_str替换search_str
STRCMP（string1,string2）//逐字符比较两字串大小,返回不同个数，前者大为正数，后者大为负数
SUBSTRING（str,position[,length]）//从str的position开始,取length个字符,
注：
mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
TRIM（[[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）返回19
LEAST（number,number2[,..]）//求最小值
MOD（numerator,denominator）//求余
POWER（number,power）//求指数
RAND（[seed]）//随机数
ROUND（number[,decimals]）//四舍五入,decimals为小数位数]
（3）日期时间类
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_valued_type）//在date2中加上日期或时间
DATE_FORMAT（datetime,FormatCodes）//使用formatcodes格式显示datetime
DATE_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）//分返回符号,正负或0
SQRT（number2）//开平方
升级会员 