mysql函数变量存储_MySQL存储过程、存储函数、变量

简单说，存储过程就是一条或者多条SQL语句的集合

格式如下：

MySQL

1

2

CREATEPROCEDUREsp_name([proc_parameter[,...]])

[characteristic...]routine_body

sp_name参数是存储过程的名称。

proc_parameter表示存储过程的参数列表。

characteristic参数指定存储过程的特性。

routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。

proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。

其形式如下：

MySQL

1

[IN|OUT|INOUT]param_nametype

IN表示输入参数、OUT表示输出参数、INOUT表示是输入输出。

param_name参数是存储过程的参数名称。

type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型。

我们来看下具体例子

首先我们创建一个表，也可以使用之前的：

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

createtablet_shop(

orderidint(10),

supplieridint(10),

itemnamevarchar(20),

priceint(10),

totalint(10),

primary key(orderid)

);

insertintot_shopvalues(1,1000,"apple",2,10);

insertintot_shopvalues(2,1000,"orange",4,20);

insertintot_shopvalues(3,1001,"lemon",10,2);

insertintot_shopvalues(4,1001,"apple",2,10);

insertintot_shopvalues(5,1001,"orange",3,20);

insertintot_shopvalues(6,1002,"lemon",9,10);

insertintot_shopvalues(8,1001,"orange",5,5);

先创建一个不带参数的存储过程：

MySQL

1

2

3

4

createprocedureselectMaxCost()

begin

selectmax(price*total)fromt_shop;

end;

你会发现MySQL错误，这是因为;是语句分隔符，在执行完select max(price*total)  from t_shop;这句后结束了，再去执行end;，这样是不符合我们的想法的。

解决办法就是使用delimiter //，它可以临时把//当成语句分隔符，整个存储过程结束后，再设置delimiter ;改回默认语句分隔符

MySQL

1

2

3

4

5

delimiter//

createprocedureselectMaxCost()

begin

selectmax(price*total)fromt_shop;

end//

调用这个存储过程使用call：

MySQL

1

callselectMaxCost();

我们再来看看带有参数的例子：

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

delimiter//

dropprocedureif existsselectMaxCost//

createprocedureselectMaxCost(innsupplieridint,outmaxcostint,outmincostint)

begin

selectmax(price*total)intomaxcostfromt_shopwheresupplierid=nsupplierid;

selectmin(price*total)intomincostfromt_shopwheresupplierid=nsupplierid;

end//

delimiter;

callselectMaxCost(1001,@maxcost,@mincost);

select@maxcost,@mincost;

我们增加了一句drop procedure if exists selectMaxCost，是为了在练习过程中保证存储过程名不会重复。

存储过程中将max(price*total)的值使用into方法传给maxcost，然后在call调用该存储过程的时候需要在maxcost前加上@，就能得到值。

最后使用select查看数值。

创建存储函数

格式如下：

MySQL

1

2

3

CREATE FUNCTIONsp_name([func_parameter[,...]])

RETURNStype

[characteristic...]routine_body

sp_name表示存储函数的名称；

func_parameter表示存储函数的参数列表；

RETURNS type指定返回值的类型；

characteristic参数指定存储函数的特性

routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。

func_parameter用法跟存储过程的参数差不多，只是去掉了IN、OUT、INOUT

我们直接把上面的例子修改下：

MySQL

1

2

3

4

5

6

7

8

9

10

delimiter//

drop functionif existsselectMaxCost//

create functionselectMaxCost(nsupplieridint)

returnsint

begin

return(selectmax(price*total)fromt_shopwheresupplierid=nsupplierid);

end//

delimiter;

selectselectMaxCost(1001);

变量的使用

在存储过程和函数中，可以定义和使用变量。可以使用DECLARE关键字来定义变量，这些变量只能在BEGIN…END之前使用

定义变量的格式如下：

MySQL

1

DECLAREvar_name[,...]type[DEFAULTvalue]

例如：

MySQL

1

declaremNumintdefault0;

设置变量值则使用SET：

MySQL

1

setmNum=30;

或者使用SELECT语句的查询结果为变量赋值：

MySQL

1

selectmax(price*total)intomNumfromt_shopwheresupplierid=nsupplierid;

具体来个例子，假设price是美元，在存储过程中换成人民币得出查询结果，修改上面的例子：

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

delimiter//

drop functionif existsselectMaxCost//

create functionselectMaxCost(nsupplieridint)

returnsfloat

begin

declaremNumfloatdefault0;

selectmax(price*total)intomNumfromt_shopwheresupplierid=nsupplierid;

setmNum=mNum*6.89;

returnmNum;

end//

delimiter;

selectselectMaxCost(1001);