Использование оператора с побочным действием

als-a
Member

Откуда:
Сообщений: 7

Не пойму что не так (ногами не бейте вчера MsSql Server 2005 Rus поставил)
в Microsoft SQL Server Management Studio (9.00.2047.00) пытаюсь создать фунцию
вот текст
— ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
CREATE FUNCTION GET_ID
(
— Add the parameters for the function here
)
RETURNS int
AS
BEGIN
DECLARE @a int, @ID_COUNT_0 int
Select @ID_COUNT_0=ID_COUNT_0 from dbo.Counters
Set @a=@ID_COUNT_0
Set @ID_COUNT_0=@ID_COUNT_0+1

Update dbo.Counters set ID_COUNT_0 = @ID_COUNT_0
RETURN (@a)

END
GO

Нажимаю кнопочку «Показать предполагаемый план выполнения» и получаю следующее сообщение:

Сообщение 443, уровень 16, состояние 15, процедура GET_ID, строка 17
Недопустимое использование оператора с побочным действием или зависимого от времени в «UPDATE» в функции.

Таблица dbo.Counters состоит из одного столбца int в ней одна запись где ID_COUNT_0=1
Что не так ?

daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

в функциях запрещены dml операторы над постоянными таблицами.

Posted via ActualForum NNTP Server 1.4

Glory
Member

Откуда:
Сообщений: 104760

als-a

Таблица dbo.Counters состоит из одного столбца int в ней одна запись где ID_COUNT_0=1
Что не так ?

А что непонятного в сообщении
Недопустимое использование оператора с побочным действием или зависимого от времени в «UPDATE»

als-a
Member

Откуда:
Сообщений: 7

а процедуры ?

Glory
Member

Откуда:
Сообщений: 104760

Двести

daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

>а процедуры ?

в процедурах — можно. но процедуры нельзя напрямую в запросах использовать.

Posted via ActualForum NNTP Server 1.4

daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

>а процедуры ?

или вы в смысле — вызывать процедуры в функциях?
вызов процедур там тоже запрещен. расширенные (extended) только можно.

Posted via ActualForum NNTP Server 1.4

als-a
Member

Откуда:
Сообщений: 7

То есть запихнуть Update в процедуру и вызвать процедуру из функции тоже не прокатит ?

проходящий.

Guest

als-a

Не пойму что не так (ногами не бейте вчера MsSql Server 2005 Rus поставил)
Set @ID_COUNT_0=@ID_COUNT_0+1

Update dbo.Counters set ID_COUNT_0 = @ID_COUNT_0

Ничего не знаю, ничего не понимаю, но первое что сделаю — свой генератор последовательностей.
Без него ж никак! ????

Glory
Member

Откуда:
Сообщений: 104760

als-a

То есть запихнуть Update в процедуру и вызвать процедуру из функции тоже не прокатит ?

Можно один раз открыть в хелпе перечень ограничений udf-ов и узнать о них всех сразу

pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927

проходящий.

ну и делайте на основе процедуры.

als-a
Member

Откуда:
Сообщений: 7

Ну по поводу Ничего не знаю, ни чего не понимаю: Я более 10 лет работал с Oracle.
а по поводу но первое что сделаю — свой генератор последовательностей.
Без него ж никак! : на чем-то надо ж эксперементы проводить…

Кстати в Oracle в фунции можно писать что угодно, чего-то парни из Microsoft не додумали …

Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36903

als-a

Кстати в Oracle в фунции можно писать что угодно, чего-то парни из Microsoft не додумали …

У вас два варианта: или отвыкать от того, к чему вы привыкли на оракле, или писать дальше на оракле.

pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927

автор

а по поводу но первое что сделаю — свой генератор последовательностей.

М.б. свойством IDENTITY(1, 1) обойдетесь?!

als-a
Member

Откуда:
Сообщений: 7

да обойтись можно то можно а как же эксперименты ?

Glory
Member

Откуда:
Сообщений: 104760

als-a

да обойтись можно то можно а как же эксперименты ?

После 10лет работы с Oracle это скорее будут не эксперименты, а попытки эмулировать Oracle на MSSQL

pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927

als-a

да обойтись можно то можно а как же эксперименты ?

О каких экспериментах идет речь?! Попытка реализовать на MS SQL Oracle?

als-a
Member

Откуда:
Сообщений: 7

Гавриленко Сергей Алексеевич

als-a

Кстати в Oracle в фунции можно писать что угодно, чего-то парни из Microsoft не додумали …

У вас два варианта: или отвыкать от того, к чему вы привыкли на оракле, или писать дальше на оракле.

В другое место ушел работать, на Oracle золотого запаса здесь не хватает. Тут базы под акцесом сделаны, так то это не очень хорошо … Так что надо потихонечку мигрировать куда — нибудь, похоже на MS SQL Server …..

als-a
Member

Откуда:
Сообщений: 7

да и под нарезанные задачи — Oracle все равно что из пушки по воробьям …

pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927

als-a

Так что надо потихонечку мигрировать куда — нибудь, похоже на MS SQL Server …..

Тогда резко забываем об Oracle и осваиваем «парадигму» MS SQL. ????

tpg
Member

Откуда: Novosibirsk
Сообщений: 23902

als-a

…чего-то парни из Microsoft не додумали …

С чего это вы решили, что не додумали? Половина команды разработки сиквела — это аккурат бывшие разработчики оракла. Видимо таким образом они решили отказаться от тупиковых путей (перейдя в MS) ????

SQLMantis
Member

Откуда: Москва
Сообщений: 239

Если бы подобное было разрешено, что вернул бы запрос:
select GET_ID, ID_COUNT_0 from dbo.Counters ?

Ссылка на сообщение

Ссылка (включая название темы)

Ссылка (URL)

Автор: Gwire 5.2.2012, 04:46

Доброго здоровья.
Я проектирую базу, которая будет еще в дальнейшем изменяться и расти, по мере поступления требований от заказчика.
(Может, требования и не поступят, но лучше на это заложиться)
Каждая таблица имеет поле [id] которое primary key.

Идея моей проблемы такова.
Написать функцию (назовем её [Get_Using_Count]), которая
получает: имя таблицы и id записи
возвращает: кол-во применений этого id в связанных таблицах.

Что это значит. Например:
Есть таблицы [Накладные], [Товары к накладным] и [Чеки оплаты]
Случается что накладные создаются по ошибке и не имеют связанных «Товаров к накладным» и «Чеков оплаты»
То есть ни одна запись в этих таблицах не ссылается на «нашу» накладную (у них есть поле накладная_id).

Выполнив запрос «SELECT *, Get_Using_Count(‘dbo.Накладные’, [id]) FROM [Накладные]»,
можно смело удалять записи у которых кол-во связанных записей 0

Следующий код формирует уже готовые запросы на выборку для каждой связанной таблицы с указанной.

Код

DECLARE @ID bigint = 200
DECLARE @TableName varchar(50) = ‘dbo.Накладные’

SELECT
    ‘(SELECT COUNT(id) FROM [‘+
    SCHEMA_NAME(P.[schema_id]) +’].[‘+
    OBJECT_NAME(P.[object_id]) +’] WHERE [‘+
    (SELECT name FROM [sys].[columns]
       WHERE ([object_id]=F.[parent_object_id])
         AND ([column_id]=F.[parent_column_id])
    ) +’]=’+ CAST(@ID as varchar) +’)+’
  FROM [sys].[foreign_key_columns] AS F,
       [sys].[objects] AS P
  WHERE referenced_object_id = OBJECT_ID(@TableName,’U’)
    AND (P.[object_id] = F.[parent_object_id])

На ваших базах она тоже отработает ничего не переделывая (только тип @ID, возможно)

Натравив на нее CURSOR я намеревался собрать их в одну строку и выполнить

Код

DECLARE TableList CURSOR FOR
SELECT …— Из предыдущего блока

DECLARE @T nvarchar(100)
DECLARE @S nvarchar(4000) = ‘SELECT ‘

OPEN TableList
FETCH NEXT FROM TableList INTO @T
WHILE @@FETCH_STATUS = 0
BEGIN
SET @S = @S + @T
FETCH NEXT FROM TableList INTO @T
END
CLOSE TableList
DEALLOCATE TableList

EXEC (@S + ‘0’) —*1*

Но вот не задача — я не могу выполнить EXEC, так чтобы он мне вернул результат не на экран а в переменную.
Допустим её можно решить через еще один CURSOR.
Заменить строчку *1* на

Код

DECLARE @Res int
EXEC (‘DECLARE TempCursor CURSOR FOR ‘+ @S + ‘0’) —*2*
OPEN TempCursor
FETCH NEXT FROM TempCursor INTO @Res
CLOSE TempCursor
DEALLOCATE TempCursor

SELECT @Res

Но возникает вторая проблема которую я не смог побороть
Это:
Сообщение 443, уровень 16, состояние 14, процедура Get_Using_Count, строка 32
Недопустимое использование оператора «EXECUTE STRING», оказывающего побочное действие, в функции.

Возникает в строчке *2*

Вопрос:
Как обойти этот запрет? Или как решить задачу другим методом?

Автор: tzirechnoy 5.2.2012, 10:33

То есть, если Вам вдруг захочется, допустим, создать к накладным список необходимых подписей, подключить его через классическое многие-ко-многим и прописать дефолт — то вся Ваша изящная схема успешно накернится. Или, допустим, не Вам, а следующему программисту, ковыряющему эту опердень. Он будет рад, вероятно.

В общем, не выпендривайтесь. Перечислите список зависимых таблиц и полей явно.

Если очень хочется сделать одну абстракцыю в БД, отвечающую за это — вынесите во view. Впрочем, по-моему, дажэ в этом смысла нет: оно, скорее всего, нужно строго в одном месте — потому делайте просто запросом, без функцый и прочих наворотов.

Автор: Gwire 5.2.2012, 15:46

Цитата(tzirechnoy @ 5.2.2012, 10:33 )

через классическое многие-ко-многим

Я никогда не связываю таблицы этим методом. Это плохая практика.
Если такие ситуации возникают, создается таблица для избавления от «порочных» связей
К примеру: Две таблицы [Пользователи] и [Привилегии] видим отношение многие-ко-многим.
Создаем таблицу [Назначенные_Привилегии] в которой 3 поля [id], [пользователь_id] и [привилегия_id].

В моем случаи «классическое многие-ко-многим» совсем не классическое .

Цитата(tzirechnoy @ 5.2.2012, 10:33 )

не Вам, а следующему программисту, ковыряющему эту опердень.

Другого программиста не будет. У меня заказчик родной дядя.
А если и будет, то ему просто придется немного подумать и не создавать связи многие-ко-многим

Автор: Gwire 5.2.2012, 18:06

Цитата(Zloxa @ 5.2.2012, 16:10 )

Дайте угадаю, вы накрутили foreign keys с каскадными операциями, а теперь пытаетесь реализовать их первородную функцию?

Нет. Обычный внешний ключ, не позволяющий удалять если есть связи.

Логика такова: если запись попала в базу и к ней были добавлены связанные записи, она не может быть удалена.
Например: Таблица [Список_Товаров]. Как я могу позволить удалить какой-то товар, если есть накладные которые используют этот товар. А представляете, что будет если удалить товар каскадно.

Мне эта функция нужна для информативности. Что бы оператор понимал какие записи (накладные, товары, клиенты, адреса …) являются «мусором». Для чистки базы от этого «мусора» (вручную. Возможно какие-то записи будут добавлены наперед, и об этом знает только оператор).

Цитата(Zloxa @ 5.2.2012, 16:10 )

В отличии от каскадных операций на внешних ключах.

Как я уже сказал я не использую каскадные операций.
При удалении записи, запись не удаляется физически (если есть связи), а устанавливается поле [enable] = 0.

Цитата(Zloxa @ 5.2.2012, 16:10 )

Вы описали что ни наесть классическое многие-ко-многим

Логически они существуют, как в моем примере. Но в базе нет таблиц связанных между собой методом «многие-ко-многим».
Как я уже говорил «Это плохая практика». От этих моментов нужно избавляться еще на этапе проектирования базы.

Автор: Zloxa 5.2.2012, 20:15

Цитата(Gwire @ 5.2.2012, 18:06 )

Как я уже говорил «Это плохая практика». От этих моментов нужно избавляться еще на этапе проектирования базы.

Вы бредите.

Цитата(Gwire @ 5.2.2012, 18:06 )

Логически они существуют, как в моем примере.

Ваш пример описал физическую реализацию связи многие ко многим.
Она классически реализуется физически через третью таблицу. И это нормальная парктика. Вы себе противоречите.

Цитата(Gwire @ 5.2.2012, 18:06 )

Как я могу позволить удалить какой-то товар, если есть накладные которые используют этот товар.

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

Тем более что вы врядли сможете его реализовать более эффективно нежели это уже сделано на стороне СУБД. Вы не думали над тем, что в процессе вычисления вашей функции, могут измениться данные, повлиявшие на ее результат? Например вы удаляете товар: функция уже просмотрела таблицу накладных, стала смотреть в таблице остатков, а в это время другой пользователь создал накладную, которая приходует удаляемый вами товар, а к тому моменту, как функция закончит свою работу, вполне возможно, товар уже будет проведен и по остаткам. Как вы полагали избегать такой ситуации? Захватывать в монопольный доступ все объекты базы, которые используют товар? Проверка по внешним ключам, эту задачу решает куда меньшим количеством ресурсов. Пытаемся удалить запись, если попытка обламывается с ошибкой по нарушению внешнего ключа, размечаем запись как не активную — и все.

Автор: Gwire 6.2.2012, 05:11

Zloxa, у меня складывается впечатление, что Вы читаете посты через слово
или не особо разбираетесь в разнице между связями таблица-таблица и сущность-сущность.
Вы о какой связи думали когда писали:

Цитата(Zloxa @ 5.2.2012, 20:15 )

Цитата(Gwire @ 5.2.2012, 18:06 )
Как я уже говорил «Это плохая практика». От этих моментов нужно избавляться еще на этапе проектирования базы.

Вы бредите.

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

Цитата(Zloxa @ 5.2.2012, 20:15 )

Она классически реализуется физически через третью таблицу

Здесь, Вы уже говорите о таблицах, где я с вами солидарен, тем более, что я всегда говорил о таблицах.

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

А то что, связь между сущностями многие-ко-многим —

Цитата(Zloxa @ 5.2.2012, 16:10 )

что ни наесть классическое

ни кто не спорит.

Цитата(Zloxa @ 5.2.2012, 20:15 )

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

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

Цитата(Gwire @ 5.2.2012, 18:06 )

Имелось ввиду, что оператор принимает решение, что является «мусором». Программа, со своей стороны, просто
подсвечивает несвязанные записи. И «оператор» имеется ввиду любой пользователь программы (с любыми привилегиями).

Цитата(Zloxa @ 5.2.2012, 20:15 )

Пытаемся удалить запись, если попытка обламывается с ошибкой по нарушению внешнего ключа, размечаем запись как не активную

Так и реализовано. Но этот метод не подходит для того, что бы просто посмотреть «пустая» запись или нет.

Автор: Zloxa 6.2.2012, 09:55

Цитата(Gwire @ 6.2.2012, 05:11 )

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

Цитата(Gwire @ 6.2.2012, 05:11 )

Программа, со своей стороны, просто подсвечивает несвязанные записи

Вы еще и в запросе собираетесь эту функцию использовать?
Что ж. Дело ваше. Лоб ваш, не мне его жалеть.
Впрочем, может статься, вы его и не расшибете… в этот раз

Автор: Gwire 6.2.2012, 14:48

Zloxa, прошу прощения. MS SQL действительно не умеет создавать настоящие связи многие-ко-многим между двумя таблицами. Моя ошибка не проверил. Потому как был уверен что, все СУБД это умеют (точно могут FoxPro для dos, Access 2003, и Oracle не помню версию). Теперь понятно почему у вас с Akina, мои реплики вызвали негодование. MS SQL убрав возможность создавать такие связи не могла убрать самого понятия «многие-ко-многим», и они его перефразировали.

Цитата(https://www.codenet.ru/db/oracle/oraclepr_04.php)

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

По всей видимости tzirechnoy тоже думал о тех-же «многие-ко-многим» что и я.

Цитата(tzirechnoy @ 5.2.2012, 10:33 )

То есть, если Вам вдруг захочется, допустим, создать к накладным список необходимых подписей, подключить его через классическое многие-ко-многим

Теперь не вижу проблемы.
Считаю вопрос со связями решенным.

Остается «первородный» вопрос:
Можно ли выполнить запрос сохраненный в строке из функции?
Если «Да» — подскажите как.

Точку зрения tzirechnoy я понял. Если совсем ничего не найдется — реализую обычным перечисление.

Автор: Akina 6.2.2012, 15:03

Цитата(Gwire @ 6.2.2012, 15:48 )

был уверен что, все СУБД это умеют

Те СУБД, которые это умеют, на самом деле просто умеют создать эту самую дополнительную промежуточную связующую таблицу и далеко спрятать её от конечного пользователя.

Автор: Gwire 6.2.2012, 15:23

Akina, так Вы согласны, что мое мнение не было ошибочным.
И что Вы с Zloxa просто могли сказать, что MS SQL не поддерживает прямые связи такого типа
(если конечно знали что бывает по другому).

Автор: Akina 6.2.2012, 16:31

Gwire, да я вообще дилетант… но твёрдо знаю, что много-ко-много следует реализовывать через связующую таблицу, даже когда мне предлагают этот сервис в готовой форме (Аксесс-2003 не умеет, а 2007 умеет — но ничто не заставит меня этой возможностью воспользоваться). Ибо «всё, что ты не укажешь как именно делать, имеет право делаться как угодно» — а мне такого добра даром не надо.

Автор: Zloxa 6.2.2012, 17:48

Цитата(Gwire @ 6.2.2012, 14:48 )

Oracle не помню версию

Постарайтесь вспомнить. Мне очень интересно.
8,9,10,11 — не умеют создавать fk к полю, не имеющему ограничение уникальности.

Цитата(Gwire @ 6.2.2012, 14:48 )

По всей видимости tzirechnoy тоже думал о тех-же «многие-ко-многим» что и я.

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

Читайте также: Побочные действия ромашки чай

Предыдущая статья

Капли назальные побочные действия

Следующая статья

Кофе свойства и противопоказания

Источник