mysql 存储过程 （procedure）和常用函数（function）

一， 基本知识

1, 定义

  一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

2，demo

DELIMITER //
DROP PROCEDURE IF EXISTS `pro_test`// 
CREATE PROCEDURE `pro_test`(IN temp INT, OUT outTest VARCHAR)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE len INT DEFAULT 100;
    DECLARE `sum` INT DEFAULT 0;
    DECLARE `count` INT DEFAULT 0;

    SELECT COUNT(*) INTO `count` FROM t_student;
END //
DELIMITER ;

CALL pro_test(3, @out);

• DELIMITER：声明分隔符为 // ，防止被当成sql 语句进行处理；
• BEGIN, END ： 存储过程开始和结束；
• IN : 输入参数， OUT： 输出参数， INPUT： 输入输出参数
• DECLARE ： 声明变量；
• into ： 将从数据库查询的结构放入到参数中

二，条件和循环语句

1，条件语句（ if code then code else code end if)

: eg:

DELIMITER //
DROP PROCEDURE IF EXISTS `pro_test`// 
CREATE PROCEDURE `pro_test`(IN temp INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE len INT DEFAULT 100;
    DECLARE `sum` INT DEFAULT 0;
    DECLARE `count` INT DEFAULT 0;

    IF i < len THEN
        SET `sum` = `sum` + 1;
        SET i = i + 1;
    ELSE 
        SET `sum` = 44;
    END IF;

    SELECT COUNT(*) INTO `count` FROM t_student;
    SELECT i, len, `sum`, `count`;
END //
DELIMITER ;

2，while 循环（while code do code end while）

: eg :

DELIMITER //
DROP PROCEDURE IF EXISTS `pro_test`// 
CREATE PROCEDURE `pro_test`(IN temp INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE len INT DEFAULT 100;
    DECLARE `sum` INT DEFAULT 0;
    DECLARE `count` INT DEFAULT 0;

    WHILE i < 100 DO
        SET `sum` = `sum` + i;
        SET i = i + 1;
    END WHILE;

    SELECT COUNT(*) INTO `count` FROM t_student;
    SELECT i, len, `sum`, `count`;
END //
DELIMITER ;

3，repeat 循环（repeat code until code end repeat）

: eg:

DELIMITER //
DROP PROCEDURE IF EXISTS `pro_test`// 
CREATE PROCEDURE `pro_test`(IN temp INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE len INT DEFAULT 100;
    DECLARE `sum` INT DEFAULT 0;
    DECLARE `count` INT DEFAULT 0;

    REPEAT 
        SET `sum` = `sum`+ i;
        SET i = i + 1;
    UNTIL i = 100
    END REPEAT;

    SELECT i, len, `sum`, `count`;
END //
DELIMITER ;

4，loop 循环（）

: eg:

DELIMITER //
DROP PROCEDURE IF EXISTS `pro_test`// 
CREATE PROCEDURE `pro_test`(IN temp INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE len INT DEFAULT 100;
    DECLARE `sum` INT DEFAULT 0;
    DECLARE `count` INT DEFAULT 0;

    loop_lable:LOOP
        SET `sum` = `sum` + 1;
        SET i = i + 1;
        IF i>=100 THEN
            LEAVE loop_lable;
        END IF;
    END LOOP;

    SELECT i, len, `sum`, `count`;
END //
DELIMITER ;

三，基本函数

-- mysql 常用函数

SELECT CONCAT('连接函数1', '--', '连接函数2');

SELECT LENGTH('字节长度');

SELECT CHAR_LENGTH('字符长度');

SELECT SUBSTRING('截取字符长度', 1, 3);

SELECT REPLACE('替换旧字符串内容', '旧字符串', '新字符串');

SELECT REVERSE('反转字符串内容');

SELECT POSITION('一' IN '找到字符串第一次出现的位置');

SELECT LOCATE('一', '找到字符串第一次出现的位置');

SELECT TRIM(' 移除字符串前后的空字符串 ');

SELECT CURRENT_TIMESTAMP(); -- 当前的时间

字符函数

函数名称	函数描述	举例
concat()	连接多个字符串	concat(‘a’, ‘b’, ‘cd’)
concat_ws()	使用指定的分隔符连接多个字符串	concat_ws(‘-‘, ‘a’, ‘b’, ‘cd’)
format()	格式化数字	format(1233.1334, 2)
lower()	将字符串变为小写	lower(‘AdbdD’)
upper()	将字符串变为大写	upper(‘iDsdD’)
left()	获取左边字符串n 位	left(‘mysql’, 3)
right()	获取右边字符串 n位	right(‘mysql’, 2)
length()	获取字符串长度（包含空格）	length(‘abcd’)
ltrim()	删除前导空格	ltrim(‘abcd’)
rtrim()	删除后导空格	rtirm(‘abcd’)
trim()	删除字符串前后空格	trim(‘abcd’)
replace()	使用新字符串取代旧字符串	replace(‘abcdefa’, ‘a’, ‘–’)
substring()	截取字符串(起始位置可以是负值, 第二个参数表示截取的长度，不是终止位置)	substring(‘abcde’, 2,3)

数字运算符和函数

函数名称	函数描述	举例
ceil()	进 1 取整	ceil(3.3)
floor()	舍1 取整	floor(3,3)
round()	小数点后n位，四舍五入	round(3.3334， 2)
a div b	整数除法	5 div 3
a mod b	取余数	5 mod 3
power()	幂运算	power(3, 4)

比较运算符

函数名称	函数描述	举例
[not] between .. and …	判断数字是否在一个区间	3 between 1 and 5
[not] in	判断是否在一个集合之中	‘x’ in (‘a’, ‘c’, ‘x’, ‘y’)
is [not] null	判断是否为空	null is null

日期函数

函数名称	函数描述	举例
now()	获取当前时间	now()
curDate()	当前日期
curTime()	当前时间
version()	获取当前版本

信息函数

函数名称	函数描述	举例
connection_id()
database()	获取当前数据库
last_insert_id()	获取最后一次插入的id
user()	获取当前用户

聚合函数

函数名称	函数描述	举例
avg()	求平均值
count()	计数
max()	求最大值
min()	求最小值
sum()	求和

参考地址：
http://blog.csdn.net/sugang_ximi/article/details/6664748