按学历查询
--学历查询
create procedure search_degree @name varchar(4) as begin
declare search_degree cursor for select worker_no from degree_info
where worker_no in(select Worker_no from degree_info where Degree=@name) open search_degree
declare @no1 varchar(15)
fetch next from search_degree into @no1 while @@FETCH_STATUS = 0 begin select
worker_info.Worker_name,worker_info.Department_name,home_info.Marriage_info,degree_info.Major,degree_info.degree, worker_info.Work_date
from worker_info,degree_info,pro_info,home_info where worker_info.Worker_no=@no1 and home_info.Worker_no=@no1 and pro_info.Worker_no=@no1 and degree_info.Worker_no=@no1 fetch next from search_degree into @no1 end
close search_degree
deallocate search_degree end
4.2．7
按部门查询
create procedure search_department @name varchar(15) as begin
declare search_department cursor for select worker_no from worker_info where Department_name=@name open search_department declare @no1 varchar(15)
fetch next from search_department into @no1 while @@FETCH_STATUS = 0 begin select
worker_info.Worker_name,worker_info.Department_name,home_info.Marriage_info,degree_info.Major,worker_info.Work_date
from worker_info,degree_info,pro_info,home_info where
13
worker_info.Worker_no=@no1 and home_info.Worker_no=@no1 and pro_info.Worker_no=@no1 and degree_info.Worker_no=@no1 fetch next from search_department into @no1 end
close search_department
deallocate search_department end
4.2．9
按婚姻状况查询
create procedure search_marriage @name varchar(15) as begin
declare search_marriage cursor for select worker_no from home_info
where worker_no in(select Worker_no from home_info where marriage_info=@name) open search_marriage declare @no varchar(15)
fetch next from search_marriage into @no while @@FETCH_STATUS = 0 begin
select * from worker_info,degree_info,pro_info,home_info where worker_info.Worker_no=@no and home_info.Worker_no=@no and pro_info.Worker_no=@no and degree_info.Worker_no=@no fetch next from search_marriage into @no end
close search_marriage
deallocate search_marriage end
4.3 创建触发器 4.3．1
定义一个触发器，当在数据库中插入一条员工信息后，触发相应的部门人数加1 create trigger insert_worker on worker_info after insert as begin
update department_info set
department_info.Real_number = department_info.Real_number + 1 from
department_info as department_info,inserted as worker_info
14
where department_info.Department_name=worker_info.Department_name end;
4.3．2
定义一个触发器，当在数据库中删除一条员工信息后，触发相应的部门人数减1 create trigger delete_worker on worker_info after delete as begin
update department_info set
department_info.Real_number = department_info.Real_number - 1 from
department_info as department_info,deleted as worker_info
where department_info.Department_name=worker_info.Department_name end;
delete from worker_info where worker_no='j1';
4.3．3
定义一个触发器，当在数据库中员工所属的部门更改后，触发转出的部门人数减1，转入的部门人数加1
create trigger update_worker on worker_info after update
as IF(UPDATE(Department_name)) begin
--转出的部门人数减 update department_info set
department_info.Real_number = department_info.Real_number - 1 from
department_info as department_info,deleted as worker_info
where department_info.Department_name=worker_info.Department_name --转入的部门人数加 update department_info set
department_info.Real_number = department_info.Real_number + 1 from
department_info as department_info,inserted as worker_info
where department_info.Department_name=worker_info.Department_name end;
4.3．4
15
定义一个触发器，当在数据库中删除一个员工的基本信息时连带删除该员工的职称,学历,家庭信息
create TRIGGER del_worker ON worker_info for DELETE AS BEGIN
delete home_info From
home_info as home_info , deleted as worker_info Where home_info.worker_no= worker_info.worker_no END BEGIN
delete degree_info
From degree_info as degree_info , deleted as worker_info Where degree_info.worker_no=worker_info.worker_no END BEGIN
delete pro_info
From pro_info as pro_info , deleted as worker_info Where pro_info.worker_no=worker_info.worker_no END
4.3．5
创建一个触发器，检查部门员工是否已满，满人则不进行操作
create Trigger check_department on department_info for update as
IF(UPDATE(Real_number)) begin declare
@size smallint, @MaxSize smallint,
@Department_name varchar(15)
select @department_name =department_name from inserted
select @MaxSize =Need_number ,@size = Real_number from department_info where department_name = @department_name begin
if( @size > @MaxSize ) begin
print '该部门员工已满' rollback Transaction
16
return ; end end end
4.3．6
创建一个触发器，插入或更新时检查部门是否存在，不存在则不进行操作 create trigger check_dapartment_name on worker_info for update,insert as begin declare
@department_name varchar(15)
select @department_name = department_name from inserted
if( not exists(select * from department_info where department_name = @department_name ) ) begin
print '该部门不存在' rollback Transaction return; end end
4.4 创建视图 4.4．1
建立一个查看所有员工的所有信息的视图
create view all_worker(员工编号,姓名,性别,出生日期,年龄,身份证号,部门编号,部门,经理编号,职位,电话,工作时间,员工类型,学历号,学位,
专业,毕业学校,毕业时间,职称号,评定时间,评定单位,评定职称,备注,家庭编号,婚姻状况,家庭人口,家庭电话,家庭地址) as
select
worker_info.Worker_no,worker_info.Worker_name,worker_info.Sex,worker_info.Birthday,worker_info.Age,worker_info.ID,
department_info.Department_no,worker_info.Department_name,department_info.Manager_no,worker_info.Post,worker_info.Phone,worker_info.Work_date,worker_info.Worker_type,degree_info.Degree_no,
degree_info.Degree,degree_info.Major,degree_info.School,degree_info.Gradute_date,pro_info.Pro_no,pro_info.Assess_date,pro_info.Assess_unit,
pro_info.Assess_title,pro_info.Remark,home_info.Home_no,home_info.Marriage_
17