SQL server 数据库练习题及答案（练习5）-存储过程

SQL server数据库—存储过程

一、选择题

1. 系统存储过程以（ C）为前缀

1. @@           B. @             C. sp_               D. up_

1. 系统存储过程主要存储在（ B ）数据库中

1. Tempdb        B. Master         C. Model            D. msdb

1. 定义存储过程中的输出参数时，要在参数后使用（ B ）关键字

1. EFAULT         B. OUTPUT       C. INPUT             D. WITH

1. 系统存储过程sp_helptext的作用是：（  C）

1. 查看帮助
2. 查看权限
3. 查看创建对象的定义

• 二、创建数据库

创建一个名为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