MySQL云数据库应用与实践 课件 ch07-数据库服务端编程

第七章

数据库服务端编程Database西北工业大学NorthwesternPolytechnicalUniversity数据库目录7.1

实战目标与准备7.2变量7.3

函数7.4存储过程7.5SQL控制流程语句7.6游标7.7触发器7.8预处理SQL语句7.9银行场景化综合实战7.1实战目标与准备实战目标数据库服务端编程指编写运行在数据库服务端的程序，具体主要包括服务端的存储过程、自定义函数、触发器等。为了便于描述，本书将存储过程、用户自定义函数、触发器等不同形式的数据库服务端程序统称为SQL程序。掌握MySQL数据库服务端编程的基本知识，包括编写服务端程序需要的变量、控制流程语句、游标、存储过程、自定义函数、触发器、事件等。7.2变量全局变量（global，系统变量，不能自定义，可改值）会话变量（session，系统变量，不能自定义，可改值）用户变量（@var，用户可以自定义）局部变量（参数,declare）

7.2变量MySQL的用户变量

@var，以“@”开头，可以作用于当前整个连接,但是若当前连接断开后,所定义的用户变量都会消失。可以在存储过程之间传递全局范围的变量。

赋值：set@count=1;

selectcount(id)into@count

fromitemswhereprice<99;读取：select@count；7.2变量MySQL的局部变量

注意：

1）支持SQL的数据类型

2）给出DEFAULT，该变量进入BEGIN块时初始化为该值.例：DECLARE语句部分声明变量,SET进行赋值。declarenamesvarchar(10)default'';declareiintdefault0;setnames=concat('test','');字符串拼接函数：concat7.2变量MySQL变量对比

7.3函数——系统内置函数SQL常用的内置函数可以分为：数学函数（如绝对值函数等）聚合函数（如求和、求平均函数等）字符串函数（如求字符串长度、求子串函数等）日期和时间函数（如返回当前日期函数等）格式化函数（如字符串转IP地址函数等）控制流函数（如逻辑判断函数等）加密函数（如使用密钥对字符串加密函数等）系统信息函数（如返回当前数据库名、服务器版本函数等）7.3函数——用户自定义函数delimiter$$createfunction函数名(参数类型)returns返回类型Begin

方法体

return数据类型；end$$delimiter；select函数名

7.3函数——用户自定义函数要求：指定年和月时，编写一个自定义函数：求该年该月的平均气温。某气象站有一张表temperature，每天在2点，8点，14点，20点自动采集温度7.3函数——用户自定义函数DELIMITER$CREATEFUNCTIONtemp_avg(myyearint,mymonthint)RETURNSrealBEGINDECLAREtemprealDEFAULT0;SELECT(SUM(T02)+SUM(T08)+SUM(T14)+SUM(T20))/(Count(T02)+Count(T08)+Count(T14)+Count(T20))INTOtempFROMTemperatureWHEREYear=myyearANDMonth=mymonth;returntemp;END$某气象站有一张表temperature，每天在2点，8点，14点，20点自动采集温度7.4存储过程delimiter$$createprocedure存储过程名(参数)Begin

方法体end$$delimiter；call存储过程名

7.4存储过程MYSQL存储过程/存储函数完整语法7.4存储过程存储过程示例7.4存储过程MySQL的参数传递存储过程：IN，OUT，INOUT类型函数：所有参数为IN类型7.5SQL控制流程语句MySQL流程控制：条件分支IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDIFCASEcase_value

WHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASECASE

WHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASEIFCASEWHEN7.5SQL控制流程语句MySQL流程控制：循环REPEAT[begin_label:]REPEATstatement_listUNTILsearch_conditionENDREPEAT[end_label]mysql>delimiter//mysql>CREATEPROCEDUREdorepeat(p1INT)BEGINSET@x=0;

REPEATSET@x=@x+1;

UNTIL@x>p1ENDREPEAT;END//QueryOK,0rowsaffected(0.00sec)mysql>CALLdorepeat(1000)//QueryOK,0rowsaffected(0.00sec)7.5SQL控制流程语句MySQL流程控制：循环WHILE[begin_label:]WHILEsearch_conditionDOstatement_listENDWHILE[end_label]CREATEPROCEDURE

dowhile()BEGINDECLAREv1INTDEFAULT5;

WHILEv1>0DO...SETv1=v1-1;

ENDWHILE;END;7.5SQL控制流程语句MySQL流程控制：循环LOOP[begin_label:]LOOPstatement_listENDLOOP[end_label]CREATEPROCEDUREdoiterate(p1INT)BEGIN

label1:LOOPSETp1=p1+1;IFp1<10THEN

ITERATElabel1;ENDIF;

LEAVElabel1;

ENDLOOP

label1;SET@x=p1;END;7.6游标游标

若查询SQL只返回一条记录可以放入一个变量，当返回

多条记录时，需使用游标逐行处理结果集。声明游标(DECLARE)打开游标(OPEN)使用游标读取数据(FETCH)关闭游标(CLOSE)7.6游标delimiter$$createprocedureget_cs_s()begin

declareout_namesvarchar(100)default'';declaretmpvarchar(100)default'';declarep_snovarchar(5)default'';declareP_snamevarchar(10)default'';declaredonebooleandefault0;

declarecursor_namecursorforselectsno,snamefromswheresdept='CS';

declarecontinuehandlerforsqlstate'02000'

setdone=1;

--ER_SP_FETCH_NO_DATA

opencursor_name;

fetchcursor_nameintop_sno,p_sname;

repeat

settmp=concat(p_sno,'_',p_sname);setout_names=concat(out_names,tmp,'');

fetchcursor_nameintop_sno,p_sname;

untildone

endrepeat;

closecursor_name;

selectout_names;end$$delimiter;7.7触发器1.触发器定义-MySQL7.7触发器2.触发器的分类（DML）BEFORE触发器AFTER触发器INSTEADOF触发器执行INSTEADOF触发器代替通常的增删改等触发动作。PG:仅支持视图上的该触发器。INSERT触发器UPDATE触发器TRUNCATE触发器DELETE触发器在定义了触发器的表上发生修改操作时，会自动为触发器的运行而派生两个记录：1)Old—存放旧记录(for:delete,update)2)New—存放新记录(for:insert,update)REFERENCING子句：将对应记录声明为一个表格使用7.7触发器2.触发器的分类（DML）WhenEventRow-levelStatement-level(mysql不支持)BEFORE/AFTER

INSERT/UPDATE/DELETETableTableandViewBEFORE/AFTERTRUNCATE-TableINSTEADOF（mysql不支持）INSERT/UPDATE/DELETEViewTRUNCATE:清空表中的数据，但是不删除表结构Trigger可以暂停或者启用7.7触发器3.触发器修改/删除修改：不支持

删除DROPTRIGGERdatabase.trigger_name(mysql)DROPTRIGGERtrigger_nameONtablename

(其他多数DBMS)查看触发器：SHOWTRIGGERS7.7触发器引用：/doc/refman/8.0/en/signal.htmlusestudent;droptriggerifexistsdel_s;delimiter$$createtriggerdel_sbeforedeleteonsforeachrowbeginifOLD.snoin(selectdistinct(sno)fromsc)then

signalsqlstate'03100'setmessage_text='不能删除有选课记录的学生';endif;end$$delimiter;sqlstate：='00'(success)='01'(warning)='02'(notfound)>'02'(exception)='40'MySQL中的异常处理：signalsqlstate7.7触发器signalsqlstate'31000'：错误代码说明（00表示正常，不能用于signal语句）

7.7触发器/doc/refman/8.0/en/trigger-syntax.html同一个表上可以存在两个同类型同事件的触发器（如两个beforeupdate），顺序是按照创建顺序被触发；如果希望显示指定触发器的顺序，可以通过:precedes或者follows关键字指定7.7触发器4.MySQL中的Event

(不是一种通常意义的触发器）/doc/refman/8.0/en/create-event.html7.7触发器5.触发器示例1-MySQL例1：创建一个触发器，当每插入一条记录时累加金额CREATETRIGGERins_sumBEFOREINSERTONaccountFOREACHROWSET@sum=@sum+NEW.amount;DROPTRIGGERins_sum;7.8预处理SQLprepare预处理：指将SQL语句中的关键字和数据分离，对固定SQL语句中只进行一次解析或查询计划生成，这样在数据不同但SQL语句相同的情况下，执行性能会显著提升。PREPAREstmt_nameFROMpreparable_stmtEXECUTEstmt_name[USING@var_name[,@var_name]...]{DEALLOCATE|DROP}PREPAREstmt_name7.8预处理SQLmysql>