MySQL的存储过程学习小结 附pdf文档下载

2022-11-12 09:22:35
内容摘要
这篇文章主要为大家详细介绍了MySQL的存储过程学习小结 附pdf文档下载，具有一定的参考价值，可以用来参考一下。 对此感兴趣的朋友，看看idc笔记做的技术笔记！存储过程是一种存储
文章正文

这篇文章主要为大家详细介绍了MySQL的存储过程学习小结 附pdf文档下载，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

存储过程是一种存储在数据库库中的程序（就像正规语言里的子程序一样），准确的来说，MySql支持的“routine（例程）”有两种：一是我们说的存储过程，二是在其它sql语句中可以返回值的函数（使用起来和mysql预装载的函数一样，如pi（））。一个存储过程包括名字，参数列表，以及可以包括很多sql语句的sql语句集。在这里对局部变量，异常处理，循环控制和if条件语句有新的语法定义。下面是一个包括存储过程的实例声明：

代码如下:

1
2
3
4
5
6
7
8
9
10
11
<code>
CREATE PROCEDURE procedurel /*name 存储过程名*/
(IN parameter1 INTEGER) /*parameters 参数*/
BEGIN /*start of blokc 语句块头*/
DECLARE variable1 CHAR(10); /*variables变量声明*/
IF parameter1 = 17 THEN /*start of IF IF条件开始*/
SET variable1 = 'birds'; /*assignment赋值*/
END IF; /*end of IF IF结束*/
INSERT INTO table1 VALUES (variable1);/*statement SQL语句*/
END /*end of block 语句块结束*/
</code>
MySQL 版本：5.0.45 phpMyAdmin版本：2.11.3首先看MySQL 5.0参考手册中关于创建存储过程的语法说明：CREATE[DEFINER = { user | CURRENT_USER }]PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...] routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typetype:Any valid MySQL data typecharacteristic:LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }| COMMENT 'string'routine_body:Valid SQL procedure statement如果你对MySQL还不太熟悉的话，单单看这个语法结构当然不足以进行MySQL存储过程编程。我之前基本都是使用MS SQL SERVER，所以以下记录我熟悉MySQL存储过程的过程，也是重点介绍MS SQL SERVER与MySQL区别较大的地方。第一步，当然是写个Hello Word的存储过程，如下：

代码如下:

1
2
3
4
5
6
<code>
CREATE PROCEDURE phelloword()
BEGIN
SELECT 'Hello Word!' AS F;
END;
</code>
将上面创建phelloword存储过程的语句拷到phpMyAdmin中执行，报如下错误：#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3在这个问题上我纠缠了很久，在MySQL的命令行工具中执行同样不成功，但是根据提示信息可以知道执行在 SELECT 'Hello Word!' AS F;处结束，后面的END;没有执行，这显然会导致错误。这里需要选择以个分隔符，语法如下：DELIMITER //分隔符是通知MySQL客户端已经输入完成的符号。一直都是用“;”，但是在存储过程中不行，因为存储过程中很多语句都需要用到分号。因此上面的存储过程改为：

代码如下:

1
2
3
4
5
6
<code>
CREATE PROCEDURE ptest()
BEGIN
SELECT 'Hello Word!' AS F;
END //
</code>
另外在phpMyAdmin中执行时，在Delimiter文本框中填写 //，这次存储过程即可创建成功。第二步，写一个包括参数，变量，变量赋值，条件判断，UPDATE语句，SELECT返回结果集的完整的一个存储过程，如下：

代码如下:

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
<code>
CREATE PROCEDURE plogin
(
p_username char(15),
p_password char(32),
p_ip char(18),
p_logintime datetime
)
LABEL_PROC:
BEGIN
DECLARE v_uid mediumint(8);
DECLARE v_realpassword char(32);
DECLARE v_nickname varchar(30);
DECLARE v_oltime smallint(6);
SELECT u.uid, u.password, f.nickname, u.oltime INTO v_uid, v_realpassword, v_nickname, v_oltime
FROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;
IF (v_uid IS NULL) THEN
SELECT 2 AS ErrorCode;
LEAVE LABEL_PROC;
END IF;
IF (p_password <> v_realpassword) THEN
SELECT 3 AS ErrorCode;
LEAVE LABEL_PROC;
END IF;
UPDATE ipsp_userexpands SET lastloginip = p_ip, lastlogintime = p_logintime WHERE uid = v_uid;
SELECT 0 AS ErrorCode, v_uid AS uid, v_nickname AS nickname, v_oltime AS oltime;
END LABEL_PROC //
</code>
首先要说的是给变量赋值的语法，MySQL中使用SELECT u.uid, u.password, f.nickname, u.oltime INTO v_uid, v_realpassword, v_nickname, v_oltime FROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;这种方式给变量赋值。其次是条件判断的语法结构，如下所示：

代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
<code>
IF ... THEN
...;
ELSE
IF ... THEN
...;
ELSEIF
...;
ELSE
...;
END IF;
END IF;
</code>
最后说说LEAVE 语法的使用。当满足某种条件，不继续执行下面的SQL时，在MS SQL SERVER中使用RETURN语法，在MySQL中我没有找到对应的关键字，但是这里可以利用LEAVE语法来满足要求，在存储过程的BEGIN前定义一个标签，如：“LABEL_PROC:” 然后再需要用到RETURN中断执行的地方执行“LEAVE LABEL_PROC;”即可。第三步，创建一个执行动态SQL的存储过程。

代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<code>
CREATE PROCEDURE ipsp_getresourcedir
(
p_hashcode char(40)
)
LABEL_PROC:
BEGIN
DECLARE v_sql varchar(200);
SET v_sql = CONCAT('SELECT filedir FROM ipsp_resources WHERE hashcode =\'', p_hashcode, '\' LIMIT 0, 1');
SET @sql = v_sql;
PREPARE sl FROM @sql;
EXECUTE sl;
DEALLOCATE PREPARE sl;
END LABEL_PROC //
</code>
这里提一下 “\”是转义字符，拼接成的SQL类似 SELECT filedir FROM ipsp_resources WHERE hashcode ='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' LIMIT 0, 1另外@sql这个属于用户变量，具体用法请查询MySQL参考手册。如果有在MS SQL SERVER上编写存储过程的经验的话，看完这些，我想基本的MySQL存储过程编程应该可以应付了吧！想了解更多的内容可查询MySQL参考手册或者相关书籍！mysql 5.0存储过程学习总结(更详细) 一.创建存储过程1.基本语法：create procedure sp_name()begin………end2.参数传递二.调用存储过程1.基本语法：call sp_name()注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递三.删除存储过程1.基本语法：drop procedure sp_name//2.注意事项(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程四.区块，条件，循环1.区块定义，常用begin……end;也可以给区块起别名，如：lable:begin………..end lable;可以用leave lable;跳出区块，执行区块以后的代码2.条件语句if 条件 thenstatementelsestatementend if;3.循环语句(1).while循环[label:] WHILE expression DOstatementsEND WHILE [label] ;(2).loop循环[label:] LOOPstatementsEND LOOP [label];(3).repeat until循环[label:] REPEATstatementsUNTIL expressionEND REPEAT [label] ;五.其他常用命令1.show procedure status显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等2.show create procedure sp_name显示某一个存储过程的详细信息mysql存储过程中要用到的运算符mysql存储过程学习总结－操作符算术运算符+ 加 SET var1=2+2; 4- 减 SET var2=3-2; 1* 乘 SET var3=3*2; 6/ 除 SET var4=10/3; 3.3333DIV 整除 SET var5=10 DIV 3; 3% 取模 SET var6=10%3 ; 1比较运算符> 大于 1>2 False< 小于 2<1 False<= 小于等于 2<=2 True>= 大于等于 3>=2 TrueBETWEEN 在两值之间 5 BETWEEN 1 AND 10 TrueNOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 FalseIN 在集合中 5 IN (1,2,3,4) FalseNOT IN 不在集合中 5 NOT IN (1,2,3,4) True= 等于 2=3 False<>, != 不等于 2<>3 False<=> 严格比较两个NULL值是否相等 NULL<=>NULL TrueLIKE 简单模式匹配 "Guy Harrison" LIKE "Guy%" TrueREGEXP 正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" FalseIS NULL 为空 0 IS NULL FalseIS NOT NULL 不为空 0 IS NOT NULL True逻辑运算符与(AND)

AND

TRUE

FALSE

NULL

TRUE

TRUE

FALSE

NULL

FALSE

FALSE

FALSE

NULL

NULL

NULL

NULL

NULL
或(OR)

OR

TRUE

FALSE

NULL

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

FALSE

NULL

NULL

TRUE

NULL

NULL
异或(XOR)

XOR

TRUE

FALSE

NULL

TRUE

FALSE

TRUE

NULL

FALSE

TRUE

FALSE

NULL

NULL

NULL

NULL

NULL
位运算符 | 位或& 位与<< 左移位>> 右移位~ 位非(单目运算，按位取反)mysq存储过程中常用的函数，字符串类型操作，数学类，日期时间类。 mysql存储过程基本函数一.字符串类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 ) //去除前端空格REPEAT (string2 ,count ) //重复count次REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_strRPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为lengthRTRIM (string2 ) //去除后端空格STRCMP (string1 ,string2 ) //逐字符比较两字串大小,SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1mysql> select substring('abcd',0,2);+———————–+| substring('abcd',0,2) |+———————–++———————–+1 row in set (0.00 sec)mysql> select substring('abcd',1,2);+———————–+| substring('abcd',1,2) |+———————–+| ab |+———————–+1 row in set (0.02 sec)TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符UCASE (string2 ) //转换成大写RIGHT(string2,length) //取string2最后length个字符SPACE(count) //生成count个空格二.数学类 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)返回19LEAST (number , number2 [,..]) //求最小值MOD (numerator ,denominator ) //求余POWER (number ,power ) //求指数RAND([seed]) //随机数ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]注：返回类型并非均为整数，如：(1)默认变为整形值mysql> select round(1.23);+————-+| round(1.23) |+————-+| 1 |+————-+1 row in set (0.00 sec)mysql> select round(1.56);+————-+| round(1.56) |+————-+| 2 |+————-+1 row in set (0.00 sec)(2)可以设定小数位数，返回浮点型数据mysql> select round(1.567,2);+—————-+| round(1.567,2) |+—————-+| 1.57 |+—————-+1 row in set (0.00 sec)SIGN (number2 ) //返回符号,正负或0SQRT(number2) //开平方三.日期时间类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 ) //秒数转成时间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) //分附:可用在INTERVAL中的类型DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,

注：关于MySQL的存储过程学习小结 附pdf文档下载的内容就先介绍到这里，更多相关文章的可以留意

代码注释
上一篇利用MySQL加密函数保护Web网站敏感数据的方法分享下一篇MySQL截取函数常用方法使用说明

作者：喵哥笔记

IDC笔记

学的不仅是技术，更是梦想！