这里只是为了记录,由于自身水平实在不怎么样,难免错误百出,有错的地方还望大家多多指出,谢谢。
参考 mysql存储过程详解
- 存储过程简介
- 关于MySQL的存储过程
- MySQL存储过程的创建
- MySQL存储过程的调用
- MySQL存储过程的查询
- MySQL存储过程的修改
- MySQL存储过程的删除
- MySQL存储过程的控制语句
- MySQL存储过程的基本函数
1. 存储过程简介 |
存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
一个存储过程可以由SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。
存储过程的优点:
(1)存储过程可以用流控制语言编写,可以完成复杂的判断和运算,有很强的灵活性。
(2)存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
(3)存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的,在首次运行一个存储过程时,优化器会对其进行分析优化,并且给出最红被存储在系统表中的执行计划。而批处理的Transation-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一点。
(4)存储过程能减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的
Transation-SQL语句被组织成存储过程,那么当客户在计算机上调用该存储过程时,只需要发送调用该存储过程的语句即可。从而大大增加了网络流量并降低了网络负载。
(5)存储过程可被作为一种安全机制来充分利用。系统管理员通过对某一存储过程的权限进行控制,能够实现对相应的数据访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
2. 关于MySQL的存储过程 |
在MySQL5.0之前并不支持存储过程。好在5.0之后已经开始支持存储过程了,这大大提高了数据库的处理速度,同时也可以提高数据库编程的灵活性。
3. MySQL存储过程的创建 |
(1)格式
MySQL存储过程创建的格式:CREATE PROCEDURE 过程名 ([过程参数[, …]]) [特性….] 过程体
这里先举个例子:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
注:
- 这里需要注意的是DELIMITER //和DELIMITER ;两句,DELIMITER是分割符的意思,因为MySQL默认以”;”为分隔符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事先用DELIMITER关键字申明当前段分隔符,这样MySQL才会将”;”当做存储过程中的代码,不会执行这些代码,用完了之后要把分隔符还原。
- 存储过程根据需要可能会有输入、输出、输入输出参数,这里有一个输出参数s,类型是int型,如果有多个参数用”,”分割开。
- 过程体的开始与结束使用BEGIN与END进行标识。
这样,我们的一个MySQL存储过程就完成了,是不是很容易呢?看不懂也没关系,接下来,我们详细的讲解。
(2)声明分隔符
其实,关于声明分割符,上面的注解已经写得很清楚,不需要多说,只是稍微要注意一点的是:如果是用MySQL的Administrator管理工具时,可以直接创建,不再需要声明。
(3)参数
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形…])
IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
OUT 输出参数:该值可在存储过程内部被改变,并可返回
INOUT 输入输出参数:调用时指定,并且可被改变和返回
- I IN参数例子
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
执行结果:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
以上可以看出,p_in虽然在存储过程中被修改,但并不影响@p_id的值
- Ⅱ.OUT参数例子
创建:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
执行结果:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- Ⅲ. INOUT参数例子
创建:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
执行结果:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
(4)变量
-
I. 变量定义
DECLARE variable_name[,variable_name…] datatype [DEFAULT value];
其中,datatype 为MySQL的数据类型,如int, float, date, varchar(length)
例如:declare l_int int unsigned default 400000
-
Ⅱ. 变量赋值
set 变量名 = 表达式值[, variable_name = expression…] -
Ⅲ. 用户变量
在MySQL客户端使用用户变量
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
在存储过程中使用用户变量
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
在存储过程间传递全局范围的用户变量
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
注意:
①用户变量名一般以@开头
②滥用用户变量会导致程序难以理解及管理
(5)注释
- 双横杆 – :一般用户单行注释,示例如下:
- 1
- c风格: 一般用于多行注释,如下:
- 1
- 2
- 3
- 4
- 5
4. MySQL存储过程的调用 |
格式:call 过程名[(参数1[,参数2…])]
括号里面根据需要,加入参数,参数包括输入参数、输出参数、输入输出参数。
5. MySQL存储过程的查询 |
(1)查看存储过程:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
6. MySQL存储过程的修改 |
….
7. MySQL存储过程的删除 |
删除一个存储过程比较简单,和删除表一样:
- 1
8. MySQL存储过程的控制语句 |
(1)变量作用域
内部的变量在其作用域范围内享有更高的优先权,当执行到end时,
此时内部变量就不会再有效了,但是可以通过out参数或者将其指派给会话变量来保存其值。示例如下:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
(2)条件语句
- Ⅰ. if-then-elseif-then-else语句
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- Ⅱ. case语句:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
(3)循环语句
- Ⅰ. while …do…end while;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
Ⅱ. repeat …util…end repeat;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
Ⅲ. loop…end loop;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- Ⅳ. LABELS标号
标号可以用在begin、repeat、while或者loop语句前,语句标号只能在合法的语句前面使用。可以跳出循环,使运行指令达到复合语句的最后一步。
(4)ITERATER迭代
- ⅠIterate:
通过引用符合语句的标号,来重新开始符合语句。
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
9. 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 ) //去除前端空格
- 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
- UCASE (string2 ) //转换成大写
- RIGHT(string2,length) //取string2最后length个字符
- SPACE(count) //生成count个空格
(2) 数学类
- [sql] view plain copy 在CODE上查看代码片派生到我的代码片
- 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)默认变为整形值
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
(2)可以设定小数位数,返回浮点型数据
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
(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 , INTERVAL d_value d_type ) //在date2中加上日期或时间
- DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
- DATE_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 ) //秒数转成时间
- STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
- TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
- TIME_TO_SEC (time ) //时间转秒数]
- 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) //开平方