Обновление таблицы на основе запроса Select в хранимой процедуре/ColdFusion

Я использую ColdFusion для проекта, и у меня есть написанный запрос, который, я думаю, может быть быстрее с использованием хранимой процедуры, но я не человек T-SQL, поэтому я не уверен, как это сделать для сравнения.

Я запускаю начальный запрос, который выбирает ряд полей из таблицы на основе динамически созданного cfquery. Кажется, я знаю, как преобразовать этот запрос в хранимую процедуру SQL Server.

Однако сразу после этого я беру все идентификаторы первичных ключей из этого запроса и запускаю другой запрос к отдельной таблице, которая «блокирует» записи с этими идентификаторами. Блокировка — это битовое поле (флаг) во второй таблице, которое сообщает системе, что эта запись «извлечена». Я завернул оба запроса в cftransaction, чтобы они выполнялись как единое целое.

Обзор кода:

<cftransaction>
   <cfquery name="selectQuery">
      SELECT id, field2, field3
      FROM table1
      WHERE (bunch of conditions here)
   </cfquery>

   <cfquery name="updateQuery">
      UPDATE table2
      SET lockField = 1
      WHERE table2.id IN (#ValueList(selectQuery.id#)
   </cfquery>
</cftransaction>

Затем я возвращаю набор результатов selectQuery в свое приложение, которое использует его для вывода некоторых данных. Как мне выполнить то же самое в одной хранимой процедуре SQL Server 2008, которую я мог бы вызвать с помощью cfstoredproc?

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

Любые идеи?


sql-server stored-procedures tsql coldfusion
person Bryan Lewis    schedule 26.08.2009    source источник

Ответы (4)


arrow_upward
1
arrow_downward

Вы можете добавить предложение OUTPUT в оператор UPDATE, чтобы захватить идентификаторы обновленных записей и вставить их в табличную переменную/временную таблицу. Затем ПРИСОЕДИНЯЙТЕСЬ к таблице1, чтобы вернуть набор результатов.

DECLARE @UpdatedRecords TABLE ( ID INT )

UPDATE  t2
SET     t2.lockField = 1
OUTPUT  Inserted.ID INTO @UpdatedRecords ( ID )
FROM    table2 t2 INNER JOIN table1 t1  ON t2.id = t1.id
WHERE   (bunch of conditions for table1 here)

SELECT  t1.id, t1.field2, t1.field3
FROM    table1 t1 INNER JOIN @UpdatedRecords u ON t1.id = u.id

Имейте в виду, что если таблица1 находится в постоянном изменении, другие значения («поле2» и «поле3») не обязательно будут такими, какими они были, когда произошло ОБНОВЛЕНИЕ. Но я думаю, что ваш текущий метод также подвержен этой проблеме.

person Leigh    schedule 26.08.2009

arrow_upward
1
arrow_downward

Ваша проблема в том, что "здесь куча условий". Всегда ли эти условия статичны? Так ВСЕГДА: (FOO = @x AND BAR = @y)? Или это условно, где иногда FOO вообще не существует как условие?

Если FOO не всегда присутствует, у вас проблема с хранимой процедурой. T-SQL не может выполнять построение динамических запросов, фактически, даже если бы это было разрешено, это как бы свело на нет смысл процесса, который заключается в компиляции и предварительной оптимизации SQL. Вы МОЖЕТЕ сделать это, конечно, но в конечном итоге вам просто нужно создать строку SQL внутри тела процедуры, а затем выполнить ее в конце. Вам гораздо лучше использовать CFQuery с cfqueryparams. На самом деле вы рассматривали возможность сделать это вместо этого?

   <cfquery name="updateQuery">
      UPDATE table2
      SET lockField = 1
      WHERE table2.id IN (SELECT id
                          FROM table1
                          WHERE (bunch of conditions here))
   </cfquery>
person ryber    schedule 26.08.2009
comment
Проблема в том, что мне нужно вернуть набор результатов оператора select из таблицы 1, а не просто обновить таблицу 2. - person Bryan Lewis; 26.08.2009

arrow_upward
0
arrow_downward

Вы можете выполнить обновление в одном запросе, сделав свой первый запрос подзапросом, а затем используя отдельный оператор для возврата результатов. Все это может быть одной хранимой процедурой:

  CREATE PROCEDURE myUpdate
      @Variable [datatype], etc...
  AS
  BEGIN
    UPDATE table2
    SET lockField = 1
    WHERE table2.id IN (
        SELECT id
        FROM table1
        WHERE (bunch of conditions here)
    )
    SELECT id, field2, field3
    FROM table1
    WHERE (bunch of conditions here)
  END

Возможно, вам придется передать некоторые параметры, но это основная структура хранимой процедуры. Затем вы можете вызвать его из ColdFusion следующим образом:

<cfstoredproc procedure="myUpdate">
    <cfprocparam type="[CF SQL Type]" value="[CF Variable]">
    etc...
    <cfprocresult name="selectQuery" resultSet="1">
</cfstoredproc>

Вы можете использовать эти результаты запроса точно так же, как вы использовали их раньше.

person pb.    schedule 26.08.2009
comment
Хорошо, мне нужна проверка вменяемости для этого ответа. Данные table1 находятся в постоянном потоке... по сути, это очередь. Поскольку это находится внутри одного хранимого процесса, он по сути работает как одна транзакция, верно? Это означает, что, поскольку я запускаю SELECT для таблицы 1 дважды, мне нужно гарантировать, что оба раза я получаю одинаковые результаты. Поскольку многие пользователи будут регулярно использовать эту функцию, мне нужно убедиться, что блокировка работает правильно. - person Bryan Lewis; 26.08.2009
comment
Нет, это не работает как отдельная транзакция. Если вам нужны данные для вывода перед запуском обновления, вам нужно будет сделать это другим способом. Я не уловил всей блокировки в вашем вопросе. - person pb.; 26.08.2009

arrow_upward
0
arrow_downward

Нет необходимости в SPROC.

UPDATE table2
SET table2.lockField = 1
FROM table1 
WHERE table1.id = table2.id
  AND table1.field2 = <cfqueryparam ....> 
  AND table1.field3 = <cfqueryparam ....>
person Shawn    schedule 16.03.2015
comment
Упс. Только что понял, что это какой-то крупный вопрос некромантии. :-/ - person Shawn; 17.03.2015