存储过程与自定义函数

存储过程——概述

公众账号：InsideMySQL

存储在数据库端的一组SQL语句集

用户可以通过存储过程名和传参多次调用的程序模块

存储过程的特点：

使用灵活，可以使用流控制语句、自定义变量等完成复杂的业务逻辑

提高数据安全性，屏蔽应用程序直接对表的操作，易于进行审计

减少网络传输

提高代码维护的复杂度，实际使用中要评估场景是否适合

存储过程-流控制语言 公众账号：InsideMySQL

流控制

描述

IFsearch_conditionTHENstatement_list

IF

[ELSEIFsearch_conditionTHENstatement_list][ELSEstatement_list]

ENDIF

CASEcase_value

CASE

WHENwhen_valueTHENstatement_list

[ELSEstatement_list]ENDCASE

WHILE

WHILEsearch_conditionDOstatement_listENDWHILE

REPEAT

REPEATstatement_list

UNTILsearch_conditionENDREPEAT

存储过程-基本语法

CREATE

[DEFINER={user|CURRENT_USER}]

PROCEDUREsp_name([proc_parameter[,...]])

[characteristic...]routine_body

proc_parameter:

[IN|OUT|INOUT]param_nametypetype:

AnyvalidMySQLdatatype

characteristic:

COMMENT'string'

|[NOT]DETERMINISTIC

routine_body:

ValidSQLroutinestatement

公众账号：InsideMySQL

存储过程-示例与调用 公众账号：InsideMySQL

CREATEPROCEDUREproc_test1(INtotalINT,OUTresINT)BEGIN

mysql>set@total=10;

QueryOK,0rowsaffected

DECLAREiINT;SETi=1;

SETres=1;

IFtotal<=0THEN

SETtotal=1;

mysql>set@res=1;

QueryOK,0rowsaffected

mysql>callproc_test1(@total,@res);

QueryOK,1rowaffected

END;

ENDIF;

WHILEi<=totalDO

SETres=res*i;

INSERTINTOtbl_proc_test(num)VALUES(res);SETi=i+1;

ENDWHILE;

mysql>select@res;

+ +

|@res |

+ +

|3628800|

+ +

1rowinset

与存储过程非常类似

但是必须带有返回值

与内置的SUM(),MAX()等MySQL原生函数使用方法类似

SELECTfunc(val);

SELECT*fromtblwherecol=func(val);

自定义函数可能在遍历每条记录中使用

注意性能损耗

自定义函数 公众账号：InsideMySQL

自定义函数——基本语法

CREATE

[DEFINER={user|CURRENT_USER}]

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

RETURNStype

[characteristic...]routine_bodyfunc_parameter:

param_nametypetype:

AnyvalidMySQLdatatype

characteristic:

COMMENT'string'

|[NOT]DETERMINISTIC

routine_body:

ValidSQLroutinestatement

公众账号：InsideMySQL

自定义函数——示例与调用 公众账号：InsideMySQL

CREATEFUNCTIONfunc_test1(totalINT)RETURNSINT

BEGIN

DECLAREiINT;DECLAREresINT;SETi=1;

SETres=1;

IFtotal<=0THEN

SETtotal=1;

mysql>selectfunc_test1(4);

+ +

|func_test1(4)|

+ +

| 24|

+ +

1rowinset

END;

ENDIF;WHILEi<=

total

DO