mysql存储过程

存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

MySQL存储过程创建的格式：CREATE PROCEDURE 过程名 ([IN|OUT|INOUT] 参数名数据类型,…,…)[特性 …] 过程体

注意事项：
MySQL默认以”;”为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将”;”当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用”,”分割开。

过程体的开始与结束使用BEGIN与END进行标识。

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数:该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数:调用时指定，并且可被改变和返回

调用存储过程用必须使用”@”符号用户变量一般以@开头

set @param='xx'; //如果需要设置参数
--select 'xx' into @param; //或者使用select设置 "--"表示mysql的注释 "/*..*/"为多行注释
--select concat(@param,'xx'); //select可使用concat函数追加
call 存储过程名(@param); //有参数则传入，没有就不传
select @param; //打印输出参数

set @param='xx'; //如果需要设置参数

--select 'xx' into @param; //或者使用select设置 "--"表示mysql的注释 "/*..*/"为多行注释

--select concat(@param,'xx'); //select可使用concat函数追加

call 存储过程名(@param); //有参数则传入，没有就不传

select @param; //打印输出参数

定义变量
declare 变量名[多个用”,”隔开] 变量类型 [default value]; 变量类型如：int float date varchar(20)等
例如：
DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric number(8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT ‘1999-12-31’;
DECLARE l_datetime datetime DEFAULT ‘1999-12-31 23:59:59’;
DECLARE l_varchar varchar(20) DEFAULT ‘hello world’;

变量赋值
set 变量名=表达式值如：set test=test+1;

查看某库有哪些存储过程
select name from mysql.proc where db=’数据库名’;

查看存储过程详情
show create procedure 数据库名.存储过程名

//输出test表的总记录数
delimiter//
create PROCEDURE proc_count(OUT test int)
BEGIN
	SELECT count(1) INTO test FROM test;
END;
//
delimiter;

CALL proc_count(@test);
SELECT @test;


//调用存储过程后输出13、3 然后打印输入参数输出13 可以看出IN参数在过程体中修改后只在过程体中有效，不会对原值产生影响
delimiter//
create PROCEDURE proc_lookParamsIn(IN test INT)
BEGIN
	SELECT test;
	SET test=3;
	SELECT test;
END;
//
delimiter;

SET @test=13;
CALL proc_lookParamsIn(@test);
SELECT @test;

//同理，以上参数类型改成OUT，则分别输出null、3、3，可以看出外部设置对OUT参数无效，但在过程体中修改会对原值产生影响
//同理，以上参数类型改成INOUT，则分别输出13、3、3，可以看出外部设置对INOUT参数有效，并且在过程体中修改也会对原值产生影响

//while 条件 do ... end while  |   if 条件 then  ... else ... end if   |   case 变量 when 值1 then ... when 值2 then ... end case
delimiter//
create PROCEDURE proc_demo()
BEGIN
declare i INT;
SET i=1;
WHILE i<50 DO
	IF i<25 THEN
		INSERT INTO test (name) VALUES(i+100);
	ELSE
		CASE i WHEN 47 THEN
			INSERT INTO test (name) VALUES(i+1000);
		WHEN 48 THEN
			INSERT INTO test (name) VALUES(i+10000);
		ELSE
			INSERT INTO test (name) VALUES(i+5000);
		END CASE;
	END IF;
SET i=i+1;
END WHILE;
END;
//
delimiter;

call proc_demo();

//repeat...end repeat |  loop...end loop  |  lables标号  |  iterate迭代等不详述

//输出test表的总记录数

delimiter//

create PROCEDURE proc_count(OUT test int)

BEGIN

SELECT count(1) INTO test FROM test;

END;

delimiter;

CALL proc_count(@test);

SELECT @test;

//调用存储过程后输出13、3 然后打印输入参数输出13 可以看出IN参数在过程体中修改后只在过程体中有效，不会对原值产生影响

delimiter//

create PROCEDURE proc_lookParamsIn(IN test INT)

BEGIN

SELECT test;

SET test=3;

SELECT test;

END;

delimiter;

SET @test=13;

CALL proc_lookParamsIn(@test);

SELECT @test;

//同理，以上参数类型改成OUT，则分别输出null、3、3，可以看出外部设置对OUT参数无效，但在过程体中修改会对原值产生影响

//同理，以上参数类型改成INOUT，则分别输出13、3、3，可以看出外部设置对INOUT参数有效，并且在过程体中修改也会对原值产生影响

//while 条件 do ... end while | if 条件 then ... else ... end if | case 变量 when 值1 then ... when 值2 then ... end case

delimiter//

create PROCEDURE proc_demo()

BEGIN

declare i INT;

SET i=1;

WHILE i<50 DO

IF i<25 THEN

INSERT INTO test (name) VALUES(i+100);

ELSE

CASE i WHEN 47 THEN

INSERT INTO test (name) VALUES(i+1000);

WHEN 48 THEN

INSERT INTO test (name) VALUES(i+10000);

ELSE

INSERT INTO test (name) VALUES(i+5000);

END CASE;

END IF;

SET i=i+1;

END WHILE;

END;

delimiter;

call proc_demo();

//repeat...end repeat | loop...end loop | lables标号 | iterate迭代等不详述

注：mysql中处理字符串时，默认第一个字符下标为1，php中为0

常用函数:

//字符串类：
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个字符,
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)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
SQRT(number2) //开平方

//日期时间类：
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

//字符串类：

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个字符,

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)返回19

LEAST (number , number2 [,..]) //求最小值

MOD (numerator ,denominator ) //求余

POWER (number ,power ) //求指数

RAND([seed]) //随机数

ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

SQRT(number2) //开平方

//日期时间类：

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

参考地址：http://mp.weixin.qq.com/s?__biz=MjM5NDMwNjMzNA==&mid=203926493&idx=1&sn=6538524997d1df8c6e8563fe3d5d564e&scene=2#rd

唐晓锋–BLOG

This is just a personal note.

发表评论取消回复

发表评论 取消回复

发表评论取消回复