MySQL基础之函数和存储过程

1、函数定义语法：

create function fun_name(参数名称 参数类型) returns 返回值类型    方法体；

函数体说明：

1. 函数体由合法的SQL语句构成；

2.函数体可以是简单的select或者insert 语句；

3.函数体如果为复合结构则使用begin end语句

4.复合结构可以包含声明、循环、控制语句。

下面举例说明函数的简单应用

2、无参函数：

更新商品名称并且获取商品总数：

CREATE FUNCTION f1()RETURNS INT

BEGIN

DECLARE num int UNSIGNED DEFAULT 0;

UPDATE goods SET goods_name=concat(goods_id,'_',goods_name);

SELECT count(*) FROM goods INTO num ;

RETURN num;

END;

调用函数：call f1();

3、带参数的函数：

查询商品id大于n的平均价格 

DROP FUNCTION IF EXISTS f1;

CREATE FUNCTION f1(n int )RETURNS FLOAT

BEGIN

DECLARE num FLOAT UNSIGNED DEFAULT 0;

SELECT avg(price) FROM goods WHERE goods_id>n INTO num;

RETURN num;

END;

select f1(3);

1、MySQL语句需要先编译再执行。其编译执行过程如下：

     SQL命令→SQL引擎→SQL引擎对其进行分析，语法是否正确，若正确→将MySQL语句编译成识别命令，然后执行→将结果返回客户端。

存储过程是预编译，第一次调用，将编译结果存储到内存，下次调用直接从内存里面获取。

下面从无参、带 in 参数、带out参数、带inout参数的存储过程来说明

2、1. 无参存储过程:请参考  MySQL之存储过程  篇。

3、带 in 参数的存储过程：参数的值在调用时指定，可以在存储过程中修改，但不可返回。

例子：

找出价格大于n的商品

DROP PROCEDURE if EXISTS p1;

CREATE PROCEDURE p1(in n INT)

BEGIN

SELECT * FROM goods WHERE price>n;

END;

CALL p1(2200);

4、带out参数的存储过程：可在存储过程中被修改，可返回。

找出价格大于n的商品的平均价格

DROP PROCEDURE if EXISTS p1;

CREATE PROCEDURE p1(in n INT,OUT  avgPrice INT)

BEGIN

SELECT avg(price) FROM goods WHERE price>n INTO avgPrice;

END;

CALL p1(2200,@avgPrice);

SELECT @avgPrice;

5、带inout参数的存储过程：调用时指定，可返回。

返回格式化的时间 ：

DROP PROCEDURE if EXISTS p1;

CREATE PROCEDURE p1(INOUT time VARCHAR(64))

BEGIN

SELECT DATE_FORMAT(time,'%Y年%m月%d日 %h点:%i分:%s秒') INTO time;

END;

SET @Tm=nOW();

CALL p1(@Tm);

SELECT @Tm;