求SQL问题答案

Question

在yggl数据库中创建存储函数
1.创建一个存储函数num_em，返回员工的总人数。并调用该存储函数
2.创建一个存储函数is_em，根据给定的员工号，判断该员工号是否在employees表中存在，若employees表中存在返回false，若employees表中不存在则删除该员工在salary表中的信息并返回true。并调用该存储函数查看员工号为00001的信息。
3.创建一个存储函数is_yfb，根据给定员工号，判断员工是否在研发部工作，若是则返回其学历，若不是返回字符串“no”。调用此存储函数查看员工号为‘000001’与员工号为‘302566’的信息。
4.创建一个存储函数abc，根据给定的员工号，判断该员工工作时间如果满4年，工资增加500，返回true，否则返回FALSE.
5.创建一个存储函数zj，根据给定的员工号，判断该员工工作时间是否满6年，若满6年，转到经理办公室工作
附加题：
6.创建一个存储函数zengjia1，判断员工工作时间如果满4年，工资增加500，返回true，否则返回FALSE.(提示：使用游标)
7.创建一个存储函数zj，判断员工工作时间是否满6年，若满6年，转到经理办公室工作(提示：使用游标) 能做几个是几个 高分

Answer 1

1,IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'num_em' AND type = 'P')
DROP PROCEDURE num_em
GO
CREATE PROCEDURE num_em
AS
-- 员工号 userid
SELECT count(userid) userid FROM employeesGO
2,IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'is_em' AND type = 'P')
DROP PROCEDURE is_em
GO
CREATE PROCEDURE is_em@userid varchar(20),@zt bit out
ASbegin
-- 员工号 userid
if EXISTS (SELECT userid FROM employees where userid=@userid) begin return false end else begin delete from salary where userid=@userid if @@error <>0 return true endselect * FROM employees where userid='00001'endGO 3,
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'is_yfb' AND type = 'P')
DROP PROCEDURE is_yfb
GO
CREATE PROCEDURE is_yfb@userid varchar(20),@xueli varchar(20) out
ASbegin declare @xuli1 varchar(20)
--部门 cdept 员工 userid
if EXISTS (SELECT @xuli1=xueli FROM employees where cdept='研发部' and userid=@userid) begin return @xuli1 end else begin return 'no' endselect * FROM employees where userid in ('000001','302566')endGO 4, IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'abc' AND type = 'P')
DROP PROCEDURE abc
GO
CREATE PROCEDURE abc@userid varchar(20),@zt bit out
ASbegin
--入职日期为djoindate 员工号位userid
if EXISTS (SELECT userid FROM employees where
(DateDiFF(Year,djoindate,GetDate()) - (Case when Datepart(Month,djoindate)>Datepart(Month,GetDate()) then 1 when Datepart(Month,djoindate)=Datepart(Month,GetDate()) and Datepart(Day,djoindate)>Datepart(Day,GetDate()) then 1 else 0 end ))>=4
and userid=@userid) begin
--工资 gz
update employees set gz=gz+500 where userid=@userid
return true end else begin return false end
endGO5,
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'zj' AND type = 'P')
DROP PROCEDURE zj
GO
CREATE PROCEDURE zj@userid varchar(20)
ASbegin
--入职日期为djoindate 员工号位userid
if EXISTS (SELECT userid FROM employees where
(DateDiFF(Year,djoindate,GetDate()) - (Case when Datepart(Month,djoindate)>Datepart(Month,GetDate()) then 1 when Datepart(Month,djoindate)=Datepart(Month,GetDate()) and Datepart(Day,djoindate)>Datepart(Day,GetDate()) then 1 else 0 end ))>=6
and userid=@userid) begin
--部门 cdept
update employees set cdept='经理办公室' where userid=@userid end
endGO
6,IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'zengjia1' AND type = 'P')
DROP PROCEDURE zengjia1
GO
CREATE PROCEDURE zengjia1@zt bit out
ASbegin
declare @userid varchar(20)
DECLARE userid_cursor CURSOR FOR
SELECT userid
FROM employees
where (DateDiFF(Year,djoindate,GetDate()) - (Case when Datepart(Month,djoindate)>Datepart(Month,GetDate()) then 1 when Datepart(Month,djoindate)=Datepart(Month,GetDate()) and Datepart(Day,djoindate)>Datepart(Day,GetDate()) then 1 else 0 end ))>=4
OPEN userid_cursor
FETCH NEXT FROM userid_cursor INTO @userid IF @@FETCH_STATUS <> 0
PRINT '无4年以上员工 ' WHILE @@FETCH_STATUS = 0
BEGIN
--工资 gz 员工号=userid
update employees set gz=gz+500 where userid=@userid
if @@error <>0
begin
return false
end FETCH NEXT FROM userid_cursor INTO @userid

END

CLOSE userid_cursor
DEALLOCATE userid_cursor return true
endGO 7,IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'zj' AND type = 'P')
DROP PROCEDURE zj
GO
CREATE PROCEDURE zj@zt bit out
ASbegin
declare @userid varchar(20)
DECLARE userid_cursor CURSOR FOR
SELECT userid
FROM employees
where (DateDiFF(Year,djoindate,GetDate()) - (Case when Datepart(Month,djoindate)>Datepart(Month,GetDate()) then 1 when Datepart(Month,djoindate)=Datepart(Month,GetDate()) and Datepart(Day,djoindate)>Datepart(Day,GetDate()) then 1 else 0 end ))>=6
OPEN userid_cursor
FETCH NEXT FROM userid_cursor INTO @userid IF @@FETCH_STATUS <> 0
PRINT '无6年以上员工 ' WHILE @@FETCH_STATUS = 0
BEGIN
--部门 cdept
update employees set cdept='经理办公室' where userid=@userid
if @@error <>0
begin
return false
end
FETCH NEXT FROM userid_cursor INTO @userid

END

CLOSE userid_cursor
DEALLOCATE userid_cursor
return trueendGO 以上存储过程没具体测试，

Answer 2

分在哪呢？我天天都在写存储过程