MySQL的存储过程解析.docx

MySQL的存储过程解析1、存储过程简介我们使用的sql语言，需要先编译然后才会执行，然而存储过程（procedure）是为了万恒特定功能的sql语句集合，经编译后存储在数据库中，用户通过存储过程的名字并给定参数或传值的方式来调用执行他。一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有控制语句和sql语句组成。适用于不同的平台和应用程序执行相同的函数。存储过程具有以下优点：1)、 存储过程增强了sql语言的功能和灵活性。存储过程可以用流控制语句的编写，具有很强的灵活性，可以完成复杂的判断和较为复杂的运算。2)、 存储过程允许的标准组件是编程。存储过程被创建以后，在程序中多次被调用，而不必进行重新编写。对存储过程的修改不会影响程序源代码的执行。3)、 存储过程可以执行较快的执行速度。若某一操作中包含大量的事物处理代码或分别被多次执行，那么储存过程要比批处理的执行速度快很多。因为存储过程是预编程的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理需要每次进行编译和优化，速度相对要慢。（总结：存储过程比批处理要快很对，原因是：前者进行预编译。）4)、 存储过程可以减少网络流量。针对数据库操作的增删改查，存储过程在网路中只是传递该调用的语句。5)、 存储过程可看做是一种安全机制来充分利用。可以对存储过程的权限进行设置，进而事项对响应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证数据的安全。（总结：个人感觉，不太实用）2、MySQL存储过程的创建1)、 格式格式：create procedure 过程名（过程参数,）特性过程体。需要注意的是：delimiter /和delimiter ;两句，delimiter 是分隔符的意思，因为MySQL默认以“;” 为分隔符，如果没有生命分隔符，那么编译器会把编译器存储过程当成sql语句进行处理，则存储过程的编译过程会报错，所以要实现用delimiter关键字申明当前段分隔符，这样MySQL才会将”;” 当做存储过程的代码，而不会执行这些代码，当然用完之后要用delimiter ; 进行分割符的还原。存储过程根据需要可能有输入、输出或者是输入输出；过程体的开始和结束是以begin和end进行标示的，后边不加”;” 2)、 参数MySQL的储存过程共存在三种类型的参数分别是：IN 、OUT 和INOUT 格式为：create procedure 过程名(IN |OUT|INOUT参数名 数据类型)IN 输入参数：标示该参数必须在调用存储过程时制定，存储过程中进行参数的修改不能被返回，为默认值。OUT输出参数：该值在存储过程中可以被改变，返回修改后的值。INOUT输入输出参数：调用时候可以进行修改和返回。IN参数输入例子：创建一个名为demo_in 的存储过程执行结果：输入一个参数后，可以获取新输入的值。OUT输出参数例子：创建一个demo_out的过程执行结果：可以看出OUT 无法向过程里进行传值，过程中的值可以正确的返回。INOUT参数的例子：创建一个名为demo_inoutde 过程名执行结果：可以看出既可以输入参数也可以返回参数。3)、 变量i. 变量定义Declare variable_name,variable_name datatype default value其中datatype为MySQL的数据类型，如int float date varchar例如：declare l_int int default 4000;declare l_numeric number(8,2) default 10.99;declare l_date date default 1990-09-11;declare l_datetime datetime default 1990-09-11 24:00:00;declare l_varchar varchar(20) default you are very good;ii. 变量赋值Set 变量名=表达式值iii. 用户变量客户端使用用户变量例子：使用开始定义变量存储过程是使用用户变量案例：跨存储过程的用户变量间的传递：4)、 注释MySQL存储过程的注释方式是：-例子：3、MySQL存储过程的查询我们可以用select name from c where db=数据库名;或者select routine_name from information_schema.routines where routine_schema=数据库名;或者show procedure status where db=数据库名; 进行查询。SHOW CREATE PROCEDURE 数据库.存储过程名;就可以查看当前存储过程的详细。4、MySQL存储过程的修改alter procedure 存储过程的名称create or replace procedure 存储过程名称以上两者实测在再MySQL中均无法采用命令方式进行修改，但是可以使用编辑器修改。5、MySQL存储过程的删除Drop procedure 名称；6、MySQL存储过程的控制语句1. 变量作用域内部变量在其内部享有更高的优先权，当执行end时，内部变量作用消失，此时进入到外部的作用域中，变量不在可见了。我们可以将变量指派到外部进行变量数值的传递。注意：在进行嵌套begin end 时候，end后边必须进行添加；分隔符，否则创建过程会出现错误。2. 条件语句If then else . end if;Case whenthen.end case;3. 循环语句Whiledoend while;Repeat until end repeat;Loopifthen.leave LOOP_LABLE end if; end loop;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必须大于等于1 TRIM(BOTH|LEADING|TRAILING padding FROMstring2) /去除指定位置的指定字符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 , INTERVAL d_value d_type ) /在date2中加上日期或时间DATE_FORMAT (datetime ,FormatCodes ) /使用formatcodes格式显示datetimeDATE_SUB (date2 , INTERVAL d_value d_type ) /在date2上减去一个时间DATEDIFF (date1 ,date2 ) /两个日期差DAY (date ) /返回日期的天DAYNAME (date ) /英文星期DAYOFWEEK (date ) /星期(1-7) ,1为星期天DAYOFYEAR (date ) /一年中的第几天EXTRACT (interval_name FROM date ) /从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 (datet