实验6 数据库实验——存储过程和触发器

实验6存储过程和触发器1．实验目的（1）掌握通过SQL Server管理平台和Transact-SQL语句CREATE PROCEDURE创建存储过程的方法和步骤。

（2）掌握使用Transact-SQL语句EXECUTE执行存储过程的方法。

（3）掌握通过SQL Server管理平台和Transact-SQL语句ALTER PROCEDURE修改存储过程的方法。

（4）掌握通过SQL Server管理平台和Transact-SQL语句DROP PROCEDURE删除存储过程的方法。

（5）掌握通过SQL Server管理平台和Transact-SQL语句CREATE TRIGGER创建触发器的方法和步骤。

（6）掌握引发触发器的方法。

（7）掌握使用SQL Server管理平台或Transact-SQL语句修改和删除触发器。

（8）掌握事务、命名事务的创建方法，了解不同类型的事务的处理情况。

2．实验内容及步骤请先附加studentsdb数据库，然后完成以下实验。

（1）在查询设计器中输入以下代码，创建一个利用流控制语句的存储过程letters_print，该存储过程能够显示个小写字母。

语句：CREATE PROCEDURE letters_printASDECLARE@count intSET@count=0WHILE@count<26BEGINPRINT CHAR(ASCII('a')+@count)SET@count=@count+1ENDexec letters_print（2）输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名对应的学生的各科成绩。

语句：create proc stu_info@name char(10)asbeginSELECT姓名,g.课程编号,分数FROM dbo.student_info s JOIN grade gON s.学号=g.学号WHERE s.姓名=@nameEndexec stu_info'马东'（3）使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g。

实验六存储过程和触发器一、目的与要求1.掌握编写数据库存储过程的方法。

2.掌握建立数据库触发器的方法，通过实验观察触发器的作用和触发条件设置等相关操作。

二、实验准备1.了解编写存储过程和调用的T-SQL语法；2.了解触发器的作用；3.了解编写触发器的T-SQL语法。

三、实验内容(一)存储过程在studentdb数据库中建立存储过程getPractice，查询指定院系(名称)(作为存储过程的输入参数)中参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称，若院系不存在，返回提示信息。

提示：D_Info表中存储了院系代码D_ID，而St_Info表中学号字段St_ID的前两位与之对应，则D_Info表与St_Info表之间的联系通过这两个字段的运算构成连接条件。

1.分别执行存储过程getPractice，查询“法学院”和“材料科学与工程学院”的学生中参与“实践”课程的所有学生学号、姓名、所学课程编号和课程名称。

create procedure getPractice@D_Name varchar(30)outputasbeginif not exists(select *from D_Infowhere D_Name= @D_Name)print '对不起，该院系不存在'elseselect st_info.St_ID,C_Info.C_No,C_Namefrom s_c_info inner join st_info on st_info.St_ID=s_c_info.st_idinner join C_Info on s_c_info.c_no=C_Info.C_Nowhere st_info.St_ID in( select St_IDfrom st_info join D_Info on D_Info.D_ID =left(st_info.St_ID,2)where C_Info.C_Type='实践' and D_Info.D_Name= @D_Name) end go2.利用系统存储过程sp_rename将getPractice更名为getPctStuexecute sp_rename getPractice , getPctStu3.修改存储过程getPctStu，返回指定院系中参与实践课程的学生人次数，并利用该存储过程以“法学院”为输入参数验证执行的结果alter procedure getPctStu@D_Name varchar(30)asbeginif not exists(select *from D_Infowhere D_Name= @D_Name)print '对不起，该院系不存在'elseselect st_info.St_ID,C_Info.C_No,C_Name,count (st_info.St_ID) as 人次数from s_c_info inner join st_info on st_info.St_ID=s_c_info.st_idinner join C_Info on s_c_info.c_no=C_Info.C_Nowhere st_info.St_ID in( select St_IDfrom st_info join D_Info on D_Info.D_ID =left(st_info.St_ID,2)where C_Info.C_Type='实践' and D_Info.D_Name= @D_Name)group by st_info.St_ID,C_Info.C_No,C_NameendGoexec getPctStu '法学院'Go4.再修改存储过程getPctStu，返回指定院系中参与实践课程的学生人数。

实验六存储过程和触发器实验六存储过程与触发器⼀、⽬的与要求1.掌握编写数据库存储过程的⽅法。

2.掌握建⽴数据库触发器的⽅法,通过实验观察触发器的作⽤与触发条件设置等相关操作。

⼆、实验准备1.了解编写存储过程与调⽤的T-SQL语法;2.了解触发器的作⽤;3.了解编写触发器的T-SQL语法。

三、实验内容(⼀)存储过程在studentdb数据库中建⽴存储过程getPractice,查询指定院系(名称)(作为存储过程的输⼊参数)中参与“实践”课程学习的所有学⽣学号、姓名、所学课程编号与课程名称,若院系不存在,返回提⽰信息。

提⽰:D_Info表中存储了院系代码D_ID,⽽St_Info表中学号字段St_ID的前两位与之对应,则D_Info表与St_Info表之间的联系通过这两个字段的运算构成连接条件。

1.分别执⾏存储过程getPractice,查询“法学院”与“材料科学与⼯程学院”的学⽣中参与“实践”课程的所有学⽣学号、姓名、所学课程编号与课程名称。

create procedure getPractice@D_Name varchar(30)outputasbeginif not exists(select *from D_Infowhere D_Name= @D_Name)print '对不起,该院系不存在'elseselect st_info、St_ID,C_Info、C_No,C_Namefrom s_c_info inner join st_info on st_info、St_ID=s_c_info、st_idinner join C_Info on s_c_info、c_no=C_Info、C_Nowhere st_info、St_ID in( select St_IDfrom st_info join D_Info on D_Info、D_ID =left(st_info、St_ID,2)where C_Info、C_Type='实践' and D_Info、D_Name= @D_Name)endgo2.利⽤系统存储过程sp_rename将getPractice更名为getPctStuexecute sp_rename getPractice , getPctStu3.修改存储过程getPctStu,返回指定院系中参与实践课程的学⽣⼈次数,并利⽤该存储过程以“法学院”为输⼊参数验证执⾏的结果alter procedure getPctStu@D_Name varchar(30)asbeginif not exists(select *from D_Infowhere D_Name= @D_Name)print '对不起,该院系不存在'elseselect st_info、St_ID,C_Info、C_No,C_Name,count (st_info、St_ID) as ⼈次数from s_c_info inner join st_info on st_info、St_ID=s_c_info、st_idinner join C_Info on s_c_info、c_no=C_Info、C_Nowhere st_info、St_ID in( select St_IDfrom st_info join D_Info on D_Info、D_ID =left(st_info、St_ID,2)where C_Info、C_Type='实践' and D_Info、D_Name= @D_Name)group by st_info、St_ID,C_Info、C_No,C_NameendGoexec getPctStu '法学院'Go4.再修改存储过程getPctStu,返回指定院系中参与实践课程的学⽣⼈数。

实验六存储过程和触发器陈聪1、目的与要求（1）掌握存储过程的使用方法。

（2）掌握触发器的使用方法。

2、实验准备（1）了解存储过程的使用方法。

（2）了解触发器的使用过程。

（3）了解inserted逻辑表和deleted逻辑表的使用。

（4）了解如何编写CRL存储过程与触发器。

3、实验内容。

（1）存储过程。

①创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。

注：此段T-SQL命令，书上变量名书写错误，漏掉了下划线。

（@nu_MBER1和@nu MBER1区别）执行该存储过程，并查看结果。

②创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1.执行该存储过程并查改结果。

③创建添加职员记录的存储过程EmployeeAdd。

执行该存储过程。

执行上面T-SQL命令，在结果栏中显示一行受影响，说明结果已经插入成功，在表Employees中也够看到结果已经存在。

④创建存储过程，使用游标确定一个员工的实际收入是否排在前3名。

结果为1表示是，结果为0表示否。

执行该存储过程，在结果栏中显示，命令已成功完成，并且在左侧对象资源管理器中进行刷新会显示刚刚创建的存储过程。

执行该存储过程：上面T-SQL结果中输出“1”，代表着员工“108991”的实际收入排在前三名。

【思考与练习】A、创建存储过程，要求当一个员工的工作年份大于6年时，将其转到经理办公室工作。

创建这个存储过程时候，在存储过程的条件判断中我添加了一项，判断工作年份在6年以上的员工是否在经理办公室，只有不在经理办公室的时候才进行调整，这样更符合实际问题些。

然后执行上面的存储过程，并且查看结果：此时有可能会出现（我就出现了）这样的问题，在之前实验三中就出现了。

解决方法同样是解除规则的绑定。

查看表Employees中员工“000001”的DepartmentID已经修改为“3”（经理办公室）。

B、创建存储过程，根据每个员工的学历将收入提高500.首先创建一个存储过程，利用什么学历增加收入，在这里，本科学历增加收入500.执行上面的存储过程，查看结果：由于在执行这个存储过程之前，我先执行了一个查询语句，将增加收入之前的所有本科学历职工的收入输出，便于与执行操作之后的结果进行比较。

实验六存储过程和触发器一、目的与要求1.掌握编写数据库存储过程的方法。

2.掌握建立数据库触发器的方法，通过实验观察触发器的作用和触发条件设置等相关操作。

二、实验准备1.了解编写存储过程和调用的T-SQL语法;2.了解触发器的作用；3.了解编写触发器的T-SQL语法。

三、实验内容（一）存储过程在studentdb数据库中建立存储过程getPractice，查询指定院系（名称）（作为存储过程的输入参数）中参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称，若院系不存在，返回提示信息。

提示：D」nfo表中存储了院系代码D_ID，而St_lnfo表中学号字段St_ID 的前两位与之对应，则D」nfo表与St_lnfo表之间的联系通过这两个字段的运算构成连接条件。

1.分别执行存储过程getPractice，查询“法学院”和“材料科学与工程学院”的学生中参与“实践”课程的所有学生学号、姓名、所学课程编号和课程名称create procedure getPractice@D_Name varchar(30)outputasbegi nif not exists(select *from D_I nfowhere D_Name= @D_Name)print'对不起，该院系不存在’elseselect st_in fo.St_ID,C」n fo.C_No,C_Namefrom s_c_i nfo inner joi n st_i nfo on st_i nfo.St_ID=s_c_i nfo.st_id inner joi n C_I nfo on s_c_i nfo.c_ no=C_I nfo.C_Nowhere st_in fo.St_ID in(select St_IDfrom st_i nfo join D_I nfo on D_In fo.D_ID =left(st_i nfo.St_ID,2) where C」n fo.C_Type=' 实践'and D_ln fo.D_Name= @D_Name)endgo|D_常prxta ■疋不.起，该箱>-f琴址*al>d9al«e? aT_iK.fd Sc^ID. C_Inf5 ■,. C^Nasic-frcai. ■ c JJZ^O -~~-=r OJ_Z:ST LZL±== sz 3 5~. I^-.s c ZXJ E D. - z j-J.j.r-：er _ = IT.C_Inf D M aF_e_infQ B C_DO-C_1G£n C_Hcwmtre 4c_inro.sc_iDjpele = E TO±zcx sjm jciri D■ 二上u 匚二D o D_ID i 3Z _Z3Lt=-. =z_ZD^ 2 1rfbUI IJlBdEa. C_T J^P RE-' 1an± i&_lA£a.p)_JfadM" (C_lamE：«nc£print 'Xl'TiLH谪僥爲不存左"selcn a^intc-St^ID G^IinTo ■ C_IPo.ClausetTon 9 c info 1 -7 _'T at inf o ar into ■旨耳11^9 ? Into,卸睪Jt)丄2_：e J.J Jlr. C IaXg g s c l^Tg . c oo^C I D J LD.□K D■rh« z e st._j_ZL.f 3 . St_12> —njs-el—et St_£Df EMi st_infe -D 1 -1 D_Inf o> cm D_InfQ. D_IB ■ r r T■ j»e_i nf a. St_IB r 3 wn e re c_ in ire ・G_T snw 宴践-■ ru_ i ntc 匚D_NWIC- t D_w«e©口=Xed ^etfxaetlC- * 学帕g=*D皿，对不起.谨院枭序存在，s&Jec：!：ar.^inrc . Sr ID. C Tore C Eg C M址尼ircm s匚JL二£□ 丄旦二亡二二_i■二st ■L二f■口as s± zzs-fa.^" ■LAat u AEL£Q . st idC_lnf& on a_c_infc. e_nci&C_lntciX_Sowbsic f g.(Kiter s^.ezrziL rt i^.fc _ j_r. □ Xaf?1二口D Izifc Z ZZ —Left st 1E£G St _ ID 2wtie£e C_in« .C 巧!PL'实踐'二計戈ii_inro. D_»Hm±- ftijkscMendgs “心—IT科学宇目□O2.利用系统存储过程sp_rename 将getPractice 更名为getPctStuexecute sp_re name getPractice , getPctStu尹■ dbc/t_^infc JXXV417J9>tLJd«ntdb - SQLQutryl «qh 寺- ax-ac^t a vp gat Practice . 口3•修改存储过程getPctStu，返回指定院系中参与实践课程的学生人次数, 并利用该存储过程以“法学院”为输入参数验证执行的结果alter procedure getPctStu@D_Name varchar(30)asbegi nif not exists(select *from D_I nfowhere D_Name= @D_Nameprint'对不起，该院系不存在’elseselect st_in fo.St_ID,C」n fo.C_No,C_Name,co unt (st_i nfo.St_ID) as 次数from s_c_i nfo inner joi n st_i nfo on st_i nfo.St_ID=s_c_i nfo.st_id inner joi n C_I nfo on s_c_i nfo.c_ no=C_I nfo.C_Nowhere st_in fo.St_ID in(select St_IDfrom st_i nfo join D_I nfo on D_In fo.D_ID =left(st_i nfo.St_ID,2) where C」n fo.C_Type=' 实践'and D_ln fo.D_Name= @D_Name)group by st_i nfo.St_ID,C」n fo.C_No,C_NameendGoexec getPctStu '法学院'Go血十口辭皿啊砒-SQlQutryL科I" MV|print '对不I九i枣FR系尸存守"el«select 3t_lntd. 5t_I D■ &_In£o-C_Ha f C_N^me. .. ； t 3t_infO-St_lDi 璋E/•.制惜f rw >_c_info n- - JD1rt_in£o cn«t_i!nfci-3't_I&-i s i B_in.£a ■|b_Ldinjier3°in C_lnfQ OT ■.c.ipfg ・*.!^<.玉旳£.13口■where st_in.Eci 5t_ID 1 naclest 5t_lnE K=-cjkn D_ln£e g &_I K£Q■ 1>_ID *W・殆VK t'lsfc.t l¥Ft-J实霸■"二 D D W WK- St_y«icEzu—P L-J!“七_丄1让<Z 上二_丄0匕_1±注右.口C l-BJV：ttndl3UT-xec j-tBnt5tu '迭学【完・G£>□茁黑血:占豐5l」D C-Nsw ' *「企t i出:;血[5 72001 ] 如忖U (IB罷伸a i3 2KHD5-：H0~ 57ZO13. 犬宇计刘曲気14.再修改存储过程getPctStu，返回指定院系中参与实践课程的学生人数注：“人数”和“人次数”是不同的，对某一学生而言，如果参与了多门实践课程，则“人次数”是指其参与的课程门数，而“人数”仍为1。

实验六存储过程和触发器142208100149 陈聪1、目的与要求（1）掌握存储过程的使用方法。

（2）掌握触发器的使用方法。

2、实验准备（1）了解存储过程的使用方法。

（2）了解触发器的使用过程。

（3）了解inserted逻辑表和deleted逻辑表的使用。

（4）了解如何编写CRL存储过程与触发器。

3、实验内容。

（1）存储过程。

①创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。

注：此段T-SQL命令，书上变量名书写错误，漏掉了下划线。

（@nu_MBER1和@nu MBER1区别）执行该存储过程，并查看结果。

②创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1.执行该存储过程并查改结果。

③创建添加职员记录的存储过程EmployeeAdd。

执行该存储过程。

执行上面T-SQL命令，在结果栏中显示一行受影响，说明结果已经插入成功，在表Employees中也够看到结果已经存在。

④创建存储过程，使用游标确定一个员工的实际收入是否排在前3名。

结果为1表示是，结果为0表示否。

执行该存储过程，在结果栏中显示，命令已成功完成，并且在左侧对象资源管理器中进行刷新会显示刚刚创建的存储过程。

执行该存储过程：上面T-SQL结果中输出“1”，代表着员工“108991”的实际收入排在前三名。

【思考与练习】A、创建存储过程，要求当一个员工的工作年份大于6年时，将其转到经理办公室工作。

创建这个存储过程时候，在存储过程的条件判断中我添加了一项，判断工作年份在6年以上的员工是否在经理办公室，只有不在经理办公室的时候才进行调整，这样更符合实际问题些。

然后执行上面的存储过程，并且查看结果：此时有可能会出现（我就出现了）这样的问题，在之前实验三中就出现了。

解决方法同样是解除规则的绑定。

查看表Employees中员工“000001”的DepartmentID已经修改为“3”（经理办公室）。

实验六-数据库的存储过程和触发器
大学实验报告（六）2014年04月08日
专业年级
2012级
信计专业
（1）班
学号201221143025
成绩
姓名
课程名称
《数据库
管理系统》
实验名称
数据库的存储过程和
触发器
实验目的和要求(1)掌握存储过程的创建与应用
(2)了解触发器的创建与使用.
实验内容和步骤1)用存储过程查询指定课程选课的学生人
数、最高成绩、最低成绩和平均成绩.
2)用存储过程求某系学生选修的课程号及成
绩.
3)用存储过程查询某门课程成绩大于80分
的学生姓名.
4)用存储过程查询缺成绩的学生名和课程
号.
5）查询每位学生已选课程的门数和总平均成绩.
6）假设有两张结构完全一样的表，创建触发器a，当一张表某条记录更新时，另一张表该条记录也跟着更新；创建触发器b，当一张表添加一条记录时，另一张表也自动添加；
研究与探讨
评语。

实验6 存储过程和触发器一、实验目的1、加深和巩固对存储过程和触发器概念的理解。

2、掌握触发器的简单应用。

3、掌握存储过程的简单应用。

二、实验内容一）存储过程：1. 创建一存储过程，求l+2+3+…+n，并打印结果。

CREATE PROCEDURE addresultASDECLARE @n int=10,/*最后一个数*/@i int=0,@result int=0 /*结果*/BEGINWHILE(@i<=@n)BEGINSET @result=@result+@iSET @i=@i+1ENDPRINT'1+2+3+...+n的结果是:'PRINT @resultRETURN(@result)ENDGO2．调用上面的addresult存储过程，打印l十2+3+…+10的结果。

EXEC addresult3. 修改上述存储过程为addresult1,使得@n为输入参数，其具体值由用户调用此存储过程时指定。

CREATE PROCEDURE addresult1@n int=10 /*最后一个数*/ASDECLARE @i int=0,@result int=0 /*结果*/BEGINWHILE(@i<=@n)BEGINSET @result=@result+@iSET @i=@i+1ENDPRINT'1+2+3+...+n的结果是:'PRINT @resultRETURN(@result)ENDGO4. 调用上面修改后的addresult1存储过程，打印l+2+3+ (100)结果。

EXEC addresult1 1005．修改上述存储过程为addresult2，将@n参数设定默认值为10，并改设@sum为输出参数，让主程序能够接收计算结果。

CREATE PROCEDURE addresult2@n int=10,/*最后一个数*/@sum int out/*结果*/ASDECLARE @i int=0BEGINset @sum=0WHILE(@i<=@n)BEGINSET @sum=@sum+@iSET @i=@i+1ENDENDGO6．调用上面修改后的addresult2存储过程，设置变量@s接收计算l+2+3+…+10的结果。

实验6 存储过程和触发器一、实验目的1、加深和巩固对存储过程和触发器概念的理解。

2、掌握触发器的简单应用。

3、掌握存储过程的简单应用。

二、实验容一）存储过程：1. 创建一存储过程，求l+2+3+…+n，并打印结果。

CREATE PROCEDURE addresultASDECLARE n int=10,/*最后一个数*/i int=0,result int=0 /*结果*/BEGINWHILE(i<=n)BEGINSET result=result+iSET i=i+1ENDPRINT'1+2+3+...+n的结果是:'PRINT resultRETURN(result)ENDGO2．调用上面的addresult存储过程，打印l十2+3+…+10的结果。

EXEC addresult3. 修改上述存储过程为addresult1,使得n为输入参数，其具体值由用户调用此存储过程时指定。

CREATE PROCEDURE addresult1n int=10 /*最后一个数*/ASDECLARE i int=0,result int=0 /*结果*/BEGINWHILE(i<=n)BEGINSET result=result+iSET i=i+1ENDPRINT'1+2+3+...+n的结果是:'PRINT resultRETURN(result)ENDGO4. 调用上面修改后的addresult1存储过程，打印l+2+3+…+100的结果。

EXEC addresult1 1005．修改上述存储过程为addresult2，将n参数设定默认值为10，并改设sum为输出参数，让主程序能够接收计算结果。

CREATE PROCEDURE addresult2n int=10,/*最后一个数*/sum int out/*结果*/ASDECLARE i int=0BEGINset sum=0WHILE(i<=n)BEGINSET sum=sum+iSET i=i+1ENDENDGO6．调用上面修改后的addresult2存储过程，设置变量s接收计算l+2+3+…+10的结果。

实验六存储过程和触发器实验六存储过程和触发器一、目的与要求1.掌握编写数据库存储过程的方法。

2.掌握建立数据库触发器的方法，通过实验观察触发器的作用和触发条件设置等相关操作。

二、实验准备1.了解编写存储过程和调用的T-SQL语法；2.了解触发器的作用；3.了解编写触发器的T-SQL语法。

三、实验内容(一)存储过程在studentdb数据库中建立存储过程getPractice，查询指定院系(名称)(作为存储过程的输入参数)中参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称，若院系不存在，返回提示信息。

提示：D_Info表中存储了院系代码D_ID，而St_Info表中学号字段St_ID的前两位与之对应，则D_Info表与St_Info表之间的联系通过这两个字段的运算构成连接条件。

1.分别执行存储过程getPractice，查询“法学院”和“材料科学与工程学院”的学生中参与“实践”课程的所有学生学号、姓名、所学课程编号和课程名称。

create procedure getPractice@d_name varchar(30)asselectst_info.st_id,st_info.st_name,s_c_info.c_no,c_info.c_name from st_info,d_info,s_c_info,c_infowhere d_info.d_name=@d_name and st_info.st_id=s_c_info.st_idand d_info.d_id=left(st_info.st_id,2)and s_c_info.c_no=c_info.c_no and c_info.c_type='实践'Goexec getPractice '法学院'exec getPractice '材料科学与工程学院'2.利用系统存储过程sp_rename将getPractice更名为getPctStusp_rename getpractice,getPctStu3.修改存储过程getPctStu，返回指定院系中参与实践课程的学生人次数，并利用该存储过程以“法学院”为输入参数验证执行的结果alter procedure getPctStu@d_name varchar(30)asselect count(s_c_info.st_id)from s_c_info,c_info,d_info,st_infowhere d_info.d_name=@d_name and st_info.st_id=s_c_info.st_id andd_info.d_id=left(st_info.st_id,2) ands_c_info.c_no=c_info.c_noand c_info.c_type='实践'goexec getPctStu '法学院'4.再修改存储过程getPctStu，返回指定院系中参与实践课程的学生人数。

实验六存储过程与触发器一、目的与要求1.掌握编写数据库存储过程的方法。

2.掌握建立数据库触发器的方法,通过实验观察触发器的作用与触发条件设置等相关操作。

二、实验准备1.了解编写存储过程与调用的T-SQL语法;2.了解触发器的作用;3.了解编写触发器的T-SQL语法。

三、实验内容(一)存储过程在studentdb数据库中建立存储过程getPractice,查询指定院系(名称)(作为存储过程的输入参数)中参与“实践”课程学习的所有学生学号、姓名、所学课程编号与课程名称,若院系不存在,返回提示信息。

提示:D_Info表中存储了院系代码D_ID,而St_Info表中学号字段St_ID的前两位与之对应,则D_Info表与St_Info表之间的联系通过这两个字段的运算构成连接条件。

1.分别执行存储过程getPractice,查询“法学院”与“材料科学与工程学院”的学生中参与“实践”课程的所有学生学号、姓名、所学课程编号与课程名称。

create procedure getPractice@D_Name varchar(30)outputasbeginif not exists(select *from D_Infowhere D_Name= @D_Name)print '对不起,该院系不存在'elseselect st_info、St_ID,C_Info、C_No,C_Namefrom s_c_info inner join st_info on st_info、St_ID=s_c_info、st_idinner join C_Info on s_c_info、c_no=C_Info、C_Nowhere st_info、St_ID in( select St_IDfrom st_info join D_Info on D_Info、D_ID =left(st_info、St_ID,2)where C_Info、C_Type='实践' and D_Info、D_Name= @D_Name)endgo2.利用系统存储过程sp_rename将getPractice更名为getPctStuexecute sp_rename getPractice , getPctStu3.修改存储过程getPctStu,返回指定院系中参与实践课程的学生人次数,并利用该存储过程以“法学院”为输入参数验证执行的结果alter procedure getPctStu@D_Name varchar(30)asbeginif not exists(select *from D_Infowhere D_Name= @D_Name)print '对不起,该院系不存在'elseselect st_info、St_ID,C_Info、C_No,C_Name,count (st_info、St_ID) as 人次数from s_c_info inner join st_info on st_info、St_ID=s_c_info、st_idinner join C_Info on s_c_info、c_no=C_Info、C_Nowhere st_info、St_ID in( select St_IDfrom st_info join D_Info on D_Info、D_ID =left(st_info、St_ID,2)where C_Info、C_Type='实践' and D_Info、D_Name= @D_Name)group by st_info、St_ID,C_Info、C_No,C_NameendGoexec getPctStu '法学院'Go4.再修改存储过程getPctStu,返回指定院系中参与实践课程的学生人数。

实验6 存储过程和触发器欧阳家百（2021.03.07）一、实验目的1、加深和巩固对存储过程和触发器概念的理解。

2、掌握触发器的简单应用。

3、掌握存储过程的简单应用。

二、实验内容一）存储过程：1. 创建一存储过程，求l+2+3+…+n，并打印结果。

CREATEPROCEDURE addresultASDECLARE @n int=10,/*最后一个数*/@i int=0,@result int=0 /*结果*/BEGINWHILE(@i<=@n)BEGINSET @result=@result+@iSET @i=@i+1ENDPRINT'1+2+3+...+n的结果是:'PRINT @resultRETURN(@result)ENDGO2．调用上面的addresult存储过程，打印l十2+3+…+10的结果。

EXEC addresult3. 修改上述存储过程为addresult1,使得@n为输入参数，其具体值由用户调用此存储过程时指定。

CREATEPROCEDURE addresult1@n int=10 /*最后一个数*/ASDECLARE @i int=0,@result int=0 /*结果*/BEGINWHILE(@i<=@n)BEGINSET @result=@result+@iSET @i=@i+1ENDPRINT'1+2+3+...+n的结果是:'PRINT @resultRETURN(@result)ENDGO4. 调用上面修改后的addresult1存储过程，打印l+2+3+ (100)结果。

EXEC addresult1 1005．修改上述存储过程为addresult2，将@n参数设定默认值为10，并改设@sum为输出参数，让主程序能够接收计算结果。

CREATEPROCEDURE addresult2@n int=10,/*最后一个数*/@sum intout/*结果*/ASDECLARE @i int=0BEGINset @sum=0WHILE(@i<=@n)BEGINSET @sum=@sum+@iSET @i=@i+1ENDENDGO6．调用上面修改后的addresult2存储过程，设置变量@s接收计算l+2+3+…+10的结果。

实验6 存储过程和触发器之阿布丰王创作一、实验目的1、加深和巩固对存储过程和触发器概念的理解.2、掌握触发器的简单应用.3、掌握存储过程的简单应用.二、实验内容一）存储过程：1. 创立一存储过程,求l+2+3+…+n,并打印结果. CREATEPROCEDURE addresultASDECLARE @n int=10,/*最后一个数*/@i int=0,@result int=0 /*结果*/BEGINWHILE(@i<=@n)BEGINSET @result=@result+@iSET @i=@i+1ENDPRINT'1+2+3+...+n的结果是:'PRINT @resultRETURN(@result)ENDGO2．调用上面的addresult存储过程,打印l十2+3+…+10的结果. EXEC addresult3. 修改上述存储过程为addresult1,使得@n为输入参数,其具体值由用户调用此存储过程时指定.CREATEPROCEDURE addresult1@n int=10 /*最后一个数*/ASDECLARE @i int=0,@result int=0 /*结果*/BEGINWHILE(@i<=@n)BEGINSET @result=@result+@iSET @i=@i+1ENDPRINT'1+2+3+...+n的结果是:'PRINT @resultRETURN(@result)ENDGO4. 调用上面修改后的addresult1存储过程,打印l+2+3+…+100的结果.EXEC addresult1 1005．修改上述存储过程为addresult2,将@n参数设定默认值为10,并改设@sum为输出参数,让主法式能够接收计算结果. CREATEPROCEDURE addresult2@n int=10,/*最后一个数*/@sum intout/*结果*/ASDECLARE @i int=0BEGINset @sum=0WHILE(@i<=@n)BEGINSET @sum=@sum+@iSET @i=@i+1ENDENDGO6．调用上面修改后的addresult2存储过程,设置变量@s接收计算l+2+3+…+10的结果.DECLARE @s intset @s=0EXEC addresult2 10,@sum=@s outPRINT'1+2+3+...+n的结果是:'PRINT @s7．创立一存储过程Proc_Student,用于显示学号为“0102”的学生基本信息（包括学号、姓名、性别和系）. CREATEPROCEDURE Proc_StudentASBEGINselect*from Swhere S.sno=0102ENDGOEXEC Proc_Student8．创立一存储过程Stu_grade,通过读取某门课的编号,求出不及格的学生的学号.CREATEPROCEDURE Stu_grade@n char(10)ASBEGINselect snofrom SCwhere cno=@n and grade<60ENDGO9．调用上面的存储过程Stu_grade,求出课程编号为“0101”的不及格的学生.EXEC Stu_grade 010110．创立一存储过程avgGrade,通过读取学生的学号,以参数形式返回该学生的平均分.CREATEPROCEDURE avgGrade@n char(10)ASBEGINselect AVG(grade)平均分from SCwhere sno=@ngroupby snoENDGO11．调用上面的存储过程avgGrade,求出学号为“990102014”的平均分.EXEC avgGrade 99010201412．删除上述存储过程avgGrade.dropprocedure avgGrade13.创立存储过程search,该存储过程有三个参数,分别为@t、@p1,@p2,根据这些参数,找出版名与@t有关,价格在@p1与@p2(@p2>=@p1)之间的书的编号,书名,价格,出舨日期.如果用户调用时没有指定@t参数的值．则暗示可为任意值,如用户没有指定@p2,则书本价格没有上限.用到的关系为：titles (title_id,title,price,pubdate).CREATEPROCEDURE search@t char(10)="%",@p1 char(10),@p2 char(10)=NULLASBEGINselect title_id,title,price,pubdatefrom titleswhere title=@t and price<=@p2 and price>=@p1ENDGO14．调用上面的存储过程search,求出版名与computer有关,而且价格小于$20年夜于$10的书.EXEC search "computer",10,20二）触发器：1、在学生成果库中创立触发器trigger1,实现如下功能：当在学生成果表（xscj）中拔出一条学生选课信息后,自动实现更新该学生在学生情况表（xsqk）中的总学分信息.分析：根据题意,也即要求在学生成果表中拔出一条记录时,自动更新学生情况表中的相应记录信息.可以通过在学生成果表中界说INSERT类型的触发器,触发器中语句要完成的功能是更新学生情况表中的相应学生的总学分信息.其实,只要在该生原总学分基础上加上新选课程的学分就可以了.createtrigger trigger1on xscjafterinsertasdeclare @credit int;select @credit=credit from inserted xscj;update xsqk set allcredit=allcredit+@credit;go2、创立触发器trigger2,实现当修改学生课程表（xskc）中的数据时,显示提示信息“学生课程表被修改了”.createtrigger trigger2on xskcafterupdateasprint'学生课程表被修改了';go3、创立触发器trigger3,实现当删除学生课程表中某门课程的记录时,对应学生成果表中所有有关此课程的记录均删除. createtrigger trigger3on xskcafterdeleteasdeclare @cname char(10);select @cname=cname from deleted xsks;delete xscj where cname=@cname;go4、创立触发器trigger4,实现当修改学生课程表（xskc）中的某门课的课程号时,对应学生成果表（xscj）中的课程号也作相应修改.createtrigger trigger4on xskcafterupdateasdeclare @cno char(10);declare @cname char(10);select @cname=ame,@cno=o from updated xsks; update xscj set o=@cno where ame=@cname;go5、创立触发器trigger5,实现当向学生成果表（xscj）中拔出一条选课记录时,检查该学生的信息是否存在在学生信息表中,如果不存在,则把该学生的基本信息加入到学生信息表中. createtrigger trigger5on xscjafterinsertasdeclare @sname1 char(10);declare @sname2 char(10)=NULL;select @sname1=xscj.sname from updated xscj;select @sname2=xsqk.sname from xsqk wherexsqk.sname=@sname1;if @sname2=NULLinsertinto xsqk values(NULL,@sname1);go6、在学生成果库中创立触发器trigger6,实现如下功能：当在学生成果表（xscj）中拔出一条学生选课信息后,检查该学生的信息是否存在在学生信息表中,如果不存在,则给出“该记录不能被拔出！”的毛病提示,并裁撤拔出把持；同样,如果课程信息在课程信息表中不存在,给出“该记录不能被拔出！”的毛病提示,并裁撤拔出把持.createtrigger trigger6on xscjforinsertasbeginif notexists(select xsqk.sname from xsqk where xsqk.sname in(select xscj.sname from inserted xscj))beginraiserror('该记录不能被拔出！',16,1)rollbackreturnendendgo7、创立触发器trigger7,强制实现业务规则：当向学生成果表中拔出一条记录时,自动修改学生情况表中该学生的总学分,要求总学分为该学生所有已修课程的学分总和.createtrigger trigger7on xscjforinsertasbegintransactiondeclare @credit int;select @credit=credit from inserted xscj;update xsqk set allcredit=allcredit+@credit; committransactiongo8、分别用触发器和存储过程实现对学生情况表（xsqk）和学生成果表（xscj）表的级联删除.createtrigger trigger8on xsqkafterdeleteasdeletefrom xscjwhere xscj.sname in(select xsqk.sname from deleted xsqk)goCREATEPROCEDURE del_qk_cj@sname char(10)=NULLASBEGINdeletefrom xscjwhere xscj.sname=@sname;deletefrom xsqkwhere xsqk.sname=@sname;ENDGO9、创立触发器8,要求实现：当向xscj表拔出一条记录时,判断该学生的总学分,如果总学分年夜于即是25,则给出“该学生已修满,不需要再选修！”的提示信息；否则,自动更新该学生的总学分.createtrigger trigger88on xscjforinsertasbegindeclare @credit1 int;declare @credit2 int;declare @sname char(10);select @credit1=xscj.credit,@sname=xscj.sname frominserted xscj;select @credit2=xsqk.allcredit from xsqk where xsqk.sname=@sname;if(@credit1+@credit2)>=25beginraiserror('该学生已修满,不需要再选修！',16,1) rollbackreturnendelseinsertinto xscj values(@credit1,NULL,NULL,@sname); endgo10、在数据库中用以下语句创立两张表：CREATE TABLE 卷烟销售表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,购货商 VARCHAR(40) NULL,销售数量 INT NULL,销售单价 MONEY NULL,销售金额 MONEY NULL)GO--业务规则：库存金额 = 库存数量 * 库存单价业务规则. CREATE TABLE 卷烟库存表(卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,库存数量 INT NULL,库存单价 MONEY NULL,库存金额 MONEY NULL)GO创立触发器[T_INSERT_卷烟库存表],实现每当[卷烟库存表]发生INSERT 举措,则引发该触发器.触发器功能：强制执行业务规则,保证拔出的数据中,库存金额 = 库存数量 * 库存单价.针对[卷烟库存表],拔出测试数据：注意,第一条数据（红塔山新势力）中的数据符合业务规则,第二条数据（红塔山人为峰）中,[库存金额]空,不符合业务规则,第三条数据（云南映像）中,[库存金额]不即是[库存数量]乘以[库存单价],不符合业务规则.第四条数据库存数量为0.请注意在拔出数据后,检查[卷烟库存表]中的数据是否库存金额 = 库存数量 * 库存单价.INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( '红塔山新势力',100,12,1200)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( '红塔山人为峰',100,22,null)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( '云南映像',100,60,500)INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)values( '玉溪',0,30,0)11、创立触发器[T_INSERT_卷烟销售表],实现每当卷烟库存表发生 INSERT 举措,则引发该触发器.触发器功能：实现业务规则：如果销售的卷烟品牌不存在库存或者库存为零,则返回毛病.否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额. createtrigger T_INSERT_卷烟销售表on卷烟销售表forinsertasbegintransactionif notexists(select卷烟品牌from卷烟库存表where卷烟品牌in(select卷烟品牌from inserted 卷烟库存表))beginraiserror('该卷烟不存在于库存,不能销售！',16,1);rollbackreturnendif exists(select库存数量from卷烟库存表where卷烟品牌in(select卷烟品牌from inserted 卷烟库存表)and库存数量<=0)beginraiserror('该卷烟库存小于即是,不能销售！',16,1);rollbackreturnendupdate卷烟库存表set库存金额=库存数量*库存单价where卷烟品牌in(select卷烟品牌from inserted 卷烟库存表) declare @卷烟品牌VARCHAR(40)set @卷烟品牌=(select卷烟品牌from inserted 卷烟销售表) declare @销售数量INTset @销售数量=(select销售数量from inserted 卷烟销售表)update卷烟库存表set库存数量=库存数量-@销售数量,库存金额=(库存数量-@销售数量)*库存单价where卷烟品牌=@卷烟品牌committransactiongo三）T-SQL编程1、从学生_课程数据库中查询所有学生选课成果情况：姓名、课程名、成果.要求：将学生的百分制转换为5级评分制,成果年夜于即是90显示为“优秀”,成果在80-89分显示为“良好”,“70-79”分显示为“中等”,成果在60-69显示为“及格”,60以下显示为“不及格”,没成果的显示为“未考”.而且输出记录按下列要求排序：先按学号升序,再按课程号升序,最后按成果降序.CREATEPROCEDURE选课成果情况ASBEGINselect S.sno 学号,S.sname 姓名,o 课程号,ame 课程名,成果=casewhen SC.grade>=90 then'优秀'when SC.grade>=80 then'良好'when SC.grade>=70 then'中等'when SC.grade>=60 then'及格'when SC.grade>=0 then'不及格'else'未考'endfrom SC,S,Cwhere o=o and SC.sno=S.snoorderby SC.grade descENDGO2、利用学生_课程数据库中的SC表,编程实现：如果所有学生所有课程的平均成果高于80分,使用while循环就将每门成果减5分,然后查找所有学生所有课程中的最高分,如果最高分年夜于或即是85分,while循环重新启动并再次将每门成果减5分并继续查找所有学生所有课程中的最高分,一直循环到最高分低于85分循环停止,然后退出.CREATEPROCEDURE选课成果情况ASBEGINwhile(select max(grade)from SC)>=85beginupdate SCset grade=grade-5select avg(grade)from SCif(select max(grade)from SC)<85 break;elsecontinueendselect avg(grade)平均成果from SC ENDGOEXEC选课成果情况。