Введение в SQL 7 страница

25. ИСПОЛЬЗОВАНИЕ SQL С ДРУГИМ ЯЗЫКОМ (ВЛОЖЕННЫЙ SQL) В ЭТОЙ ГЛАВЕ ВЫ УЗНАЕТЕ КАК SQL ИСПОЛЬЗУЕТСЯ для расширения программ написанных на других языках. Хотя непроцедурность языка SQL делает его очень мощным, в то же время это накладывает на него большое число ог- раничений. Чтобы преодолеть эти ограничения, вы можете включать SQL в программы написанные на том или другом процедурном языке(имеющем оп- ределенный алгоритм). Для наших примеров, мы выбрали Паскаль, считая что этот язык наиболее прост в понимании для начинающих, и еще потому, что Паскаль - один из языков для которых ANSI имеет полуофициальный стандарт. ЧТО ТАКОЕ - ВЛОЖЕНИЕ SQL Чтобы вложить SQL в другой язык, вы должны использовать пакет прог- рамм который бы обеспечивал поддержку вложения SQL в этот язык и ко- нечно же, поддержку самого языка. Естественно, вы должны быть знакомы с языком который вы используете. Главным образом, вы будете использо- вать команды SQL для работы в таблицах базы данных, передачи результа- тов вывода в программу и получение ввода из программы в которую они вкладываются, обобщенно ссылаясь к главной программе (которая может или не может ппринимать их из диалога или посылать обратно в диалог пользователя и программы). ЗАЧЕМ ВКЛАДЫВАТЬ SQL? Хотя и мы потратили некоторое время на то чтобы показать что умеет делать SQL, но если вы - опытный программист, вы вероятно отметили, что сам по себе, он не очень полезен при написании программ. Самое очевидное ограничение - это то, что в то время как SQL может сразу вы- полнить пакет команды, интерактивный SQL в основном выполняет по одной команде в каждый момент времени. Типы логических конструкций типа if... then ("если... то"), for... do ("для... выполнить") и while... repeat("пока... повторять") - используемых для структур большинства компьютерных программ, здесь от- сутствуют, так что вы не сможете принять решение - выполнять ли, как выполнять, или как долго выполнять одно действие в результате другого действия. Кроме того, интерактивный SQL не может делать многого со значениями, кроме ввода их в таблицу, размещения или распределения их с помощью запросов, и конечно вывода их на какое-то устройство. Более традиционные языки, однако, сильны именно в этих областях. Они разработаны так чтобы программист мог начинать обработку данных, и ос- новываясь на ее результатах, решать, делать ли это действие или дру- гое, или же повторять действие до тех пока не встретится некоторое ус- ловие, создавая логические маршруты и циклы. Значения сохраняются в переменных, которые могут использоваться и изменяться с помощью любого числа команд. Это дает вам возможность указывать пользователям на ввод или вывод этих команд из файла, и возможность форматировать вывод сложными способами (например, преобразовывать числовых данных в диаг- раммы). Цель вложенного SQL состоит в том, чтобы объединить эти воз- можности, позволяющие вам создавать сложные процедурные программы ко- торые адресуют базу данных посредством SQL - позволяя вам устранить сложные действия в таблицах на процедурном языке который не ориентиро- ван на такую структуру данных, в тоже время поддерживая структурную строгость процедурного языка. КАК ДЕЛАЮТСЯ ВЛОЖЕНИЯ SQL. Команды SQL помещаются в исходный текст главной программы, которой предшествует фраза - EXEC SQL (EXECute SQL). Далее устанавливаются некоторые команды которые являются специальными для вложенной формы SQL, и которые будут представлены в этой главе. Строго говоря, стандарт ANSI не поддерживает вложенный SQL как тако- вой. Он поддерживает понятие, называемое - модуль, который более точ- но, является вызываемым набором процедур SQL, а не вложением в другой язык. Официальное определение синтаксиса вложения SQL, будет включать расширение официального синтаксиса каждого языка в который может вкла- дываться SQL, что весьма долгая и неблагодарная задача, которую ANSI избегает. Однако, ANSI обеспечивает четыре приложения (не являющиеся частью стандарта), которые определяют синтаксис вложения SQL для че- тырех языков: КОБОЛ, ПАСКАЛЬ, ФОРТРАН, и ПЛ/1. Язык C - также широко поддерживается как и другие языки. Когда вы вставляете команды SQL в текст программы написанной на другом языке, вы должны выполнить пред- компиляцию прежде, чем вы окончательно ее скомпилируете. Программа называемая прекомпилятором (или препроцессором), будет просматривать текст вашей программы и преобразовывать команды SQL, в форму удобную для использования базовым языком. Затем вы используете обычный транслятор чтобы преобразовывать прог- рамму из исходного текста в выполняемый код. Согласно подходу к модульному языку определенному ANSI, основная программа вызывает процедуры SQL. Процедуры выбирают параметры из главной программы, и возвращают уже обработанные значения, обратно в основную программу. Модуль может содержать любое число процедур, каж- дая из которых состоит из одиночной команды SQL. Идея в том, чтобы процедуры могли работать тем же самым способом чтго и процедуры на языке в который они были вложены(хотя модуль еще должен идентифициро- вать базовый язык из-за различий в типах данных различных языков). Реализации могут удовлетворить стандарту, выполнив вложение SQL таким способом, как если бы модули уже были точно определены. Для этой цели прекомпилятор будет создавать модуль, называемый модулем доступа. Только один модуль, содержащий любое число процедур SQL, может сущест- вовать для данной программы. Размещение операторов SQL непосредственно в главном коде, происходит более просто и более практично чем непос- редственно создание самих модулей. Каждая из программ использующих вложение SQL, связана с ID доступа, во время ее выполнения. ID доступа, связанный с программой, должен иметь все привилегии чтобы выполнять операции SQL, выполняемые в прог- рамме. Вообще то, вложенная программа SQL регистрируется в базе дан- ных, также как и пользователь, выполняющий программу. Более подробно, это определяет проектировщик, но вероятно было бы неплохо для включить в вашу программу команду CONNECT или ей подобную. ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННЫХ ======= ОСНОВНОГО ЯЗЫКА В SQL ======= Основной способ которым SQL и части базового языка ваших программ будут связываться друг с другом - это с помощью значений переменных. Естественно, что разные языки распознают различные типы данных для пе- ременных. ANSI определяет эквиваленты SQL для четыре базовых языков - ПЛ/1, Паскаль, КОБОЛ, и ФОРТРАН; все это подробности описаны в Прило- жении B. Эквиваленты для других языков - определяет проектировщик. Имейте в виду, что типы, такие как DATE, не распознаются ANSI; и следовательно никаких эквивалентных типов данных для базовых языков, не существуют в стандарте ANSI. Более сложные типы данных базового языка, такие как матрицы, не имеют эквивалентов в SQL. Вы можете ис- пользовать переменные из главной программы во вложенных операторах SQL везде, где вы будете использовать выражения значений. (SQL, использу- емый в этой главе, будет пониматься как к вложенный SQL, до тех пор пока это не будет оговорено особо.) Текущим значением переменной, может быть значение, используемое в команде. Главные переменные должны - * быть обьявленными в SQL DECLARE SESSION (РАЗДЕЛ ОБЪЯВЛЕНИЙ) кото- рый будет описан далее. * иметь совместимый тип данных с их функциями в команде SQL (напри- мер, числовой тип если они вставляется в числовое поле) * быть назначеными значению во время их использования в команде SQL, если команда SQL самостоятельно не может сделать назначение. * предшествовать двоеточию (:) когда они упоминаются в команде SQL. Так как главные переменные отличаются от имен столбцов SQL наличием у них двоеточия, вы можете использовать переменные с теми же самыми именами что и ваши столбцы, если это конечно нужно. Предположим что вы имеете четыре переменных в вашей программе, с именами: id_num, sales- person, loc, и comm. Они содержат значения которые вы хотите вставить в таблицу Продавцов. Вы могли бы вложить следующую команду SQL в вашу программу: EXEC SQL INSERT INTO Salespeople VALUES (:id_num,:salesperson,:loc,:comm) Текущие значения этих переменных будут помещены в таблицу. Как вы можете видеть, переменная comm имеет то же самое имя что и столбец в который это значение вкладывается. Обратите внимание, что точка с за- пятой в конце команды отсутствует. Это потому, что соответствующее за- вершение для вложенной команды SQL зависит от языка для которого дела- ется вложение. Для Паскаля и PL/1, это будет точка с запятой; для КО- БОЛА, слово END-EXEC; и для ФОРТРАНА не будет никакого завершения. В других языках это зависит от реализации, и поэтому мы договоримся что будем использовать точку с запятой (в этой книге) всегда, чтобы не противоречить интерактивному SQL и Паскалю. Паскаль завершает вложен- ный SQL и собственные команды одинаково - точкой с запятой. Способ сделать команду полностью такой как описана выше, состоит в том, чтобы включать ее в цикл и повторять ее, с различными значениями переменных, как например показано в следующем примере: while not end-ot-file (input) do begin readln (id_num, salesperson, loc, comm); EXEC SOL INSERT INTO Salespeople VALUES (:id_num,:salesperson,:loc,:comm); end; Фрагмент программы на ПАСКАЛЕ, определяет цикл, который будет считы- вать значения из файла, сохранять их в четырех проименованных перемен- ных, сохранять значения этих переменных в таблице Продавцов, и затем считывать следующие четыре значения, повторяя этот процесс до тех пор пока весь входной файл не прочитается. Считается, что каждый набор значений завершается возвратом каретки (для незнакомых с Паскалем, функция readln считывает вводимую информацию и переходит на следующую строку источника этой информации). Это дает вам простойспособ передать данные из текстового файла в реляционную структуру. Конечно, вы можете сначала обработать данные любыми возможными спо- собами на вашем главном языке, например для исключения всех комиссион- ных ниже значения.12 while not end-ot-file (input) do begin readln (id_num, salesperson, loc, comm); if comm > =.12 then EXEC SQL INSERT INTO Salespeople VALUES (:id_num,:salesperson,:loc,:comm); end; Только строки которые встретят условие comm >=.12 будут вставлены в вывод. Это показывает что можно использовать и циклы и условия как нормальные для главного языка. ОБЪЯВЛЕНИЕ ПЕРЕМЕННЫХ Все переменные на которые имеется ссылка в предложениях SQL, должны сначала быть обьявлены в SQL DECLARE SECTION (РАЗДЕЛе ОБЪЯВЛЕНИЙ), использующем обычный синтаксис главного языка. Вы можете иметь любое число таких разделов в программе, и они могут размещаться где-нибудь в коде перед используемой переменной, подчиненной ограничениям опреде- ленным в соответствии с главным языком. Раздел объявлений должен начи- нать и кончаться вложенными командами SQL - BEGIN DECLARE SECTION (Начало Раздела Объявлений) и END DECLARE SECTION (Конец Раздела Объ- явлений), которым предшествует, как обычно EXEC SQL (Выполнить). Чтобы обьявить переменные используемые в предыдущем примере, вы мо- жете ввести следующее: EXEC SQL BEGIN DECLARE SECTION; Var id-num: integer; Salesperson: packed array (1..10) ot char; loc: packed array (1..10) ot char; comm: real; EXEC SQL END DECLARE SECTION; Для незнакомых с ПАСКАЛем, Var - это заголовок который предшествует ряду обьявляемых переменных, и упакованным (или распакованным) мас- сивам являющимися серией фиксированных переменных значений различаемых с помощью номеров(например, третий символ loc будет loc (3)). Ис- пользование точки с запятой после каждой переменной указывает на то что это - Паскаль, а не SQL. ИЗВЛЕЧЕНИЕ ЗНАЧЕНИЙ ПЕРЕМЕННЫХ Кроме помещения значений переменных в таблицы используя команды SQL, вы можете использовать SQL чтобы получать значения для этих перемен- ных. Один из способов делать это - с помощью разновидности команды SE- LECT которая содержит предложение INTO. Давайте вернемся к нашему пре- дыдущему примеру и переместим строку Peel из таблицы Продавцов в наши переменные главного языка. EXEC SQL SELECT snum, sname, city, comm INTO:id_num,:salesperson,:loc,:comm FROM Salespeople WHERE snum = 1001;
Выбранные значения помещаются в переменные с упорядоченными именами указанными в предложении INTO. Разумееется, переменные с именами ука- занными в предложении INTO должны иметь соответствующий тип чтобы при- нять эти значения, и должна быть своя переменная для каждого выбранно- го столбца. Если не учитывать присутствие предложения INTO, то этот запрос - похож на любой другой. Однако, предложение INTO добавляет значительное ограничение к запросу. Запрос должен извлекать не более одной строки. Если он извлекает много строк, все они не могут быть вставлены одновременно в одну и ту же переменную. Команда естественно потерпит неудачу. По этой причине, SELECT INTO должно использоваться только при следующих условиях: * когда вы используете предикат проверяющий значения, которое как вы знаете, могут быть уникальным, как в этом примере. Значения кото- рые, как вы знаете, могут быть уникальными - это те значения кото- рые имеют принудительное ограничение уникальности или уникальный индекс, как это говорилось в Главах 17 и 18. * когда вы используете одну или более агрегатных функций и не исполь- зуете GROUP BY. * когда вы используете SELECT DISTINCT во внешнем ключе с предикатом ссылающимся на единственное значение родительского ключа (обеспечи- вая вашей системе предписание справочной целостность), как в следу- ющем примере: EXEC SQL SELECT DISTINCT snum INTO:salesnum FROM Customers WHERE snum = (SELECT snum FROM Salespeople WHERE sname = 'Motika'); Предпологалось что Salespeople.sname и Salespeople.snum - это соот- ветственно, уникальный и первичный ключи этой таблицы, а Custo- mers.snum - это внешний ключ ссылающийся на Salespeople.snum, и вы предполагали что этот запрос произведет единственную строку. Имеются другие случаи, когда вы можете знаете, что запрос должен произвести единственную строку вывода, но они мало известны и, в большинстве слу- чаев, вы основываетесь на том что ваши данные имеют целостность, кото- рая не может быть предписана с помощью ограничений. Не полагайтесь на это! Вы создаете программу которая, вероятно, будет использоваться в течение некоторого времени, и лучше всего проиграть ее чтобы быть га- рантированным в будущем от возможных отказов. Во всяком случае, нет необходимости группировать запросы которые производут одиночные стро- ки, поскольку SELECT INTO - используется только для удобства. Как вы увидите, вы можете использовать запросы выводящие многочис- ленные строки, используя курсор. КУРСОР Одна из сильных качеств SQL - это способность функционировать на всех строках таблицы, чтобы встретить определенное условие как блок запись, не зная сколько таких строк там может быть. Если десять строк удовлетворяют предикату, то запрос может вывести все десять строк. Ес- ли десять миллионов строк определены, все десять миллионов строк будут выведены. Это несколько затруднительно, когда вы попробуете связать это с другими языками. Как вы сможете назначать вывод запроса для пе- ременных когда вы не знаете как велик будет вывод? Решение состоит в том, чтобы использовать то, что называется - курсором. Вы вероятно знакомы с курсором, как с мигающей черточкой, которая отмечает вашу позицию на экране компьютера. Вы можете рассматривать SQL курсор как устройство, которое аналогично этому, отмечает ваше место в выводе запроса, хотя аналогия не полная. Курсор - это вид переменной, которая связана с запросом. Значением этой переменной может быть каждая строка, которая выводится при запро- се. Подобно главным переменным, курсоры должны быть обьявлены прежде, чем они будут использованы. Это делается командой DECLARE CURSOR, сле- дующим образом: EXEC SQL DECLARE CURSOR Londonsales FOR SELECT * FROM Salespeople WHERE city = 'London'; Запрос не выполнится немедленно; он - только определяется. Курсор немного напоминает представление, в котором курсор содержит запрос, а содержание курсора - напоминает любой вывод запроса, каждый раз когда курсор становится открытым. Однако, в отличие от базовых таблиц или представлений, строки курсора упорядочены: имеются первая, вторая...... и последняя строка курсора. Этот порядок может быть произвольным с явным управлением с помощью предложения ORDER BY в запросе, или же по умолчанию следовать какому-то упорядочению определяемому инструмен- тально-определяемой схемой. Когда вы находите точку в вашей программе в которой вы хотите выполнить запрос, вы открываете курсор с помощью следующей команды: EXEC SQL OPEN CURSOR Londonsales; Значения в курсоре могут быть получены, когда вы выполняете именно эту команду, но не предыдущую команду DECLARE и не последующую команду FETСH. Затем, вы используете команду FETCH чтобы извлечь вывод из это- го запроса, по одной строке в каждый момент времени. EXEC SQL FETCH Londonsales INTO:id_num,:salesperson,:loc,:comm; Это выражение переместит значения из первой выбраной строки, в пере- менные. Другая команда FETCH выводет следующий набор значений. Идея состоит в том, чтобы поместить команду FETCH внутрь цикла, так чтобы выбрав строку, вы могли переместив набор значений из этой строки в пе- ременные, возвращались обратно в цикл чтобы переместить следующий на- бор значений в те же самые переменные. Например, возможно вам нужно чтобы вывод выдавался по одной строке, спрашивая каждый раз у пользо- вателя, хочет ли он продолжить чтобы увидеть следующую строку Look_at_more:= True; EXEC SQL OPEN CURSOR Londonsales; while Look_at_more do begin EXEC SQL FETCH Londonsales INTO:id_num,:Salesperson,:loc,:comm; writeln (id_num, Salesperson, loc, comm); writeln ('Do you want to see more data? (Y/N)'); readln (response); it response = 'N' then Look_at_more: = False end; EXEC SQL CLOSE CURSOR Londonsales; В Паскале, знак: = означает - "является назначенным значением из", в то время как = еще имеет обычное значение " равно ". Функция writeln записывает ее вывод, и затем переходит к новой строке. Одиночные ка- вычки вокруг символьных значений во втором writeln и в предложении if... then - обычны для Паскаля, что случается при дубликатах в SQL. В результате этого фрагмента, Булева переменная с именем Look_at_mo- re должна быть установлена в состояние верно, открыт курсор, и введен цикл. Внутри цикла, строка выбирается из курсора и выводится на экран. У пользователя спрашивают, хочет ли он видеть следующую строку. Пока он не ответил N (Нет), цикл повторяется, и следующая строка значений будет выбрана. Хотя переменные Look_at_more и ответ должны быть обьявлены как Буле- ва переменная и символьная(char) переменная, соответственно, в разделе обьявлений переменных в Паскаля, они не должны быть включены в раздел обьявлений SQL, потому что они не используются в командах SQL. Как вы можете видеть, двоеточия перед именами переменных не исполь- зуются для не-SQL операторов. Далее обратите внимание, что имеется оператор CLOSE CURSOR соответствующий оператору OPEN CURSOR. Он, как вы поняли, освобождает курсор значений, поэтому запрос будет нужно вы- полнить повторно с оператором OPEN CURSOR, прежде чем перейти в выбору следующих значений. Это необязательно для тех строк которые были выб- раны запросом после закрытия курсора, хотя это и обычная процедура. Пока курсор закрыт, SQL не следит за тем, какие строки были выбраны. Если вы открываете курсор снова, запрос повторно выполняется с этой точки, и вы начинаете все сначала. Этот пример не обеспечивает автома- тический выхода из цикла, когда все строки уже будут выбраны. Когда у FETCH нет больше строк которые надо извлекать, он просто не меняет значений в переменных предложения INTO. Следовательно, если данные ис- черпались, эти переменные будут неоднократно выводиться с идентичными значениями, до тех пор пока пользователь не завершит цикл, введя ответ - N. ============== SQL КОДЫ =================== Хорошо было бы знать, когда данные будут исчерпаны, так чтобы можно было сообщить об этом пользователю и цикл завершился бы автоматически. Это - даже более важно чем например знать что команда SQL выполнена с ошибкой. Переменная SQLCODE (называемая еще SQLCOD в ФОРТРАНе) пред- назначена чтобы обеспечить эту функцию. Она должна быть определена как переменная главного языка и должна иметь тип данных который в главном языке соответствует одному из точных числовых типов SQL, как это пока- зано в Приложении B. Значение SQLCODE устанавливается каждый раз, ког- да выполняется команда SQL. В основном существуют три возможности: 1. Команда выполнилась без ошибки, но не произвела никакого действия. Для различных команд это выглядит по разному: а) Для SELECT, ни одна строка не выбрана запросом. б) Для FETCH, последняя строка уже была выбрана, или ни одной строки не выбрано запросом в курсоре. в) Для INSERT, ни одной строки не было вставлено (подразумевается что запрос использовался чтобы сгенерировать значения для вставки, и был отвергнут при попытке извлечения любой строки. г) Для UPDATE и DELETE, ни одна строка не ответила условию преди- ката, и следовательно никаких изменений сделано в таблице не будет. В любом случае, будет установлен код SQLCODE = 100. 2. Команда выполнилась нормально, не удовлетворив ни одному из выше указанных условий. В этом случае, будет установлен код SQLCOD = 0. 3. Команда сгенерировала ошибку. Если это случилось, изменения сделан- ные к базе данных текущей транзакцией, будут восстановлены(см. Главу 23). В этом случае будет установлен код SQLCODE = некоторому отрицательному числу, определяемому проектировщиком. Задача этого числа, идентифицировать проблему, так точно насколько это возможно. В принципе, ваша система должна быть снабжена подпрограммой, кото- рая в этом случае, должна выполниться чтобы выдать для вас информа- цию расшифровывающее значение негативного числа определенного вашим проектировщиком. В этом случае некоторое сообщение об ошибке будет выведено на экран или записано в файл протокола, а программа в это время выполнит восстановление изменений для текущей транзакции, отключится от базы данных и выйдет из нее. Теперь мы можем усовер- шенствовать ИСПОЛЬЗОВАНИЕ SQLCODE ДЛЯ УПРАВЛЕНИЯ ЦИКЛАМИ Наш предыдущий пример для выхода из цикла автоматически, при условии что курсор пуст, все строки выбраны, или произошла ошибка: Look_at_more: = lhe; EXEC SQL OPEN CURSOR Londonsales; while Look_at_more and SQLCODE = O do begin EXEC SQL FETCH London$ales INTO:id_num,:Salesperson,:loc,:comm; writeln (id_num, Salesperson, loc, comm); writeln ('Do you want to see more data? (Y/N)'); readln (response); If response = 'N' then Look_at_more: = Fabe; end; EXEC SQL CLOSE CURSOR Londonsales; ПРЕДЛОЖЕНИЕ WHENEVER Это удобно для выхода при выполненом условии - все строки выбраны. Но если вы получили ошибку, вы должны предпринять нечто такое, что описано для третьего случая, выше. Для этой цели, SQL предоставляет предложение GOTO. Фактически, SQL позволяет вам применять его доста- точно широко, так что программа может выполнить команду GOTO автомати- чески, если будет произведено определенное значение SQLCODE. Вы можете сделать это соввместно с предложением WHENEVER. Имеется кусгок из при- мера для этого случая: EXEC SQL WHENEVER SQLERROR GOTO Error_handler; EXEC SQL WHENEVER NOT FOUND CONTINUE; SQLERROR- это другой способ сообщить что SQLCODE < 0; а NOT FOUND - это другой способ сообщить что SQLCODE = 100. (Некоторые реализации называют последний случай еще как - SQLWARNING.) Error_handler - это имя того места в программе в которое будет перенесено выполнение прог- раммы если произошла ошибка (GOTO может состоять из одного или двух слов). Такое место определяется любым способом соответствующим для главного языка, например, с помощью метки в Паскале, или имени раздела или имени параграфа в КОБОЛЕ (в дальнейшем мы будем использовать тер- мин - метка). Метка более удачно идентифицирует стандартную процедуру распространяемую проектировщиком для включения во все программы. CONTINUE не делает чего-то специального для значения SQLCODE. Оно также является значением по умолчанию. если вы не используете команду WHENEVER, определяющую значение SQLCODE. Однако, эти неактивные опре- деления дают вам возможность переключаться вперед и назад, выполняя и не выполняя действия, в различных точках(метках) вашей программы. Нап- ример, если ваша программа включает в себя несколько команд INSERT, использующих запросы, которые реально должны производить значения, вы могли бы напечатать специальное сообщение или сделать что-то такое, что поясняло бы, что запросы возвращаются пустыми и никакие значения не были вставлены. В этом случае, вы можете ввести следующее: EXEC SQL WHENEVER NOT FOUND GOTO No_rows; No_rows - это метка в некотором коде, содержащем определенное дейс- твие. С другой стороны, если вам нужно сделать выборку в программе позже, вы можете ввести следующее в этой точке, EXEC SQL WHENEVER NOT FOUND CONTINUE; что бы выполнение выборки повторялось до тех пор пока все строки не будут извлечены, что является нормальной процедурой не требующей спе- циальной обработки. ========= МОДИФИЦИРОВАНИЕ КУРСОРОВ ======== Курсоры могут также быть использованы, чтобы выбирать группу строк из таблицы, которые могут быть затем модифицированы или удалены одна за другой. Это дает вам возможность, обходить некоторые ограничения предикатов используемых в командах UPDATE и DELETE. Вы можете ссылать- ся на таблицу задействованную в предикате запроса курсора или любом из его подзапросов, которые вы не можете выполнить в предикатах самих этих команд. Как подчеркнуто в Главе 16, стандарт SQL отклоняет попыт- ку удалить всех пользователей с рейтингом ниже среднего, в следующей форме: EXEC SQL DELETE FROM Customers WHERE rating < (SELECT AVG (rating) FROM Customers); Однако, вы можете получить тот же эффект, используя запрос для выбора соответствующих строк, запомнив их в курсоре, и выполнив DELETE с ис- пользованием курсора. Сначала вы должны обьявить курсор: EXEC SQL DECLARE Belowavg CURSOR FOR SELECT * FROM Customers WHERE rating < (SELECT AVG (rating) FROM Customers); Затем вы должны создать цикл, чтобы удалить всех заказчиков выбранных курсором: EXEC SQL WHENEVER SQLERROR GOTO Error_handler; EXEC SQL OPEN CURSOR Belowavg; while not SOLCODE = 100 do begin EXEC SOL FETCH Belowavg INTO:a,:b,:c,:d,:e; EXEC SOL DELETE FROM Customers WHERE CURRENT OF Belowavg; end; EXEC SOL CLOSE CURSOR Belowavg; Предложение WHERE CURRENT OF означает что DELETE применяется к стро- ке которая в настоящее время выбрана курсором. Здесь подразумевается, что и курсор и команда DELETE, ссылаются на одну и ту же таблицу, и следовательно, что запрос в курсоре - это не обьединение. Курсор должен также быть модифицируемым. Являясь модифицируемым, курсор должен удовлетворять тем же условиям что и представления (см. Главу 21). Кроме того, ORDER BY и UNION, которые не разрешены в представлениях, в курсорах - разрешаются, но предохраняют курсор от модифицируемости. Обратите внимание в вышеупомянутом примере, что мы должны выбирать строки из курсора в набор переменных, даже если мы не собирались использовать эти переменные. Этого требует синтаксис коман- ды FETCH. UPDATE работает так же. Вы можете увеличить значение комис- сионных всем продавцам, которые имеют заказчиков с оценкой=300, следу- ющим способом. Сначала вы обьявляете курсор: EXEC SOL DECLARE CURSOR High_Cust AS SELECT * FROM Salespeople WHERE snum IN (SELECT snum FROM Customers WHERE rating = 300); Затем вы выполняете модификации в цикле: EXEC SQL OPEN CURSOR High_cust; while SQLCODE = 0 do begin EXEC SOL FETCH High_cust INTO:id_num,:salesperson,:loc,:comm; EXEC SQL UPDATE Salespeople SET comm = comm +.01 WHERE CURRENT OF High_cust; end; EXEC SQL CLOSE CURSOR High_cust; Обратите внимание: что некоторые реализации требуют, чтобы вы указы- вали в определении курсора, что курсор будет использоваться для выпол- нения команды UPDATE на определенных столбцах. Это делается с помощью заключительной фразы определения курсора - FOR UPDATE. Чтобы обьявить курсор High_cust таким способом, так чтобы вы могли мо- дифицировать командой UPDATE столбец comm, вы должны ввести следующее предложение: EXEC SQL DECLARE CURSOR High_Cust AS SELECT * FROM Salespeople WHERE snum IN (SELECT snum FROM Customers WHERE rating = 300) FOR UPDATE OF comm; Это обеспечит вас определенной защитой от случайных модификаций, кото- рые могут разрушить весь порядок в базе данных. ========= ПЕРЕМЕННАЯ INDICATOR ========== Пустые (NULLS) значения - это специальные маркеры определяемые самой SQL. Они не могут помещаться в главные переменные. Попытка вставить NULL значения в главнуюпеременную будет некорректна, так как главные языки не поддерживают NULL значений в SQL, по определению. Хотя ре- зультат при попытке вставить NULL значение в главную переменную опре- деляет проектировщик, этот результат не должен ротиворечить теории ба- зы данных, и поэтому обязан произвести ошибку: код SQLCODE ввиде отри- цательного числа, и вызвать подпрограмму управления ошибкой. Естеcт- венно вам нужно этого избежать. Поэтому, вы можете выбрать NULL значе- ния с допустимыми значениями, не приводящими к разрушению вашей прог- раммы. Даже если программа и не разрушится, значения в главных пере- менных станут неправильными, потому что они не могут иметь NULL значе- ний. Альтернативным методом предоставляемым для этой ситуацией являет- ся - функция переменной indicator(указатель). Переменная indicator - обьявленная в разделе объявлений SQL напоминает другие переменные. Она может иметь тип главного языка который соответствует числовому типу в SQL. Всякий раз, когда вы выполняете операцию, которая должна помес- тить NULL значение в переменную главного языка, вы должны использовать переменную indicator, для надежности. Вы помещаете переменную indica- tor в команду SQL непосредственно после переменной главного языка ко- торую вы хотите защитить, без каких-либо пробелов или запятых, хотя вы и можете, при желании, вставить слово - INDICATOR. Переменной indica- tor в команде, изначально присваивается значение 0. Однако, если про- изводится значение NULL, переменная indicator становится равной отри- цательному числу. Вы можете проверить значение переменной indicator, чтобы узнать, было ли найдено значение NULL. Давайте предположим, что поля city и comm, таблицы Продавцов, не имеют ограничения NOT NULL, и что мы объявили вразделе обьявлений SQL, две ПАСКАЛЬевские переменные целого типа, i_a и i_b. (Нет ничего такого в разделеобьявлений, что могло бы представить их как переменные indicator. Они станут перемен- ными indicator, когда будут использоваться как переменные indicator.) Имеется одна возможность: EXEC SQL OPEN CURSOR High_cust; while SQLCODE = O do begin EXEC SQL FETCH High_cust INTO:id_num,:salesperson,:loc:i_a,:commINDlCATOR:i_b; If i_a > = O and i_b > = O then {no NULLs produced} EXEC SQL UPDATE Salespeople SET comm = comm +.01 WHERE CURRENT OF Hlgh_cust; else {one or both NULL} begin If i_a < O then writeln ('salesperson ', id_num, ' has no city'); If i_b < O then writeln ('salesperson ', id_num, ' has no commission'); end; {else} end; {while} EXEC SQL CLOSE CURSOR High_cust; Как вы видите, мы включили, ключевое слово INDICATOR в одном случае, и исключили его в другом случае, чтобы показать, что эффект будет оди- наковым в любом случае. Каждая строка будет выбрана, но команда UPDATE выполнится только если NULL значения не будут обнаружены. Если будут обнаружены NULL значения, выполнится еще одна часть программы, которая распечатает предупреждающее сообщение, где было найдено каждое NULL значение. Обратите внимание: переменные indicator должны проверяться в главном языке, как указывалось выше, а не в предложении WHERE команды SQL. Последнее в принципе не запрещено, но результат часто бывает непред- виденным. ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННОЙ INDICATOR ДЛЯ ЭМУЛЯЦИИ NULL ЗНАЧЕНИЙ SQL Другая возможность состоит в том, чтобы обрабатывать переменную in- dicator, связывая ее с каждой переменной главного языка, специальным способом, эмулирующим поведение NULL значений SQL. Всякий раз, когда вы используете одно из этих значений в вашей прог- рамме, например в предложении if... then, вы можете сначала проверить связанную переменную indicator, является ли ее значение=NULL. Если это так, то вы обрабатываете переменную по-другому. Например, если NULL значение было извлечено из поля city для главной переменной city, ко- торая связана с переменной indicator - i_city, вы должны установить значение city равное последовательности пробелов. Это будет необходи- мо, только если вы будете распечатывать его на принтере; его значение не должно отличаться от логики вашей программы. Естественно, i_city автоматически установливается в отрицательное значение. Предположим, что вы имели следующую конструкцию в вашей программе: If sity = 'London' then comm: = comm +.01 else comm: = comm -.01 Любое значение, вводимое в переменную city, или будет равно "London" или не будет равно. Следовательно, в каждом случае значение комиссион- ных будет либо увеличино либо уменьшено. Однако, эквивалентные команды в SQL выполняются по разному: EXEC SQL UPDATE Salespeople SET comm = comm +.01 WHERE sity = 'London'; и EXEC SQL UPDATE Salespeople SET comm = comm.01; WHERE sity < > 'London'; (Вариант на ПАСКАЛе работает только с единственным значением, в то время как вариант на SQL работает со всеми таблицами.) Если значение city в варианте на SQL будет равно значению NULL, оба предиката будут неизвестны, и значение comm, следовательно, не будет изменено в любом случае. Вы можете использовать переменную indicator чтобы сделать поведение вашего главного языка непротиворечащим этому, с помощью создания усло- вия, которое исключает NULL значения: If i_city > = O then begin If city = 'London' then comm: = comm +.01 else comm: = comm -.01; end; {begin and end нужны здесь только для понимания} -------------------------------------------------------------- ПРИМЕЧАНИЕ: Последняя строка этого примера сожержит ремарку - { begin и end необходимы только для понимания } -------------------------------------------------------------- В более сложной программ, вы можете захотеть установить Булеву пере- менную в "верно", чтобы указать что значение city =NULL. Затем вы мо- жете просто проверять эту переменную всякий раз, когда вам это необхо- димо. ДРУГОЕ ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННОЙ INDICATOR Переменная indicator также может использоваться для назначения зна- чения NULL. Просто добавьте ее к имени главной переменной в команде UPDATE или INSERT тем же способом что и в команде SELECT. Если пере- менная indicator имеет отрицательное значение, значение NULL будет по- мещено в поле. Например, следующая команда помещает значения NULL в поля city и comm, таблицы Продавцов, всякий раз, когда переменные in- dicator - i_a или i_b будут отрицательными; в противном случае она по- мещает туда значения главных переменных: EXEC SQL INSERT INTO Salespeople VALUES (:Id_num,:salesperson,:loc:i_a,:comm:i_b); Переменная indicator используется также, чтобы показывать отбрасыва- емую строку. Это произойдет если вы вставляете значения символов SQL в главную переменную которая не достаточно длинна чтобы вместить все символы. Это особая проблема с нестандартным типами данных - VARCHAR и LONG (смотри Приложению C). В этом случае, переменная будет заполне- на первыми символами строки, а последние символы будут потеряны. Если используется переменная indicator, она будет установлена в положитель- ное значение, указывающее на длину отбрасываемой части строки, позво- ляя таким образом вам узнать, сколько символов было потеряно. В этом случае, Вы можете проверить с помощью просмотра -значение пе- ременной indicator > 0, или < 0. ================ РЕЗЮМЕ ================= Команды SQL вкладываются в процедурные языках, чтобы объединить силы двух подходов. Некоторые дополнительные средства SQL необходимы, чтобы выполнить эту работу. Вложенные команды SQL транслируемые программой, называемой прекомпилятором, в форму пригодную для использования транс- лятором главного языка, и используемые в этом главном языке, как вызо- вы процедуры к подпрограммам которые создает прекомпилятор, называются - модулями доступа. ANSI поддерживает вложение SQL в языки: ПАСКАЛЬ, ФОРТРАН, КОБОЛ, и PL/I. Другие языки также используются, особенно Си. В попытке кратко описать вложенный SQL, имеются наиболее важные места в этой главе: * Все вложенные команды SQL начинаются словами EXEC SQL и заканчивают- ся способом который зависит от используемого главного языка. * Все главные переменные доступные в командах SQL, должны быть обьяв- лены в разделе объявлений SQL прежде, чем они будут использованы. * Всем главным переменным должно предшествовать двоеточие когда они используются в команде SQL. * Запросы могут сохранять свой вывод непосредственно в главных пере- менных, используя предложение INTO, если и только если, они выбирают единственную строку. * Курсоры могут использоваться для сохранения вывода запроса, и досту- па к одной строке в каждый момент времени. Курсоры бывают обьявлены- ми (если определяют запрос в котором будут содержаться), открытыми(если выполняют запрос), и закрытыми (если удаляют вывод запроса из курсора). Если курсор открыт, команда FETCH, используется чтобы пе- ремещать его по очереди к каждой строке вывода запроса. * Курсоры являются модифицируемыми или только-чтение. Чтобы стать мо- дифицируемым, курсор должен удовлетворять всем критериям которым удовлетворяет просмотр; кроме того, он не должен использовать пред- ложений ORDER BY или UNION, которые в любом случае не могут исполь- зоваться просмотрами. Не модифицируемый курсор является курсором только-чтение. * Если курсор модифицируемый, он может использоваться для определения, какие строки задействованы вложенными командами UPDATE и DELETE че- рез предложение WHERE CURRENT OF. DELETE или UPDATE должны быть вне той таблицы к которой курсор обращается в запросе. * SQLCODE должен быть обьявлен как переменная числового типа для каж- дой программы которая будет использовать вложенный SQL. Его значение установливается автоматически после выполнения каждой команды SQL. * Если команда SQL выполнена как обычно, но не произвела вывода или ожидаемого изменения в базе данных, SQLCODE = 100. Если команда про- извела ошибку, SQLCODE будет равняться некоторому аппаратноопреде- ленному отрицательному числу которое описывает ошибку. В противном случае, SQLCODE = 0. * Предложение WHENEVER может использоваться для определения действия которое нужно предпринять когда SQLCODE = 100 (не найдено) или когда SQLCODE равен отрицательному числу (SQLERROR). Действием может быть или переход к некоторой определенной метке в программе (GOTO) или отсутствие какого-либо действия вообще(продолжить). Последнее, установлено по умолчанию. * Числовые переменные могут также использоваться как переменные indi- cator. Переменные indicator следуют за другим именами переменных в команде SQL, без каких бы то ни было посторонних символов кроме (не- обязательного) слова INDICATOR. * Обычно, значение переменной indicator = 0. Если команда SQL пытается поместить NULL значение в главную переменную которая использует in- dicator, indicator будет установлен в отрицательное значение. Этот факт можно использоваться чтобы предотвращать ошибки, и для помеча- ния NULL значений SQL для специальной обработки их в главной прог- рамме. * Переменная indicator может использоваться для вставки NULL значений в команды SQL - INSERT или UPDATE. Она также может принимать положи- тельное значение указывающее на длинну отбрасываемой части строки не поместившейся в предельные границы какойнибудь переменной, куда эта строка помещалась. ************ РАБОТА С SQL *************** Обратите внимание: Ответы для этих упражнений написаны в псевдоко- дах, являющихся английским языком описания логики, которой должна сле- довать программа. Это сделано для того чтобы помомочь читателям кото- рые могут быть незнакомы с Паскалем(или любым другим языком). Кроме того это лучше сфокусирует ваше внимание на включемых понятиях, опус- кая частности того или другого языка. Чтобы непротиворечить нашим при- мерам, стиль псевдокода будет напоминать Паскаль. Мы опустим из программ все что не относится напрямую к рассматривае- мым вопросам, например, определение устройств ввода-вывода, подключе- ние к базе данных, и так далее. Конечно, имеется много способов чтобы выполнять такие упражнения; и совсем не обязательно что представленые варианты решений являются самыми удачными. 1. Разработайте простую программу которая выберет все комбинации полей snum и cnum из таблиц Порядков и Заказчиков и выясните, всегда ли предыдущая комбинация такая же как последующая. Если комбинация из таблицы Порядков не найдена в таблице Заказчиков, значение поля snum для этой строки будет изменено на удовлетворяющее условию сов- падения. Вы должны помнить, что курсор с подзапросом - модифицируем (ANSI ограничение, также применимо к просмотрам, и что базисная целостность базы данных это не тоже самое что проверка на ошибку(т.е. первичные ключи уникальны, все поля cnums в таблице Порядков правильны, и так далее). Проверьте раздел обьявлений, и убедитесь что там обьявлены все используемые курсоры. 2. Предположим, что ваша программа предписывает ANSI запрещение курсо- ры или просмотры использующие модифицируемые подзапросы. Как вы должны изменить вышеупомянутую программу? 3. Разработайте программу которая подсказывает пользователям изменить значения поля city продавца, автоматически увеличивает комиссионные на.01 для продавца переводимого в Барселону и уменьшает их на.01 для продавца переводимого в Сан Хосе. Кроме того, продавец находя- щийся в Лондоне должен потерять.02 из своих комиссионных, незави- симо от того меняет он город или нет, в то время как продавец не находящийся в Лондоне должен иметь увеличение комиссионных на.02. Изменение в комиссионных основывающееся на нахождении продавца в Лондоне, может применяться независимо от того куда тот переводится. Выясните могут ли поле city или поле comm содержать NULL значения, и обработайте их, как это делается в SQL. Предупреждение!: эта программа имеет некоторые сокращения. Приложение A. -------------- ОТВЕТЫ ДЛЯ УПРАЖНЕНИЙ Глава 1. 1. cnum 2. rating 3. Другим словом для строки является - запись. Другим словом для столбца является - поле. 4. Потому что строки, по определению, находятся без какого либо опре- деленного упорядочения. Глава 2. 1. Символ (или текст) и номер 2. Нет 3. Язык Манипулирования Данными (ЯЗЫК DML) 4. Это слово в SQL имеет специальное учебное значение. Глава 3. 1. SELECT onum, amt, odate FROM Orders; 2. SELECT * FROM Customers WHERE snum = 1001; 3 SELECT city, sname, snum, comm FROM Salespeople; 4. SELECT rating, cname FROM Customers WHERE city = 'SanJose'; 5. SELECT DISTINCT snum FROM Orders; Глава 4. 1. SELECT * FROM Orders WHERE amt > 1000; 2. SELECT sname, city FROM Salespeople WHERE city = 'London' AND comm >.10; 3. SELECT * FROM Customers WHERE rating > 100 OR city = 'Rome'; или SELECT * FROM Customers WHERE NOT rating < = 100 OR city = 'Rome'; или SELECT * FROM Customers WHERE NOT (rating < = 100 AND city < > 'Rome'); Могут быть еще другие решения. 4. onum amt odate cnum snum 3001 18.69 10/03/1990 2008 1007 3003 767.19 10/03/1990 2001 1001 3005 5160.45 10/03/1990 2003 1002 3009 1713.23 10/04/1990 2002 1003 3007 75.75 10/04/1990 2004 1002 3008 4723.00 10/05/1990 2006 1001 3010 1309.95 10/06/1990 2004 1002 3011 9891.88 10/06/1990 2006 1001 5. onum amt odate cnum snum 3001 18.69 10/03/1990 2008 1007 3003 767.19 10/03/1990 2001 1001 3006 1098.16 10/03/1990 2008 1007 3009 1713.23 10/04/1990 2002 1003 3007 75.75 10/04/1990 2004 1002 3008 4723.00 10/05/1990 2006 1001 3010 1309.95 10/06/1990 2004 1002 3011 9891.88 10/06/1990 2006 1001 6. SELECT * FROM Salespeople; Глава 5. 1. SELECT * FROM Orders WHERE odate IN (10/03/1990,10/04/1990); и SELECT * FROM Orders WHERE odate BETWEEN 10/03/1990 AND 10/04,1990; 2. SELECT * FROM Customers WHERE snum IN (1001,1004); 3. SELECT * FROM Customers WHERE cname BETWEEN 'A' AND 'H'; ПРИМЕЧАНИЕ: В ASCII базовой системе Hoffman не буде выведен из-за ко- нечных пробелов после H. По той же самой причине воторая граница не может быть G, поскольку она не выведет имена Giovanni и Grass. G может использоваться в сопровождении с Z, так чтобы следовать за другими символами в алфавитном порядке, а не предшествовать им, как это делают пробелы. 4. SELECT * FROM Customers WHERE cname LIKE 'C%'; 5. SELECT * FROM Orders WHERE amt < > O AND (amt IS NOT NULL); или SELECT * FROM Orders WHERE NOT (amt = O OR amt IS NULL); Глава 6. 1. SELECT COUNT(*) FROM Orders WHERE odate = 10/03/1990; 2. SELECT COUNT (DISTINCT city) FROM Customers; 3. SELECT cnum, MIN (amt) FROM Orders GROUP BY cnum; 4 SELECT MIN (cname) FROM Customers WHERE cname LIKE 'G%'; 5. SELECT city, MAX (rating) FROM Customers GROUP BY city; 6 SELECT odate, count (DISTINCT snum FROM Orders GROUP BY odate; Глава 7. 1. SELECT onum, snum, amt *.12 FROM Orders; 2. SELECT 'For the city ', city, ', the highest rating is ', ", MAX (rating) FROM Customers GROUP BY city; 3 SELECT rating, cname, cnum FROM Customers ORDER BY rating DESC; 4. SELECT odate, SUM (amt) FROM Orders GROUP BY odate ORDER BY 2 DESC; Глава 8. 1. SELECT onum, cname FROM Orders, Customers WHERE Customers.cnum = Orders.cnum; 2. SELECT onum, cname, sname FROM Orders, Customers, Salespeople WHERE Customers.cnum = Orders.cnum AND Salespeople.snum = Orders.snum; 3. SELECT cname, sname, comm FROM Salespeople, Customers WHERE Salespeople.snum = Customers.snum AND comm *.12; 4. SELECT onum, comm * amt FROM Salespeople, Orders, Customers WHERE rating > 100 AND Orders.cnum = Customers.cnum AND Orders.snum = Salespeople.snum; Глава 9. 1. SELECT first.sname, second.sname FROM Salespeople first, Salespeople second WHERE first.city = second.city AND first.sname < second.sname; Псевдонимам нет необходимости иметь именно такие имена. 2. SELECT cname, first.onum, second.onum FROM Orders first, Orders second, Customers WHERE first.cnum = second.cnum AND first.cnum = Customers.cnum AND first.onum < second.onum; Ваш вывод может иметь некоторые отличия, но в вашем ответе все логи- ческие компоненты должны быть такими же. 3. SELECT a.cname, a.city FROM Customers a, Customers b WHERE a.rating = b.rating AND b.cnum = 2001; Глава 10. 1. SELECT * FROM Orders WHERE cnum = (SELECT cnum FROM Customers WHERE cname = 'Cisneros'); или SELECT * FROM Orders WHERE cnum IN (SELECT cnum FROM Customers WHERE cname = 'Cisneros'); 2. SELECT DISTINCT cname, rating FROM Customers, Orders WHERE amt > (SELECT AVG (amt) FROM Orders) AND Orders.cnum = Customers.cnum; 3. SELECT snum, SUM (amt) FROM Orders GROUP BY snum HAVING SUM (amt) > (SELECT MAX (amt) FROM Orders); Глава 11. 1. SELECT cnum, cname FROM Customers outer WHERE rating = (SELECT MAX (rating) FROM Customers inner WHERE inner.city = outer.city); 2. Решение с помощью соотнесенного подзапроса: SELECT snum, sname FROM Salespeople main WHERE city IN (SELECT city FROM Customers inner WHERE inner.snum < > main.snum); Решение с помощью объединения: SELECT DISTINCT first.snum, sname FROM Salespeople first, Customers second WHERE first.city = second.city AND first.snum < > second.snum; Соотнесенный подзапрос находит всех заказчиков не обслуживаемых данным продавцом и выясняет: живет ли кто-нибудь из их в его городе. Решение с помощью обьединения является более простым и более интуитивным. Оно находит случаи где поля city совпадают, а поля snums нет. Следователь- но обьединение является более изящным решением для этой проблемы, чем то которое мы исследовали до этого. Имеется еще более изящное решение с помощью подзапроса, с которым Вы столкнетесь позже. Глава 12. 1. SELECT * FROM Salespeople first WHERE EXISTS (SELECT * FROM Customers second WHERE first.snum = second.snum AND rating = 300); 2. SELECT a.snum, sname, a.city, comm FROM Salespeople a, Customers b WHERE a.snum = b.snum AND b.rating = 300; 3. SELECT * FROM Salespeople a WHERE EXISTS (SELECT * FROM Customers b WHERE b.city = a.city AND a.snum < > b.snum); 4. SELECT * FROM Customers a WHERE EXISTS (SELECT * FROM Orders b WHERE a.snum = b.snum AND a.cnum < > b.cnum) Глава 13. 1. SELECT * FROM Customers WHERE rating > = ANY (SELECT rating FROM Customers WHERE snum = 1002); 2. cnum cname city rating snum 2002 Giovanni Rome 200 1003 2003 Liu San Jose 200 1002 2004 Grass Berlin 300 1002 2008 Cisneros SanJose 300 1007 3. SELECT * FROM Salespeople WHERE city < > ALL (SELECT city FROM Customers); или SELECT * FROM Salespeople WHERE NOT city = ANY (SELECT city FROM Customers); 4. SELECT * FROM Orders WHERE amt > ALL (SELECT amt FROM Orders a, Customers b WHERE a.cnum = b.cnum AND b.city = 'London'); 5. SELECT * FROM Orders WHERE amt > (SELECT MAX (amt) FROM Orders a, Customers b WHERE a.cnum = b.cnum AND b.city = 'London'); Глава 14. 1. SELECT cname, city, rating, 'High Rating' FROM Customers WHERE rating > = 200 UNION SELECT cname, city, rating, ' Low Ratlng' FROM Customers WHERE rating < 200; или SELECT cname, city, rating, 'High Rating' FROM Customers WHERE rating > = 200 UNION SELECT cname, city, rating, ' Low Rating' FROM Customers WHERE NOT rating > = 200; Различие между этими двумя предложениями, в форме второго предиката. Обратите внимание что, в обоих случаях, строка "Low Rating" имеет в начале дополнительный пробел для того чтобы совпадать со строкой "High Rating" по длине. 2. SELECT cnum, cname FROM Customers a WHERE 1 < (SELECT COUNT (-) FROM Orders b WHERE a.cnum = b.cnum) UNION SELECT snum, sname FROM Salespeople a WHERE 1 < (SELECT COUNT (*) FROM Orders b WHERE a.snum = b.snum) ORDER BY 2; 3. SELECT snum FROM Salespeople WHERE city = 'San Jose' UNION (SELECT cnum FROM Customers WHERE city = 'San Jose' UNION ALL SELECT onum FROM Orders WHERE odate = 10/03/1990); Глава 15. 1. INSERT INTO Salespeople (city, cname, comm, cnum) VALUES ('San Jose', 'Blanco', NULL, 1100); 2. DELETE FROM Orders WHERE cnum = 2006; 3. UPDATE Customers SET rating = rating + 100 WHERE city = 'Rome'; 4. UPDATE Customers SET snum = 1004 WHERE snum = 1002; Глава 16. 1. INSERT INTO Multicust SELECT * FROM Salespeople WHERE 1 < (SELECT COUNT (*) FROM Customers WHERE Customers.snum = Salespeople.snum); 2. DELETE FROM Customers WHERE NOT EXISTS (SELECT * FROM Orders WHERE cnum = Customers.cnum); 3. UPDATE Salespeople SET comm = comm + (comm *.2) WHERE 3000 < (SELECT SUM (amt) FROM Orders WHERE snum = Salespeople.snum); В более сложный вариант этой команды можно было бы вставить проверку чтобы убедиться, что значения комиссионных не превышают 1.0 (100 %): UPDATE Salespeople SET comm = comm + (comm *.2) WHERE 3000 < (SELECT SUM (amt) FROM Orders WHERE snum = Salespeople.snum) AND comm + (comm *.2) < 1.0; Эти проблемы могут иметь другие, такие же хорошие решения. Глава 17. 1. CREATE TABLE Customers (cnum integer, cname char(10), city char(10), rating integer, snum integer); 2. CREATE INDEX Datesearch ON Orders(odate); (Все индексные имена используемые в этих ответах - произвольные.) 3. CREATE UNIQUE INDEX Onumkey ON Orders(onum); 4. CREATE INDEX Mydate ON Orders(snum, odate); 5. CREATE UNIQUE INDEX Combination ON Customers(snum, rating); Глава 18. 1. CREATE TABLE Orders (onum integer NOT NULL PRIMARY KEY, amt decimal, odate date NOT NULL, cnum integer NOT NULL, snum integer NOT NULL, UNIOUE (snum, cnum)); или CREATE TABLE Orders (onum integer NOT NULL UNIQUE, amt decimal, odate date NOT NULL, cnum integer NOT NULL, snum integer NOT NULL, UNIQUE (snum, cnum)); Первое решение предпочтительнее. 2. CREATE TABLE Salespeople (snum integer NOT NULL PRIMARY KEY, sname char(15) CHECK (sname BETWEEN 'AA' AND 'MZ'), city char(15), comm decimal NOT NULL DEFAULT =.10); 3. CREATE TABLE Orders (onum integer NOT NULL, amt decimal, odate date, cnum integer NOT NULL, snum integer NOT NULL, CHECK ((cnum > snum) AND (onum > cnum))); Глава 19. 1. CREATE TABLE Cityorders (onum integer NOT NULL PRIMARY KEY, amt decimal, cnum integer, snum integer, city char (15), FOREIGN KEY (onum, amt, snum) REFERENCES Orders (onum, amt, snum), FOREIGN KEY (cnum, city) REFERENCES Customers (cnum, city)); 2. CREATE TABLE Orders (onum integer NOT NULL, amt decimal, odate date, cnum integer NOT NULL, snum integer, prev integer, UNIQUE (cnum, onum), FOREIGN KEY (cnum, prev) REFERENCES Orders (cnum,onum));9 Глава 20. 1. CREATE VIEW Highratings AS SELECT * FROM Customers WHERE rating = (SELECT MAX (rating) FROM Customers); 2. CREATE VIEW Citynumber AS SELECT city, COUNT (DISTINCT snum) FROM Salespeople GROUP BY city; 3. CREATE VIEW Nameorders AS SELECT sname, AVG (amt), SUM (amt) FROM Salespeople, Orders WHERE Salespeople.snum = Orders.snum GROUP BY sname; 4. CREATE VIEW Multcustomers AS SELECT * FROM Salespeople a WHERE 1 < (SELECT COUNT (*) FROM Customers b WHERE a.snum = b.snum); Глава 21. 1. #1 - не модифицируемый, потому что он использует DISTINCT. #2 - не модифицируемый, потому что он использует обьединение, агре- гатную функцию, и GROUP BY. #3 - не модифицируемый, потому что он основывается на #1, который сам по себе не модифицируемый. 2. CREATE VIEW Commissions AS SELECT snum, comm FROM Salespeople WHERE comm BETWEEN.10 AND.20 WITH CHECK OPTION; 3. CREATE TABLE Orders (onum integer NOT NULL PRIMARY KEY, amt decimal, odate date DEFAULT VALUE = CURDATE, snum integer, cnum integer); CREATE VIEW Entryorders AS SELECT onum, amt, snum, cnum FROM Orders; Глава 22. 1. GRANT UPDATE (rating) ON Customers TO Janet; 2. GRANT SELECT ON Orders TO Stephen WITH GRANT OPTION; 3. REVOKE INSERT ON Salespeople FROM Claire; 4. Шаг 1: CREATE VIEW Jerrysview AS SELECT * FROM Customers WHERE rating BETWEEN 100 AND 500 WITH CHECK OPTION; Шаг 2: GRANT INSERT, UPDATE ON Jerrysview TO Jerry; 5. Шаг 1: CREATE VIEW Janetsview AS SELECT * FROM Customers WHERE rating = (SELECT MIN (rating) FROM Customers); Шаг 2: GRANT SELECT ON Janetsview TO Janet; Глава 23. 1. CREATE DBSPACE Myspace (pctindex 15, pctfree 40); 2. CREATE SYNONYM Orders FOR Diane.Orders; 3. Они должны быть откатаны обратно назад. 4. Блокировка взаимоисключающего доступа. 5. Толко чтение Глава 24. 1. SELECT a.tname, a.owner, b.cname, b.datatype FROM SYSTEMCATOLOG a, SYSTEMCOLUMNS b WHERE a.tname = b.tname AND a.owner = b.owner AND a.numcolumns > 4; Обратите Внимание: из-за того что большинство имен столбца объединяе- мых таблиц - различны, не все из используемых псевдонимов a и b в вы- шеупомянутой команде - строго обязательны. Они представлены просто для понимания. 2. SELECT tname, synowner, COUNT (ALL synonym) FROM SYTEMSYNONS GROUP BY tname, synowner; 3. SELECT COUNT (*) FROM SYSTEMCATALOG a WHERE numcolumns/2 < (SELECT COUNT (DISTINCT cnumber) FROM SYSTEMINDEXES b WHERE a.owner = b.tabowner AND a.tname = b.tname); Глава 25. 1. EXEC SQL BEGIN DECLARE SECTION; SQLCODE:integer; {требуемый всегда} cnum integer; snum integer; custnum: integer; salesnum: integer; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE Wrong_Orders AS CURSOR FOR SELECT cnum, snum FROM Orders a WHERE snum < > (SELECT snum FROM Customers b WHERE a.cnum = b.cnum); { Мы пока еще используем здесь SQL для выполнения основной работы. Запрос выше размещает строки таблицы Порядков которые не согласуются с таблицей Заказчиков. } EXEC SQL DECLARE Cust_assigns AS CURSOR FOR SELECT cnum, snum FROM Customers; {Этот курсор используется для получения правильных значений snum} begin { основная программа } EXEC SQL OPEN CURSOR Wrong_Orders; while SQLCODE = O do {Цикл до тех пор пока Wrong_Orders не опустеет} begin EXEC SQL FETCH Wrong_Orders INTO (:cnum,:snum); if SQLCODE = O then begin {Когда Wrong_Orders опустеет, мы не хотели бы продолжать выполнение этого цикла до бесконечности} EXEC SQL OPEN CURSOR Cust_Assigns; repeat EXEC SQL FETCH Cust_Assigns INTO (:custnum,:salesnum); until:custnum =:cnum; {Повторять FETCH до тех пор пока... команда будет просматривать Cust_Assigns курсор до строки которая соответствует текущему значению cnum найденого в Wrong_Orders} EXEC SQL CLOSE CURSOR Cust_assigns; {Поэтому мы будем начинать новый вывод в следующий раз через цикл. Значение в котором мы получим из этого курсора сохраняется в переменной - salesnum.} EXEC SQL UPDATE Orders SET snum =:salesnum WHERE CURRENT OF Wrong_Orders; end; {Если SQLCODE = 0}. end; { Пока SQLCODE... выполнить } EXEC SQL CLOSE CURSOR Wrong_Orders; end; { основная программа } 2. Для данной программы которую я использовал, решение будет состоять в том, чтобы просто включить поле onum, первичным ключом таблицы Порядков, в курсор Wrong_Orders. В команде UPDATE, вы будете затем использовать предикат WHERE onum =:ordernum (считая целую перемен- ную - odernum, обьявленной), вместо WHERE CURRENT Of Wrong_Orders. Результатом будет программа наподобии этой (большинство коммента- риев из предыдущей программы здесь исключены): EXEC SQL BEGIN DECLARE SECTION; SQLCODE: integer; odernum integer; cnum integer; snum integer; custnum: integer; salesnum: integer; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE Wrong_Orders AS CURSOR FOR SELECT onum, cnum, snum FROM Orders a WHERE snum < > (SELECT snum FROM Customers b WHERE a.cnum = b.cnum); EXEC SQL DECLARE Cust _ assigns AS CURSOR FOR SELECT cnum, snum FROM Customers; begin { основная программа } EXEC SQL OPEN CURSOR Wrong_Orders; while SQLCODE = O do {Цикл до тех пор пока Wrong_Orders не опустеет} begin EXEC SQL FETCH Wrong_Orders INTO (:odernum,:cnum,:snum); if SQLCODE = O then begin EXEC SQL OPEN CURSOR Cust_Assigns; repeat EXEC SQL FETCH Cust_Assigns INTO (:custnum,:salesnum); until:custnum =:cnum; EXEC SQL CLOSE CURSOR Cust_assigns; EXEC SQL UPDATE Orders SET snum =:salesnum WHERE CURRENT OF Wrong_Orders; end; {If SQLCODE = 0} end; { While SQLCODE... do } EXEC SQL CLOSE CURSOR Wrong_Orders; end; { main program } 3. EXEC SQL BEGIN DECLARE SECTION; SQLCODE integer; newcity packed array[1..12] of char; commnull boolean; citynull boolean; response char; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE CURSOR Salesperson AS SELECT * FROM SALESPEOPLE; begln { main program } EXEC SQL OPEN CURSOR Salesperson; EXEC SQL FETCH Salesperson INTO (:snum,:sname,:city:i_cit,:comm:i_com); {Выборка первой строки} while SQLCODE = O do {Пока эти строки в таблице Продавцов.} begin if i_com < O then commnull: = true; if i_cit < O then citynull: = true; { Установить логические флаги которые могут показать NULLS.} if citynull then begin write ('Нет текущего значения city для продавца ', snum, ' Хотите предоставить хотя бы одно? (Y/N)'); {Подсказка покажет значение city состоящее из NULL значений.} read (ответ); {Ответ может быть сделан позже.} end {если конечно - citynull} else { не citynull } begin if not commnull then { Чтобы выполнять сравнение и операции только для не-NULL значений связи } begin if city = 'London' then comm: = comm *.02 *.02 else comm: = comm +.02; end; {Даже если значение и не - commnull, begin и end здесь для ясности.} write ('Текущий city для продавца', snum, 'есть', city, Хотите его изменить? (Y/N)'); 3. Обратите Внимание: Продавец не назначеный в данное время в опреде- ленный город, не будет иметь изменений комиссионых при определении находятся ли он в Лондоне. read (ответ); {Ответ теперь имеет значение независимо от того что citynull - верен или неверен.} end; {иначе не citynull} if response = 'Y' then begin write ('Введите новое значение city:'); read (newcity); if not commnull then {Эта операция может быть выполнена только для не-NULL значений. } case newcity of: begin 'Barcelona':comm:= comm +.01, 'San Jose': comm: = comm *.01 end; {случно и если не commnull} EXEC SQL UPDATE Salespeople SET city =:newcity, comm =:comm:i_com WHERE CURRENT OF Salesperson; {Переменная индикатора может поместить NULL значение в поле comm если так назначено.} end; { Если ответ = 'Y', или если ответ < > 'Y', изменений не будет. } EXEC SQL FETCH Salesperson INTO (:snum,:sname,:city:i_clt,:comm:l_com); {выборка следующей строки} end; {если SQLCODE = 0} EXEC SQL CLOSE CURSOR Salesperson; end; {основной программы} Приложение B ------------- ТИПЫ ДАННЫХ В SQL ТИПЫ ДАННЫХ РАСПОЗНАВАЕМЫЕ С ПОМОЩЬЮ ANSI, состоят из символов и различных типов чисел, которые могут классифицироваться как точные числа и приблизительные числа. Точные числовые типы - это номера, с десятичной точкой или без десятичной точки. Приблизительные числовые типы - это номера в показательной (экспоненциальной по основанию - 10) записи. Для все прочих типов, отличия слишком малы чтобы их как-то классифи- цировать. Иногда типы данных используют аргумент, который я называю размером аргумента, чей точный формат и значение меняется в зависимости от конкретного типа. Значения по умолчанию обеспечены для всех типов, ес- ли размер аргумента отсутствует. ======== ТИПЫ ANSI ========= Ниже представлены типы данных ANSI (имена в круглых скобках - это синонимы): TEXT ТЕКСТ ---------------------------------------------------------------------- CHAR 1 0Строка текста в реализационно-определенном фор- (или CHARACTER) мате. Размер агрумента здесь это единственное неотрицательное целое число которое ссылается к максимальной длине строки. Значения этого типа, должны быть заключены в одиночные кавычки, нап- ример 'text'. Две рядом стоящие одиночные кавыч- ки ('') внутри строки будет пониматься как одна одиночная кавычка ('). ПРИМЕЧАНИЕ: Здесь и далее, фраза Реализационно-Определенный или Реализацион- но-Зависимый, указывает, что этот аргумент или формат зависит от конкретной программы в которой реализуются данные. EXACT NUMERIC 1 0ТОЧНОЕ ЧИСЛО ---------------------------------------------------------------------- DEC Десятичное число; то есть, число которое может (или DECIMAL) иметь десятичную точку. Здесь аргумент размера имеет две части: точность и масштаб. Масштаб не может превышать точность. Сначала указывается точность, разделительная запятая и далее аргу- мент масштаба. Точность указывает сколько значащих цифр имеет число. Максимальное десятичное число составляю- щее номер - реализационно-определенное значение, равное или большее чем этот номер. Масштаб ука- зывает максимальное число цифр справо от деся- тичной точки. Масштаб = нулю делает поле эквива- лентом целого числа. NUMERIC Такое же как DECIMAL за исключением того, что максимальное десятичное не может превышать аргу- мента точности. INT Число без десятичной точки. Эквивалентно DECI- (или INTEGER) MAL, но без цифр справа от десятичной точки, то-есть с масштабом равным 0. Аргумент размера не используется (он автоматически установливает- ся в реализационно-зависимое значение). SMALLINT Такое же как INTEGER, за исключением того, что, в зависимости от реализации, размер по умолчанию может (или не может) быть меньшее чем INTEGER. APPROXIMATE NUMERIC ПРИБЛИЗИТЕЛЬНОЕ ЧИСЛО ---------------------------------------------------------------------- FLOAT Число с плавающей запятой на основе 10 показа- тельной функции. Аргумент размера состоит из од- ного числа определяющего минимальную точность. REAL Такое же как FLOAT, за исключением того, что ни- какого аргумента размера не используется. Точ- ность установлена реализационно-зависимую по умолчанию. DOUBLE Такое же как REAL, за исключением того, что реа- PRECISION лизационно-определяемая точность для DOUBLE PRE- (или DOUBLE) CISION должна превышать реализационно-определяе- мую точность REAL. ЭКВИВАЛЕНТНЫЕ ТИПЫ ===== ДАННЫХ В ДРУГИХ ЯЗЫКАХ ====== Когда используется вложение SQL в другие языки, значения используе- мые и произведенные командами SQL, обычно сохраняются в переменных главного языка(см. Главу 25). Эти переменные должны иметь тип данных совместимый со значениями SQL, которые они будут получать. В дополне- ниях, которые не являются частью официального SQL стандарта, ANSI обеспечивает поддержку при использовании вложения SQL в четыре языка: Паскаль, PL/I, КОБОЛ, и ФОРТРАН. Между прочим, он включает определение эквивалентов SQL, для данных типов переменных используемых в этих язы- ках. Эквиваленты типов данных четырех языков определенных ANSI: ПЛ/I SQL ТИП ЭКВИВАЛЕНТ ПЛ/I -------------------------------------- CHAR CHAR DECIMAL FIXED DECIMAL INTEGER FIXED BINARY FLOAT FLOAT BINARY КОБОЛ SQL ТИП ЭКВИВАЛЕНТ КОБОЛА ------------------------------------------ CHAR () PIC X () INTEGER PIC S () USAGE COMPUTTATIONAL NUMERIC PIC S (< nines with embedded V >) DISPLAY SING LEADING SEPERATE ПАСКАЛЬ SQL ТИП ЭКВИВАЛЕНТ ПАСКАЛЯ ------------------------------------ INTEGER INTEGER REAL REAL CHAR () PACKED ARRAY [1..] OF CHAR ФОРТРАН SQL ТИП ЭКВИВАЛЕНТ ФОРТРАНА ------------------------------------------ CHAR CHAR INTEGER INTEGER REAL REAL DOUBLE PRECISION DOUBLE PRECISION Приложение C ------------- НЕКОТОРЫЕ ОБЩИЕ НЕСТАНДАРТНЫЕ СРЕДСТВА SQL ИМЕЕТСЯ РЯД ОСОБЕННОСТЕЙ ЯЗЫКА SQL которые пока не определены как часть стандарта ANSI или стандарта ISO (Международная Организация По Стандартизации), и являются общими для многочисленных реализаций, так как они были получены для практического использования. Это дополни- тельные элементы чисел этих особенностей. Конечно, эти особенности ме- няются от программы к программе, и их обсуждение предназначено только чтобы показать некоторые общие подходы к ним. ============== ТИПЫ ДАННЫХ ================= Типы данных поддерживаемые стандартом SQL, собраны в Приложении B. Это колличество для CHARACTER и