Файл: ycheb/sql_teach/ch25.php
Строк: 225
<?php
include '../../config.php';
$title='Учебник SQL';
aut();
head();
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<link rel="stylesheet" href="css.css" type="text/css">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1251">
<meta http-equiv="Content-Language" content="ru">
<title>Глава 25.</title></head>
<body>
<h1>Глава 25. ИСПОЛЬЗОВАНИЕ SQL С ДРУГИМ ЯЗЫКОМ (ВЛОЖЕННЫЙ/ВСТРОЕННЫЙ SQL)</h1>
<hr width="50%">
<p>В этой главе вы узнаете, как SQL используется для расширения программ, написанных на других языках. Хотя непроцедурность языка SQL делает его очень мощным,
одновременно это накладывает на него большое
количество ограничений. Чтобы преодолеть эти ограничения, вы можете включать SQL в программы, написанные на том или
ином процедурном языке (имеющем определённый алгоритм).<br>
Для наших примеров мы выбрали Паскаль, считая, что
этот язык наиболее прост в понимании для начинающих, и ещё потому, что Паскаль - один из языков, для которых ANSI имеет полуофициальный стандарт.</p>
<a name="25.1"></a>
<h3>ЧТО ТАКОЕ - ВЛОЖЕНИЕ SQL?</h3>
<p>Чтобы вложить SQL в другой язык, вы должны использовать пакет программ, который обеспечивал бы поддержку вложения SQL в этот язык и,
конечно же, поддержку самого языка. Естественно, вы должны быть знакомы с языком,
который вы используете.</p>
<p>В основном вы будете использовать команды SQL для работы в таблицах баз данных, передачи
результатов вывода в программу и получение ввода из программы, в которую они вкладываются, обобщённо ссылаясь к главной программе
(которая может или не может принимать их из диалога или посылать обратно в диалог пользователя и программы).</p>
<a name="25.2"></a>
<h3>ЗАЧЕМ ВКЛАДЫВАТЬ SQL?</h3>
<p>Хотя мы и потратили некоторое время на то, чтобы показать возможности SQL, но, если вы - опытный программист, вы, вероятно, отметили, что
сам по себе он не очень полезен при написании программ. Самое очевидное ограничение - то, что, в то время как SQL может сразу выполнить пакет команды, интерактивный SQL в основном выполняет
по одной команде в каждый момент времени.</p>
<p>Логические конструкции типа if ... then ("если ... то"), for ... do
("для ... выполнить") и while ... repeat ("пока ... повторять"), используемые для структур большинства компьютерных программ, здесь отсутствуют, так что вы не сможете принять решение - выполнять ли, как выполнять или как долго выполнять одно действие в результате другого действия. Кроме того, интерактивный SQL не может делать
ничего со значениями, кроме ввода их в таблицу, размещения или распределения их с помощью запросов и, конечно, вывода их на какое-то устройство.</p>
<p>Более традиционные языки, однако, разработаны так, чтобы программист мог начинать обработку данных
и, основываясь на её результатах, решать, делать ли то действие или другое, или же повторять действие до тех
пор, пока не встретится некоторое условие, создавая логические маршруты и циклы. Значения сохраняются в переменных, которые могут использоваться и изменяться с помощью любого
числа команд. Это даёт вам возможность указывать пользователям на ввод или вывод этих команд из файла и возможность форматировать вывод сложными способами (например,
преобразовывать числовые данные в диаграммы).</p>
<p>Цель вложенного SQL состоит в том, чтобы объединить эти возможности, позволяющие вам создавать сложные процедурные программы, которые
адресуют базу данных (БД) посредством SQL, позволяя устранить сложные действия в таблицах на процедурном языке, который не ориентирован на такую структуру данных, в то же время поддерживая структурную
строгость процедурного языка.</p>
<a name="25.3"></a>
<h3>КАК ДЕЛАЕТСЯ ВЛОЖЕНИЕ SQL?</h3>
<p>Команды SQL помещаются в исходный текст главной программы; им предшествует фраза EXEC SQL (EXECute SQL). Далее устанавливаются некоторые команды, которые являются специальными для вложенной
формы SQL и которые будут рассмотрены в этой главе.</p>
<p>Строго говоря, стандарт ANSI не поддерживает вложенный SQL как таковой. Он поддерживает понятие, называемое
"модуль", которое, более точно, является вызываемым набором процедур SQL, а не вложением в другой язык. Официальное определение синтаксиса вложения SQL будет включать
расширение официального синтаксиса каждого языка, в который может вкладываться SQL, что весьма долгая и неблагодарна
работа, которой ANSI избегает. Однако ANSI обеспечивает четыре приложения (не являющиеся
частью стандарта), которые определяют синтаксис вложения SQL для четырех языков: КОБОЛ, ПАСКАЛЬ, ФОРТРАН и ПЛ/1.</p>
<p>Язык C так же широко поддерживается, как и другие языки. Когда вы вставляете команды SQL в текст программы, написанной на другом языке, вы должны выполнить прекомпиляцию, прежде чем вы окончательно её скомпилируете.</p>
<p>Программа, называемая прекомпилятором (или препроцессором), будет просматривать текст вашей программы и преобразовывать команды SQL
в форму, удобную для использования базовым языком.</p>
<p>Затем вы используете обычный транслятор, чтобы преобразовывать программу из исходного текста в исполняемый код.</p>
<p>Согласно подходу к модульному языку, определённому ANSI, основная программа вызывает процедуры SQL. Процедуры выбирают параметры из главной программы и возвращают уже обработанные значения обратно в основную программу. Модуль может содержать любое число процедур, каждая из
которых состоит из одиночной команды SQL. Идея в том, чтобы процедуры могли работать тем же самым способом, что и процедуры на языке, в который они были вложены (хотя модуль ещё должен идентифицировать базовый
язык из-за различий в типах данных различных языков).</p>
<p>Реализации могут удовлетворить стандарту, выполнив вложение SQL таким
способом, как если бы модули уже были точно определены. Для этой цели прекомпилятор будет создавать модуль, называемый модулем доступа. Только
один модуль, содержащий любое число процедур SQL, может существовать для данной программы. Размещение операторов SQL непосредственно в главном коде происходит более просто и более практично, чем непосредственно
создание самих модулей.</p><p>Каждая из программ, использующих вложение SQL, связана с ID доступа
во время её выполнения. ID доступа, связанный с программой, должен иметь все привилегии, чтобы выполнять операции SQL в программе.
Вообще-то вложенная программа SQL регистрируется в БД, так же как и пользователь, выполняющий программу. Более подробно это определяет проектировщик, но, вероятно, было бы неплохо включить в вашу программу команду CONNECT или ей подобную.</p>
<a name="25.4"></a>
<h3>ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННЫХ ОСНОВНОГО ЯЗЫКА В SQL </h3>
<p>Основной способ, которым SQL и части базового языка ваших программ будут связываться друг с другом - значения
переменных. Естественно, что разные языки распознают различные типы данных для
переменных. ANSI определяет эквиваленты SQL для четыре базовых языков: ПЛ/1, Паскаль, КОБОЛ и ФОРТРАН; всё это подробно описано в
<a href="b.php">Приложении B</a>.
Эквиваленты для других языков определяет проектировщик.</p><p>Имейте в виду, что такие типы как DATE не распознаются ANSI, и,
следовательно, никакие эквивалентные типы данных для базовых языков не существуют в стандарте ANSI. Более сложные типы данных базового
языка, такие как матрицы, не имеют эквивалентов в SQL. Вы можете использовать переменные из главной программы во вложенных операторах SQL везде, где вы будете использовать выражения значений. (SQL, используемый в этой главе, будет пониматься как вложенный SQL до тех пор, пока это не будет оговорено особо.)</p>
<p>Текущим значением переменной может быть значение, используемое в команде. Главные переменные должны:</p>
<ul><li>быть объявленными в SQL DECLARE SESSION (РАЗДЕЛ ОБЪЯВЛЕНИЙ), который будет описан далее;</li>
<li>иметь совместимый тип данных с их функциями в команде SQL (например, числовой тип, если он вставляется в числовое поле);</li>
<li>быть присвоенными значению во время их использования в команде SQL, если команда SQL самостоятельно не может сделать назначение;</li>
<li>предшествовать двоеточию (:), когда они упоминаются в команде SQL.</li></ul>
<p>Так как главные переменные отличаются от имён столбцов SQL наличием у них двоеточия, вы можете использовать переменные с теми же самыми
именами, что и ваши столбцы, если это, конечно, нужно.</p>
<p>Предположим, что у вас есть четыре переменные с именами id_num, salesperson, loc и comm. Они содержат значения,
которые вы хотите вставить в таблицу Продавцов. Вы могли бы вложить следующую команду SQL в вашу программу:</p>
<pre> EXEC SQL INSERT INTO Salespeople
VALUES (:id_num, :salesperson, :loc, :comm)</pre>
<p>Текущие значения этих переменных будут помещены в таблицу. Как видите, переменная comm имеет то же самое имя, что и столбец, в
который это значение вкладывается. Обратите внимание, что точка с запятой в конце команды отсутствует. Это
потому, что соответствующее завершение для вложенной команды SQL зависит от языка, для которого делается вложение.</p>
<p>Для Паскаля и PL/1 это будет точка с запятой, для КОБОЛА - слово END-EXEC, а для ФОРТРАНА не будет никакого завершения.
В других языках это зависит от реализации, и поэтому мы договоримся,
что будем использовать точку с запятой (в этой книге) всегда, чтобы не
противоречить интерактивному SQL и Паскалю. Паскаль завершает вложенный SQL и собственные команды одинаково: точкой с запятой.
Способ сделать команду полностью такой, как описано выше, состоит в том, чтобы включать её в цикл и повторять её с различными значениями переменных, как показано в следующем примере:</p>
<pre> 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;</pre>
<p>Фрагмент программы на ПАСКАЛЕ определяет цикл, который будет считывать значения из файла, сохранять их в четырёх именованных
переменных, сохранять значения этих переменных в таблице Продавцов, а затем считывать следующие четыре значения, повторяя этот процесс
до тех пор, пока весь входной файл не будет прочитан. Считается, что каждый набор значений завершается возвратом каретки (для не знакомых
с Паскалем: функция readln считывает вводимую информацию и переходит на следующую строку источника этой информации). Это дает вам
простой способ передать данные из текстового файла в реляционную структуру.</p>
<p>Конечно, вы можете сначала обработать данные любыми возможными
способами на вашем главном языке, например, для исключения всех комиссионных ниже значения .12</p>
<pre> 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;</pre>
<p>Только строки, которые выполнят условие comm >= .12, будут вставлены в вывод. Это показывает, что можно использовать и циклы, и условия как
нормальные для главного языка.</p>
<a name="25.5"></a>
<h3>ОБЪЯВЛЕНИЕ ПЕРЕМЕННЫХ</h3>
<p>Все переменные, на которые имеется ссылка в предложениях SQL, должны сначала быть объявлены в SQL DECLARE SECTION (РАЗДЕЛЕ ОБЪЯВЛЕНИЙ), использующем обычный синтаксис главного языка. Вы можете иметь любое число таких разделов в программе, и они могут размещаться где-нибудь в коде перед используемой переменной, подчиняясь ограничениям, определённым в соответствии с главным языком.</p>
<p>Раздел объявлений должен начинаться и кончаться вложенными командами SQL: BEGIN DECLARE SECTION (Начало Раздела Объявлений)
и END DECLARE SECTION (Конец Раздела Объявлений), которым предшествует, как обычно, EXEC SQL (Выполнить
SQL).</p>
<p>Чтобы объявить переменные, используемые в предыдущем примере, вы можете ввести следующее:</p>
<pre> 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;</pre>
<p>Для не знакомых с ПАСКАЛем: Var это заголовок, который предшествует ряду объявляемых переменных и упакованным (или распакованным) массивам, являющимися серией фиксированных переменных значений, различаемых с помощью номеров (например, третий символ loc будет loc (3)).
Использование точки с запятой после каждой переменной указывает на то, что это - Паскаль, а не SQL.</p>
<a name="25.6"></a>
<h3>ИЗВЛЕЧЕНИЕ ЗНАЧЕНИЙ ПЕРЕМЕННЫХ</h3>
<p>Кроме помещения значений переменных в таблицы с помощью команды
SQL, вы можете использовать SQL для получения значений этих переменных.<br>
Один из способов
сделать это - с помощью разновидности команды SELECT, которая содержит предложение INTO. Давайте вернемся
к нашему предыдущему примеру и переместим строку Peel из таблицы Продавцов в наши переменные главного языка.</p>
<pre> EXEC SQL SELECT snum, sname, city, comm
INTO :id_num, :salesperson, :loc, :comm
FROM Salespeople
WHERE snum = 1001;</pre>
<p>Выбранные значения помещаются в переменные с упорядоченными именами, указанными в предложении INTO. Разумеется, переменные с именами, указанными в предложении INTO, должны иметь соответствующий
тип, чтобы принять эти значения, и должна быть своя переменная для каждого выбранного столбца.
Если не учитывать присутствие предложения INTO, то этот запрос похож на любой другой. Однако предложение INTO добавляет значительное ограничение к запросу. Запрос должен извлекать не более одной строки. Если он извлекает много строк, все они не могут быть вставлены
одновременно в одну и ту же переменную. Команда, естественно, потерпит неудачу.</p>
<p>По этой причине, SELECT INTO должна использоваться
только при следующих условиях:</p>
<ul><li>когда вы используете предикат, проверяющий значения, которые, как вы знаете, могут быть уникальным, как в этом примере. Значения, которые,
как вы знаете, могут быть уникальными, это те значения, которые имеют принудительное ограничение уникальности или уникальный индекс,
как это говорилось в Главах <a href="ch17.php">17</a> и <a href="ch18.php">18</a>;</li>
<li>когда вы используете одну или более агрегатных функций и не используете GROUP BY;</li>
<li>когда вы используете SELECT DISTINCT во внешнем ключе с предикатом, ссылающимся на единственное значение родительского ключа
(обеспечивая вашей системе предписание справочной целостности), как в следующем примере:</li></ul>
<pre> EXEC SQL SELECT DISTINCT snum
INTO :salesnum
FROM Customers
WHERE snum =
(SELECT snum
FROM Salespeople
WHERE sname = 'Motika');</pre>
<p>Предполагалось что Salespeople.sname и Salespeople.snum это, соответственно, уникальный и первичный ключи этой таблицы, а Customers.snum
- внешний ключ, ссылающийся на Salespeople.snum, и вы предполагали, что этот запрос произведёт единственную строку.</p>
<p>Имеются другие случаи, когда вы знаете, что запрос должен произвести единственную строку вывода, но они мало известны, и в большинстве случаев вы основываетесь на том, что ваши данные имеют целостность, которая не может быть предписана с помощью ограничений. Не
полагайтесь на это! Вы создаёте программу, которая, вероятно, будет использоваться в течение некоторого времени, и лучше всего проиграть её,
чтобы иметь гарантированное отсутствие в будущем возможных отказов. Во всяком случае, нет необходимости группировать запросы, которые производят
одиночные строки, поскольку SELECT INTO используется только для удобства.</p>
<p>Как вы увидите, можно использовать запросы, выводящие многочисленные строки, с помощью курсора.</p>
<a name="25.7"></a>
<h3>КУРСОР</h3>
<p>Одно из сильных качеств SQL - способность функционировать на всех строках таблицы, чтобы найти определенное условие как блок
"запись",
не зная сколько таких строк там может быть. Если десять строк удовлетворяют предикату, то запрос может вывести все десять строк. Если десять
миллионов строк определены, все десять миллионов строк будут выведены. Это несколько затруднительно, когда вы попробуете связать это с другими
языками. Как вы сможете назначать вывод запроса для переменных, когда не знаете, насколько велик будет вывод? Решение состоит в том, чтобы использовать то, что называется курсором.
Вы, вероятно, знакомы с курсором - мигающей черточкой, которая отмечает вашу позицию на экране компьютера. Вы можете рассматривать
SQL-курсор как устройство, которое, аналогично этому, отмечает ваше место в выводе запроса, хотя аналогия не полная.</p>
<p>Курсор это вид переменной, которая связана с запросом. Значением этой переменной может быть каждая строка, которая выводится при запросе.
Подобно главным переменным, курсоры должны быть объявлены, прежде чем они будут использованы. Это делается командой DECLARE CURSOR
следующим образом:</p>
<pre> EXEC SQL DECLARE CURSOR Londonsales FOR
SELECT *
FROM Salespeople
WHERE city = 'London';</pre>
<p>Запрос не выполнится немедленно; он только определяется.</p>
<p>Курсор немного напоминает представление, в котором курсор содержит запрос,
а содержание курсора напоминает любой вывод запроса каждый раз, когда курсор становится открытым. Однако, в отличие от базовых таблиц или представлений,
строки курсора упорядочены: имеются первая, вторая ... и последняя строка курсора. Этот порядок может быть произвольным,
с явным управлением с помощью предложения ORDER BY в запросе, или же по умолчанию следовать какому-то упорядочиванию, определяемому инструментально определяемой схемой.
Когда вы ищете точку в вашей программе, в которой вы хотите выполнить запрос, вы открываете курсор с помощью следующей команды:</p>
<pre> EXEC SQL OPEN CURSOR Londonsales;</pre>
<p>Значения в курсоре могут быть получены, когда вы выполняете именно эту команду, но не предыдущую команду DECLARE и не последующую
команду FETСH. Затем вы используете команду FETCH, чтобы извлечь вывод из этого запроса, по одной строке в каждый момент времени.</p>
<pre> EXEC SQL FETCH Londonsales INTO :id_num,
:salesperson, :loc, :comm;</pre>
<p> Это выражение переместит значения из первой выбранной строки в переменные. Другая команда FETCH выводит следующий набор значений. Идея состоит в том, чтобы поместить команду FETCH внутрь цикла
так, чтобы, выбрав строку, вы могли, переместив набор значений из этой строки в переменные, возвращаться обратно в цикл, чтобы переместить
следующий набор значений в те же самые переменные.</p>
<p> Например, возможно, вам нужно, чтобы вывод выдавался по одной строке,
спрашивая каждый раз у пользователя, хочет ли он продолжить, чтобы увидеть следующую строку</p>
<pre> 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;</pre>
<p>В Паскале знак : = означает "является назначенным значением из",
в то время как = ещё имеет обычное значение "равно". Функция writeln записывает её вывод, а затем переходит к новой строке.
Одиночные кавычки вокруг символьных значений во втором writeln и в предложении if ... then обычны для Паскаля, что случается при
дубликатах в SQL.</p><p>В результате выполнения этого фрагмента, булева переменная с именем Look_at _more должна быть установлена в состояние верно, открыт курсор, и
запущен цикл. Внутри цикла строка выбирается из курсора и выводится на экран. У пользователя спрашивают, хочет ли он видеть следующую
строку. Пока он не ответил N (Нет), цикл повторяется, и следующая строка значений будет выбрана.</p>
<p>Хотя переменные Look_at_more и ответ должны быть объявлены как булева переменная и символьная (char) переменная, соответственно, в
разделе объявлений переменных в Паскаля, они не должны быть включены в раздел объявлений SQL, потому что они не используются в командах SQL.</p>
<p>Как видите, двоеточия перед именами переменных не используются для не-SQL операторов.
Также обратите внимание, что имеется оператор CLOSE CURSOR, соответствующий оператору OPEN
CURSOR. Он, как вы поняли, освобождает курсор значений, поэтому запрос нужно будет выполнить повторно с оператором OPEN CURSOR,
прежде чем перейти в выбору следующих значений. Это не обязательно для тех строк, которые были выбраны запросом после закрытия курсора, хотя это и обычная процедура.
Пока курсор закрыт, SQL не следит за тем, какие строки выбраны. Если вы открываете курсор снова, запрос повторно выполняется с этой
точки, и вы начинаете всё сначала.</p>
<p>Этот пример не обеспечивает автоматический выхода из цикла, когда
все строки уже будут выбраны. Когда у FETCH нет больше строк которые надо извлекать, он просто не меняет значений в переменных предложения INTO.
Следовательно, если данные исчерпались, эти переменные будут неоднократно
выводиться с идентичными значениями до тех пор, пока пользователь не завершит
цикл, введя ответ N.</p>
<a name="25.8"></a>
<h3>SQLCODE</h3>
<p>Хорошо было бы знать, когда данные будут исчерпаны, чтобы можно было сообщить об этом пользователю, и цикл завершился бы автоматически.
Это даже более важно, чем, например, знать, что команда SQL выполнена с ошибкой. Переменная SQLCODE (называемая еще SQLCOD в
ФОРТРАНе) предназначена для того, чтобы обеспечить эту функцию. Она должна быть определена как переменная главного языка и должна иметь
тип данных, который в главном языке соответствует одному из точных числовых типов SQL, как это показано в <a href="b.php">Приложении B</a>.
Значение SQLCODE устанавливается каждый раз, когда выполняется команда SQL.</p>
<p>В основном существуют три возможности:</p>
<ol><li>Команда выполнилась без ошибки, но не произвела никакого действия. Для различных команд это выглядит по разному:<br>
а) Для SELECT, ни одна строка не выбрана запросом.<br>б) Для FETCH, последняя строка уже была выбрана, или ни
одной строки не выбрано запросом в курсоре.<br>в) Для INSERT, ни одной строки не было вставлено (подразумевается, что запрос использовался, чтобы сгенерировать значения для вставки, и был отвергнут при попытке извлечения любой строки.<br>
г) Для UPDATE и DELETE, ни одна строка не ответила условию предиката и, следовательно, никаких изменений в таблице
сделано не будет. В любом случае будет установлен код SQLCODE = 100.</li>
<li>Команда выполнилась нормально, не удовлетворив ни одному из вышеуказанных условий. В этом случае будет установлен код SQLCOD = 0.</li>
<li>Команда сгенерировала ошибку. Если это случилось, изменения, сделанные в БД текущей транзакцией, будут восстановлены (см.
<a href="ch23.php">Главу 23</a>). В этом случае будет установлен код SQLCODE = некоторому отрицательному числу, определяемому проектировщиком. Задача этого числа
- идентифицировать проблему так точно, насколько это возможно. В принципе, ваша система должна быть снабжена подпрограммой, которая в этом случае должна выполниться, чтобы выдать для вас информацию,
расшифровывающую значение негативного числа, определенного вашим проектировщиком. В этом случае некоторое сообщение об ошибке будет выведено на экран или записано в файл протокола, а программа в это время
выполнит восстановление изменений для текущей транзакции, откл́ючится от базы данных и выйдет из нее.</li></ol>
<a name="25.9"></a>
<h3>ИСПОЛЬЗОВАНИЕ SQLCODE ДЛЯ УПРАВЛЕНИЯ ЦИКЛАМИ</h3>
<p>Теперь мы можем усовершенствовать наш предыдущий пример для выхода из цикла автоматически, при условии что курсор пуст, все строки выбраны или произошла ошибка:</p>
<pre> 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;</pre>
<a name="25.10"></a>
<h3>ПРЕДЛОЖЕНИЕ WHENEVER</h3>
<p>Это удобно для выхода при выполненном условии, что все строки выбраны. Но если вы получили ошибку, вы должны предпринять нечто такое, что
описано для третьего случая, выше. Для этой цели, SQL предоставляет
предложение GOTO. Фактически SQL позволяет вам применять его достаточно широко, так что программа может выполнить команду GOTO
автоматически, если будет произведено определенное значение SQLCODE.</p>
<p>Вы можете сделать это совместно с предложением WHENEVER.
Вот блок из примера для этого случая:</p>
<pre> EXEC SQL WHENEVER SQLERROR GOTO Error_handler;
EXEC SQL WHENEVER NOT FOUND CONTINUE;</pre>
<p>SQLERROR это другой способ сообщить, что SQLCODE < 0; а NOT FOUND это другой способ сообщить, что SQLCODE
= 100. (Некоторые реализации называют последний случай ещё SQLWARNING.)<br>
Error_handler это имя того места в программе, в которое будет передано выполнение программы, если произошла ошибка (GOTO может
состоять из одного или двух слов). Такое место определяется любым способом главного языка, например, с помощью метки в Паскале или имени раздела или имени параграфа в КОБОЛе (в
дальнейшем мы будем использовать термин "метка"). Метка более удачно идентифицирует стандартную процедуру, распространяемую проектировщиком для включения во все программы.</p>
<p>CONTINUE не делает чего-то специального для значения SQLCODE. Оно
также является значением по умолчанию, если вы не используете команду WHENEVER, определяющую значение SQLCODE. Однако эти неактивные
определения дают вам возможность переключаться вперёд и назад, выполняя и не выполняя действия в различных точках (метках) вашей программы.<br>
Например, если ваша программа включает в себя несколько команд INSERT, использующих запросы, которые реально должны производить значения, вы
могли бы напечатать специальное сообщение или сделать что-то такое, что поясняло бы, что запросы возвращаются пустыми и никакие значения не были вставлены. В этом случае, вы можете ввести следующее:</p>
<pre> EXEC SQL WHENEVER NOT FOUND GOTO No_rows;</pre>
<p>No_rows это метка в некотором коде, содержащем определенное действие. С другой стороны, если вам нужно сделать выборку в программе позже,
вы можете ввести следующее в этой точке:</p>
<pre> EXEC SQL WHENEVER NOT FOUND CONTINUE;</pre>
<p>чтобы выполнение выборки повторялось до тех пор, пока все строки не будут извлечены, что является нормальной процедурой не требующей специальной обработки.</p>
<a name="25.11"></a>
<h3>МОДИФИЦИРОВАНИЕ КУРСОРОВ</h3>
<p>Курсоры могут также быть использованы, чтобы выбирать группу строк из таблицы, которые могут быть затем модифицированы или удалены одна за другой. Это дает вам возможность, обходить некоторые ограничения
предикатов, используемых в командах UPDATE и DELETE. Вы можете ссылаться на таблицу, задействованную в предикате запроса курсора или любом из его подзапросов, которые вы не можете выполнить в предикатах
самих этих команд. Как подчёркнуто в <a href="ch16.php">Главе 16</a>, стандарт SQL отклоняет попытку удалить всех пользователей с рейтингом ниже среднего, в следующей форме:</p>
<pre> EXEC SQL DELETE FROM Customers
WHERE rating <
(SELECT AVG (rating)
FROM Customers);</pre>
<p>Однако вы можете получить тот же эффект, используя запрос для выбора соответствующих строк, запомнив их в курсоре и выполнив DELETE с использованием курсора. Сначала вы должны объявить курсор:</p>
<pre> EXEC SQL DECLARE Belowavg CURSOR FOR
SELECT *
FROM Customers
WHERE rating <
(SELECT AVG (rating)
FROM Customers);</pre>
<p>Затем вы должны создать цикл, чтобы удалить всех заказчиков, выбранных курсором:</p>
<pre> 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;</pre>
<p>Предложение WHERE CURRENT OF означает, что DELETE применяется к строке, которая в настоящее время выбрана курсором. Здесь подразумевается, что и курсор, и команда DELETE ссылаются на одну и ту же таблицу и, следовательно, что запрос в курсоре - это не объединение.
Курсор должен также быть модифицируемым. Являясь модифицируемым, курсор должен удовлетворять тем же условиям, что и представления
(см. <a href="ch21.php">Главу 21</a>).</p>
<p>Кроме того, ORDER BY и UNION, которые не разрешены в представлениях, в курсорах разрешаются, но предохраняют курсор от модифицируемости. Обратите внимание в вышеупомянутом примере, что
мы должны выбирать строки из курсора в набор переменных, даже если
мы не собирались использовать эти переменные. Этого требует синтаксис команды FETCH.</p>
<p>UPDATE работает так же.</p>
<p>Вы можете увеличить значение комиссионных всем продавцам, которые имеют заказчиков с оценкой = 300, следующим способом. Сначала вы объявляете курсор:</p>
<pre> EXEC SOL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN
(SELECT snum
FROM Customers
WHERE rating = 300);</pre>
<p>Затем вы выполняете модификации в цикле:</p>
<pre> 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;</pre>
<p>Обратите внимание, что некоторые реализации требуют, чтобы вы указывали в определении курсора, что курсор будет использоваться для выполнения команды UPDATE на определенных столбцах. Это делается с помощью заключительной фразы определения курсора - FOR UPDATE <column list />.
Для объявления курсора High_cust таким способом, чтобы вы могли модифицировать командой UPDATE столбец comm, вы должны ввести
следующее предложение:</p>
<pre> EXEC SQL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN
(SELECT snum
FROM Customers
WHERE rating = 300)
FOR UPDATE OF comm;</pre>
<p>Это обеспечит вас определенной защитой от случайных модификаций, которые могут разрушить весь порядок в базе данных.</p>
<a name="25.12"></a>
<h3>ПЕРЕМЕННАЯ INDICATOR</h3>
<p>Пустые (NULL) значения это специальные маркеры, определяемые самим SQL. Они не могут помещаться в главные переменные. Попытка вставить NULL-значения в главную переменную будет некорректна, так как главные языки не поддерживают NULL-значений в SQL по определению. Хотя результат при попытке вставить NULL-значение в главную переменную
определяет проектировщик, этот результат не должен противоречить теории БД и поэтому обязан выдавать ошибку - код SQLCODE в виде
отрицательного числа - и вызывать подпрограмму управления ошибкой. Естественно, вам нужно этого избежать. Поэтому вы можете
заменить NULL-значения на допустимые значения, не приводящие к разрушению вашей
программы. Даже если программа и не разрушится, значения в главных переменных станут неправильными, потому что они не могут иметь NULL-значений.</p>
<p>Альтернативным методом, предоставляемым для этой ситуации, является функция переменной indicator (указатель).
Переменная indicator, объявленная в разделе объявлений SQL, напоминает другие переменные. Она может иметь тип главного языка, который соответствует числовому типу в SQL. Всякий раз, когда вы выполняете операцию,
которая должна поместить NULL-значение в переменную главного языка, вы должны использовать переменную indicator для надежности.<br>
Вы помещаете переменную indicator в команду SQL непосредственно после переменной главного языка, которую вы хотите защитить, без каких-либо пробелов
или запятых, хотя вы и можете, при желании, вставить слово INDICATOR. Переменной indicator в команде изначально присваивается значение 0.
Однако, если производится значение NULL, переменная indicator становится равной отрицательному числу. Вы можете проверить значение переменной indicator, чтобы узнать, было ли найдено значение NULL.</p>
<p>Давайте
предположим, что поля city и comm таблицы Продавцов не имеют ограничения NOT NULL, и что мы объявили в разделе объявлений SQL две ПАСКАЛевские переменные целого типа, i_a и i_b.
(Нет ничего такого в разделе объявлений, что могло бы представить их как переменные indicator. Они станут переменными indicator, когда будут использоваться как переменные indicator.)</p>
<p>Имеется одна возможность:</p>
<pre> 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;</pre>
<p>Как видите, мы включили, ключевое слово INDICATOR в одном случае и исключили его в другом случае, чтобы показать, что эффект будет одинаковым в любом случае. Каждая строка будет выбрана, но команда UPDATE выполнится, только если NULL-значения не будут обнаружены.
Если будут обнаружены NULL-значения, выполнится ещё одна часть программы, которая распечатает предупреждающее сообщение
- где было найдено каждое NULL-значение.</p>
<p>Обратите внимание: переменные indicator должны проверяться в главном
языке, как указывалось выше, а не в предложении WHERE команды SQL. Последнее в принципе не запрещено, но результат часто бывает непредсказуем.</p>
<a name="25.13"></a>
<h3>ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННОЙ INDICATOR<br>
ДЛЯ ЭМУЛЯЦИИ NULL-ЗНАЧЕНИЙ SQL</h3>
<p>Другая возможность состоит в том, чтобы обрабатывать переменную indicator, связывая её с каждой переменной главного языка, специальным способом, эмулирующим поведение NULL-значений SQL.
Всякий раз, когда вы используете одно из этих значений в вашей программе, например, в предложении if ... then, вы можете сначала проверить связанную переменную indicator:
равно ли её значение NULL. Если это так, то вы обрабатываете переменную по-другому.<br>
Например,
если NULL-значение было извлечено из поля city для главной переменной city, которая связана с переменной indicator i_city, вы должны
установить значение city, равное последовательности пробелов. Это будет необходимо, только если вы будете распечатывать его на принтере;
его значение не должно отличаться от логики вашей программы.
Естественно, i_city автоматически устанавливается в отрицательное значение.</p>
<p>Предположим, что вы имели следующую конструкцию в вашей программе:</p>
<pre> If sity = 'London' then
comm: = comm + .01
else comm: = comm - .01</pre>
<p>Любое значение, вводимое в переменную city, будет равно "London"
или не будет равно. Следовательно, в каждом случае значение комиссионных будет либо увеличено, либо уменьшено. Однако эквивалентные
команды в SQL выполняются по разному:</p>
<pre> EXEC SQL UPDATE Salespeople
SET comm = comm + .01
WHERE sity = 'London';</pre>
<p>и</p>
<pre> EXEC SQL UPDATE Salespeople
SET comm = comm .01;
WHERE sity < > 'London';</pre>
<p>(Вариант на ПАСКАЛе работает только с единственным значением, в то время как вариант на SQL работает со всеми таблицами.)
Если значение city в варианте на SQL будет равно значению NULL, оба предиката будут неизвестны, и значение comm, следовательно, не будет
изменено в любом случае. Вы можете использовать переменную indicator, чтобы сделать поведение вашего главного языка не противоречащим этому, с помощью создания условия, которое исключает NULL значения:</p>
<pre> If i_city > = O then
begin
If city = 'London' then
comm: = comm + .01
else comm: = comm - .01;
end;
{begin and end нужны здесь только для понимания}</pre>
<h6><b>ПРИМЕЧАНИЕ:</b> Последняя строка этого примера содержит ремарку - { begin и end необходимы только для понимания}</h6>
<p>В более сложной программе вы можете захотеть установить булеву переменную в "true/верно", чтобы указать, что значение city = NULL. Затем вы
можете просто проверять эту переменную всякий раз, когда вам это необходимо.</p>
<a name="25.14"></a>
<h3>ДРУГОЕ ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННОЙ INDICATOR</h3>
<p>Переменная indicator также может использоваться для просвоения NULL-значения. Просто добавьте её к имени главной переменной в команде UPDATE или INSERT тем же способом, что и в команде SELECT.
Если переменная indicator имеет отрицательное значение, значение NULL
будет помещено в поле.</p>
<p>Например, следующая команда помещает значения NULL в поля city и comm таблицы Продавцов всякий раз, когда
переменные indicator i_a или i_b будут отрицательными; в противном случае она помещает туда значения главных переменных:</p>
<pre>EXEC SQL INSERT INTO Salespeople
VALUES (:Id_num, :salesperson, :loc:i_a, :comm:i_b);</pre>
<p>Переменная indicator также используется, чтобы показывать отбрасываемую
строку. Это произойдет, если вы вставляете значения символов SQL в главную переменную, которая недостаточно длинна, чтобы вместить все символы.
Это особая проблема с нестандартным типами данных VARCHAR и LONG (смотри <a href="c.php">Приложение C</a>). В этом случае переменная будет заполнена
первыми символами строки, а последние символы будут потеряны. Если используется переменная indicator, в неё будет установлено положительное значение, указывающее на длину отбрасываемой части строки, позволяя таким образом узнать, сколько символов было потеряно.
В этом случае вы можете проверить с помощью просмотра значение переменной indicator > 0 или < 0.</p>
<a name="25.15"></a>
<h3>РЕЗЮМЕ</h3>
<p>Команды SQL вкладываются в процедурные языки, чтобы объединить возможности двух подходов. Некоторые дополнительные средства SQL необходимы
для выполнения этой работу. Вложенные команды SQL, транслируемые программой, называемой прекомпилятором, в форму, пригодную для использования транслятором главного языка, и используемые в этом главном языке как вызовы процедуры к подпрограммам, которые создаёт прекомпилятор, называются модулями доступа.
ANSI поддерживает вложение SQL в языки ПАСКАЛЬ, ФОРТРАН, КОБОЛ и PL/I. Другие языки также используются, особенно С.<br>
В попытке кратко описать вложенный SQL, наиболее важное в этой главе:</p>
<ul><li>Все вложенные команды SQL начинаются словами EXEC SQL и заканчиваются способом, который зависит от используемого главного языка;</li>
<li>Все главные переменные, доступные в командах SQL, должны быть объявлены в разделе объявлений SQL, прежде чем они будут использованы;</li>
<li>Всем главным переменным должно предшествовать двоеточие, когда они используются в команде SQL;</li>
<li>Запросы могут сохранять свой вывод непосредственно в главных переменных, используя предложение INTO, если, и только если, они выбирают единственную строку;</li>
<li>Курсоры могут использоваться для сохранения вывода запроса и доступа к одной строке в каждый момент времени. Курсоры бывают объявленными
(если определяют запрос, в котором будут содержаться), открытыми (если выполняют запрос) и закрытыми (если удаляют вывод запроса из курсора). Если курсор открыт, команда FETCH используется,
чтобы перемещать его по очереди к каждой строке вывода запроса;</li>
<li>Курсоры являются модифицируемыми или только-для-чтения. Чтобы стать модифицируемым, курсор должен удовлетворять всем критериям, которым удовлетворяет просмотр; кроме того, он не должен использовать
предложений ORDER BY или UNION, которые в любом случае не могут использоваться просмотрами. Немодифицируемый курсор является курсором только-для-чтения;</li>
<li>Если курсор - модифицируемый, он может использоваться для определения того, какие строки задействованы вложенными командами UPDATE и
DELETE, через предложение WHERE CURRENT OF. DELETE или UPDATE должны быть вне той таблицы, к которой курсор обращается в
запросе;</li><li>SQLCODE должен быть объявлен как переменная числового типа для каждой программы, использующей вложенный SQL. Его значение устанавливается
автоматически после выполнения каждой команды SQL;</li>
<li>Если команда SQL выполнена, как обычно, но не произвела вывода или ожидаемого изменения в базе данных, SQLCODE = 100. Если команда
произвела ошибку, SQLCODE будет равняться некоторому аппаратно определяемому отрицательному числу, которое описывает ошибку. В противном случае SQLCODE = 0;</li>
<li>Предложение WHENEVER может использоваться для определения действия, которое нужно предпринять, когда SQLCODE = 100 (не найдено)
или когда SQLCODE равен отрицательному числу (SQLERROR). Действием может быть или переход к некоторой определённой метке в
программе (GOTO <метка>), или отсутствие какого-либо действия вообще (продолжить). Последнее установлено по умолчанию;</li>
<li>Числовые переменные могут также использоваться как переменные indicator. Переменные indicator следуют за другим именами переменных в команде SQL без каких бы то ни было посторонних символов
кроме (необязательного) слова INDICATOR;</li>
<li>Обычно значение переменной indicator = 0. Если команда SQL пытается поместить NULL-значение в главную переменную, которая использует indicator,
в indicator будет установлено отрицательное значение. Этот факт можно
использовать для предотвращения ошибки и для пометки NULL-значений SQL для специальной обработки их в главной программе;</li>
<li>Переменная indicator может использоваться для вставки NULL-значений в
SQL-команды INSERT или UPDATE. Она также может
принимать положительное значение, указывающее длину отбрасываемой части строки, не поместившейся в предельные границы какой-нибудь переменной, куда эта строка помещалась.</li></ul>
<a name="25.16"></a>
<h3>РАБОТА СО SQL</h3>
<p><i><b>Обратите внимание:</b></i> ответы для этих упражнений написаны в псевдокодах, являющихся английским языком описания логики, которой должна
следовать программа. Это сделано для того, чтобы помочь читателям, которые могут быть не знакомы с ПАСКАЛем (или любым другим языком).<br>
Кроме того, это лучше сфокусирует ваше внимание на включаемых понятиях, опуская частности того или другого языка. Чтобы не противоречить нашим примерам, стиль псевдокода будет напоминать Паскаль.</p>
<p>Мы опустим из программ всё, что не относится напрямую к рассматриваемым вопросам, например, определение устройств ввода-вывода, подключение к базе данных и так далее. Конечно, имеется много способов
выполнения таких упражнений; и совсем не обязательно, что представленные варианты решений являются самыми удачными.</p>
<pre>1. Разработайте простую программу, которая выберет все комбинации полей snum и cnum
из таблиц Заказов и Заказчиков, и выясните, всегда ли предыдущая
комбинация - такая же, как последующая. Если комбинация из таблицы Заказов не найдена
в таблице Заказчиков, значение поля snum для этой строки будет изменено на удовлетворяющее
условию совпадения. Вы должны помнить, что курсор с подзапросом
модифицируем (ANSI-ограничение также применимо к просмотрам) и что
базисная целостность базы данных это не тоже самое, что проверка на ошибку
(т.е. первичные ключи уникальны, все поля cnums в таблице Заказов правильны, и так далее).
Проверьте раздел объявлений и убедитесь, что там объявлены все используемые курсоры.
2. Предположим, что ваша программа предписывает ANSI запрещение
курсоров или просмотров, использующих модифицируемые подзапросы. Как вы
должны изменить вышеупомянутую программу?
3. Разработайте программу, которая подсказывает пользователям изменить значения
поля city продавца, автоматически увеличивает комиссионные на .01 для продавца,
переводимого в Барселону, и уменьшает их на .01 для продавца, переводимого в Сан-Хосе.
Кроме того, продавец находящийся в Лондоне, должен потерять .02 из своих комиссионных,
независимо от того, меняет он город, или нет, в то время как продавец, не находящийся
в Лондоне должен иметь увеличение комиссионных на .02.
Изменение в комиссионных, основывающееся на нахождении продавца в Лондоне, может
применяться независимо от того, куда тот переводится.
Выясните, может ли поле city или поле comm содержать NULL-значения, и обработайте их,
как это делается в SQL.
Предупреждение! Эта программа имеет некоторые сокращения.
(См. ответы в <a href="a.php#25">Приложении A</a>.)</pre></body></html>
<?php
echo gb.'<a href="../../ycheb">Учебники</a>'.div;
echo gb.'<a href="'.H.'enter">Прихожая</a>'.div;
foot(); ?>