触发器视图存储过程函数习题.docx
《触发器视图存储过程函数习题.docx》由会员分享，可在线阅读，更多相关《触发器视图存储过程函数习题.docx（11页珍藏版）》请在冰豆网上搜索。
触发器视图存储过程函数习题
触发器第八章
示例理解select*fromT_DishSort查询结果如下
sortidsortnamedelsign
01AA0
02BB0
03CC1
updateT_DishSortsetdelsign=5
wheredishsortid='01'
insertd:
01AA5
deleted:
01AA0
1.在菜品分类表T_DishSort上创建触发器,当数据插入时判断如果delsign不是0时则不允许插入数据
createtriggertr_dishsort_addont_dishsortforinsertas
declare@delint
select@del=delsignfrominserted
if@del<>0
begin
raiserror（'插入失败:
delsign',16,-1）
rollback
end
2.在菜品分类表T_DishSort上创建触发器,当数据修改时判断如果delsign不是0或者1时则不允许修改
createtriggertr_dishsort_xgont_dishsortforupdateasdeclare@delint
select@del=delsignfrominserted
if@del<>0or@del<>1
begin
raiserror（'非0,1不能修改',16,-1）
rollback
end
3.在菜品分类表T_DishSort上创建触发器,当数据删除时判断如果该分类下已经有菜品信息,则不允许删除
createtriggertr_dishsort_scont_dishsortfordeleteasdeclare@delint
select@del=dishsortidfromdeleted
select*fromt_dishinfowheredishsortid=@del
if@@rowcount>0
begin
raiserror（'已经有菜品信息不能删除',16,-1）
rollback
end
4.在餐桌分类表T_TableSort上创建触发器,当数据删除时判断如果该分类下已经有餐桌信息,则不允许删除
同3
5.在员工信息表T_EmployeInfo上创建触发器,当数据删除时判断如果该员工已经有收银信息,则不允许删除
createtriggertr_Employe_sconT_EmployeInfofordeleteasdeclare@idvarchar（10）
select@id=Employeidfromdeleted
select*fromt_sellwhereEmployeid=@id
if@@rowcount>0
begin
raiserror（'已经有收银信息不能删除',16,-1）
rollback
end
6.在餐桌信息表T_TableInfo上创建触发器,当数据删除时判断如果该餐桌已经有销售信息,则不允许删除
同5
7.在销售信息主表T_Sell上创建触发器,当数据删除时也把销售菜品明细表T_SellDetail中相同billCode的记录删除（如果表中有级联外键关系,则先把外键删除掉）.
8.难:
（仅在修改数据的前提前下update）在销售信息主表T_Sell上有realPrice代表餐桌的消费金额,这个消费金额等于相同billCode的销售菜品明细表T_SellDetail中金额price*打折数abateNum/100的累加和,同时金额price=销售数量amount*单价oldPrice.这有两层的相互影响的关系,通过SQL所学知识如何做到以上相互影响关系,可以考虑先限制T_Sell的realPrice不允许修改.方法可以多种:
约束
触发器
做题思路，注意数据的变化情况:
1.单价修改将影响金额,并且影响消费总金额
2.数量修改将影响金额,并且影响消费总金额
假定不变的情况:
1.消费总金额
2.金额不能变
实现视图（第九章）
1.创建视图V_Dish将菜品分类和菜品信息两张表常用的字段存储在本视图中,常用字段为dishid,dishsortid,dishsortname,dishname,price,并使用该视图做以下应用（前面几章有做过的练习）
dishid,dishsortid,dishsortname,dishname,price,并使用该视图做以下应用（前面几章有做过的练习）
createviewV_Dishas
selectdishid,dishsortid,dishsortname,dishname,price
fromt_dishinfot1,t_dishsortt2
wheret1.dishsortid=t2.dishsortid
1.1查询各分类菜品的平均价格和菜品总数
selectdishsortid,avg（price）,count（dishsortid）
fromV_Dishgroupbydishsortid
1.2查询出各分类菜品的总菜数,且每个分类的总菜数应大于15道的菜品分类和总菜数
selectdishsortid,count（dishsortid）
fromV_Dishgroupbydishsortid
havingcount（dishsortid）>15
2.创建视图V_SimpleDish（dishid,dishsortid,dishname,price）包含了菜品信息表中关键的信息列,并使用该视图做以下应用
createviewv_simpledishas
selectdishid,dishsortid,dishname,pricefromt_dishinfo
2.1新增一道菜:
菜品主键为‘010*******’,分类为甜点,菜品名称为特好吃冰淇淋,价格为100元
2.2因入冬将甜点类菜品价格降低为原价的80%,并把调价后仍高于50元的删除（仅将T_DishInfo中DelSign的标志为改为1）,使用事务完成
3.创建视图V_Sell（第九章第四题）以销售信息T_Sell为主表关联T_TableInfo和T_EmployeInfo统计出桌号名称,收银员名称,销售金额,结帐日期,并使用该视图做以下查询
createviewV_Sellas
selecttablename,name,realprice,reckoningTimefromt_sellt1leftouterjoint_tableinfot2ont1.tableid=t2.tableidleftouterjoint_employeinfot3ont3.employeid=t1.employeid
3.1查询出杨丹丹在07年八月份共收了多少钱
selectsum（realprice）fromv_sellwherename='杨丹丹'anddatediff（month,reckoningTime,'2007-8-20'）=0
3.2修改本视图增加消费人数
alterviewV_Sellas
selecttablename,name,realprice,reckoningTime,customercountfromt_sellt1leftouterjoint_tableinfot2ont1.tableid=t2.tableidleftouterjoint_employeinfot3ont3.employeid=t1.employeid
第十章存储过程
一、使用存储过程维护餐桌分类表t_tablesort的添加,删除,修改及查询等操作
添加数据tablesort_insert:
根据用户提供的餐桌分类ID和餐桌分类名称添加一条记录,要求餐桌分类ID不能重复;
createproctablesort_insert
@idvarchar（10）,@namevarchar（20）
as
ifnotexists（select*fromt_tablesortwheretablesortid=@id）
insertintot_tablesort（tablesortid,
tablesortname）
values（@id,@name）
调用：
exectable_sort'02','test'
删除数据tablesort_delete:
根据用户提供的餐桌分类ID（主键）将主键相同的记录删除掉（删除时仅将delsign改为1而不是数据行的删除
createproctablesort_delete@idvarchar（10）as
updatet_tablesortsetdelsign=1where@id=tablesortid
调用：
exectablesort_delete'02'
修改数据tablesort_update:
根据用户提供的餐桌分类ID和餐桌分类名称修改数据,将主键等于用户提供的餐桌分类ID相同记录的餐桌分类名称更改为用户提供的餐桌分类名称
createproctablesort_update@idvarchar（10）,@namevarchar（20）as
updatet_tablesortsettablesortname=@namewheretablesortid=@id
查询数据tablesort_select:
根据用户提供的餐桌分类名称,模糊查询出没有被删除的餐桌分类信息,如果餐桌分类名称为all则全部查询出来
createproctablesort_select@namevarchar（20）as
if@name='all'select*fromt_tablesort
else
select*fromt_tablesortwheretablesortnamelike'%'+@name+'%'anddelsign=0或者
select*fromt_tablesortwhere（tablesortnamelike'%'+@name+'%'anddelsign=0）or（@name='all'）
删除数据tablesort_delete:
根据用户提供的餐桌分类ID（主键）将主键相同的记录删除掉（删除时仅将delsign改为1而不是数据行的删除）
修改数据tablesort_update:
根据用户提供的餐桌分类ID和餐桌分类名称修改数据,将主键等于用户提供的餐桌分类ID相同记录的餐桌分类名称更改为用户提供的餐桌分类名称
查询数据tablesort_select:
根据用户提供的餐桌分类名称,模糊查询出没有被删除的餐桌分类信息,如果餐桌分类名称为all则全部查询出来
二、使用存储过程维护员工信息表T_EmployeInfo的添加,删除,修改及查询等操作
添加数据employ_insert:
根据用户提供的员工ID,姓名,性别,入职时间添加一条记录,要求员工ID如果有重复则返回[员工ID重复不允许插入]的信息且性别只能输入男或者女,如果不是则返回[性别输入有错,请重新输入]的信息,
注：
如果本表中的delsign有默认值，可以把0改为default，或者去掉0
删除数据employ_delete:
根据用户提供的员工ID（主键）将主键相同的记录删除掉（删除时仅将delsign改为1而不是数据行的删除）
修改数据employ_update:
根据用户提供的员工ID,姓名,性别,入职时间修改数据,将主键等于用户提供的员工ID相同记录的姓名,性别,入职时间更改为用户提供的相应信息
查询数据employ_select:
根据用户提供的员工姓名,模糊查询出没有被删除的员工信息,如果姓名为all则全部查询出来
三、使用存储过程维护菜品信息表t_dishinfo的添加,删除及查询等操作
难:
添加数据dishinfo_insert:
根据用户提供的分类名称,菜品名称,菜品代码,价格,是否打折,单位等将数据添加表中,要求分类名称一定要存在菜品分类表中,如果不存在则不允许添加该记录;菜品主键通过程序编写生成,生成主键的规则是长度为十位,前几位是分类的ID,后面为该分类的ID下编号最大的再加1;比如:
分类为01,主键生成后为010*******
补零规则程序
删除数据dishinfo_delete:
根据用户提供的菜品主键将相同的主键记录删除掉（删除时仅将delsign改为1而不是数据行的删除）,如果用户提供的菜品主键为all或者[所有]或者[全部]等将表中所有的delsign改为1
查询数据dishinfo_select:
根据用户提供的菜品名称,模糊查询出没有被删除的菜品的信息,如果菜品名称为all则全部查询出来
四、
创建存储过程Dishinfo_sort:
根据用户给定的菜品分类查询该分类下所有的菜品信息,结果集并以单价进行升序排序
createprocDishinfo_sort@sortidvarchar（6）as
select*fromt_dishsortwheredishsortid=@sortidorderbyprice
创建存储过程dishinfo_count:
根据用户给定的最低菜数查询出各分类菜品的总菜数,且每个分类的总菜数应大于用户给定的最低菜数的菜品分类和总菜数;
createprocdishinfo_count@nintas
selectdishsortid,count（dishsortid）
fromV_Dishgroupbydishsortid
havingcount（dishsortid）>@n
创建存储过程sell_table:
根据用户给定的桌号名称统计出该桌号的销售总额
selectsum（realprice）fromt_sellt1leftouterjoint_tableinfot2ont1.tableid=t2.tableidwheretablename=@name
创建存储过程sell_time1:
根据用户给定的时间段（分为开始时间和结束时间）统计出该时间段的销售总额,实收总额,优惠的打折总额,去尾总额.
2010-10-100:
00:
000.000000->2010-10-1600:
00:
000.000000
>,betweenand,=
createprocsell_time1@startdatetime,@enddatetimeas
selectsum（realprice）,...fromt_sell
wherereckoningTime>=@startandreckoningTime<@end+1
或者datediff（day,reckoningTime,@start）=<0anddatediff（day,reckoningTime,@end）>=0
创建存储过程sell_time2:
根据用户给定的时间段（分为开始时间和结束时间）统计出桌号名称,收银员名称,销售金额,结帐日期
第十一章函数
1.定义一个函数fn_getPrice,函数功能为:
通过查找类型得到菜品相应的价格,查找类型为0查出平均价格,为1查出最高价格,为2查出最低价格,否则返回不存在的价格可以使用[-100]表示;
createfunctionfn_getPrice（@typeint）returnsnumeric（18,2）as
begin
declare@anumeric（18,2）
if@type=0
select@a=avg（price）fromt_dishinfo
elseif@type=1
select@a=max（price）fromt_dishinfo
elseif@type=2
select@a=min（price）fromt_dishinfo
elseset@a=-100
return@a
end
2.定义一个函数fn_sellcount,函数功能为:
通过传入菜品名称和给定的时间段（分为开始时间和结束时间）统计出该时间段的此菜品的销售数量;
createfunctionfn_sellcount（@namevarchar（20）,@startdatetime,@enddatetime）returnsnumeric（18,2）as
begin
declare@nnumeric（18,2）
select@n=sum（amount）fromt_sellt1leftouterjoint_selldetailt2ont1.billcode=t2.billcode
wheredatediff（day,reckoningTime,@start）=<0anddatediff（day,reckoningTime,@end）>=0anddishname=@name
return@n
end
3.定义一个函数fn_getMaxSell,函数功能为:
通过给定的时间段（分为开始时间和结束时间）查找出该时间段销售数量最大的菜品名称;
4.定义一个函数fn_getLower,函数功能为:
查找出价格最低的菜品信息的结果集,结果集为菜品ID,菜品分类名称,菜品名称,菜品价格,单位;（使用多语句表值函数和内嵌表值函数两种实现）;
createfunctionfn_getLower（）returnstableas
return（selecttop1withties*fromt_dishinfoorderbyprice）或
createfunctionfn_getLower（）returns@atable
（idvarchar（10）primarykey,
namevarchar（20）,
pricenumeric（18,2）,
unitvarchar（10））
as
begin，最低和最高
insertinto@a（id,name,price,unit）
selecttop1withtiesdishid,dishname,price,unitfromt_dishinfoorderbyprice
insertinto@a（id,name,price,unit）
selecttop1withtiesdishid,dishname,price,unitfromt_dishinfoorderbypricedesc
return
end
5.定义一个函数fn_getsellinfo,函数功能为:
根据给定的时间段（分为开始时间和结束时间）统计出桌号名称,收银员名称,销售金额,结帐日期;（使用多语句表值函数和内嵌表值函数两种实现）;
（参看第四题）
索引及其它管理类上机
升级会员 