Блог

Главная - Вопросы по программированию - Хранимая процедура : Выберите данные из таблицы «A» и вставьте данные в таблицу «B».

Хранимая процедура : Выберите данные из таблицы «A» и вставьте данные в таблицу «B».

#sql #sql-server #stored-procedures

Вопрос:

Я уже очень давно не касался хранимых процедур и функций. Поэтому я решил создать для себя мини-обучающую базу данных.

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

Вот мой сценарий создания 2 таблиц :

 create table Test_trigger (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Couleur VARCHAR(50),
    Horodate DATETIME,
    Nombre DECIMAL(6,2),
    Seuil_fixe INT
);

create table Test_alm_trigger (
    id_alm INT IDENTITY(1,1) PRIMARY KEY,
    Label VARCHAR(100),
    Horodate DATETIME,
    Seuil DECIMAL(6,2)
);

Если быть более точным, цель состоит в том, чтобы :

когда цвет ([Couleur]), например «Синий», имеет сумму столбца [Nombre] выше введенного порога ([Seuil_fixe]), процедура выполняется и вставляет строку в таблицу Test_alm_triggerс [Меткой], датой добавления ( SYSDATETIME() ) и суммой столбца [Nombre].

Я создал эту процедуру, но я не уверен, как она работает и хороша ли она или нет

 CREATE PROCEDURE ajoutL_triggerAlm
(
    @Couleur nvarchar(50),
    @Label nvarchar(200) = 'Dépassement de seuil',
    @Seuil float(4),
    @Seuil_fixe float(4),
    @Msg nvarchar(200)

)
AS
BEGIN
IF EXISTS (
    SELECT [Couleur] 
        FROM Test_trigger
        GROUP BY [Couleur], [Nombre], [Seuil_fixe]
        HAVING [Couleur] = @Couleur AND
                SUM([Nombre]) = @Seuil AND
                [Seuil_fixe] = @Seuil_fixe AND
                @Seuil > @Seuil_fixe
)
    BEGIN
        SET @Msg = 'Debug'
    END

ELSE
    BEGIN
        INSERT INTO Test_alm_trigger
        VALUES (@Label, SYSDATETIME(), @Seuil)
    END
END

Если у вас есть какие-либо ответы, советы… Я возьму их.

Заранее спасибо

Комментарии:

1. Как вы планируете вызывать свою процедуру — это то, что вы будете делать из задания агента, или вы должны внедрять after update trigger вместо процедуры?

2. Не ясно, как используется столбец по Seuil_fixe сравнению с параметром @Seuil_fixe ? Вы сохраняете порог и проходите через порог?

3. @Stu Процедура должна запускаться сама, на данный момент я протестировал ее только с помощью введенных вручную данных и «exec». Далек в моем столе, я действительно храню фиксированный порог. Например, для цвета «Красный» порог составляет 2450. И если сумма каждого значения в столбце «Nombre» превышает 2450 для красного цвета, то я хотел бы вставить строку в таблицу, чтобы сказать, что она преодолела этот порог. Поразмыслив, не нужно ли поэтому вызывать параметр, когда у меня есть столбец?

4. @Brck итак, сколько строк на цвет в Test_trigger может быть? Это кажется более чем одним, потому что вы предложили сумму, но, учитывая, что вы также храните пороговое значение, Seuil_fixe, там, вы можете получить другое значение для строки одного и того же цвета? Что действительно прояснило бы этот вопрос, так это примерные данные и ожидаемые результаты, которые иллюстрируют все варианты использования, включая крайние случаи.

5. @DaleK Когда я тренируюсь с этим упражнением, я записываю данные вручную с помощью вставки такого типа : ` вставьте в значения Test_trigger (Couleur, Horodate, Nombre, Seuil_fixe) («Оранжевый’, ‘2020-05-01 11:36:01’, 723.93, 3000); вставьте в Test_trigger (Couleur, Horodate, Nombre, Seuil_fixe) значения (‘Синий’, ‘2021-03-09 08:36:33’, 550.74, 2500); … ` Я использую около 100 строк на одном сайте ( mockaroo.com ) и у меня на самом деле нет ограничения по строкам. Я указал фиксированный порог в базе, но я думаю, что для упражнения интереснее объявить переменную в процедуре

Ответ №1:

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

Я добавил другие лучшие практики.

 CREATE PROCEDURE ajoutL_triggerAlm
(
    @Couleur nvarchar(50)
    , @Label nvarchar(200) = 'Dépassement de seuil'
    -- Almost never use float, its not a precise numeric amount and should only be used when specifically required
    , @Seuil decimal(8,4)
    -- Almost never use float, its not a precise numeric amount and should only be used when specifically required
    , @Seuil_fixe decimal(8,4)
    , @Msg nvarchar(200)
)
AS
BEGIN
    -- Best practice (I'll leave the meaning as an exercise)
    -- SQL Server recommendation is to ";" terminate all statements
    SET NOCOUNT, XACT_ABORT ON;

    -- Best practice, always list the columns you are inserting into
    -- Use set-based operations instead of procedural where you can
    INSERT INTO dbo.Test_alm_trigger (Label, Horodate, Seuil)
        SELECT @Label, SYSDATETIME(), @Seuil
        -- Question states when SUM([Nombre] above the threshold @Seuil_fixe - so I think this is the logic
        -- Although its not clear where the column Seuil_fixe comes into it
        WHERE (
            SELECT SUM([Nombre])
            FROM Test_trigger
            WHERE [Couleur] = @Couleur
        ) > @Seuil_fixe;

    -- Detect failure using @@ROWCOUNT
    SET @Msg = CASE WHEN @@ROWCOUNT = 0 THEN 'Debug' ELSE NULL END;

    -- Return a status, can use other values to indicate errors
    RETURN 0;
END;
Метки: