触发器与存储过程(函数)

针对SPJ_MNG数据库，创建并执行如下存储过程。（共计40分）实验内容、步骤以及结果

1. 1. 1. 创建一个没有参数的存储过程—jsearch1。该存储过程的作用是：当执行该存储过程时，将返回S表中北京供应商的所有信息。调用该存储过程并验证结果。（5分）

1. 1. 1. 创建带输入参数的存储过程—jsearch2。该存储过程的作用是：当输入一个供应商所在城市名时（如北京），将返回该供应商的所有信息。调用存储过程并验证结果。（5分）

     

1. 1. 1. 创建带输入参数和输出参数的存储过程(函数)—jsearch3。该存储过程的作用是：当输入一个供应商编号(输入参数SNO)时，将返回该供应商的名称(输出参数SNAME)。调用存储过程并验证结果。（5分）

1. 1. 1. 创建一个使用游标的存储过程jsearch4，创建成功后调用该存储过程并验证结果。该存储过程的功能：当输入一个工程号JNO时，将返回供应该工程零件的所有供应商的名称(SNAME)，这些供应商名拼接成一个字符串，并用逗号’,’分隔。

例如： 输入：J2，输出： '精益, 盛锡, 为民'。（10分）

1. 1. 1. 查看存储过程jsearch1和jsearch2的文本信息。（5分）

提示：show create procedure jsearch1。为方便查看，可在以上命令末尾加\G，使用两列文本形式显示。

1. 1. 1. 查看存储过程jsearch1基本状态信息。（5分）

提示：show procedure status like 'jsearch%';

1. 1. 1. 删除jsearch1存储过程。（5分）

1. 针对Student数据库，创建和执行如下的触发器：（共计40分）
   1. 1. 删除SC表上的外键约束，针对SC表创建一个名为insert_s的INSERT触发器。该触发器的功能：当用户向SC表中插入记录时，如果插入的cno值不是C表中Cno的已有值，则提示用户“不能插入C表中没有的数据”，并阻止该数据的插入；如果插入的sno值不是S表中的sno的已有值，则提示用户“不能插入S表中没有的数据”，并阻止该数据的插入。触发器创建成功之后，向SC表插入记录，验证触发器是否正常工作。(5分)

1. 1. 1. 为S表创建一个名为dele_s1的DELETE触发器，该触发器的作用是提示用户“不能删除该表中的数据”并阻止用户删除S表中的数据。触发器创建成功之后，删除S表中记录，验证触发器是否正常工作。(5分)

1. 1. 1. 为S表创建一个名为dele_s2的DELETE触发器，该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。触发器创建成功之后，删除S表中的记录，验证触发器是否正常工作（确认S表和SC表相关数据是否被删除）。(5分)

可见,表s的数据成功被删除.

1. 1. 1. 为S表创建一个名为update_s的UPDATE触发器，该触发器的作用是禁止更新S表中“sdept”字段的内容（更新不成功，并且提示“不能更新sdept字段”）。触发器创建成功之后，更新S表中“sdept”字段的内容，验证触发器是否正常工作。(5分)

1. 1. 1. 删除update_s触发器。(5分)

1. 1. 1. 自己设计一个before update触发器和after update触发器，比较before 和after触发器的区别。(5分)

对于before 触发器,沿用上题的触发器,又构造新的after 触发器,如下图. after触发器只能在触发条件被执行后起到提示作用, 不能阻止等.

1. 1. 1. 创建一个新的课程成绩统计表 CAvgGrade(Cno, Snum, examSNum, avgGrade)，分别表示课号，选该课程的学生人数，参加考试人数，该门课程的平均成绩。利用触发器实现如下的功能：当SC表中插入、删除或者更新某个人的成绩时，自动更新表CAvgGrade。注意SC表中的grade为NULL时表明该学生还未参加考试，计算平均成绩时不需要计算该成绩，但是grade为0即考试成绩为0时，计算平均成绩需要计算该学生成绩。(10分)

1.首先创建一个公共存储过程:

2.分别创建对于插入,修改,删除的触发器:

1. 插入,删除,修改数据,验证触发器是否正常运行

插入数据时,发现平均成绩被成功更改.

更新数据,平均成绩也被更改.

删除了2003号学生的成绩,平均成绩也被更改,故触发器正常运行.

1. 创建一个员工表employee(eID, eName, salary)，假设该表中有1000条员工数据，完成下列要求。（总计20分，每题10分）
   1. 1. 为了协助本题自动生成1000条员工数据，创建一个自动生成员工ID的用户自定义函数generateEID。其中员工ID要求是一个8位的数字，前四位表示插入员工数据的当前年份，后四位按照从0001到9999的顺序增长。例如2015年插入的第一条数据是20150001，所有1000条员工ID分别是20150001-20151000。调用该函数实现自动插入1000条数据。（注意插入数据的时候员工姓名可以为任意值，工资是2000-5000之间的数字）

1. 1. 1. 该公司计划为员工按照一定的规则涨工资，请使用游标创建一个存储过程，执行该存储过程完成本次工资调整。工资增长规则如下：
2. 工资在3000元以下，每月涨300元；
3. 工资在3000-4000元之间，每月涨200元；
4. 工资大于或者等于4000元，每月涨50元；

对比上题的初始salary 可以发现,salary 的增加成功.

• 实验中出现的问题以及解决方案(对于未解决问题请将问题列出来)

       实验开始不懂存储过程和触发器的语法要求, 犯过一些错误,比如不使用delimiter来修改语句结束符号, 导致触发器内部仅执行一条语句就结束; 还有不太了解mysql的流程控制语句, 最后查阅相关资料,了解到流程控制语句都有if, case, loop, repeat等,学习相关语法后,遂完成实验.

       在完成触发器实验时, 不知道怎么获取新的和旧的字段值来进行条件判断, 后来查阅资料发现使用new.字段 和 old.字段来进行比较,后来学习后完成实验