Блог

У меня есть база данных Office employees с информацией об этих сотрудниках, их JobName, их образовании (курсах) и курсах, необходимых для правильного выполнения их РАБОТЫ. Просто.

Я написал 2 хранимые процедуры SQL Server, которые работают хорошо.

• Хранимые процедуры #1 принимают a Jobname и возвращают имена пользователей с этим jobname
• Хранимые процедуры # 2 принимают Name и возвращают имена курсов (необходимых), которые не были завершены сотрудником

Что я ищу: запуск хранимой процедуры # 1 (с использованием @Jobname ), чтобы сначала получить имя сотрудника, передать это хранимой процедуре # 2 (as @Name ), а затем возвращает результаты — затем возьмите следующее имя из результата процедуры # 1 и так далее.

Это мои процедуры:

Процедура #1:

 ALTER PROCEDURE [dbo].[Functie] 
    @JobName AS NVARCHAR(50)
AS
BEGIN
    SELECT JobName, Name
    FROM Employers
    WHERE JobName = @Functie
END
  

Процедура #2:

 ALTER PROCEDURE [dbo].[ExceptTest] 
    (@Name AS NVARCHAR(50))
BEGIN
    SELECT coursename 
    FROM CourseNeeded
    WHERE CourseNeeded.coursename = @Name

    EXCEPT

    SELECT coursename 
    FROM CourseDone
    WHERE CourseDone.coursename = @Name
END
  

Я попробовал это:

 ALTER PROCEDURE [dbo].[Functie] 
    @JobName AS NVARCHAR(50)
BEGIN
    SELECT JobName, Name
    FROM Employers
    WHERE JobName = @Functie

    WHILE @JobName <> 0
    BEGIN
        EXEC Procedure2 @Name = Name
END
  

 ALTER PROCEDURE [dbo].[Functie] 
@JobName AS NVARCHAR(50)
AS
BEGIN
declare @Name AS NVARCHAR(50) 
SELECT @Name = Name
FROM Employers
WHERE JobName = @JobName

SELECT coursename 
FROM CourseNeeded
WHERE CourseNeeded.coursename = @Name

EXCEPT

SELECT coursename 
FROM CourseDone
WHERE CourseDone.coursename = @Name
END
  

На самом деле это должно выполняться как «набор», а не как цикл. Представьте, что если бы у вас здесь был миллион человек — ему нужно было бы выполнить много-много циклов. Вместо этого сила баз данных заключается в их способности выполнять все операции со всеми строками одновременно.

Таким образом, я сосредоточился на подходе, основанном на множествах, в качестве ответа и на том, как перейти от «циклического» подхода к подходу, основанному на множествах. Если вы действительно хотите выполнить цикл WHILE, выполните поиск Stack Overflow для «WHILE LOOP SQL» или аналогичный.

С точки зрения мышления и планирования, нет ничего плохого в том, чтобы начинать с циклов (действительно, для многих людей более естественно думать о циклическом процессе). Однако, прежде чем кодировать ее в SQL, попробуйте преобразовать «цикл» в ответ «на основе набора». Один из способов — заменить подход «для каждого человека, сделайте это» на «для всех, сделайте это». Затем вам нужно просто решить, как туда добраться.

Вы на полпути к своему ответу — еще несколько шагов, и это будет сделано.

Ниже приведен пример того, как вы могли бы подойти к этому.

Кроме того, поскольку вы не предоставили нам структуры данных и т. Д. — Только некоторые имена — я создал свои собственные структуры данных для ясности. Кроме того, для большей части ответа я только что сообщил Employee_ID, Job_ID и т. Д., А не имена — вы можете легко получить их, сославшись на таблицы.

Я разделил операторы для примера, но (как показано в конце) вы сможете свернуть / объединить многие из них в один оператор, который даст вам ваш ответ.

PS Я предположил, что вы имели в виду «сотрудника», а не «работодателя» (т. Е. Вы говорите о человеке, а не о компании).

Настройка данных

 -- Tables holding base data about employees, jobs and courses. 
-- Note that an employee can only have one job in this model.

CREATE TABLE #Employees (Employee_ID int PRIMARY KEY, Employee_Name nvarchar(100), Job_ID int)

CREATE TABLE #Jobs (Job_ID int PRIMARY KEY, Job_Name nvarchar(100))

CREATE TABLE #Courses (Course_ID int PRIMARY KEY, Course_Name nvarchar(100))


-- Tables holding links between these 
-- - the courses already completed by employees, and 
-- - the courses required for each job

CREATE TABLE #Employee_Course (Employee_ID int, Course_ID int, PRIMARY KEY(Employee_ID, Course_ID))

CREATE TABLE #Job_Course (Job_ID int, Course_ID int, PRIMARY KEY(Job_ID, Course_ID))

-- Note I haven't set up data or other things like Foreign Keys etc - to keep this as focussed as possible
  

Теперь для обработки.

Ваш подход заключался в том, чтобы сначала выяснить (для данной работы), кем были соответствующие сотрудники. Оттуда вы проверили каждого сотрудника, чтобы узнать, прошли ли они все необходимые курсы.

Мы немного изменим это — вместо проверки каждого сотрудника мы будем проверять всех сотрудников сразу.

 -- Step 1 - Identify employees in job

    CREATE TABLE #Emps_in_job (Employee_ID int)
    
    INSERT INTO #Emps_in_job (Employee_ID)
        SELECT  E.Employee_ID
        FROM    #Employees E
        WHERE   E.Job_ID = @Job_ID
    
    SELECT * FROM #Emps_in_job ORDER BY Employee_ID
  

 -- Step 2 - For all employees in that list, find the courses they need (based on their job)
-- Note this hasn't yet checked whether they have completed that course or not.

    CREATE TABLE #Emp_Course_Requirements (Employee_ID int, Course_ID int)

    INSERT INTO #Emp_Course_Requirements (Employee_ID, Course_ID)
        SELECT  E.Employee_ID, JC.Course_ID
        FROM    #Employees E
                INNER JOIN #Job_Course JC ON E.Job_ID = JC.Job_ID

    SELECT * FROM #Emp_Course_Requirements
  

 -- Step 3 - Find out which courses are missing and report on them
-- There are three options here - all should work (select whichever you prefer)

    -- Option 1 (using EXCEPT clause - easy to read but not frequently used)
    SELECT      ECR.Employee_ID, ECR.Course_ID
        FROM    #Emp_Course_Requirements ECR
      EXCEPT
    SELECT      EC.Employee_ID, EC.Course_ID
        FROM    #Employee_Course EC

    -- Option 2 (using LEFT OUTER JOIN, and a NULL result in that joined table indicating they don't have it)
    SELECT      ECR.Employee_ID, ECR.Course_ID
        FROM    #Emp_Course_Requirements ECR
                LEFT OUTER JOIN #Employee_Course EC
                        ON ECR.Course_ID = EC.Course_ID
                        AND ECR.Employee_ID = EC.Employee_ID
        WHERE   EC.Employee_ID IS NULL

    -- Option 3 (Delete completed employee/courses from the list. Any remaining must still be required)
    DELETE FROM ECR
        FROM    #Emp_Course_Requirements ECR
                INNER JOIN #Employee_Course EC
                        ON ECR.Course_ID = EC.Course_ID
                        AND ECR.Employee_ID = EC.Employee_ID
    SELECT      * 
        FROM    #Emp_Course_Requirements ECR
  

Однако даже в приведенном выше случае требуется дополнительная обработка. В приведенном выше примере мы создаем временные таблицы, заполняем их и используем только на следующем шаге. Вместо этого просто поместите исходный запрос во второй запрос (и так далее), пропуская необходимость создания временной таблицы.

Конечный результат может выглядеть следующим образом

 CREATE PROCEDURE #JobCourseCheck 
        @Job_ID int
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT      E.Employee_Name, C.Course_Name
        FROM    #Employees E
                INNER JOIN #Job_Course JC ON E.Job_ID = JC.Job_ID
                INNER JOIN #Courses C ON JC.Course_ID = C.Course_ID
                LEFT OUTER JOIN #Employee_Course EC
                        ON E.Employee_ID = EC.Employee_ID
                        AND JC.Course_ID = EC.Course_ID
        WHERE   E.Job_ID = @Job_ID
                AND EC.Employee_ID IS NULL

END