第10章 存储过程与存储函数（课件）-《MySQL数据库技术与应用》.pptxVIP

第十章存储过程与

存储函数;主要内容;第一节存储过程和存储函数概述;第一节存储过程和存储函数是什么;第一节存储过程和存储函数是什么;第一节为什么用存储过程和存储函数;二、存储过程和存储函数的缺点;第二节创建存储过程和存储函数;创建存储函数：

CREATEFUNCTIONfunc_name([func_parameter])

RETURNStype[characteristic...]

BEGIN

存储函数体

END

调用存储函数：

SELECT存储函数名(参数名);

例子：

CREATEFUNCTIONemail_by_id(emp_idINT)RETURNSVARCHAR(25)

CONTAINSSQL

BEGIN

RETURN(SELECTemailFROMemployeesWHEREemployee_id=emp_id);

END;存储过程体和存储函数体语法：

1.定义变量

DECLARE变量名类型[default值];

DECLAREmyparamINTDEFAULT100;

2.变量赋值

方式一：一般用于赋简单的值

SET变量名=值;

SET变量名:=值;

方式二：一般用于赋表中的字段值

SELECT字段名或表达式INTO变量名FROM表;

3.查看变量

SELECT局部变量名;

;

其他语法结构

分支结构语法:

IF表达式1THEN操作1

[ELSEIF表达式2THEN操作2]…

[ELSE操作N]

ENDIF

;

CASE语句是一种在编程语言中常见的条件语句，用于根据条件的匹配来执行不同的代码块。

情况一：类似于switch

CASE表达式

WHEN值1THEN结果1或语句1(如果是语句，需要加分号)

WHEN值2THEN结果2或语句2(如果是语句，需要加分号)

...

ELSE结果n或语句n(如果是语句，需要加分号)

END[case]（如果是放在beginend中需要加上case，如果放在select后面不需要）;情况二：类似于多重if

CASE

WHEN条件1THEN结果1或语句1(如果是语句，需要加分号)

WHEN条件2THEN结果2或语句2(如果是语句，需要加分号)

...

ELSE结果n或语句n(如果是语句，需要加分号)

END[case]（如果是放在beginend中需要加上case，如果放在select后面不需要）;循环结构LOOP

LOOP

循环体

ENDLOOP

循环结构WHILE

[while_label:]WHILE循环条件DO

循环体

ENDWHILE[while_label];

循环结构REPEAT

[repeat_label:]REPEAT

循环体

UNTIL结束循环的条件表达式

ENDREPEAT[repeat_label]

;例：创建存储过程，实现累加运算，计算1+2+…+n。

DELIMITER//

CREATEPROCEDURE`add_num`(INnINT)

BEGIN

DECLAREiINT;

DECLAREsumINT;

SETi=1;

SETsum=0;

WHILEi=nDO

SETsum=sum+i;

SETi=i+1;

ENDWHILE;

SELECTsum;

END//

DELIMITER;

;例：创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。

DELIMITER//

CREATEFUNCTIONcount_by_id(dept_idINT)

RETURNSINT

LANGUAGESQL

NOTDETERMINISTIC

READSSQLDATA

SQLSECURITYDEFINER

COMMENT查询部门平均工资

BEGIN

RETURN(SELECTCOUNT(*)FROMemployeesWHEREdepartment_id=dept_id);

END//

DELIMITER;

;MySQL存储了存储过程和函数的状态信息，用户可以使用SHOWSTATUS语句或SHOWCREATE语句来查看，也可直接从系统的information_schema数据库中查询。

使用SHOWCREATE语句查看存储过程和函数的创建信息

SHOWCREATE{PROCEDURE|FUN