SQL server数据库—存储过程
一、选择题
创建一个名为ManageDB的数据库
员工表EmpInfo
| 字段说明 | 字段 | 数据类型 | 备注 |
| 员工编号 | EmpID | int | 主键，标识列 |
| 员工姓名 | EmpName | Varchar(20) | 不能为空 |
| 员工职务 | EmpDuty | Varchar(20) | 不能为空 |
| 员工性别 | EmpSex | Char(2) | 只能是“男”或“女” |
| 员工年龄 | EmpAge | Int | 20-60之间 |
| 部门编号 | DeptID | Int | 外键 |
部门表DeptInfo
| 字段说明 | 字段 | 数据类型 | 备注 |
| 部门编号 | DeptID | int | 主键，标识列 |
| 部门名称 | DeptName | Varchar(20) | 不能为空 |
员工信息表
| 员工编号 | 员工姓名 | 员工职务 | 员工性别 | 员工年龄 | 部门编号 |
| 1001 | 张三 | 员工 | 男 | 26 | 1 |
| 1002 | 李四 | 员工 | 女 | 31 | 1 |
| 1003 | 马冬梅 | 部门经理 | 女 | 32 | 2 |
| 1004 | 魏无羡 | 部门经理 | 男 | 28 | 1 |
| 1005 | 韩商言 | 员工 | 男 | 30 | 2 |
部门信息表
| 部门编号 | 部门名称 |
| 1 | 技术部 |
| 2 | 人事部 |
五、操作题
一、创建索引
为了提高查询员工信息的速度，特此要求在员工信息表中的员工姓名这一列创建一个非聚集索引IX_EmpName,并加密，同时使用该索引查询数据
二、创建视图
创建一个视图，查询出“人事部”的部门经理的信息，显示出员工编号，姓名，职务以及部门名称
三、创建存储过程
1、创建存储过程，根据指定的员工姓名，查询出该员工的基本信息，显示出员工编号，姓名，职务和部门名称，并调用该存储过程
2、创建存储过程，根据指定的员工姓名和部门名称，查询出相应的员工的基本信息，显示出员工变化，姓名，职务和部门名称，并调用该存储过程
3、创建存储过程，向员工信息表中插入一条员工信息，默认情况下员工的职务为“员工”，性别为“男”，并调用该存储过程向表中插入一条员工信息
4、创建存储过程，根据指定的员工姓名查询出该员工所在的部门名称，并把该部门名称作为输出参数返回，然后输出“XXX所在的部门名称为：XXX”
5、创建存储过程，分别计算出男女员工的人数，并判断男员工多还是女员工多，将判断结果返回并输出。
答案：
use master ? --切换到master数据库
go
--判断ManageDB数据库是否已存在，如果存在，就删除
if exists(select * from sys.databases where name='managedb')
drop database managedb
go
--创建ManageDB数据库
create database ManageDB
on primary
(
name='managedb_dat',
filename='c:\gao\managedb_dat.mdf',
size=8,
maxsize=100,
filegrowth=1
)
log on
(
name='managedb_log',
filename='c:\gao\managedb_log.ldf',
size=3,
filegrowth=10%
)
go
use ManageDB ?--切换数据库
go
--创建部门表
if exists(select * from sys.objects where name='deptinfo')? ?--判断是否有这个表
drop table deptinfo ? ? ? ? ? --如果有就删除
go
create table deptinfo(
deptid int identity(1,1) primary key,
deptname varchar(20) not null
)
go
--创建员工表
if exists(select * from sys.objects where name='empinfo')??--判断是否有这个表
drop table empinfo ? ? ? --如果有就删除
go
create table empinfo(
empid int identity(1001,1) primary key, ? ? --员工编号
empname varchar(20) not null, ? ? ? --员工姓名
empduty varchar(20) not null, ? ? ? --员工职务
empsex char(2) check(empsex='男' or empsex='女'), ?--员工性别
empage int check(empage between 20 and 60 ), ? ?--员工年龄
deptid int references deptinfo(deptid) ? ? ? ? ?--部门编号
)
go
--向部门表插入数据
insert into deptinfo values('技术部'),('人事部')
go
--插入员工信息表
insert into empinfo(empname,empduty,empsex,empage,deptid)
select '张三','员工','男',26,1 union
select '李四','员工','女',31,1 union
select '马冬梅','部门经理','女',32,2 union
select '魏无羡','部门经理','男',28,1 union
select '韩商言','员工','男',30,2
go
select * from deptinfo ? --查询部门表
select * from empinfo ? ?--查询员工表
go
--创建索引
create ?nonclustered
index ix_empname
on empinfo(empname desc)
with fillfactor=10
go
select * from empinfo ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? --查询索引-升序
select * from empinfo with(index=ix_empname) --查询索引-降序
go
--创建一个视图
create view view_empinfo
as
select empid 员工编号,empname 员工姓名,empduty 员工职务,deptname 部门名称 from empinfo e
join deptinfo d on e.deptid=d.deptid where deptname='人事部'
go
select * from view_empinfo ? --查看视图
go
--存储过程一
create proc proc_empinfo
@prempname varchar(20)
as
select empid 员工编号,empname 员工姓名,empduty 员工职务,deptname 部门名称 from empinfo e
join deptinfo d on e.deptid=d.deptid where empname=@prempname
go
exec proc_empinfo '张三' ? ? --调用存储过程
go
--存储过程二
create proc proc_empinfo_fordept
@prempname varchar(20),
@prdeptname varchar(20)
as
select empid 员工编号,empname 员工姓名,empduty 员工职务,deptname 部门名称 from empinfo e
join deptinfo d on e.deptid=d.deptid where empname =@prempname and deptname=@prdeptname
go
exec proc_empinfo_fordept '李四','技术部' ?--调用存储过程
go
--存储过程三
create proc proc_insertempinfo
@prempname varchar(20),
@prempduty varchar(20)='员工',
@prempsex char(2)='男',
@prempage int,
@prdeptid int
as?
insert into empinfo(empname,empduty,empsex,empage,deptid) values(@prempname,@prempduty,@prempsex,@prempage,@prdeptid)
go
--插入数据
exec proc_insertempinfo '关羽',@prempage=30,@prdeptid=1
exec proc_insertempinfo '小乔','员工','女',24,1
exec proc_insertempinfo '大乔','员工','女',28,1
exec proc_insertempinfo '孙尚香','员工','女',30,2
select * from empinfo ? ?--查看
go
--存储过程四：输入部门信息
create proc proc_printdept
@prname varchar(20),
@prdept varchar(20) output
as
select @prdept=deptname from empinfo e?
join deptinfo d on e.deptid=d.deptid where empname=@prname
go
--声明变量 查看存储过程并显示
declare @prname varchar(20),@prdept varchar(20)
set @prname='李四'
exec proc_printdept @prname,@prdept output
print @prname+'所在部门是：'+@prdept
go
--存储过程五：计算男女员工比例
create proc proc_printsex
as
begin
?? ?declare @mannum int ? ?--定义一个存放男生数量的变量
?? ?declare @womannum int ?--定义一个存放女生数量的变量
?? ?declare @result varchar(50) --定义一个存放结果变量
?? ?--查询出男性员工的数量
?? ?select @mannum=count(*) from empinfo where empsex='男'
?? ?--查询出女性员工的数量
?? ?select @womannum=count(*) from empinfo where empsex='女'
?? ?--比较男性员工数量与女性员工数量
?? ?if (@mannum>@womannum)
?? ? ? set @result='男性员工数量大于女员工数量'
? ? else if(@mannum=@womannum)
?? ? ? set @result='男性员工数量等于女员工数量'
?? ?else
?? ? ? set @result='男性员工数量小于女员工数量'
? ?--输出结果
? ?select ?@result
end
go
exec proc_printsex ? --显示存储过程结果
select * from empinfo ?--查询
go