Работа с родительской и дочерними таблицами

[nikedeforest]

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

[nikedeforest]

Решил, что спасение в хранимой процедуре, но вот незадача, не пойму, выдается ошибка Column unknown
NAME_POLYA, все просмотрел, не вижу где. Если не сложно ткните пальцем
--------------

Код:

set term!!
create procedure control (name_table varchar(10), name_polya varchar(10), znach varchar(50))
as
declare variable temp integer;
begin
temp=0;
select pn from sprav
where :name_polya=:znach
into :temp;
        if (temp=0) then
        begin
               select min(pn) from sprav
               where :name_polya=0
               into :temp;
                     if (temp<>0)then
                          begin
                             insert into sprav (name_polya)
                             values (:znach);
                                insert into name_table (name_polya)
                                values (:temp);
                          end
                     else
                       begin
                            select max(pn) from sprav
                            into :temp;
                                  insert into sprav (pn, name_polya)
                                  values (:temp+1, :znach);
                                      insert into name_table(name_polya)
                                      values (:temp+1);
                       end
        end
        else
          begin
          insert into name_table (name_polya)
          values (:temp);
          end
end!!
set term;

[Dimka]

спасение, действительно, в ХП

не знаю, что за диалект, наверно IB какой-нибудь, однако

where :name_polya=0

Это есть сравнение значения переменной с 0, но не поля таблицы, хранящегося в :name_polya с 0. Для реализации подстановки поля нужно построить динамический запрос (в виде строчки) и выполнить его. Наверно, выполнять его будет функция exec. Но не ручаюсь.

конкретно ошибки здесь

insert into sprav (name_polya)

и

insert into name_table (name_polya)

и

insert into sprav (pn, name_polya)

и

insert into name_table(name_polya)

и

insert into name_table (name_polya)

двоеточие пропущено. Но всё равно работать не будет, нужен динамический запрос.

[nikedeforest]

Я пробовал вставить двоеточие в запросы insert into ... , но происходит ругательство на двоеточие. Поэтому, вроде как там я прав.
 С where  я кажется и вправду тупанул, щас проверю. Функция ехес - это, я про такую в SQL не слышал, такую я видел в Си и Дельфи.

[nikedeforest]

убрал двоеточие в where не помогло . Я не пойму, эта процедура, написанная мною, вообще к жизни не пригодна ?. Где я не прав? Я уже врроде с этими двоеточиями все перепробовал, может дело не в них.
Насчет диалекта, то это interbase поставляемый  с DELPHI 7. Я сначала на нем пишу, потом перевожу на MySQL.

"Для реализации подстановки поля нужно построить динамический запрос (в виде строчки) и выполнить его."-не совсем понял про динамический запрос, может я о нем слышал под другим названием, что это.

[Dimka]

потому что в insert и в select должны быть названия полей, а не переменные. Повторяю, нужен динамический запрос. Я не ручаюсь за правильность, так как под рукой нету IB, чтобы проверить, но что-то в духе нижеследующего:

Код:

declare variable sqlstr varchar(2000);
sqlstr = 'select pn from sprav where ' + :name_polya + '=' + :znach;
exec :sqlstr into :temp

Т.е. запрос надо записать как текст, вставив в него через сложение строк переменные, содержащие поля. И выполнить эту строчку интерпретатором.

[nikedeforest]

Щас попробую, я вообще-то такое первый раз вижу, (обидно, столько книжек прочел). Этот код, он должен находиться в теле процедуры???

[nikedeforest]

Код:

set term!!
create procedure control (name_table varchar(10), name_polya varchar(10), znach varchar(50))
as
declare variable temp integer;
declare variable sqlstr varchar(70);
begin
temp=0;
sqlstr='select pn from sprav where'+ name_polya+'='+:znach;
exec :sqlstr into :temp;
        if (temp=0) then
        begin
............
end!!
set term ;

Вот так сделал, происходит ругательство такого типа

Dynamic SQL Error
SQL error code = -104
Token unknown - line 8, char 4
:

Удаляю двоеточие перед sqlstr  происходит ругательство типа, что за sqlstr.
Я вообще удивляюсь, что на ехес ругательства нет, редактор не выделил жирным это слово, т.е. он слово ехес не посчитал за ключевое. А execute procedure это не тоже, что ехес?

[HandKot]

Может конечно не совсем допонял задачу, но мне кажется все можно решить с помощью связей и индексов

[nikedeforest]

HandKot, теперь я не совсем понял тебя. ИМХО процедура то, что мне надо, вот только проблема стала в том, чтобы написать ее правильно, т.е. чтобы она работала, но видать моих знаний тут недостаточно. Вот и прошу о помощи, чтобы сделать процедуру эту рабочей.

[Dimka]

sqlstr='select pn from sprav where'+ name_polya+'='+:znach;

пусть name_polya = 'test', а znach = '3'
твой запрос образует строчку

Код:

'select pn from sprav wheretest=3'

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

[HandKot]

nikedeforest
тута порылся и вроде в IB нет команды EXEC

может тогда попробовать так

Код:

create procedure control (name_table varchar(10), name_polya varchar(10), znach varchar(50))
as
declare variable temp integer;
declare variable sprPN integer;
BEGIN
sprPN = 0
if (name_polya = 'Поле1') then
begin
  SELECT pn FROM sprav WHERE Поле1 = :znach INTO :temp;
  if (temp = 0) then
     'нет такой записи в справочнике - вставляем
     'формируем новый ключ
     SELECT MAX(pn)+1 FROM sprav INTO :temp 
     INSERT INTO sprav(pn, Поле1) VALUES (:temp,:znach)    
     sprNP = temp
  else
     SELECT pn FROM sprav WHERE 'Поле1' = :znach INTO :sprPN
  end
end
else
begin
 ---другое поле
end

'всавляем ключ в подчиненные таблицы
if (name_table = 'Таблица1') then
begin
  insert into Таблица1(pn) VALUES (:sprPN)
end
else
begin
 ---другая таблица
end
END!!!

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

Код:

insert into [Имя таблицы](pn) values (control(ИмяПоля, Значение)

[Dimka]

Одна эта ошибка

Dynamic SQL Error

Говорит, что динамические запросы в IB есть Если не пойдёт, можно попробовать писать в скобках. В SQL Server, с которым я работаю, динамические запросы выполняются как exec('select ........'), а вызов процедур без скобок.

exec - это и есть сокращение от execute. Ещё со времён dBase (а может и раньше, не знаю) повелось, что команды можно писать сокращённо . В FoxPro (в старых версиях точно) команды различались лишь по первым 4 символам, там и вместо select можно писать sele. В других СУБД это не всегда так, но exec - штука довольно распространённая.

[nikedeforest]

HandKot, насколько я понял, ты предлагаешь вместо переменных name_polya и name_table использовать конкретно имя столбца(поля) и имя таблицы в запросах . Но понимаешь, таблица-то не одна, их на данный момент уже 3, в дальнейшем их может прибавиться, хотелось бы чтобы ХП не была такой чувствительной к изменением в БД. А еще если представить, что полей тоже не мало, то вообще так подумать, это сколько же мне надо писать оператор if и запросов для каждого поля и таблицы, и опять же никакой расширяемости для БД. Не очень-то подходит твой способ, ты уж не обижайся, но в этом случае мне вообще нет смысла в этой хранимой процедуре, она должна бать все-таки по универсальнее.
dimka, понял, но уже проверю только завтра. Мне надо к экзамену завтрашнему хоть немного подготовиться, поэтому отвечу после экзамена.
 А вот чуть не забыл, у препода сегодня спросил про динамический запрос, оказывается мы такие вовсе не затрагивали (да и в книжках я не встречал), поэтому не мудрено, что я про них не слышал. Но он сказал, что динамический запрос нельзя вставлять в хранимую процедуру. А теперь HandKot говорит, что exec нет в IB. dimka ты уверен, что это то что надо?

[nikedeforest]

Так запросы динамические есть. А вот ехес есть в IB? Может там что-то другое?

[Dimka]

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

Попробуй выполнить простую процедуру вида

Код:

create procedure test 
as
begin
exec('select * from sprav');
end

Поиграйся с exec: со скобками и без, заменить на execute и т.п. Если она заработает  - ты получишь безошибочное исполнение, то твой преподаватель ошибается.

[nikedeforest]

dimka, т.е. если так как ты говоришь, то мне достаточно туда пробел добавить и все должно заработать. Сам смысл того что я делал правильный?

[nikedeforest]

Блин, когда писал последний пост, не весь прочел предидущий  пост (т.е. твой dimka). Надо в принципе так и сделать как ты сказал, заняться экспериментом с IB.

[Dimka]

Вот читаю документацию по IB и вижу одну простую инструкцию, исполняющую динамические запросы: EXECUTE IMMEDIATE. Однако, инструкция эта в консоли IB не работает и в ХП соответственно, предназначена для других целей (см. доки  Embedded SQL Guide). Для каких именно - не понял, куда-то это встраивается в код на C/C++. Разбираться тщательно нет времени. Так что, не выйдет. Признаться, я об IB был более высокого мнения.

Тогда остаётся лишь один выход: вынести код на клиентское приложение - там можно динамически запросы собирать и исполнять.

[nikedeforest]

dimka, ты гонец плохих новостей. Слушай, а в MySql это все реально сотворить? Мне в принципе все это надо для MySql, просто я к нему еще не привык и по началу все делаю на IB. Но в этом случае может придеться сразу в MySql.

[Oldy]

Если позволите, я бы попытался сделать так:

create procedure control (name_table varchar(10), name_polya varchar(10), znach varchar(50))
as
declare variable temp integer;
begin
  temp=0;
  if :name_polya = 'FIeld1' then
  begin
     select pn from sprav where Field1=:znach into :temp;
        if (temp=0) then
          begin
             insert into sprav (Field1)   values (:znach);
            // и далее то, что нужно если значение уникально
          end
        else
          begin
            // здесьделаем что нужно, если значение поля не уникально
          end
  end

  if :name_polya = 'FIeld2' then //все тоже самое, что и для первого поля
  begin
    .
    .
    .
  end
  и т.д.
end

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

[Dimka]

Описанный способ с условиями выше предлагался, но неудобно...

А насчёт справочников даже согласен. Почему в одной таблице 4 поля, а заполняется лишь одно из них? Это недостаточная нормализация. Это ещё оправдать нужно какими-то соображениями.

[Oldy]

Согласен, что неудобно, но это единственно возможный  способ для InterBase.
Если-бы это был Yaffil или FireBird 1.5 то тогда можно-бы было применить "Execute varchar" или "Execute statement"
см. https://forum.shelek.ru/index.php/topic,3877.0.html

[nikedeforest]

В справочнике 4 поля (одно поле это порядковый номер оно не считается, получается 3 поля). Объясняю почему заполняется одно поле. Кстати заполняться могут и все поля, но заполнение справочника происходит, когда в дочернюю таблицу нужно внести значение которое в справочнике еще не содержиться. Дочерних табдиц несколько, между собой они никак несвязана, но у них есть одинаковые поля. Вот нам нужно внести значение в одну из дочерних таблиц (заполнить надо все поля). Рассмотри 2 варианта:
1) Мы заносим данные во все поля и получается что во все эти поля вносяться значения которых нет в справочнике, тогда в справочнике заполняються все поля.
2) Мы заносим данные во все поля и получается что для одного поля дочерний таблицы такое значение есть в соответствующем ей поле в справочнике, а вот для других полей нет и соответствующие им поля справочника будут заполнены этими недостающими значениями.
Вроде как понятно объяснил ИМХО.
Возможно вас смутит, что в справочнике в некоторых полях будут храниться значения равные NULL, но сразу скажу, что их будет не так много и в принципе неудобств это по-идеи никаких не несет.В придачу NULL места много не сожрет .
Но ответьте на вопрос поставленный мною выше. В MySql можно реализовать то, что мне нужно способом указанным Димкой (ничего что логин по-русски нкаписал?)  через динамический запрос или так, как пытался я (этот способ указан во втором посте этой темы), что было бы еще лучше. 
P.s. Oldy, ссылку еще не смотрел, после того как отправлю пост посмотрю.
Кабздец видать моему экзамену, совсем не могу сосредоточиться

[nikedeforest]

Oldy, ты той ссылкой очердной раз доказал, что нельзя в IB и в FB 1.0.3(с которой я не знаком и пока нет желания знакомиться)  в запросы select, insert и в фиг еще знает какие передать вместо имени поля переменную. Но скажи, можно ли это сделать в MySql и можно ли использовать динамический запрос в ХП в MySql. Если не ответите, то умру ведь неучем . 

[Dimka]

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

[nikedeforest]

Вот предметная область
В первой таблице имя которой str_pr(строящееся жилье) содержиться 10 полей, я перечислю первые 3, остальные значения не имеют для данной задачи
Поле №1 rayon(район) Поле№2 tip_doma(тип дома) Поле №3 sanuzel(санузел)

Вторая таблица liv_pr(жилой комплекс), в ней 11 полей, но первые 3 такие же как в предидущей таблице, я их перечислять не буду.

Третья таблица kmz_pr(комплекс малоэтажной застройки), в ней 16 полей, но требуеться только первое, которое называется rayon(район).

Таблица справочник имя которой sprav,  состоит из трех полей:
Поле №1 rayon(район) Поле№2 tip_doma(тип дома) Поле №3 sanuzel(санузел)

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

[Dimka]

знал бы - ответил бы Попробуй.

Насчёт жилого комплекса - это тебе виднее, зачем там санузел и тип дома. А вот назначение справочника я не понял. Какой интерес хранить всевозможные сочетания районов, типов домов и санузлов? Это 3 отдельных справочника. Объясни, где ты справочник в нынешнем виде используешь.

[Oldy]

nikedeforest , боюсь что возможности MySql еще беднее чем у InterBase. Вот все, что я нашел  http://www.mysql.ru/docs/man/Variables.html по использованию переменных в запросе. Может плохо искал? 

[HandKot]

nikedeforest
есть такая идея:
в справочнике всего 3 поля key - ключ, name_polya - имя поля, znach - значние (твою таблицу справочник положили боком, если можно так выразится)
пример:
key Name_polya Znach
1    Поле1       ффф
1    Поле2       ыыы
2    Поле3       ччч
3    Поле1       ккк
может так решится твоя проблема и с запросом легче будет 

а использование динамических запросов - это результат неправильной структуры данных и плохой признак программирования ИМХО

ЗЫЖ я использую динамические запросы только на клиентской стороне (программа на VB)