MySQL存储过程书写

DELIMITER的使用

MySQL默认以分号为语句结束符，但是存储过程编写过程中有多条语句，需要用分号来分隔，而SQL一遇到分号就会开始
执行，这在命令行界面编写过程是个棘手的问题，所以使用DELIMITER关键字来重新定义分隔符，如：
DELIMITER ;;
这样存储过程中间就可以使用分号而不被部分执行，在存储过程最后写上;;即可。然后重新还原分节符为分号。
DELIMITER ;
这种用法多见于命令行编写存储过程时，现在我们使用图形界面的客户端编写存储过程，就很少使用到这个了，因为你可以输入完毕以后再运行，这样就没有回车导致提前运行的烦恼。
一般的编写套路就是：
DELIMITER ;; // 重定义结束符
... // 过程编写
;; // 结束
DELIMITER ; // 还原结束符

创建存储过程

CREATE PROCEDURE pr_add(a int)
BEGIN
SELECT * FROM t_user u WHERE u.ID=a;
END
这样就创建了一个简单可调用的存储过程。这里只出现了编写存储过程必要的几个要素。
需要注意的是存储过程体用关键字BEGIN和END标识，这个编程语言的的大括号是一个道理。

入参，出参，出入参

涉及的关键字为IN，OUT，INOUT，有过编程经验的朋友一看就能猜个大概了，有些c/c++程序员在编写
函数时就喜欢用in,out前缀来修饰形参，以显示表明参数特性，这里就是这个用途。
IN：入参，调用时必须给用此关键字修饰的形参传递实参，存储过程中对此入参的修改返回时无效，不写是默认是此类型；
OUT：可在存储过程中修改此参数，并能将修改的值返回；
INOUT：调用时必须指定，且能返回修改后的值；
具体使用形式如下：
CREATE PROCEDURE pr_add(IN a int)
......

定义局部变量

作为一个函数，使用变量是再平常不过的事情了，存储过程也是一个函数，所以也提供了定义变量的能力。
形式为:DECLARE var_name [,var_name...] datatype [DEFAULT value]; 
DECLARE str VARCHAR(10);
DECLARE i INT;
DECLARE pageNum INT DEFAULT 0;
上面有写了几种定义变量的形式，定义变量使用关键字DECLARE，后面跟变量名，然后是类型，然后是可选的默认值，这种写法和SQL风格相似，很好理解，不必多讲。

局部变量赋值

给变量赋值的形式和高级编程语言里不一样，所以拿出来说说，形式为：
SET var_name = expression
SET strName = "John";

Mysql用户变量和系统变量

在MySQL中，跟会话同生命周期的变量叫用户变量，跟MySQL数据库系统同生命周期的叫系统变量，使用方式如下：
用户变量：
SET @var_name = "hello world";
SELECT @var_name;
系统变量：
SET @@sort_buffer_size=8288608;
SELECT @@sort_buffer_size;
用户变量在存储过程中的作用就是全局变量的作用，使用的时候注意这个特点就行了。

条件语句

IF THEN
...
ELSE
...
END IF
使用实例如下
IF var_name is NULL THEN
SET var_name="default name"
END IF

case语句

case var_name
when 'John' then 'John Smith'
when 'Steven' then 'Steven Jobs'
else 'Hello'
end case
使用方式和高级编程语言有一定差别，需要多练习使熟练。

循环语句

有三种方式可以做循环，至于用那种，等有了具体的业务场景，自然就知道了，重要的是三种方式都熟练。
WHILE i <  10 DO
SET i = i + 1;
END WHILE

REPEATE
SET i = i + 1;
until i > 10
END REPEATE

test_loop : LOOP
SET i = i + 1;
IF i > 10 THEN
LEAVE test_loop;
END IF
END LOOP

执行存储过程

下面这里是正常流程，注意要用全局变量@，

prepare stmt from @queryStr;
execute stmt;
deallocate prepare stmt;

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 


(2).数学类 
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为小数位数] 


(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) //开平方