西北工业大学数据库实验报告5.doc

1、数据库原理实验报告学号 姓名 班级 日期题目：实验一数据库和表的创建与管理一. 实验内容、步骤以及结果1. 使用系统存储过程(sp_rename)将视图“V_SPJ”更名为“V_SPJ_三建”。（5 分）exec sp_rename v_spj,v_spj_三建2. 针对 SPJ 数据库，创建并执行如下的存储过程：（共计 35 分）(1) 创建一个带参数的存储过程jsearch。该存储过程的作用是：当任意输入一个工程代号时，将返回供应该工程零件的供应商的名称(SNAME)和零件的名称(PNAME)以及工程的名称(JNAME)。执行 jsearch 存储过程，查询“J1”对应的信息。（10分）创

2、建：create procedure jsearch(search_jno nchar(20) )asbeginselect j.jname,s.sname,p.pnamefrom s,p,j,spjwherespj.jno=search_jno and spj.jno=j.jno and spj.sno=s.sno and spj.pno=p.pnoend执行：EXEC jsearch search_jno=J1(2) 使用 S 表，为其创建一个加密的存储过程jmsearch。该存储过程的作用是：当执行该存储过程时，将返回北京供应商的所有信息。（10 分）创建：create procedur

3、e jmsearch with encryptionasbegin select * from S where city=北京 end使用系统存储过程 sp_helptext 查看 jsearch, jmsearch 的文本信息。（5 分）exec sp_helptext jsearchexec sp_helptext jmsearch(3) 执行 jmsearch 存储过程，查看北京供应商的情况。（5 分）执行：exec jmsearch(4) 删除 jmsearch 存储过程。（5 分）drop procedure jmsearch3. 针对 Student 数据库，创建和执行如下的触发器

4、：（共计 40 分）(1) 删除 SC 表上的外键约束，针对 SC 表创建一个名为 insert_s 的 INSERT 触发器。该触发器的功能：当用户向 SC 表中插入记录时，如果插入的 cno 值不是 C 表中Cno 的已有值，则提示用户“不能插入记录这样的纪录”，否则提示“记录插入成功”。触发器创建成功之后，向 SC 表插入记录，验证触发器是否正常工作。(5分)create trigger insert_son SCinstead of insertasif (exists(select *from inserted where cno not in (select cno from C)

5、beginprint 不能插入这样的记录！rollback transactionendelseprint 记录插入成功！验证：insert into sc values (95009,8,98)更改数据：insert into sc values (95009,5,98)(2) 为 S 表创建一个名为 dele_s1 的 DELETE 触发器，该触发器的作用是禁止删除 S 表中的记录。触发器创建成功之后，删除 S 表中的记录，验证触发器是否正常工作。(5 分)创建：create trigger dele_s1 on sinstead of delete as rollback transac

6、tionprint 禁止删除验证：delete from s where sno=95001(3) 为 S 表创建一个名为 dele_s2 的 DELETE 触发器，该触发器的作用是删除 S 表中的记录时删除 SC 表中该学生的选课纪录。触发器创建成功之后，删除 S 表中的记录删除 S 表中的记录，验证触发器是否正常工作(SC 表中的数据被正常删除)。(5 分)创建：create trigger dele_s2 on safter delete asdelete from scwhere sno in (select sno from deleted)验证：delete from s wher

7、e sno=95001S 表中：Sc 表中：(4) 为 S 表创建一个名为 update_s 的 UPDATE 触发器，该触发器的作用是禁止更新 S表中“sdept”字段的内容。触发器创建成功之后，更新 S 表中“sdept”字段的内容，验证触发器是否正常工作。(5 分)创建:create trigger update_s on s instead of updateas if update(sdept)begin raiserror(sdept 不能被修改,10,1)end验证：(5) 禁用 update_s 触发器。禁用之后，更新 S 表中的“sdept”字段的内容，验证触发器是否还继续正

8、常工作。(5 分)禁用触发器：disable trigger update_s on s验证：update s set sdept=CS1where sno =95002(6) 删除 update_s 触发器。(5 分)drop trigger update_s(7) 创建一个新的课程成绩统计表 CAvgGrade(Cno, Snum, examSNum, avgGrade)，分别表示课号，选该课程的学生人数，参加考试人数，该门课程的平均成绩。利用触发器实现如下的功能：当 SC 表中有记录插入、删除或者某个人的成绩更新时，自动更新表 CAvgGrade。注意 SC 表中的 grade 为 NU

9、LL 时表明该学生还未参加考试，计算平均成绩时不需要计算该成绩，但是 grade 为 0 即考试成绩为 0 时，需要计算该成绩。(10 分)创建表：创建触发器：use studentgocreate trigger update_sc_cavggrade on scfor insert , delete , update as begin declare cno char(10)declare ssum int declare examssum int declare avggrade intselect cno from insertedselect cno from deletedsele

10、ct ssum =count(*) from sc where cno=cnoselect examssum= count(*) from sc where cno=cno and cgrade=0select avggrade= AVG(cgrade) from sc where cno=cno and cgrade=0update CAvgGradeset Ssum=ssum,examSsum=examssum,avgGrade=avggradewhere cno=cnoend验证触发器：初始的 CAvgGrade 表插入数据时：insert into sc values(95004,1,

11、65)Sc 表：CAvgGrade 表：删除数据时：delete from sc where sno=95001sc 表的变化：CAvgGrade 表变化：更新数据时：update scset cgrade=99 where sno=95004SC 表的变化：CAvgGrade 表变化：4. 创建一个 works 数据库，其中包含员工表 empoyee(eID, eName, salary)，假设该表中有 1000 条员工数据，完成下列要求（总计 20 分，每题 10 分）。(1) 为了协助本题自动生成 1000 条员工数据，创建一个自动生成员工 ID 的用户自定义函数 generateEID

12、。其中员工 ID 要求是一个 8 位的数字，前四位表示插入员工数据的当前年份，后四位按照从 0001 到 9999 的顺序增长。例如 2015 年插入的第一条数据是 20050001，所有 1000 条员工 ID 分别是 20150001-20151000。调用该函数实现自动插入 1000 条数据。（注意插入数据的时候员工姓名可以为任意值，工资是 2000-5000 之间的数字）创建了一个存储过程来完成：use workgocreate procedure generateEIDasbegindeclare i int set i=0while (i1000)begin insert into

13、 dbo.employee values(20160001+i,name+CAST(i as nchar(20),2000+CAST(FLOOR(rand()*3001)as int)set i=i+1return endend(2) 该公司计划为员工按照一定的规则涨工资，请使用游标创建一个存储过程，执行该存储过程完成本次工资调整：工资增长规则如下： 工资在 3000 元以下，每月涨 300 元； 工资在 3000-4000 元之间，每月涨 200 元； 工资大于或者等于 4000 元，每月涨 50 元；use workgodeclare mycursor CURSOR forselect s

14、alary from employeeopen mycursordeclare salary int fetch next from mycursor into salarywhile FETCH_STATUS=0begin if(salary3000)beginupdate employee set salary=salary+300 where current of mycursorendelse if(salary4000)beginupdate employee set salary=salary+200 where current of mycursorendelse beginupdate employee set salary=salary+50 where current of mycursor endfetch next from mycursor into salaryendclose mycursordeallocate mycursor未改变时：改变后：