数据库系统原理与设计实验教程 第4版 课件 第5章 数据库编程技术

第1页第5章数据库编程技术数据库系统原理实验教程第4版5.1相关知识5.1.1游标5.1.2存储过程5.1.3触发器5.2实验十二游标与存储过程5.2.1实验目的与要求5.2.2实验案例5.2.3实验内容5.3实验十三触发器5.3.1实验目的与要求5.3.2实验案例5.3.3实验内容目录第3页5.1相关知识5.1.1游标游标是一种允许用户访问单独的数据行的数据访问机制。游标主要用在存储过程、触发器和T-SQL脚本中，使用游标，可以对由SELECT语句返回的结果集记录进行逐行处理。使用游标必须经历五个步骤：①定义游标：DECLARE②打开游标：OPEN③逐行提取游标集中的行：FETCH④关闭游标：CLOSE⑤释放游标：DEALLOCATE第4页1.定义游标语法：DECLAREcursor_nameSCROLLCURSORFORsql_staments[FOR[READONLY|UPDATE{OFcolumn_name_list[,...n]]]其中：·cursor_name：用户定义的游标名。·sql_staments：定义游标结果集的标准SELECT语句。·FOR：后面的短语定义游标属性只读或更新，缺省时为UPDATE。·UPDATE{OFcolumn_name_list}：定义游标内可更新的列。如果指定OFcolumn_name_list[,...n]参数，则只允许修改所列出的列。如果在UPDATE中未指定列的列表，则可以更新所有列。第5页·READONLY：在UPDATE或DELETE语句的WHERECURRENTOF子句中不能引用游标。该选项替代要更新的游标的默认功能。·SCROLL：指定所有的提取选项(FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE)均可用。如果在DECLARECURSOR中未指定SCROLL，则NEXT是唯一支持的提取选项。注意：①当游标移至尾部，不可以再读取游标，必须关闭游标然后重新打开游标。②可以通过检查全局变量@@fetch_status来判断是否已读完游标集中所有行。第6页2.打开游标使用OPEN语句执行SELECT语句并生成游标。语法为：OPENcurser_name3.提取游标①逐行提取游标集中的行：FETCHcurser_name[INTO@variable_name[,...n]]②FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar

}|Relative{n|@nvar}][FROM{cursor_name|@cursor_variable_name}[INTO@variable_name[,...n]]第7页其中：·NEXT：返回紧跟当前行之后的结果行，并且当前行递增为结果行。如果FETCHNEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。·PRIOR：返回紧临当前行前面的结果行，并且当前行递减为结果行。如果FETCHPRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。·FIRST：返回游标中的第一行并将其作为当前行。·LAST：返回游标中的最后一行并将其作为当前行。·ABSOLUTE{n|@nvar}：如果n或@nvar为正数，返回从游标头开始的第n行并将返回的行变成新的当前行。如果n或@nvar为负数，返回游标尾之前的第n行并将返回的行变成新的当前行。如果n或@nvar为0，则没有行返回。n必须为整型常量且@nvar必须为smallint、tinyint或int。·RELATIVE{n|@nvar}：如果n或@nvar为正数，返回当前行之后的第n行并将返回的行变成新的当前行。如果n或@nvar为负数，返回当前行之前的第n行并将返回的行变成新的当前行。如果n或@nvar为0，返回当前行。如果对游标的第一次提取操作时将FETCHRELATIVE的n或@nvar指定为负数或0，则没有行返回。n必须为整型常量且@nvar必须为smallint、tinyint或int。·INTO@variable_name[,...n]：把每列中的数据转移到指定的变量中。

第8页4.关闭游标关闭游标可以释放某些资源，如游标结果集和对当前行的锁定，如果重新发出一个OPEN语句，则该游标结构仍可用于处理。语法为：CLOSEcurser_name5.释放游标DEALLOCATE语句则完全释放分配给游标的资源，包括游标名称。在游标被释放后，必须使用DECLARE语句重新生成游标。语法为：DEALLOCATEcurser_name6.删除游标集中当前行语法：DELETEFROMtable_nameWHERECURRENTOFcurser_name注意：从游标中删除一行后，游标定位于被删除的游标之后的一行，必须再用FETCH得到该行。第9页7.更新游标集中当前行语法：UPDATEtable_name

SETcolumn_name=expression[,column_name=expression]WHERECURRENTOFcurser_name第10页5.1.2存储过程SQLServer提供了一种方法，它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成，以实现某个任务，这种方法就是存储过程。存储过程是经过编译和优化后存储在数据库服务器中SQL语句写的过程，使用时只要调用即可。存储过程的优点是：(1)提供了在服务器端快速执行SQL语句的有效途径。(2)降低了客户机和服务器之间的通信量(3)方便实施企业规则。(4)业务封装后，对数据库系统提供了一定的安全保证。第11页创建存储过程时，需要确定存储过程的3个组成部分：①所有的输入参数以及传给调用者的输出参数。②被执行的针对数据库的操作语句，包括调用其它存储过程的语句。③返回给调用者的状态值，以指明调用是成功还是失败。第12页1.创建存储过程语法：CREATEPROCEDUREprocedure_name

[;number][{@parameterdatatype}[OUTPUT]][,...n]ASsql_statement[,...n]其中：·procedure_name：存储过程的名称。创建临时过程，在procedure_name前面加一个编号符，即#procedure_name；创建全局临时过程，在procedure_name前面加两个编号符，即##procedure_name。完整的名称(包括#或##)不能超过128个字符。过程所有者的名称是可选的。第13页·number：是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。DROPPROCEDUREorderproc语句将除去整个组。·@parameter：过程中的参数，最多可以有2100个参数。·datatype：参数的数据类型。所有数据类型(包括text、ntext和image)均可以用作存储过程的参数。·OUTPUT：表明参数是输出参数，text、ntext和image参数可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。·n：表示最多可以指定2100个参数的占位符。·AS：指定过程要执行的操作。·sql_statement：过程中的Transact-SQL语句。第14页2.执行存储过程语法：EXECUTE{procedure_name[;number]|@procedure_name_var}[OUTPUT][,...n]其中：·procedure_name：拟调用的存储过程名。·@procedure_name_var：局部定义的变量名。·@parameter：过程参数，在CREATEPROCEDURE语句中定义。参数名称前必须加上符号@。在以@parameter_name=value格式使用时，参数名称和常量不一定按照CREATEPROCEDURE语句中定义的顺序出现。但是，如果有一个参数使用@parameter_name=value格式，则其它所有参数都必须使用这种格式。·OUTPUT：指定存储过程必须返回一个参数。使用OUTPUT参数，参数值必须作为变量传递。在执行过程之前，必须声明变量的数据类型并赋值。返回参数可以是text或image数据类型以外的任意数据类型。第15页3.重命名存储过程语法：

Sp_rename'procedure_name1','procedure_name2'4.删除存储过程语法：

DROPPROCEDUREprocedure_name第16页5.1.3触发器触发器是一种特殊的存储过程，当INSERT、DELETE或UPDATE语句修改指定表的一行或多行时，自动执行触发器。在触发器的使用中，系统会自动产生两张临时表Deleted和Inserted。用户不能直接修改这两个表的内容。①Deleted表：存储在DELETE和UPDATE语句执行时所影响的行的拷贝，在DELETE和UPDATE语句执行前被作用的行转移到Deleted表中。②Inserted表：存储在INSTERT和UPDATE语句执行时所影响的行的拷贝，在Insert和UPDATE语句执行期间，新行被同时加到Inserted和触发器表中。第17页触发器仅在当前DB中生成，触发器有3种类型，即插入、删除和更新。(1)INSERT类型的触发器：当对指定表TableName执行了插入操作时系统自动执行触发器代码。(2)UPDATE类型的触发器：当对指定表TableName执行了更新操作时系统自动执行触发器代码。(3)DELETE类型的触发器：当对指定表TableName执行了删除操作时系统自动执行触发器代码。第18页在触发器内不能使用如下的SQL命令：①所有数据库对象的生成命令，如CREATETABLE、CREATEINDEX等。②所有数据库对象的结构修改命令，如ALTERTABLE、ALTERDATABASE等。③创建临时保存表。④所有DROP命令。⑤GRANT和REVOKE命令。⑥TRUNCATETABLE命令。⑦LOADDATABASE和LOADTRANSACTION命令。

⑧RECONFIGURE命令。第19页1.创建触发器语法：CREATETRIGGERtrigger_nameONtable_nameFOR<INSERT|UPDATE|DELETE>AS

sql_statement2.删除触发器语法：DROPTRIGGERtrigger_name3.修改触发器语法：ALTERTRIGGERtriggernameONtable_nameFOR<INSERT|UPDATE|DELETE>ASsql_statement第20页5.2实验十二游标与存储过程5.2.1实验目的与要求(1)掌握游标的定义和使用方法。(2)掌握存储过程的定义、执行和调用方法。(3)掌握游标和存储过程的综合应用方法。第21页5.2.2实验案例[例5.1]利用游标查询业务科员工的编号、姓名、性别、部门和薪水，并逐行显示游标中的信息。DECLAREcur_empSCROLLCURSORFORSELECTemployeeno,employeename,sex,department,salaryFROMemployeeWHEREdepartment='业务科'ORDERBYemployeeno

/*定义游标*/OPENcur_emp

/*打开游标*/SELECT'CURSOR内数据条数'=@@cursor_rows

/*显示游标内记录的个数*/FETCHNEXTFROMcur_emp

/*逐行提取游标中的记录*/WHILE(@@FETCH_status<>-1)

/*判断FETCH语句是否执行成功*/BEGINSELECT'cursor读取状态'=@@FETCH_status/*显示游标的读取状态*/FETCHNEXTFROMcur_emp

/*提取游标下一行信息*/ENDCLOSEcur_emp

/*关闭游标*/DEALLOCATEcur_emp

/*释放游标*/第22页

本例中，@@cursor_rows是返回连接上最后打开的游标中当前存在的合格行的数量。具体参数信息见表5-1所示。第23页@@FETCH_status是返回被FETCH语句执行的最后，而不是任何当前被连接打开的游标的状态。具体参数见表5-2所示。第24页[例5.2]利用游标查询业务科员工的编号、姓名、性别、部门和薪水，并以格式化的方式输出游标中的信息。DECLARE@emp_nochar(8),@emp_namechar(10),@sexchar(1),@deptchar(4)DECLARE@salarynumeric(8,2),@textchar(100)/*用户自定义的几个变量*/DECLAREemp_curSCROLLCURSORFORSELECTemployeeNo,employeeName,sex,department,salaryFROMEmployeeWHEREdepartment='业务科'ORDERBYemployeeNo/*定义游标*/SELECT@text='========业务科员工情况列表==========='PRINT@textSELECT@text='编号

姓名

性别

部门

薪水'PRINT@textSELECT@text='----------------------------------'PRINT@text/*按照用户要求格式化输出相关信息*/OPENemp_cur

/*打开游标*/第25页FETCHemp_curINTO@emp_no,@emp_name,@sex,@dept,@salary/*提取游标中的信息传递并分别给内存变量*/WHILE(@@FETCH_status=0)/*判断是否提取成功*/BEGINSELECT@text=@emp_no+''+@emp_name+''+@sex+''+@dept+''+convert(char(10),@salary)/*给@text赋字符串值*/PRINT@text/*打印字符串值*//*提取游标中的信息传递并分别给内存变量*/FETCHemp_curinto@emp_no,@emp_name,@sex,@dept,@salaryENDCLOSEemp_cur

/*关闭游标*/DEALLOCATEemp_cur

/*释放游标*/本例中，主要结合SELECT和PRINT命令将创建游标后逐行提取游标的信息以格式化的方式输出，提高了脚本的可读性

第26页[例5.3]不带参数的存储过程：利用存储过程计算出’E2020002’业务员的销售总金额。①创建存储过程CREATEPROCEDUREsales_tot1ASSELECTsum(orderSum)FROMOrderMasterWHEREsalerNo=’E2020002’②执行存储过程EXECsales_tot1上述操作只能统计业务员’E2020002’的销售业绩，执行此存储过程不能统计任意一个业务员的销售业绩。第27页[例5.4]带输入参数的存储过程：统计某业务员的销售总金额。①创建存储过程CREATEPROCEDUREsales_tot2@e_no

char(8)ASSELECTsum(orderSum)FROMOrderMasterWHEREsalerNo=@e_no②执行存储过程EXECsales_tot2'E2020003'

注：

程序中使用@符号表示一个变量来指定参数名称，且每个过程的参数仅用于该过程本身。上述操作只要在执行存储过程时添加输入参数(即被统计的业务员的编号)就能统计任一业务员的销售业绩。问题：任意一个业务员的销售总金额如何被其他用户/程序方便调用呢？

第28页[例5.5]带输入/输出参数的存储过程：统计某业务员的销售总金额并返回其结果。①创建存储过程CREATEPROCEDUREsales_tot3@E_nochar(8),@p_tot

intOUTPUTASSELECT@p_tot=sum(orderSum)FROMOrderMasterWHEREsalerNo=@E_no②执行存储过程DECLARE@tot_amt

intEXECsales_tot3'E2020003',@tot_amtOUTPUTSELECT销售总额=@tot_amt上述操作可以统计任一员工的销售业绩并能实现其结果的调用。第29页[例5.6]带通配符参数的存储过程(模糊查找)：统计所有姓陈的员工的销售业绩并输出他们姓名和所在部门。①创建存储过程CreateProcedureemp_name

@E_name

varchar(10)ASSELECTa.EmployeeName,a.department,ssumFROMEmployeea,(SELECTSalerNo,ssum=sum(OrderSum)FROMOrderMasterGROUPBYSalerNo)bWHEREa.EmployeeNo=b.SalerNoANDa.EmployeeNameLIKE@E_name②执行存储过程EXECemp_name

@E_name='陈%'第30页[例5.7]重命名存储过程：将存储过程sales_tot2改名为sale_tot。

Sp_rename‘sales_tot2’,‘sale_tot’[例5.8]删除存储过程：将存储过程sale_tot删除。DROPPROCEDUREsale_tot第31页[例5.9]游标和存储过程的综合应用：请使用游标和循环语句编写一个存储过程emp_tot，根据业务员姓名，查询该业务员在销售工作中的客户信息及每一客户的销售记录，并输出该业务员的销售总金额。第32页①创建存储过程CREATEPROCEDUREemp_tot@v_emp_namechar(10)ASBEGINDECLARE@sv_emp_namevarchar(10),@v_custnamevarchar(10),@p_totintDECLARE@sumint,@countint,@order_novarchar(10)SELECT@sum=0,@count=0DECLAREget_totCURSORFORSELECTEmployeeName,CustomerNo,b.OrderNo,OrderSumFROMEmployeea,OrderMasterbWHEREa.EmployeeName=@v_emp_nameANDa.EmployeeNo=b.SalerNoOPENget_totFETCHget_totINTO@sv_emp_name,@v_custname,@order_no,@p_tot第33页WHILE(@@FETCH_status=0)

BEGIN

SELECT业务员=@sv_emp_name,客户=@v_custname,订单编号=@order_no,订单金额=@p_totSELECT@sum=@sum+@p_totSELECT@count=@count+1FETCHget_totINTO@sv_emp_name,@v_custname,@order_no,@p_totENDCLOSEget_totDEALLOCATEget_totIF@count=0SELECT0ELSESELECT业务员销售总金额=@sumEND第34页②执行存储过程

EXECemp_tot'张小娟'本例中，先建立一个游标用于临时储存业务员的基本销售信息，包括：业务员姓名、客户编号、订单编号、订单销售金额；再利用游标能逐行提取的功能，提取游标中每一记录，同时输出这些信息；最后统计其相应定单金额的总额，并输出订单总额。第35页5.2.3实验内容在订单数据库OrderDB中请完成以下实验内容：

(1)根据订单明细表中的数据，利用游标修改OrderMaster表中orderSum的值。(2)创建存储过程，要求：按第2章员工表定义中的CHECK约束自动产生员工编号。该过程的输入参数为员工入职的年份，输出参数是自动生成的员工编号，该编号满足第2章员工表定义中的CHECK约束，且后三位流水号等于表中与入职年份相同的员工编号最大值加1，如：输入参数为2020，且员工表中该年度最大的编码是E2020005，自动产生的编号为E20200006；如果该入职年份没有其他员工，则流水号为001。第36页

(3)

创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=============大客户中热销的前种商品的销售信息===========商品编号

商品名称

总销售金额P20200003三星-Galaxy-A949381.00P20200001vivo-X939173.80P20200002中兴AXON天机7(A2017)27891.00

第37页(4)请使用游标和循环语句创建存储过程proSearchCustomer，输入参数为客户编号，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出，制作日期取系统的当前日期，输出格式如下：===================客户订单表====================---------------------------------------------------------------------------------客户名称：

兴隆股份有限公司

客户地址：

天津市

总金额：

29986.00--------------------------------------------------------------------------------商品编号

总数量

平均价格

P2020000142798.00P2020000322599.00P2020000543399.00--------------------------------------------------------------------------------报表制作人

张小娟

制作日期

2022-07-08

(5)请利用游标嵌套和循环语句创建存储过程proInvoice，输入参数有两个，一个是定单的开始时间，一个是定单的结束时间，要求根据输入的时间范围，输出每个定单的发票信息，包括：客户名称、定单日期、发票号码、业务员名称、定单总金额及定单明细信息等，发票打印日期取系统的当前日期，输出格式如下：业务员销售时间范围为:2020-03-01----2020-10-19

==============================通用机打发票==============================-----------------------------------------------------------------------------------------------------------------------客户名称：兴隆股份有限公司

定购日期：2020-03-01发票号码：I000000006-----------------------------------------------------------------------------------------------------------------------商品名称

数量

单价

金额vivo-X942798.0011192.00TCL-D55A630U13399.003399.00----------------------------------------------------------------------------------------------------------------------商品类数:2商品数量:5合计:14591.00----------------------------------------------------------------------------------------------------------------------定单销售员：张露

发票打印日期：2022-07-11----------------------------------------------------------------------------------------------------------------------

==============================通用机打发票==============================------------------------------------------------------------------------------------------------------------------------客户名称：五一商厦

定购日期：2020-03-02发票号码：I000000007------------------------------------------------------------------------------------------------------------------------商品名称

数量

单价

金额vivo-X922798.005596.00中兴AXON天机7(A13099.003099.00三星-Galaxy-A932599.007797.00-------------------------------------------------------------------------------------------------------------------------商品类数:3商品数量:6合计:16492.00------------------------------------------------------------------------------------------------------------------------定单销售员：张小娟

发票打印日期：2022-07-11------------------------------------------------------------------------------------------------------------------------第39页5.3实验十三触发器5.3.1实验目的与要求(1)掌握触发器的创建和使用方法。(2)掌握游标和触发器的综合应用方法。第40页5.3.2实验案例[例5.10]删除触发器：编写一个允许用户一次只删除一条记录的触发器。

CREATETRIGGERTr_EmpONEmployeeFORDELETEAS/*对表Employee定义一个删除触发器*/DECLARE@row_cnt

int/*定义变量@row_cnt，用于跟踪Deleted表中记录的个数*/SELECT@Row_Cnt=Count(*)FROMDeletedIf@row_cnt>1/*判断Deleted表中记录的个数是否大于1*/BEGINPRINT‘此删除操作可能会删除多条人事表数据!!!'ROLLBACKTRANSACTION/*如果Deleted表中记录的个数大于1，事务回滚*/END第41页分析：本例中，触发器约束了用户只能对Employee这张表删除一次删除一条记录。我们可验证触发器的作用效果。验证过程如下：(1)DELETEFROMEmployeeWHEREsex='F'在(1)执行后，结果可能出现二种情况：①系统提示：“外键约束冲突”错误。②系统提示：“此删除操作可能会删除多条人事表数据!!!”。第①种情况，由于Employee表与其它表建立了外键约束关系，在删除表中元组时必须满足参照完整性约束的要求。只有删除外键约束，在执行删除操作时才能激活触发器。第②中情况，由于解除了外键约束后，删除操作激活触发器，但由于删除的元组多于一个，所以出现正确系统提示信息。第42页[例5.11]更新触发器：请使用游标和循环语句为OrderDetail表建立一个更新触发器updateorderdetail，要求当用户修改定单明细表中某个商品的数量或单价时自动修改定单主表中的订单金额。分析：本例中，Deleted和Inserted表结构与OrderDetail表结构相同。如果用户修改了销售明细表中某个货品的数量或单价时，Deleted表记载了更新前信息，Inserted表记载了更新后信息，本例正是利用这两张表结合游标将正确的订单金额修改到定单主表中。用户同样可以用UPDATE命令修改OrderDetail从而验证触发器的作用。第43页CREATETRIGGERupdatesaleitemONOrderDetailFORUPDATEAS/*对表Employee定义一个更新触发器*/IfUPDATE(quantity)ORUPDATE(price)/*判断对指定列quantity或price的更新*/BEGIN/*定义两个内存变量用于跟踪游标中订单编号和商品编号的值*/DECLARE@ordernoint,@productnochar(5)

/*Deleted表中数据信息存入到一个游标结果集中*/

DECLAREcur_orderdetailCURSORFORSELECTorderno,productnoFROMDeletedOPENcur_orderdetail/*打开游标*/

BEGINTRANSACTION/*事务开始*//*提取游标中信息并传递给变量@orderno,@productno*/FETCHcur_orderdetailINTO@orderno,@productno第44页WHILE(@@fetch_status=0)/*判断如果提取成功*/BEGIN/*修改ordermaster中订单金额的值*/UPDATEordermasterSETordersum=ordersum-D.quan