MySQL的存储过程解析.docx
《MySQL的存储过程解析.docx》由会员分享,可在线阅读,更多相关《MySQL的存储过程解析.docx(26页珍藏版)》请在冰豆网上搜索。
![MySQL的存储过程解析.docx](https://file1.bdocx.com/fileroot1/2023-2/3/4c0df700-51e7-4293-ae06-af6896d651fc/4c0df700-51e7-4293-ae06-af6896d651fc1.gif)
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)//开平方