代码之家 › 专栏 › 技术社区 › Alex P

关于软件/数据库设计的建议,以避免在更新数据库时使用游标

cursor sql-server

Alex P · 技术社区 · 16 年前

我有一个数据库,记录员工何时参加了课程,何时下一次参加课程(课程往往是每年一次)。

EmployeeID    CourseID    AttendanceDate    DueDate     Status
123456        1           01/01/2010        01/01/2011  Complete

在 DueDate 当我更新员工的记录时,我用SQL计算这个值,例如DueDate=AttendanceDate+CourseFrequency(我从一个单独的表中提取课程频率)。

在上面的例子中,假设今天是2011年1月2日。在这种情况下,员工123456现在已经过期了,我想设置 Status 使人力资源经理看到他们需要采取行动,即让员工参加培训。

我可以在数据库中构建一个触发器,在夜间运行以更新 状态 在每一行上循环修改状态,这被认为是不好的做法/低效的,或者至少是要避免的事情,如果你可以的话???

或者,我可以计算 状态 状态 在数据库中不一定会匹配什么是显示在屏幕上,只是觉得明显错误的我。

有人对解决这类问题的最佳做法有什么建议吗?

如果我真的使用光标,我怀疑在任何给定的时间我都会在1000多条记录上循环。也许这是这么小的体积,使用光标是可以的?

3 回复 | 直到 16 年前

Mark Storey-Smith 16 年前

除非我从你的解释中遗漏了什么,否则根本不需要光标:

UPDATE
    dbo.YourTable
SET
    Status = âIncompleteâ
WHERE
    DueDate < GETDATE()

最好不要保留这些记录的截止日期或状态。我希望看到Employee、Course、EmployeeCourse和EmployeeCourseAttendance表,您可以使用这些表:

-- Employees that haven't attended a course 
-- within dbo.Course.Frequency of current date
SELECT
    ec.EmployeeID
    , ec.CourseID
    , eca.LastAttendanceDate
    , DATEADD(day, c.Frequency, eca.LastAttendanceDate) AS DueDate
FROM
    dbo.EmployeeCourse ec
INNER JOIN
    dbo.Course c
LEFT OUTER JOIN
    ebo.EmployeeCourseAttendance eca
ON  eca.EmployeeID = ec.EmployeeId
AND eca.CourseID = ec.CourseID
WHERE
    GETDATE() > DATEADD(day, c.Frequency, eca.LastAttendanceDate)

-- Show all employees and status for each course
SELECT
    ec.EmployeeID
    , ec.CourseID
    , eca.LastAttendanceDate
    , DATEADD(day, c.Frequency, eca.LastAttendanceDate) AS DueDate
    , CASE
        WHEN eca.LastAttendanceDate IS NULL THEN 'Has not attended'
        WHEN (GETDATE() > DATEADD(day, c.Frequency, eca.LastAttendanceDate) THEN 'Incomplete'
        WHEN (GETDATE() < DATEADD(day, c.Frequency, eca.LastAttendanceDate) THEN 'Complete'
      END AS Status
FROM
    dbo.EmployeeCourse ec
INNER JOIN
    dbo.Course c
LEFT OUTER JOIN
    ebo.EmployeeCourseAttendance eca
ON  eca.EmployeeID = ec.EmployeeId
AND eca.CourseID = ec.CourseID

Chris Bednarski 16 年前

也可以使用计算列表达式。这样,您就不必更新 STATUS 列/与日期保持同步

create table coureses
(
employeeid int not null,
courseid int not null,
attendancedate datetime null,
duedate datetime null,
[status] as case
    when duedate is null and attendancedate is null then 'n/a'
    when datediff(day,duedate, getdate()) > 0 then 'Incomplete'
    when datediff(day,attendancedate, getdate()) > 0 then 'Complete'
    else 'n/a'
    end
)

Paddy 16 年前

UPDATE TABLE SET Status = 'Incomplete' WHERE DueDate < GetDate()