简介这是一份西北工业大学《数据库原理》课程实验报告五的完整文档主要面向学习数据库原理与应用的高校学生尤其是西工大相关课程的同学。报告围绕数据库基本操作展开涵盖数据表创建与管理、系统存储过程调用、自定义存储过程编写与执行以及多种触发器的创建与验证并附带完整SQL语句和运行截图便于对照练习。压缩包内仅包含1个doc文件大小约231KB内容为11页的实验报告正文步骤清晰、结果直观。目前已有716人学习下载。通过本报告读者可以掌握sp_rename等系统存储过程的使用方法理解带参数存储过程、加密存储过程的编写思路同时能学会INSERT、DELETE、UPDATE三类触发器的设计逻辑例如禁止删除、级联删除、禁止更新字段等典型场景并了解触发器禁用、删除及自动维护统计表的综合应用。对于需要完成数据库实验或准备相关考试的学生这份报告可直接作为参考模板也可帮助加深对数据库完整性和并发控制的理解。1. 一份西工大数据库实验报告存储过程、触发器、游标全流程实战拿到这份西北工业大学《数据库原理》实验报告时我最先翻的其实不是答案而是它的实验任务顺序改视图名、建带参存储过程、连做六个触发器、最后用游标批量调工资。作为把同一套实验在 SQL Server 上跑过不止一遍的人我的判断是这份资料的价值不在“标准答案”而在于它把存储过程、触发器、游标这三块最容易写错又不报错的内容全部放在了同一套数据库环境下让你反复练手。互联网上能找到的数据库实验报告不少但多数只贴代码不解释选型这份却把验证步骤和踩坑点都留了痕迹。正在做数据库课程设计、准备期末上机的同学或者想把手写 SQL 落到真实调试场景的工程师都值得照着敲一遍。2. 存储过程三板斧带参查询、加密保护与文本校验2.1 带参存储过程为什么 jsearch 不能拿裸 SQL 凑合实验第一题要求创建一个叫 jsearch 的存储过程输入任意工程代号返回供应商名称、零件名称和工程名称。很多新手觉得这题多余——直接在查询分析器里敲一条 SELECT 不好吗但带参存储过程的价值在 jsearch 这种“参数固定、结构固定”的调用场景里体现得最清楚SQL 文本和传入值分离每次执行传入不同参数数据库会复用执行计划同时避免了把用户输入直接拼进 SQL 字符串。create procedure dbo.jsearch search_jno nchar(20) as begin select j.jname, s.sname, p.pname from dbo.s as s inner join dbo.spj on spj.sno s.sno inner join dbo.p as p on spj.pno p.pno inner join dbo.j as j on spj.jno j.jno where spj.jno search_jno; end go exec dbo.jsearch search_jno NJ1;这里有一个原报告里很容易连累初学者的小坑报告原文写的是create procedure jsearch(search_jno nchar(20))参数不带也没有类型前缀这在 T-SQL 语法里直接报错。SQL Server 的存储过程参数必须用开头变量声明同理写成上面的search_jno nchar(20)才能编译通过。字段是 nchar执行时传值加N前缀能避免本地连接和远程连接之间因为字符编码不同而产生的乱码。查询用四表 join从spj作为关联骨架开始分别挂上s、p、j本质上是在做“供应关系 → 供应商 → 零件 → 工程”的逐级展开这种从中间关系表往外扩散的写法在带参存储过程里是最常见的结构。2.2 WITH ENCRYPTION 与 sp_helptext加密存储过程的验证方式第二题要求用一个 S 表创建加密的存储过程 jmsearch返回供应商所有信息。“加密”在这个实验里的真实含义是 SQL Server 的WITH ENCRYPTION选项创建时把存储过程的定义文本做混淆处理之后系统目录里保存的是密文。注意这个“加密”保护的是存储过程源码而不是业务数据业务字段该明文存还是明文存两部分是两回事。create procedure dbo.jmsearch with encryption as begin select * from dbo.s where city N; end go exec dbo.jmsearch; go exec sp_helptext dbo.jmsearch;sp_helptext是 SQL Server 查看对象定义的系统存储过程对普通存储过程返回完整创建语句对WITH ENCRYPTION创建的对象返回空结果加一条提示信息。所以验证“是否真的加密了”最直接的观测点就是sp_helptext的输出是否为 NULL 或空文本。下表是我平时判断对象可读性的参照对象状态sp_helptext 输出能否直接查到源码普通存储过程返回完整定义文本能WITH ENCRYPTION 存储过程返回空/NULL附提示不能视图/触发器普通返回定义文本能WITH ENCRYPTION 视图返回空/NULL不能报告里的where city N看起来有点怪它只是想筛出城市字段为空的供应商作为演示够用实际业务里这个条件大概率要换成具体城市名。还需要记住一点WITH ENCRYPTION只在创建时生效创建成功后再执行ALTER重新定义密文会被新定义覆盖所以“加密”不是长期免维护的保险柜。2.3 查看、执行、删除存储过程生命周期管理报告在完成 jsearch 和 jmsearch 之后依次做了三件事sp_helptext查看文本、exec jmsearch执行、drop procedure jmsearch删除。这个顺序本身就是标准操作流程我维护线上数据库也按同样的顺序走只是多了一步幂等清理。exec sp_helptext dbo.jsearch; exec sp_helptext dbo.jmsearch; go exec dbo.jmsearch; go drop procedure dbo.jmsearch; go if object_id(dbo.jmsearch, p) is not null drop procedure dbo.jmsearch;sp_helptext适合在改存储过程之前留一份“当前版本”存档drop procedure会把对象定义从sys.sql_modules里一并清掉。如果脚本要重复执行建议先写if object_id(...) is not null drop procedure ...做幂等清理否则第二次运行会报“已存在同名对象”。3. 触发器不是黑匣子INSTEAD OF 与 AFTER 的行为差3.1 用 INSTEAD OF 说“不”用 AFTER 做“善后”报告里有一组非常典型的对照实验insert_s是instead of insertdele_s2是after delete。同样是“触发器”三个字两种触发时机语义完全不同。instead of的意思是“用触发器里的代码取代原本的 INSERT 操作”适合做校验和拦截比如向 SC 表插入记录时如果课程号在 C 表中不存在就打印提示并回滚。after的意思是“原本的 INSERT/DELETE/UPDATE 已经执行再接着去做附加动作”适合做级联和统计比如删除 S 表学生时顺带把 SC 表里这个学生的选课记录也删掉。这两种选择是触发器设计里最前置的决策。需要拦截非法操作时优先考虑instead of因为它在数据变更发生前就介入需要在合法变更之后维持其他表的一致状态统计、同步、归档、日志用after更贴合执行顺序。create trigger insert_s on dbo.sc instead of insert as begin if exists ( select 1 from inserted as i where i.cno not in (select cno from dbo.c) ) begin raiserror(N不能插入这样的记录, 16, 1); rollback transaction; end else begin print N记录插入成功; insert into dbo.sc (sno, cno, grade) select sno, cno, grade from inserted; end end这段代码里有一个隐患需要点名raiserror之后的rollback transaction不能随便删因为raiserror只是把错误抛给客户端事务状态并不会自动回滚。实验验证时要分别插入一条合法记录和一条非法记录观察提示信息是不是按预期分支走。实际项目里我更习惯用throw直接抛出异常让应用层捕获后统一处理而不是在触发器里散落print字符——print的提示只出现在 SSMS 的消息页签里应用程序基本感知不到。3.2 inserted 与 deleted触发器里的影子表inserted和deleted是触发器内部的两张内存表这是整个实验里最少被真正讲透、却最影响结果的机制。inserted保存“这次操作影响的新行”deleted保存“这次操作影响的旧行”。INSERT 只产生insertedDELETE 只产生deletedUPDATE 则两张表都有deleted放更新前的旧版本inserted放更新后的新版本。create trigger dele_s1 on dbo.s instead of delete as begin rollback transaction; print N禁止删除; end go create trigger dele_s2 on dbo.s after delete as begin delete from dbo.sc where sno in (select sno from deleted); end两个触发器放在一起对比非常清楚dele_s1用instead of delete直接rollback实现“禁止删除”S 表的物理数据没有任何变化dele_s2用after delete在 S 表记录已经删除之后从deleted表里取出被删学生的 sno 集合去 SC 表做级联清理。这里in (select sno from deleted)是触发器里最常用的写法为的是处理“一次删除多条记录”的场景。如果写成where sno (select sno from deleted)SQL Server 在多行删除时会直接报“子查询返回多行”的错误。这里还有一个报告里没提到的坑dele_s1和dele_s2在同一张表上同时存在时instead of delete会把所有 DELETE 操作拦下来after delete触发器永远没有机会触发——因为根本没有任何行被真正删除。所以实验里这两题虽然是分开验证的但如果你把两个触发器同时留在 S 表上再跑一次删除会发现 SC 表没有被级联清理。这个现象我在下面避坑章节会再展开。3.3 禁用触发器与删除触发器留后路与断后路报告里有一道操作题很耐看创建update_s触发器禁止修改 S 表的sdept字段验证生效之后用disable trigger禁用再更新sdept验证解禁成功最后drop trigger把触发器彻底删掉。create trigger update_s on dbo.s instead of update as begin if update(sdept) begin raiserror(Nsdept 不能被修改, 10, 1); end end go disable trigger update_s on dbo.s; go update dbo.s set sdept NCS1 where sno N95002; go drop trigger update_s; godisable trigger和drop trigger的区别一句话概括禁用是“留后路”触发器定义还在随时可以重新启用适合在批量导入数据或临时放行时用删除是“断后路”对象没了要恢复只能重新创建。报告里先 disable 再 drop 的顺序是想让学生体会两种操作对“后续还能不能自动拦截”的不同影响。在正式环境里我一般只用disable trigger做临时放行并在维护窗口结束时重新enable trigger不会随便drop——因为 drop 之后一旦应用层某个功能依赖了触发器的拦截逻辑就相当于把安全网给撤了。4. CAvgGrade 统计表触发器里的多行与 NULL、0 边界4.1 统计表为什么要交给触发器维护第四题要求在 Student 库里新建一张成绩统计表 CAvgGrade并利用触发器实现SC 表插入、删除、成绩更新时自动更新统计表。这道题的真实工作场景非常常见——课程平均分、选课人数这类指标如果每次展示都由应用层现场count(*)和avg(grade)数据量一大查询成本成倍上涨。所以很多系统会单独维护统计表问题是谁来保证统计表和明细表始终同步。答案是触发器。把统计逻辑放在for insert, delete, update的触发器里应用层每次对 SC 表做增删改统计表都会自动重算应用层完全无感。相比应用层在事务里“先改 SC 再改 CAvgGrade”的做法触发器更不容易漏更新也不存在“上层代码忘记调统计接口”的人为失误。4.2 原报告统计逻辑分析变量承接的隐患原报告给出的触发器核心代码是这样一个思路从inserted或deleted里取出当前被影响的课程号然后对 SC 表做统计再update CAvgGrade。create trigger update_sc_cavggrade on dbo.sc for insert, delete, update as begin declare cno nchar(10); declare ssum int; declare examssum int; declare avggrade numeric(10, 2); select cno cno from inserted; select cno cno from deleted; select ssum count(*) from sc where cno cno; select examssum count(*) from sc where cno cno and grade 0; select avggrade avg(grade) from sc where cno cno and grade 0; update CAvgGrade set ssum ssum, examSnum examssum, avgGrade avggrade where cno cno; end先说结论这个写法在“一次只插入或删除一行”的验证场景下能跑但它有一个隐蔽问题——select cno cno from inserted和select cno cno from deleted都只承接了第一个变量赋值。如果应用层一次性插入 10 条不同课程的成绩cno只会得到其中一行count和avg也只按这一行算其余课程的统计表就漏更了。稳妥做法是先按 cno 分组再把各组结果合并进统计表。create trigger update_sc_cavggrade_fixed on dbo.sc for insert, delete, update as begin set nocount on; merge into dbo.cAvgGrade as t using ( select sc.cno, count(*) as ssum, sum(case when sc.grade is not null then 1 else 0 end) as examssum, avg(sc.grade) as avggrade from dbo.sc where exists ( select 1 from (select cno from inserted union select cno from deleted) as d where d.cno sc.cno ) group by sc.cno ) as s on t.cno s.cno when matched then update set t.ssum s.ssum, t.examSnum s.examssum, t.avgGrade s.avggrade when not matched then insert (cno, ssum, examSnum, avgGrade) values (s.cno, s.ssum, s.examssum, s.avggrade); end这个修正方案有两个核心点。第一用union合并inserted和deleted的课程号避免“插入时 deleted 为空、删除时 inserted 为空”导致取不到完整课程集合。第二用merge把重算结果一次性写回统计表when matched更新已有课程when not matched插入新出现课程SC 表里课程被全部删光时统计表也能跟着收敛。实际跑实验的时候建议先插入一行新课程再删掉一行旧课程最后更新一个已有学生的成绩三个动作分别观察统计表的数字变化才能把触发器的三种触发路径都覆盖到。4.3 NULL 与 0 的边界考试人数和平均分的口径这道题最后一个容易做错的点是成绩为空和成绩为零的处理。报告明确写了grade 为 NULL 表示未参加考试计算平均分时不算grade 为 0 表示考了但得零分必须算进去。如果用where grade 0来过滤就把 0 分考生漏掉了平均分被抬高如果用where grade is not null则既排除了 NULL 也保留了 0 分口径才对得上。select cno, count(*) as ssum, sum(case when grade is not null then 1 else 0 end) as examssum, avg(grade) as avggrade from sc group by cno;avg函数对 NULL 自动忽略所以这里直接avg(grade)就天然满足“不计 NULL、计 0 分”的要求前提是 SC 表里的 grade 字段允许 NULL并且应用层严格用 NULL 表示未考试。验证时要往 SC 表插入一个grade NULL的学生和一个grade 0的学生观察统计表里考试人数和平均分的变化差异——只有这两种数据都覆盖到验证才算完整。5. 避坑排查这份实验最容易翻车的五个现场5.1 现象sp_rename 之后视图名找不到创建完视图 V_SPJ执行exec sp_rename v_spj, v_spj_三建后再查询视图报“对象名无效”。原因sp_rename的第一个参数要写对象当前的完整名称包括架构名视图重命名后如果后续脚本仍引用旧名称立即报错。报告里的v_spj_三建含中文字符在 SQL Server 里能建但客户端连接字符集不对时查询时会变成乱码或找不到对象。解决重命名时写完整名称并在同一次会话中立即用新名验证exec sp_rename dbo.v_spj, v_spj_三建; go select * from dbo.v_spj_三建;注意中文字段名和对象名在 SQL Server 里能建但跨数据库、跨工具迁移时容易踩编码坑正式环境尽量避免。5.2 现象INSTEAD OF 触发器里 rollback 后客户端报“事务计数不匹配”向 SC 表插入非法课程号触发器确实打印了“不能插入”但应用端报错事务计数不匹配程序崩溃。原因rollback transaction在触发器中会回滚整个事务批处理随后会继续跑让客户端的事务计数和服务器端不一致SQL Server 报 “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements”。解决在instead of触发器中不要靠 rollback 来“拒绝”直接在分支里判断后返回并抛出异常让应用层处理而不是用回滚手段去干扰事务状态。上面的insert_s示例里把rollback transaction换成throw或raiserror后再配合return退出就不会出现事务计数错乱。5.3 现象CAvgGrade 多行插入统计漏行应用一次性插入多条不同课程的成绩统计表只有一条课程被更新。原因原报告写法用select cno cno from inserted承接变量只保留一行结果其余课程号被覆盖统计自然不全。解决按第 4 章的merge写法以inserted union deleted作为分组骨架按 cno 分组统计后整体写回。如果因为某些原因不能改触发器结构至少也要用select distinct cno from inserted开一个游标逐条处理但不推荐性能差且容易被再次误解。5.4 现象generateEID 只插入 1 条数据就“成功”了创建完 generateEID 存储过程执行后 employee 表只有 1 行数据而不是 1000 行。原因原报告的 while 循环里return写在了循环体内存储过程第一次迭代后就直接返回后面的 999 条插入根本没机会执行。create procedure dbo.generateEID as begin set nocount on; declare i int 0; while (i 1000) begin insert into dbo.employee (eid, ename, salary) values (20160001 i, Nname convert(nvarchar(20), i), 2000 cast(floor(rand() * 3001) as int)); set i i 1; end end go exec dbo.generateEID;return的位置是重点必须放在while循环之外或整个存储过程末尾不能放在循环体内。生成员工 ID 的逻辑是 8 位数字前四位 2016后四位从 0001 递增对应20160001 i的写法代入 i0 得到 20160001i999 得到 20161000正好覆盖报告要求的 1000 条。工资用2000 cast(floor(rand() * 3001) as int)rand() 返回 0 到 1 之间的浮点数乘 3001 后取 floor得到一个 0 到 3000 的整数加上 2000 后正好落在 2000 到 5000 区间。注意rand()在循环里每次都会重新计算所以这 1000 条工资不会重复。5.5 现象游标循环卡死工资被重复涨执行游标存储过程调整工资循环一直不结束或员工工资被重复更新涨完一轮又涨一轮。原因游标循环里没有正确推进fetch next或者while条件写成了while FETCH_STATUS -1之类的错误判断导致指针永远停在第一行不断对同一行执行update ... where current of。解决游标循环的固定骨架是“open → fetch → while FETCH_STATUS 0 → 业务处理 → fetch next → close → deallocate”declare mycursor cursor local for select salary from dbo.employee; open mycursor; declare salary int; fetch next from mycursor into salary; while fetch_status 0 begin if salary 3000 update dbo.employee set salary salary 300 where current of mycursor; else if salary 4000 update dbo.employee set salary salary 200 where current of mycursor; else update dbo.employee set salary salary 50 where current of mycursor; fetch next from mycursor into salary; end close mycursor; deallocate mycursor;current of mycursor要求更新语句作用在游标当前行上不能用子查询或 join 绕过fetch_status 0是继续循环的条件-1 表示已超出结果集-2 表示行已被删除。注意工资增长规则是有边界区间的低于 3000 涨 3003000 到 4000 之间涨 200大于等于 4000 涨 50。代码里if salary 3000和else if salary 4000的组合恰好把 3000 这个临界值划到了第二档和题目规则一致。实际业务里这种按区间更新的需求完全可以用一条集合式 UPDATE 替代游标性能高得多游标只是用来理解逐行处理的教材手段。6. 把这套实验变成你自己的 SQL 调试模板实验报告做完了更值钱的是把它的验证流程沉淀成习惯。我每次在 SQL Server 里写存储过程或触发器都会强制自己走一套固定动作创建前先写幂等清理创建后立刻查sys.sql_modules看定义是否正常注册再执行一次针对性的增删改查触发验证最后清场。select o.name, m.definition from sys.objects as o left join sys.sql_modules as m on o.object_id m.object_id where o.name in (jsearch, jmsearch, insert_s, dele_s2);这组查询能一次性看到对象是否存在、定义是否加密、源码文本是什么。存储过程或触发器创建时报“对象已存在”先跑这条 SQL 确认状态比反复猜测快得多。拿这份报告来说每个触发器建完我都会做三个验证动作插入一条合法数据看是否成功入库插入一条非法数据看是否被拦截并给出明确提示删除一行数据看级联操作是否生效。三次验证都通过这张表相关的触发器才算真正可用。触发器是否被禁用也可以查询sys.triggers里的is_disabled字段而不是靠肉眼去 SSMS 里翻树节点。从那以后我每拿到一份线上发来的“触发器失灵”问题都会先问自己三个问题这个触发器是 INSTEAD OF 还是 AFTER我有没有真的执行过 DELETE 之外的操作来验证deleted表的内容统计口径里 NULL 和 0 是不是被同一个条件误伤这套实验报告让我把这些追问变成了肌肉记忆希望你照着跑一遍时也能把同样的验证顺序刻进自己的习惯里。希望帮到你。本文还有配套的精品资源点击获取