Использование переменной INDICATOR для эмуляции NULL значений SQL
Другая возможность состоит в том, чтобы обрабатывать переменную indicator, связывая ее с каждой переменной главного языка специальным способом, эмулирующим поведение NULL значений SQL.
Всякий раз, когда вы используете одно из этих значений в вашей программе, например в предложении if ... then, вы можете сначала проверить связанную переменную indicator, является ли ее значение=NULL. Если это так, то вы обрабатываете переменную по-другому. Например, если NULL значение было извлечено из поля city для главной переменной city, которая связана с переменной indicator — i_city, вы должны установить значение city равное последовательности пробелов. Это будет необходимо, только если вы будете распечатывать его на принтере; его значение не должно отличаться от логики вашей программы. Естественно, i_city автоматически устанавливается в отрицательное значение. Предположим, что вы имели следующую конструкцию в вашей программе:
If city = 'London' then comm := comm + .01
else comm := comm - .01
Любое значение, вводимое в переменную city, или будет равно 'London', или не будет равно. Следовательно, в каждом случае значение комиссионных будет либо увеличено, либо уменьшено. Однако эквивалентные команды в SQL выполняются по-разному:
EXEC SQL UPDATE Salespeople
SET comm = comm + .01
WHERE city = 'London';
и
EXEC SQL UPDATE Salespeople
SET comm = comm - .01;
WHERE city <> 'London';
Вариант на ПАСКАЛе работает только с единственным значением, в то время как вариант на SQL работает со всей таблицей.
Если значение city в варианте на SQL будет равно значению NULL, оба предиката будут неизвестны, и значение comm, следовательно, не будет изменено в любом случае.
Вы можете использовать переменную indicator, чтобы сделать поведение вашего главного языка не противоречащим этому, с помощью создания условия, которое исключает NULL значения:
If i_city >= O then
If city = 'London' then comm := comm + .01
else comm: = comm - .01;
В более сложной программе, вы можете захотеть установить Булеву переменную в "верно", чтобы указать, что значение city = NULL. Затем вы можете просто проверять эту переменную всякий раз, когда вам это необходимо.
Другое использование переменной INDICATOR
Переменная indicator также может использоваться для назначения значения NULL. Просто добавьте ее к имени главной переменной в команде UPDATE или INSERT тем же способом что и в команде SELECT. Если переменная indicator имеет отрицательное значение, значение NULL будет помещено в поле. Например, следующая команда помещает значения NULL в поля city и comm, таблицы Продавцов, всякий раз, когда переменные indicator — 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 используется, чтобы перемещать его по очереди к каждой строке вывода запроса.
· Курсоры являются модифицируемыми или только для чтения. Чтобы стать модифицируемым, курсор должен удовлетворять всем критериям, которым удовлетворяет просмотр(VIEW); кроме того, он не должен использовать предложений 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 <label>) или отсутствие какого-либо действия вообще (продолжить). Последнее установлено по умолчанию.
· Числовые переменные могут также использоваться как переменные indicator. Переменные indicator следуют за другими именами переменных в команде SQL, без каких бы то ни было посторонних символов кроме (необязательного) слова INDICATOR.
· Обычно значение переменной indicator = 0. Если команда SQL пытается поместить NULL значение в главную переменную, которая использует indicator, 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 для ответов.)
Приложение A
Ответы для упражнений
Глава 1
cnum
rating
Другим словом для строки является запись. Другим словом для столбца является поле.
Потому что строки, по определению, находятся без какого либо определенного упорядочения.
Глава 2
Символ (или текст) и номер
Нет
Язык Манипулирования Данными (ЯЗЫК DML)
Это слово в SQL имеет специальное учебное значение
Глава 3
SELECT onum, amt, odate
FROM Orders;
SELECT *
FROM Customers
WHERE snum = 1001;
SELECT city, sname, snum, comm
FROM Salespeople;
SELECT rating, cname
FROM Customers
WHERE city = 'SanJose';
SELECT DISTINCT snum
FROM Orders;
Глава 4
SELECT *
FROM Orders
WHERE amt > 1000;
SELECT sname, city
FROM Salespeople
WHERE city = 'London' AND comm > .10;
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');
Могут быть еще другие решения.
onumamtodatecnumsnum
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
onumamtodatecnumsnum
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
SELECT *
FROM Salespeople;
Глава 5
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;
SELECT *
FROM Customers
WHERE snum IN (1001,1004);
SELECT *
FROM Customers
WHERE cname BETWEEN 'A' AND 'H';
ПРИМЕЧАНИЕ: В ASCII базовой системе Hoffman не будет выведен из-за конечных пробелов после H. По той же самой причине вторая граница не может быть G, поскольку она не выведет имена Giovanni и Grass. G может использоваться в сопровождении с Z, так чтобы следовать за другими символами в алфавитном Заказе, а не предшествовать им, как это делают пробелы.
SELECT *
FROM Customers
WHERE cname LIKE 'C%';
SELECT *
FROM Orders
WHERE amt <> 0 AND (amt IS NOT NULL);
или
SELECT *
FROM Orders
WHERE NOT (amt = 0 OR amt IS NULL);
Глава 6
SELECT COUNT(*)
FROM Orders
WHERE odate = 10/03/1990;
SELECT COUNT (DISTINCT city)
FROM Customers;
SELECT cnum, MIN (amt)
FROM Orders
GROUP BY cnum;
SELECT MIN (cname)
FROM Customers
WHERE cname LIKE 'G%';
SELECT city, MAX (rating)
FROM Customers
GROUP BY city;
SELECT odate, count (DISTINCT snum)
FROM Orders
GROUP BY odate;
Глава 7
SELECT onum, snum, amt * .12
FROM Orders;
SELECT 'For the city ', city, ', the highest rating is ', MAX (rating)
FROM Customers
GROUP BY city;
SELECT rating, cname, cnum
FROM Customers
ORDER BY rating DESC;
SELECT odate, SUM (amt)
FROM Orders
GROUP BY odate
ORDER BY 2 DESC;
Глава 8
SELECT onum, cname
FROM Orders, Customers
WHERE Customers.cnum = Orders.cnum;
SELECT onum, cname, sname
FROM Orders, Customers, Salespeople
WHERE Customers.cnum = Orders.cnum AND Salespeople.snum = Orders.snum;
SELECT cname, sname, comm
FROM Salespeople, Customers
WHERE Salespeople.snum = Customers.snum AND comm * .12;
SELECT onum, comm * amt
FROM Salespeople, Orders, Customers
WHERE rating > 100 AND
Orders.cnum = Customers.cnum AND
Orders.snum = Salespeople.snum;
Глава 9
SELECT first.sname, second.sname
FROM Salespeople first, Salespeople second
WHERE first.city = second.city AND first.sname < second.sname;
Псевдонимам нет необходимости иметь именно такие имена.
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;
Ваш вывод может иметь некоторые отличия, но в вашем ответе все логические компоненты должны быть такими же.
SELECT a.cname, a.city
FROM Customers a, Customers b
WHERE a.rating = b.rating AND b.cnum = 2001;
Глава 10
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');
SELECT DISTINCT cname, rating
FROM Customers, Orders
WHERE amt > (SELECT AVG (amt)
FROM Orders)
AND Orders.cnum = Customers.cnum;
SELECT snum, SUM (amt)
FROM Orders
GROUP BY snum
HAVING SUM (amt) > (SELECT MAX (amt)
FROM Orders);
Глава 11
SELECT cnum, cname
FROM Customers outer
WHERE rating = (SELECT MAX (rating)
FROM Customers inner
WHERE inner.city = outer.city);
Решение с помощью соотнесенного подзапроса:
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
SELECT *
FROM Salespeople first
WHERE EXISTS (SELECT *
FROM Customers second
WHERE first.snum = second.snum AND rating = 300);
SELECT a.snum, sname, a.city, comm
FROM Salespeople a, Customers b
WHERE a.snum = b.snum AND b.rating = 300;
SELECT *
FROM Salespeople a
WHERE EXISTS (SELECT *
FROM Customers b
WHERE b.city = a.city AND a.snum <> b.snum);
SELECT *
FROM Customers a
WHERE EXISTS (SELECT *
FROM Orders b
WHERE a.snum = b.snum AND a.cnum <> b.cnum)
Глава 13
SELECT *
FROM Customers
WHERE rating >= ANY (SELECT rating
FROM Customers
WHERE snum = 1002);
cnumcnamecityratingsnum
2002 Giovanni Rome 200 1003
2003 Liu San Jose 200 1002
2004 Grass Berlin 300 1002
2008 Cisneros SanJose 300 1007
SELECT *
FROM Salespeople
WHERE city <> ALL (SELECT city
FROM Customers);
или
SELECT *
FROM Salespeople
WHERE NOT city = ANY (SELECT city
FROM Customers);
SELECT *
FROM Orders
WHERE amt > ALL (SELECT amt
FROM Orders a, Customers b
WHERE a.cnum = b.cnum AND b.city = 'London');
SELECT *
FROM Orders
WHERE amt > (SELECT MAX (amt)
FROM Orders a, Customers b
WHERE a.cnum = b.cnum AND b.city = 'London');
Глава 14
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" по длине.
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;
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
INSERT INTO Salespeople (city, cname, comm, cnum)
VALUES ('San Jose', 'Blanco', NULL, 1100);
DELETE FROM Orders WHERE cnum = 2006;
UPDATE Customers
SET rating = rating + 100
WHERE city = 'Rome';
UPDATE Customers
SET snum = 1004
WHERE snum = 1002;
Глава 16
INSERT INTO Multicust
SELECT *
FROM Salespeople
WHERE 1 < (SELECT COUNT (*)
FROM Customers
WHERE Customers.snum = Salespeople.snum);
DELETE FROM Customers
WHERE NOT EXISTS (SELECT *
FROM Orders
WHERE cnum = Customers.cnum);
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
CREATE TABLE Customers
(cnum integer,
cname char(10),
city char(10),
rating integer,
snum integer);
CREATE INDEX Datesearch ON Orders(odate);
(Все индексные имена, используемые в этих ответах — произвольные.)
CREATE UNIQUE INDEX Onumkey ON Orders(onum);
CREATE INDEX Mydate ON Orders(snum, odate);
CREATE UNIQUE INDEX Combination ON Customers(snum, rating);
Глава 18
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));
Первое решение предпочтительнее.
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);
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
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));
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));
Глава 20
CREATE VIEW Highratings
AS SELECT *
FROM Customers
WHERE rating = (SELECT MAX (rating)
FROM Customers);
CREATE VIEW Citynumber
AS SELECT city, COUNT (DISTINCT snum)
FROM Salespeople
GROUP BY city;
CREATE VIEW Nameorders
AS SELECT sname, AVG (amt), SUM (amt)
FROM Salespeople, Orders
WHERE Salespeople.snum = Orders.snum
GROUP BY sname;
CREATE VIEW Multcustomers
AS SELECT *
FROM Salespeople a
WHERE 1 < (SELECT COUNT (*)
FROM Customers b
WHERE a.snum = b.snum);
Глава 21
#1 — не модифицируемый, потому что он использует DISTINCT.
#2 — не модифицируемый, потому что он использует объединение, агрегатную функцию и GROUP BY.
#3 — не модифицируемый, потому что он основывается на #1, который сам по себе не модифицируемый.
CREATE VIEW Commissions
AS SELECT snum, comm
FROM Salespeople
WHERE comm BETWEEN .10 AND .20
WITH CHECK OPTION;
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
GRANT UPDATE (rating) ON Customers TO Janet;
GRANT SELECT ON Orders TO Stephen WITH GRANT OPTION;
REVOKE INSERT ON Salespeople FROM Claire;
Шаг 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;
Шаг 1:
CREATE VIEW Janetsview
AS SELECT *
FROM Customers
WHERE rating = (SELECT MIN (rating)
FROM Customers);
Шаг 2:
GRANT SELECT ON Janetsview TO Janet;
Глава 23
CREATE DBSPACE Myspace
(pctindex 15,
pctfree 40);
CREATE SYNONYM Orders FOR Diane.Orders;
Они должны быть откатаны обратно назад
Блокировка взаимоисключающего доступа
Только чтение
Глава 24
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 в вышеупомянутой команде — строго обязательны. Они представлены просто для понимания.
SELECT tname, synowner, COUNT (ALL synonym)
FROM SYTEMSYNONS
GROUP BY tname, synowner;
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
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 = 0 do { Цикл до тех пор, пока Wrong_Orders не опустеет }
begin
EXEC SQL FETCH Wrong_Orders INTO (:cnum, :snum);
if SQLCODE = 0 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; { основная программа }
Для данной программы, которую я использовал, решение будет состоять в том, чтобы просто включить поле onum первичным ключом таблицы Заказов, в курсор Wrong_Orders. В команде UPDATE вы будете затем использовать предикат WHERE onum =:ordernum (считая целую переменную ordernum объявленной), вместо 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 = 0 do {Цикл до тех пор пока Wrong_Orders не опустеет}
begin
EXEC SQL FETCH Wrong_Orders INTO (:odernum, :cnum, :snum);
if SQLCODE = 0 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; { основная программа }
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 = 0 do { Пока эти строки в таблице Продавцов. }
begin
if i_com < 0 then commnull: = true;
if i_cit < 0 then citynull: = true;
{ Установить логические флаги, которые могут показать NULLS.}
if citynull
then begin
write ('Нет текущего значения city для продавца ', snum,
' Хотите предоставить хотя бы одно? (Y/N)');
{ Подсказка покажет значение city состоящее из NULL значений. }
read (response);
{ Ответ может быть сделан позже. }
end { если citynull }
else begin { не citynull }
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)');
{ Обратите Внимание: Продавец, не назначеный в данное время в определенный город, не будет иметь изменений комиссионых при определении, находятся ли он в Лондоне. }
read (response);
{ Ответ теперь имеет значение независимо от того, что 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
Типы данных, распознаваемые стандартом SQL ANSI, состоят из символов и различных типов чисел, которые могут классифицироваться как точные числа и приблизительные числа.
Точные числовые типы — это номера, с десятичной точкой или без десятичной точки.
Приблизительные числовые типы — это номера в показательной (экспоненциальной по основанию 10) записи.
Для все прочих типов, отличия слишком малы чтобы их как-то классифицировать.
Иногда типы данных используют аргумент, который я называю размером аргумента, чей точный формат и значение меняется в зависимости от конкретного типа.
Значения по умолчанию обеспечены для всех типов, если размер аргумента отсутствует.
Типы ANSI
Ниже представлены типы данных ANSI (имена в круглых скобках — это синонимы):
TEXT —ТЕКСТ.
CHAR (CHARACTER) — Строка текста в формате, определенном реализацией. Размер аргумента здесь это единственное неотрицательное целое число, которое ссылается к максимальной длине строки. Значения этого типа, должны быть заключены в одиночные кавычки, например 'text'. Две рядом стоящие одиночные кавычки ('') внутри строки будет пониматься как одна одиночная кавычка (').
ПРИМЕЧАНИЕ:Здесь и далее, фраза определенный реализацией или зависящий от реализации, указывает, что этот аргумент или формат зависит от конкретной программы, в которой реализуются данные.
EXACT NUMERIC
ТОЧНОЕ ЧИСЛО
DEC (DECIMAL) — Десятичное число; то есть, число которое может иметь десятичную точку. Здесь аргумент размера имеет две части: точность и масштаб. Масштаб не может превышать точность. Сначала указывается точность, разделительная запятая и далее аргумент масштаба. Точность указывает сколько значащих цифр имеет число. Максимальное десятичное число составляющее номер —значение, определенное реализацией, равное или большее чем этот номер. Масштаб указывает максимальное число цифр справо от десятичной точки. Масштаб = 0 делает поле эквивалентом целого числа.
NUMERIC— Такое же как DECIMAL за исключением того, что максимальное десятичное не может превышать аргумента точности.
INT (INTEGER) — Число без десятичной точки. Эквивалентно DECIMAL, но без цифр справа от десятичной точки, то есть с масштабом, равным 0. Аргумент размера не используется (он автоматически устанавливается в значение, зависящее от реализации).
SMALLINT — Такое же как INTEGER, за исключением того, что, в зависимости от реализации, размер по умолчанию может (или не может) быть меньше, чем INTEGER.
APPROXIMATE NUMERIC — ПРИБЛИЗИТЕЛЬНОЕ ЧИСЛО
FLOAT — Число с плавающей запятой на основе 10 показательной функции. Аргумент размера состоит из одного числа, определяющего минимальную точность.
REAL — Такое же, как FLOAT, за исключением того, что никакого аргумента размера не используется. Точность установлена зависящей от реализации по умолчанию.
DOUBLE PRECISION (DOUBLE) — Такое же, как REAL, за исключением того, что точность, определяемая реализацией для DOUBLE PRECISION должна превышать определяемую реализацией точность REAL.