数据库实验报告5实战：存储过程与触发器完整拆解指南

简介西北工业大学数据库实验报告第五部分围绕数据库核心操作展开面向正在学习《数据库原理》的本校学生及需要动手实践存储过程与触发器的初学者。报告系统梳理了数据表的创建与管理、视图重命名等基础操作并重点演示了带参数存储过程jsearch的编写与执行、加密存储过程jmsearch的创建与删除以及利用系统存储过程sp_helptext查看文本信息的方法。触发器部分同样充实覆盖针对SC表和S表的INSERT、DELETE、UPDATE触发器设计如禁止插入无效记录、禁止删除S表、级联删除选课记录、禁止修改sdept字段还包含触发器禁用、删除和动态更新CAvgGrade统计表等进阶场景。每个实验都配有可运行的SQL语句和执行结果便于对照练习。压缩包包含1个doc文档约231KB完整展示11页实验报告。已有716人学习下载适合作为课内实验的参考范本也能帮助读者深入理解存储过程与触发器的创建流程及验证思路。1. 拿到西北工业大学数据库实验报告5.doc先分清要补的是SQL还是数据库对象每学期期末这类文件名总会以各种途径出现在专业课群和网盘里。我见过不少人把“西北工业大学数据库实验报告5.doc”当成一份现成答案复制粘贴后跑出结果却答不上来老师的追问。实验报告5和前四个实验有本质区别前四个实验练的是数据库增删改查考验你输入哪条SQL实验5练的是存储过程、触发器和完整性约束考验你把业务规则写进数据库本身而不是往查询窗口里塞一条长SQL。下面把实验5从建表到触发器验证的完整路径拆开帮你把报告写成一份能自圆其说的技术说明。这份路径适合正在补实验报告、准备数据库课程设计或者兜底复习数据库SQL的读者。2. 实验5到底要做什么把数据库增删改查升级成数据库对象编程2.1 为什么大学数据库实验把存储过程和触发器放在实验5国内高校数据库实验课最常见的排法前面是SQL基础、查询优化、视图和索引到实验5开始进入“数据库对象编程”。存储过程和触发器不是新的SQL语法而是把一段业务逻辑放进了数据库服务器内部由服务器来保证它要么全部执行、要么全部回滚。实验5考察的其实是你是否理解数据库SQL的“执行边界”一条SELECT在哪返回、一个事务从哪开始、一条触发器语句什么时候被悄悄触发。很多人在实验5翻车是因为还在用写查询的思路写过程。查询是“你给我条件我把结果甩给你”存储过程则是“你来调用我我把一段完整操作做完再告诉你成没成功”。差值就在这报告里最值得写的也是这个认知转变。2.2 环境与前置准备字符集、登录参数、客户端工具做实验5前先确认环境别急着把代码贴进去。最常见的组合是MySQL 8.0以上版本加Navicat或DataGrip个别学校用SQL Server语法上有差异但事务和触发器模型是相通的。先跑一次系统变量检查把结果截图放进报告这也是让实验报告5.doc看起来不那么空的第一步。mysql -u root -p -e SHOW VARIABLES LIKE character_set_%;这条命令把当前实例的字符集相关变量全部列出来重点关注character_set_server和character_set_database。如果其中一个是latin1后面插入中文姓名和备注一定会变成问号。建议在MySQL配置文件my.cnf的[mysqld]段落里固定下面的参数否则只是当前会话有效下次重启又回去了。character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci参数说明utf8mb4是utf8的超集能存emoji和生僻字数据库课程设计里用它最保险collation决定排序和比较规则utf8mb4_unicode_ci按Unicode标准排序做模糊查询时行为更直观。设置完重启MySQL服务再连一次确认这两项不再是latin1。2.3 建表脚本和初始化数据把账户、流水两张表先立起来实验5的核心业务模型不需要复杂两张表就够一张账户表accounts一张流水表transactions。账户表负责存余额流水表负责记录每一笔收付。实验报告里先贴建表脚本再贴几条插入语句这是整个实验的“地基”触发器后面全部围绕这两张表转。DROP DATABASE IF EXISTS db_exp5; CREATE DATABASE db_exp5 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE db_exp5; CREATE TABLE accounts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, balance DECIMAL(12,2) NOT NULL DEFAULT 0.00, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB; CREATE TABLE transactions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, from_account INT UNSIGNED NOT NULL, to_account INT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL, tx_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_from FOREIGN KEY (from_account) REFERENCES accounts(id), CONSTRAINT fk_to FOREIGN KEY (to_account) REFERENCES accounts(id) ) ENGINEInnoDB;说明一下几个容易在报告里被问到的点。DECIMAL(12,2)是定点数金额字段不要用FLOAT或DOUBLE二进制浮点会在小数点后产生误差TIMESTAMP配ON UPDATE CURRENT_TIMESTAMP每次UPDATE都会自动刷新最后修改时间这是实验报告里可以写一句“利用数据库内建机制维护审计字段”的细节。外键约束写在表定义里而不是应用层目的是让数据库自己拒绝不存在的账户编号这也正好呼应了“完整性约束”这个实验主题。初始化数据用一条INSERT带VALUES列表完成INSERT INTO accounts(name, balance) VALUES (张三, 1000.00), (李四, 500.00);这时候往transactions里插一条流水余额不会自动变因为触发器还没有建。先手动插一条观察两个账户余额不变这就是后面触发器的“前后对比图”素材。INSERT INTO transactions(from_account, to_account, amount) VALUES (1, 2, 100.00); SELECT * FROM accounts;跑完这条SELECT你会发现张三还是1000、李四还是500。报告里把这句话写清楚“流水记录成功但余额未同步”后面触发器演示的对比效果就出来了。别小看这一步没有这个对照组你很难向老师证明触发器真的在干活。3. 存储过程怎么写进实验报告参数、事务和死锁边界3.1 设计一个带IN参数和OUT参数的转账存储过程实验5必做项目里转账是出现频率最高的业务因为它天然需要事务扣款、入账、写流水三个动作必须同生共死。给存储过程加上IN参数控制输入OUT参数返回执行码这样调用方就能判断成功还是失败。下面这段代码可以直接跑也建议原样写进实验报告。DROP PROCEDURE IF EXISTS sp_transfer; DELIMITER $$ CREATE PROCEDURE sp_transfer( IN p_from INT UNSIGNED, IN p_to INT UNSIGNED, IN p_amount DECIMAL(12,2), OUT p_code TINYINT ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_code 1; END; START TRANSACTION; UPDATE accounts SET balance balance - p_amount WHERE id p_from AND balance p_amount; UPDATE accounts SET balance balance p_amount WHERE id p_to; INSERT INTO transactions(from_account, to_account, amount) VALUES (p_from, p_to, p_amount); COMMIT; SET p_code 0; END$$ DELIMITER ;逻辑说明EXIT HANDLER 是异常处理器任何一步报错就直接ROLLBACK并且把返回码置为1相当于把“后悔药”写进了数据库里。第一个UPDATE的WHERE条件带了balance p_amount余额不够时影响行数为0不会扣成负数第二个UPDATE给收款方加钱最后插流水。所有操作包在START TRANSACTION和COMMIT之间任一步失败前面做的全部撤销。参数说明IN p_from和IN p_to是入参类型和accounts.id严格一致用INT UNSIGNED避免隐式类型转换导致索引失效IN p_amount用DECIMAL(12,2)和表字段精度一致OUT p_code是输出参数用来告诉客户端“成功返回0异常回滚返回1”。命名上统一加p_前缀避免和列名混合产生歧义这是写存储过程的一个实用习惯。3.2 事务边界COMMIT放过程内还是调用方上面这份代码把COMMIT写在了存储过程内部。实际生产里更讲究的做法是把COMMIT放到调用方存储过程只负责业务动作和ROLLBACK因为存储过程有可能被嵌进一个更大的事务里。一旦过程内部COMMIT外层事务就没法整体回滚了。实验报告里不需要两种都写但设计说明里加一句“本实验为简化调用将事务边界放在过程中”老师就会知道你考虑过这个问题。事务边界直接关联到另一个高频考点数据库并发锁。开两个mysql会话会话A先手动执行一条UPDATE但不COMMIT会话B再去更新同一个账户会发现B卡住不动。查询SHOW PROCESSLIST能看到B的State是Waiting for store lock这就是InnoDB行锁在起作用。实验报告5的加分项之一就是把这个等待现象截图配一句话说明“数据库通过行级锁避免并发转账的覆盖写”。SHOW PROCESSLIST;这个命令会列出所有连接和当前状态。看到“Waiting for store lock”后在会话A执行COMMIT或ROLLBACKB就会立刻返回。注意不要在生产环境乱试本地实验环境随便折腾这正是课程实验最有价值的地方。3.3 调用过程和排错CALL之后看什么存储过程写好了必须调用验证。调用语句本身不难难的是调用完以后怎么判断结果对不对。下面这段验证脚本会一次把返回码、账户余额、流水记录全部查出来。CALL sp_transfer(1, 2, 50.00, rc); SELECT rc AS return_code; SELECT * FROM accounts ORDER BY id; SELECT * FROM transactions ORDER BY id;这段脚本的执行结果应该有四个特征rc返回0张三余额从1000变成950李四从500变成550transactions里多了一条金额50的流水。如果rc返回1说明事务里某个步骤抛了异常优先检查外键指向的账户ID是否存在、p_amount是否超过余额。再试一个故意出错的调用比如把转出账户ID写成999CALL sp_transfer(999, 1, 10.00, rc); SELECT rc AS return_code;这里第一个UPDATE找不到id999影响0行第二个UPDATE给张三加10INSERT流水时外键会把from_account999拒绝从而触发异常处理器ROLLBACK。最终结果是rc1张三还是950没有多出10块钱。这就是事务回滚的证据报告里必写。4. 触发器部分NEW与OLD的引用规则和报告写法4.1 一个AFTER INSERT触发器让流水表自动更新账户余额存储过程解决的是“主动调用”的问题触发器解决的是“被动触发”的问题。实验5里最典型的触发器场景是往transactions插入一条流水后数据库自动更新accounts的余额不需要应用层再调一次UPDATE。先重置账户余额到初始状态再建触发器。UPDATE accounts SET balance 1000.00 WHERE id 1; UPDATE accounts SET balance 500.00 WHERE id 2; DROP TRIGGER IF EXISTS trg_tx_balance_ins; DELIMITER $$ CREATE TRIGGER trg_tx_balance_ins AFTER INSERT ON transactions FOR EACH ROW BEGIN UPDATE accounts SET balance balance - NEW.amount WHERE id NEW.from_account; UPDATE accounts SET balance balance NEW.amount WHERE id NEW.to_account; END$$ DELIMITER ;FOR EACH ROW表示每条插入的流水都会触发一次。NEW是一个行引用指向刚插入的这行数据NEW.amount就是本次流水的金额NEW.from_account就是付款账户。触发器里不需要也不能写COMMIT它依附于触发它的那条INSERT所在的事务INSERT成功提交触发器里的UPDATE才会一并提交。这时候再执行一条INSERTINSERT INTO transactions(from_account, to_account, amount) VALUES (1, 2, 200.00); SELECT * FROM accounts;结果应该是张三800、李四700。如果还是1000和500说明触发器没有建立成功检查DELIMITER是否生效、是否有语法报错。报告里贴“插入前”和“插入后”两张accounts表截图触发器部分就算完整了。4.2 多个触发器的执行顺序和不同数据库的差异一张表上可以建多个触发器但执行顺序在不同数据库里差异很大。MySQL里多个相同事件的触发器按创建时间顺序执行SQL Server里可以显式指定第一个和最后一个Oracle里还支持FOLLOWS和PRECEDES来控制先后。实验报告不用深入到底层原理但把这层差异写进“实验环境说明”里能体现你不是只会复制代码。数据库多触发器执行顺序修改触发器定义查询触发器MySQL按创建顺序执行建议合并事件不支持ALTER TRIGGER需DROP后重建SHOW TRIGGERS / information_schema.TRIGGERSSQL Server默认无保证可用sp_settriggerorder指定首末支持ALTER TRIGGERsys.triggersOracle默认无保证可用FOLLOWS / PRECEDES支持CREATE OR REPLACE TRIGGERDBA_TRIGGERSMySQL没有ALTER TRIGGER这是个很容易踩的坑。在实验5报告里修改触发器定义不能像改表结构那样直接ALTER只能先DROP再CREATE。还有一个关联坑给表改名字触发器不会跟着改名MySQL里触发器引用的是表表被重命名后触发器仍然指向旧表名运行时会报错。做mysql数据库修改结构这类操作时养成先查一遍SHOW TRIGGERS的习惯。4.3 实验报告里触发器说明怎么写一张表加三句话这段是给报告用的不是给代码用的。老师看报告时最想知道的是这个触发器什么时候触发、因为什么触发、触发后干了什么。用表格把设计意图列出来比大段文字清楚得多。项目内容触发器名称trg_tx_balance_ins触发时机AFTER INSERT触发事件transactions表插入流水作用表accountsNEW引用NEW.amount、NEW.from_account、NEW.to_account实现逻辑流水产生后自动改两个账户余额一个减一个加表格下面再补三句话第一条AFTER INSERT保证流水已经存在如果触发器执行失败整条INSERT会回滚第二条触发器的逻辑和存储过程独立应用层只负责插流水余额同步完全由数据库承担第三条验证方式是插入前后各查一次accounts表对比余额变化。这三句话写进Word的doc里配合前面的代码整个触发器模块就完整了。5. 实验报告5避坑指南死锁、NOT FOUND和中文乱码5.1 死锁和递归触发器让连接卡死现象在transactions上建了一个AFTER INSERT触发器触发器内部又执行INSERT或UPDATE transactions结果这条INSERT一执行客户端直接卡住不动过一会儿报错。原因触发器里再操作本表属于递归触发。MySQL会直接拒绝报错“Cant update table transactions in stored function/trigger because it is already used by statement which invoked this stored function/trigger”。如果用的不是MySQL而是别的数据库递归不阻止时就会出现死锁连接互相等待对方释放数据库并发锁最后全部超时。解决触发器里不要操作触发语句所在的同一张表只操作其他表。转账触发器只改accounts绝不碰transactions。如果确实需要更新流水状态改用BEFORE或AFTER UPDATE事件或者把逻辑改成先INSERT再调用一个存储过程去修流水。排查时用SHOW PROCESSLIST查看被卡住的连接找到对应线程后用KILL加线程ID强制结束再检查触发器的FROM子句里是否有本表。5.2 SELECT INTO查不到行时存储过程直接翻车现象存储过程里执行SELECT balance INTO v_bal FROM accounts WHERE id xxx当这个id不存在时CALL过程直接报错“No data found”而不是把变量置为NULL继续跑。原因普通SQL里SELECT查不到行只返回空结果但存储过程中使用SELECT INTO给变量赋值时查不到行会触发SQLSTATE 02000的NOT FOUND异常存储过程默认认为这是出错于是中断执行。解决过程里提前声明一个CONTINUE HANDLER把“没查到”变成“给变量赋默认值”DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_bal 0.00;这条声明放在BEGIN之后、SELECT INTO之前。它的意思是一旦出现NOT FOUND异常不中断执行而是把变量置为0继续往下走。这样id不存在时返回余额0而不是过程崩溃。另一个思路是先SELECT COUNT(*)确认行存在再赋值多一次查询但逻辑更直白。两种做法任选其一写进报告里都是“异常处理考虑”的体现。5.3 中文数据写进去变成问号现象INSERT语句里带着中文例如“王五”“测试用户”执行成功但SELECT出来后显示“”甚至长度都对不上。原因字符集不一致。表的默认字符集是latin1或者会话级的character_set_client是utf8mb4但表的列是latin1。中文在latin1里没有对应编码写入时直接被替换成问号。最麻烦的是这种问题在插入时不报错只能事后发现。解决三层都改。第一层建库时带上CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci第二层建表时再指定一次防止库级默认值漏下来第三层连接时执行SET NAMES utf8mb4保证客户端发送的字符集与服务器一致。如果是JDBC连接URL里再加characterEncodingutf8参数。已经插入的乱码数据不要指望UPDATE修回来因为编码已经丢了直接DELETE掉重新插入。5.4 CHECK约束“建了等于没建”现象在accounts表上添加了CHECK (balance 0)然后插入一条余额为负的记录插入成功约束没有拦截。原因MySQL 8.0.16之前版本对CHECK约束只解析不执行这是历史遗留行为很多人踩了坑还以为自己写错了语法。解决确认MySQL版本是8.0.16及以上低于这个版本就改用触发器实现。靠BEFORE INSERT触发器检查NEW.balance小于0时用SIGNAL SQLSTATE 45000抛出一个自定义错误效果和CHECK约束一样。更高一层是把余额检查写进应用层的Service里数据库负责最终兜底应用层负责快速反馈这也是实验报告里可以写的“双层校验”设计。6. 验证实验5结果用系统表、SHOW和EXPLAIN自证清白6.1 三条命令证明你的数据库对象真的生效实验报告5.doc里最怕的是只有代码没有验证结果。运行完所有实验后用下面三条语句把“数据库对象存在且可用”这件事坐实。SHOW PROCEDURE STATUS WHERE Db db_exp5; SHOW TRIGGERS FROM db_exp5; EXPLAIN SELECT t.* FROM transactions t JOIN accounts a ON a.id t.from_account WHERE a.id 1;第一条列出db_exp5库里所有存储过程能看到sp_transfer的创建时间和修改时间证明过程已经注册进数据库。第二条SHOW TRIGGERS展示触发器名称、触发事件和触发时机直接把Timing、Event、Statement三列截图放进报告比你手抄代码更可信。第三条EXPLAIN检查SQL的执行计划确认外键关联走了索引没有出现全表扫描这是报告里体现“实验5不是随便跑通就行”的关键证据。6.2 报告末尾的设计说明怎么写才不空报告最后一段不要写“本次实验完成了基本操作”这种话要写具体结论。给出一个可以直接套用的模板本实验完成accounts和transactions两张表的设计用存储过程sp_transfer实现了带事务的转账用触发器trg_tx_balance_ins保证流水产生时余额自动变化。经调用验证事务回滚正常触发器插入前后余额正确查询计划确认连接字段可以使用索引。这段写完再附上前面三类截图的组合图整份报告在“验证闭环”这一层就站稳了。6.3 我给自己定的三条规矩第一个习惯所有存储过程和触发器对象名带版本号比如sp_transfer_v1改逻辑时先DROP再创建新版本不会出现旧定义残留导致后续报错。第二个习惯每次改表结构都单独保存一份迁移SQL不直接在原文件上覆盖数据库课程设计答辩时被问“这个字段后来为什么不在了”翻迁移记录就知道原因。第三个习惯凡是跑完一个实验立刻把前后数据变化截成一张对比图标上红圈存到一个名为“验证截图”的文件夹里。这三个习惯帮我避开了实验5里至少一半的返工。希望帮到你。本文还有配套的精品资源点击获取