Jl CHAR(4),

QTY NUMERIC(9), PRIMARY KEY ( St, Pf, Jl ), FOREIGN KEY ( St ) REFERENCES S, FOREIGN KEY ( Pt ) REFERENCES P, FOREIGN KEY ( Jt ) REFERENCES J ) ;

4.4. Ниже приведены ответы к отдельным пунктам упражнения.

а) INSERT INTO S (St, SNAME, CITY )

VALUE ( SIO, Smith, New York ) ;

Значение столбца STATUS здесь установлено равным значению по умолчанию.

б) UPDATE Р

SET COLOR = Orange WHERE COLOR = Red ;

в) DELETE FROM J

WHERE Jt NOT IN ( SELECT Jt FROM SPJ ) ;

Обратите внимание на вложенный подзапрос и оператор IN (а точнее, на отрицание оператора IN) в ответе в. Такие подзапросы рассматриваются в главе 7.

4.5. Отметим, что могут существовать поставщики, которые не поставляют деталей ни для одного проекта; в следующем ответе рассматриваются именно такие поставщики (только такие?). Сначала определим два курсора, CS и CJ.

EXEC SQL DECLARE CS CURSOR FOR

SELECT S.St, S.SNAME, S.STATUS, S.CITY

FROM S

ORDER BY St ;

EXEC SQL DECLARE CJ CURSOR FOR

SELECT J.Jt, J.JNAME, J.CITY FROM J WHERE J.Jt IN

( SELECT SPJ.Jt FROM SPJ

WHERE SPJ.St = :CS St ) ORDER BY Jt ;

(Снова обратите внимание на вложенный подзапрос и оператор IN.) Когда курсор С J открыт, базовая переменная CS St содержит значение номера поставщика, получаемое с помощью курсора CS. Логика процедуры, по существу, следующая.

EXEC SQL OPEN CS ;

DO <для всех строк S, доступных через CS>;

EXEC SQL FETCH CS INTO :CS St, :CS SN, :CS ST, tCS SC ;

print CS St, CS SN, CS ST, CS SC Г

EXEC SQL oPEN CJ ; ~

DO <для всех строк J, доступных через CJ>;

EXEC SQL FETCH С J INTO :CJ Jt, :CJ JN, :CJ JC ;

print CJ Jt, CJ JN, CJ JC 7

END DO Г

EXEC SQL CLOSE CJ ; END DO ;

EXEC SQL CLOSE CS ;

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

CALL RECURSION ( GIVENPt ) ;

RECURSION: PROC ( UPPER Pt ) RECURSIVE ; DCL UPPER Pt ... ; DCL LOWER Pt ... ;

EXEC SQL DECLARE С reopenable CURSOR FOR

SELECT MINOR Pt

FROM PART STRUCTURE

WHERE MAJOR Pt = :UPPER Pt ; print UPPER Pt ;

/* вывести значение UPPER Pt */ EXEC SQL OPEN С ;

DO <для всех строк PMT STRUCTURE, доступных через О ;

EXEC SQL FETCH С INTO :LOWER Pt ;

CALL RECURSION ( LOWER Pt ) ; END DO ;

EXEC SQL CLOSE С ; END PROC ;

Здесь подразумевается, что фиктивная спецификация reopenable ( повторно открываемый ) в операторе DECLARE CURSOR означает допустимость операции OPEN даже в том случае, если курсор уже открыт. В результате такой операции создается новый экземпляр курсора для данного табличного выражения (использующего текущие значения любых базовых переменных, на которые есть ссылки в этом выражении). Далее подразумевается, что ссылки на такой курсор в операторе FETCH и других являются ссылками на текущий экземпляр и что оператор CLOSE уничтожает именно этот экземпляр и восстанавливает предыдущий экземпляр как

текущий . Другими словами, подразумевается, что повторно открываемый курсор формирует стек, который обслуживается операторами OPEN и CLOSE так же, как обычный стек обрабатывается операторами push и pop.

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

CALL RECURSION ( GIVENPl ) ; RECURSION: PROC ( UPPER P ) RECURSIVE ; DCL UPPER P ... ;

DCL LOWER P ... INITIAL ( );

EXEC SQL DECLARE С CURSOR FOR SELECT MINOR P FROM PART STRUCTURE WHERE MAJOR P = :UPPER Pt AND MINOR PI > :LOWER Pl ORDER BY MINOR P ; print UPPER Pt ; DO <без ограничений> ; EXEC SQL OPEN С ? EXEC SQL FETCH С INTO :LOWER Pt ; EXEC SQL CLOSE С ;

IF <нет выбранных lower Pi> THEN RETURN ; END IF ; IF <ecTb выбранные lower Pi> THEN

CALL RECURSION ( LOWER P ) ; END IF ; END DO ; END PROC ;

Заметьте, что в этом решении один и тот же курсор используется в каждом вызове подпрофаммы RECURSION (но каждый раз при ее вызове динамически создаются новые экземпляры переменных UPPER P и LOWER P, причем в конце вызова эти экземпляры уничтожаются). В связи с данным фактом мы использовали следующий трюк.

... AND MINOR Pt > :LOWER Pl ORDER BY MINOR Pt

Так что при каждом вызове подпрофаммы RECURSION все промежуточные компоненты (L0WER P) текущего значения UPPER P, которые уже были обработаны, просто игнорируются.

Обсуждение некоторых альтернативных подходов к этой задаче вы найдете в [4.5], [4.7]. В главе 6 (в конце раздела 6.7) описывается оператор, имеющий отношение к данному вопросу. Он называется транзитивным замыканием (transitive closure). В приложении Б приводится обзор некоторых возможностей в стандарте SQL3, которые также имеют отношение к данной проблеме.