数据库-三级考试-题目及操作过程3.1.1

sql考试共3道操作题，对应下面的三个链接：
数据库-三级考试-题目及操作过程2.1.1
数据库-三级考试-题目及操作过程2.2.1
数据库-三级考试-题目及操作过程3.1.1

数据库-三级考试-题目及操作过程3.1.1

1.题目

3.1.1
1、操作条件
（1）SQL Server 2005中文版
（2）素材（已建数据库、已建数据表）
2、操作内容
针对给定素材完成以下操作，并将所有SQL脚本语句保存至指定Word文档。
（1）编写过程完成指定功能（写入记录）；
（2）编写函数完成指定功能（三个函数返回类型分别为“判断、表、值”）；
（3）编写过程完成指定功能（读取记录），并采用游标方式按指定格式内容输出。
3、操作要求
（1）在“C:\GATDoc\SQL3J3.1.1”内建立WORD文档
（2）将上述各查询操作对应的SQL脚本语句依次保存至该文档

 
素 材
素材内容:
已建数据库“DEMO_3_3_1”，数据库中建有表“Employee”，表结构如下：
Employee ID 雇员号 Int NOT NULL
NAME 姓名 VARCHAR(25) NOT NULL
HIREDATE 工作日期 datetime NOT NULL
JOB 岗位 VARCHAR(10) NOT NULL
SAL 工资 Numeric(8,2)
MGR 管理者编号 Int
DEPTNO 部门号 Int

具体要求：
1、完成以下所有操作，并将对应的SQL脚本依次保存至“3.1.1.doc”文件。
针对表Employee，完成以下操作内容：
（1）建立存储过程add_emp，输入雇员号、姓名、岗位、工作日期、工资、管理者编号、部门号，为Employee表插入数据。
（2）建立函数valid_id，根据输入的雇员号，检查雇员是否存在。如果雇员存在，则返回1；否则返回0。
（3）建立函数get_sal，根据输入的雇员号返回雇员名和工资。调用函数valid_id确定雇员是否存在，不存在则显示消息“该雇员不存在”。
（4）建立函数get_table,根据输入的部门号返回所有员工信息
（5）编写存储过程disp_emp，根据输入的部门号，采用游标方式按下列格式输出所有该部门的雇员名、岗位和工资。
雇员名 岗位 工资
————————————————————————
—————————————————————（此处为数据）
2、操作要求
（1）在“C:\GATDoc\SQL3J3.1.1”文件夹内建立WORD文档“3.1.1.doc”
（2）将上述各查询操作对应的SQL脚本语句依次保存至文档“3.1.1.doc”。

将本题产生的所有文件保存在“C:\GATDoc\SQL3J3.1.1”文件夹下。

2.操作过程

将考试提供的素材数据库导入sql server
右键数据库->附加

（1）建立存储过程add_emp，输入雇员号、姓名、岗位、工作日期、工资、管理者编号、部门号，为Employee表插入数据。

create proc add_emp
@id1 int, @name1 varchar(25), @hiredate1 datetime, @job1 varchar(10),
@sal1 numeric(8,2), @mgr1 int,  @deptno1 int
as
begin
insert into employee values(@id1, @name1, @hiredate1, @job1,
@sal1, @mgr1,  @deptno1)
end

exec add_emp 11, zmd, '2000-01-01', xx, 6000, 4, 4

（2）建立函数valid_id，根据输入的雇员号，检查雇员是否存在。如果雇员存在，则返回1；否则返回0。

create function valid_id(@no int)
returns bit
as 
begin
declare @ncount int 
declare @rcount int 
select @ncount = count (*) from employee where id =  @no 
if @ncount > 0
    set @rcount = 1
else 
    set @rcount = 0
return @rcount
end

（3）建立函数get_sal，根据输入的雇员号返回雇员名和工资。调用函数valid_id确定雇员是否存在，不存在则显示消息“该雇员不存在”。

create function get_sal(@no int)
returns @eptable table(name varchar (25),
sal numeric(8,2), msq varchar(50))
as begin
if dbo.valid_id(@no) = 1
insert into @eptable select name, sal, '该雇员存在'
from employee where id = @no
else 
insert into @eptable values('',0.0,'该雇员不存在')
return 
end

（4）建立函数get_table,根据输入的部门号返回所有员工信息

create function get_table(@dpno int)
returns table
as
return select * from employee where deptno=@dpno

（5）编写存储过程disp_emp，根据输入的部门号，采用游标方式按下列格式输出所有该部门的雇员名、岗位和工资。
雇员名 岗位 工资
————————————————————————
—————————————————————（此处为数据）

create procedure disp_emp
@dpno int 
as
begin 
declare @name1 varchar(25)
declare @job1 varchar(10)
declare @salary1 numeric(8,2)
declare youbiao cursor for
select name,job,sal from employee where deptno=@dpno
open youbiao
fetch next from youbiao into @name1,@job1,@salary1 
print ' 雇员名        岗位          工资'
print '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  - - - - - - - - -'
while @@fetch_status=0
begin
print @name1+'          '+@job1+'         '+cast(@salary1 as varchar)
fetch next from youbiao into @name1, @job1,@salary1
end 
close youbiao
end

cursor的意思是游标。
fetch在数据库中的意思是从游标中提取一条记录。