Visual FoxPro 9. Изменения в диалекте SQL

Содержание
Изменения в диалекте SQL
Почти неограниченное количество параметров в IN
Вложенные подзапросы
Дополнительная гибкость подзапросов
Вычисляемые поля с подзапросом
Вычисленние значения замены в UPDATE
Соотнесенные подзапросы и группировка
Использование предложения TOP N в подзапросах
Соотнесенные модификации
Соотнесённое удаление
Дальнейшее усовершенствование UNION
Использование имён в UNION
Вставка данных из объединённых запросов
Использование круглых скобки в UNION теперь не разрешается
Комбинирование DISTINCT и ORDER BY
Изменения в оптимизации
Полностью оптимизирован оператор LIKE с “%”
Улучшение быстродействия для TOP N
Улучшение эффективности с OR
Фильтрация и временные индексы
Ускорение соотнесений (корелляции)
Регистрация результатов оптимизации
Выборка из буферизированных таблиц
Отключение нового поведения
Новые возможности команд SQL
Новые возможности
Расширенные подзапросы
Составные подзапросы
GROUP BY в соотнесенном подзапросе
Предложение TOP N в не-соотнесенном подзапросе
Подзапросы в cписке полей команды SELECT
Агрегатные функции в списке полей SELECT подзапроса
Соотнесенные подзапросы позволяют сложным выражениям быть сравненимыми с соотнесенным полем
Изменения для выражений по сравнению с подзапросами.
Подзапросы в команде UPDATE...SQL
Подзапросы в предложении FROM
GROUP BY с именами полей в предложении UNION..
Эффективное выполнение TOP N
Улучшенная оптимизация для множественной таблицы с условием OR
Поддержка для локальных буферизированных данных.
Расширения других SQL команд
Предложение UNION в команде INSERT...SQL
Соотнесенная команда UPDATE...SQL
Соотнесенная команда DELETE...SQL
Обновляемые поля в команде UPDATE...SQL
SET ENGINEBEHAVIOR
Преобразование типов данных
Особенности использования команды SELECT… SQL
Определяемые пользователем функции в командах SELECT...SQL
Агрегатные функции
Правила присваивания имён столбцам
Операция объединения (UNION)
Команда SELECT...SQL c предложением WITH
Команда SET SQLBUFFERING

 

Изменения в диалекте SQL

Диалект SQL в VFP фактически не менялся в течение длительного времени. Начиная с восьмой версии, Microsoft начала включать в диалект дополнительные возможности, тем самым увеличивая соответствие его стандарту ANSI SQL-92. В VFP 9 существенно расширен диалект SQL для команд SELECT, INSERT, UPDATE и DELETE, а также добавлен ряд других усовершенствований.
Многие из ограничений, касающиеся запросов,  были отменены, было значительно расширено использование подзапросов, а в командах UPDATE и DELETE стало возможным использование условия объединения записей. В VFP 9 также предложены некоторые усовершенствования, повышающие эффективность, более простые способы проверки эффективности, и способность выполнять запросы для буферизированных таблиц.
Наиболее значимое изменение в системе запросов VFP 9 — это отмена ряда ограничений. В более ранних версиях, например, общее число объединений и подзапросов в одиночном запросе было ограничено девятью; в VFP 9 не имеется никаких ограничений. В таблице 1 показаны ограничения, связанные с запросами, отменённые или  расширенные в VFP 9.
Таблица 1. Сравнение ограничений SQL в разных версиях VFP


Описание

Ограничения в VFP 8 (и более ранних версиях)

Ограничения в VFP 9

Общее количество объединений и подзапросов

9

Нет ограничений

Общее количество Unions

9

Нет ограничений

Количество ссылок на таблицы (псевдонимы)

30

Нет ограничений

Количество значений в предложении IN

24

Определяется установкой функции SYS(3055)

Уровни вложенности для подзапросов

1

Нет ограничений

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


Листинг 1. Хорошо нормализованная база данных может включать множество обьединений и таблиц.
SELECT ProductName, CategoryName, OrderDate, Customers.CompanyName AS CustomerName, ;
         OrderDetails.Quantity, OrderDetails.UnitPrice, ;
         Suppliers.CompanyName AS SupplierName, Employees.LastName, Employees.FirstName, ;
         Territories.TerritoryDescription, Region.RegionDescription, ;
         Shippers.CompanyName AS ShipperName;
    FROM Orders ;
      LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID ;
      LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID ;
      LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID ;
      LEFT JOIN Categories ON Products.CategoryID = Categories.CategoryID ;
      LEFT JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID ;
      LEFT JOIN Shippers ON Orders.ShipVia = Shippers.ShipperID ;
      LEFT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ;
      LEFT JOIN EmployeeTerritories ;
           ON Employees.EmployeeID = EmployeeTerritories.EmployeeID ;
      LEFT JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID ;
      LEFT JOIN Region ON Territories.RegionID = Region.RegionID ;
    ORDER BY Products.ProductID, Orders.OrderDate ;
    INTO CURSOR Unfolded


В этом запросе перечислены 11 различных псевдонимов и выполняется 10 объединений; VFP 8 и более ранние версии не могли выполнять такой запрос (генерировалась ошибка 1805, «SQL — Слишком много подзапросов»). VFP 9 без проблем выполняет его. (В то время как этот пример был придуман, выборка из нормализованной базы данных может включать множество источников и объединений).

Почти неограниченное количество параметров в IN

В более ранних версиях VFP количество перечисляемых в предложении IN параметров было ограничено 24. VFP 9 не отменяет это ограничение, но предоставляет вам контроль над ним при помощи функции SYS(3055). Даже без использования этой функции существующее ограничение значительно выше, чем в более ранних версиях языка (154 параметра).
Предложение IN может использоваться как фильтр, основанный на списке параметров, так и как фильтр, основанный на результате подзапроса. Внесённое изменение относится только к использованию IN со списком параметров.
Когда вы самостоятельно управляете запросом, ограничение, налагаемое предложением IN, вообще не является проблемой. Как правило, вы находите другое решение, позволяющее избежать большого количества параметров в предложении IN. Одно из решений состоит в том, чтобы сохранить список значений в курсор и выполнить объединение с этим курсором. Например, этот запрос:

SELECT cFirst, cLast FROM Person ;
     WHERE UPPER(cLast) IN ("BLACK", "BROWN", "GREEN", "SILVER", "WHITE")
  мог бы быть заменен на:
  CREATE CURSOR Names (cName C(25))
  INSERT INTO Names VALUES ("BLACK")
  INSERT INTO Names VALUES ("BROWN")
  INSERT INTO Names VALUES ("GREEN")
  INSERT INTO Names VALUES ("SILVER")
  INSERT INTO Names VALUES ("WHITE")
  SELECT cFirst, cLast FROM Names JOIN Person ON UPPER(cLast) = RTRIM(cName)

Однако, вы не всегда имеете возможность написать код в таком виде. В частности, другие приложения, которые обращаются к данным VFP через OLE DB, могут генерировать запросы, которые используют только предложение IN, и не предлагают никакой иной возможности.
Функция SYS(3055) была добавлена в VFP 6 для того, чтобы решить проблему модифицируемых представлений. Она управляла распределением пространства для оценки предложений FOR и WHERE. Значение по умолчанию - 320, и оно может увеличиваться с приращением на 8. В наших тестах, каждое увеличение значения на 8 в SYS(3055) позволяло использовать еще 4 параметра в списке для IN.

Вложенные подзапросы

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

SELECT Company_Name FROM Customer ;
     WHERE Customer_ID NOT IN (SELECT Customer_ID FROM Orders) ;
     INTO CURSOR NoSales

Другое общее использование для подзапроса — соединение частей, которое затем позволяет основному запросу выбрать дополнительную информацию из составных результатов. Например, Вы можете использовать подзапрос для получения списка продуктов, последний раз приобретённых каждым покупателем, как показано в листинге 2. Подзапрос в этом примере кореллируется, что означает использование им поля из таблицы, не перечисленной непосредственно в подзапросе, а только в основном запросе. В этом примере OrdLast.Customer_ID используется в предложении WHERE подзапроса, но OrdLast — псевдоним для Orders, определенных в основном запросе.

Листинг 2. Здесь подзапрос находит самый последний заказ каждого заказчика, и затем использует эти результаты, чтобы получить список продуктов, включенных в этот заказ.
SELECT CustLast.Customer_ID, Product_ID FROM Order_Line_Items OLILast ;
       JOIN Orders OrdLast ON OLILast.Order_ID = OrdLast.Order_ID ;
       JOIN Customer CustLast ON OrdLast.Customer_ID = CustLast.Customer_ID ;
     WHERE OrdLast.Order_Date = (SELECT MAX(Order_Date) FROM Orders Ord ;
          WHERE OrdLast.Customer_ID = Ord.Customer_ID ) ;
     INTO CURSOR CustProducts

В VFP 8 и более ранних версиях вложенные подзапросы не поддерживались. То есть в предложении WHERE подзапроса нельзя было использовать другой подзапрос. VFP 9 позволяет использовать вложенные подзапросы, увеличивая число вопросов, на которые вы можете ответить, одиночным запросом.
Предположим, что вы хотите выяснять то, какие продукты компания включила в первый,  но не самый последний, заказ. В то время как вы могли бы использовать курсор, созданный в листинге 2 в другом запросе, в VFP 9 вы можете выполнить всю работу целиком в одиночном запросе, показанном в листинге 3.


Листинг 3. Вложеные подзапросы позволяют вам решать большее количество проблем в одиночном запросе скорее, чем в ряде запросов.
SELECT Customer.Company_Name, Product_ID FROM Order_Line_Items ;
    JOIN Orders ON Order_Line_Items.Order_ID = Orders.Order_ID ;
    JOIN Customer ON Orders.Customer_ID = Customer.Customer_ID ;
    WHERE Orders.Order_Date = ;
     (SELECT MIN(Order_Date) FROM Orders Ord ;
        WHERE Orders.Customer_ID=Ord.Customer_ID );
          AND Product_ID NOT IN ;
          (SELECT Product_ID FROM Order_Line_Items OLILast;
             JOIN Orders OrdLast ON OLILast.Order_ID = OrdLast.Order_ID ;
             JOIN Customer CustLast ;
                ON OrdLast.Customer_ID = CustLast.Customer_ID ;
             WHERE OrdLast.Order_Date = ;
               (SELECT MAX(Order_Date) FROM Orders Ord ;
                  WHERE OrdLast.Customer_ID = Ord.Customer_ID ) );
    INTO CURSOR FirstNotLast

Дополнительная гибкость подзапросов

Выше рассмотрен один способ улучшенного использования подзапросов в VFP 9. Фактически, группа VFP проделала большую работу над поддержкой подзапросов в этой версии. В VFP 8 и более ранних версиях, подзапросы могли использоваться только в предложении WHERE команды SQL, то есть только в фильтрации данных. В дополнение к отмене ограничения вложенности, VFP 9 позволяет вам использовать подзапросы в списке полей и предложении FROM запроса и в предложении SET команды SQL UPDATE. VFP 9 также позволяет использовать предложение GROUP BY в соотнесенных подзапросах (подзапросы, которые обращаются к одному или большему количеству полей из таблиц в основном запросе, подобно подзапросу в листинге 2). И, наконец, VFP 9 позволяет вам использовать в подзапросах предложение TOP в  не соотнесеных подзапросах.
Результат подзапроса в предложении FROM называется производной таблицей. Одно из использований производной таблицы — извлечение дополнительных данных, если вы применяете агрегатные функции.
Продолжим использовать базу данных TasTrade, которая поставляется с VFP, для рассмотрения проблемы обнаружения последнего заказа для каждого заказчика. Если все, что вы хотите получить - это ID заказчика и дата заказа, то это проще простого:
SELECT Customer_ID, MAX(Order_Date) FROM Orders GROUP BY 1 INTO CURSOR MostRecentOrder

Этот запрос прост и работает в каждой версии VFP. Тем не менее, предположите, что вы хотите получить не только ID заказчика, но и  дополнительную информацию о заказе, типа названия компании, контактное лицо компании и названия грузоотправителя. До VFP 9 вы должны были использовать соотнесенный подзапрос, два запроса в последовательности, или очень безобразный подзапрос. В листинге 4 показано  решение, использующее соотнесенный подзапрос, в листинге 5 показано использование методики двух последовательных запросов, и, наконец, в листинге 6 показан безобразный подзапрос. То, что делает подзапрос в листинге 6, не что иное, как безобразие - т.е. необходимость объединить ID заказчика и самую последнюю дату заказа в одно поле для сравнения.


Листинг 4. Обнаружение агрегатированных данных - одна из причин использования соотнесенного подзапроса.
SELECT Orders.Order_ID, Customer.Company_Name as Cust_Name, ;
         Shippers.Company_Name AS Ship_Name, Orders.Order_Date ;
    FROM Orders ;
      JOIN Customer ON Orders.Customer_ID = Customer.Customer_ID ;
      JOIN Shippers ON Orders.Shipper_ID = shippers.Shipper_ID ;
    WHERE Orders.Order_Date = ;
      (SELECT MAX(Order_Date) ;
            FROM Orders Ord WHERE Orders.Customer_ID=Ord.Customer_ID );
       ORDER BY Cust_Name ;
    INTO CURSOR MostRecentOrders

Листинг 5. Другой способ находить данные, связанные с составным результатом, состоит в том, чтобы использовать двапоследовательных запроса. Первый запрос выполняет группировку, и затем второй запрос выполняет объединение с полученной выборкой.
SELECT Orders.Order_ID, Customer.Company_Name as Cust_Name, ;
         Shippers.Company_Name AS Ship_Name, Orders.Order_Date ;
    FROM Orders ;
      JOIN Customer ON Orders.Customer_ID = Customer.Customer_ID ;
      JOIN Shippers ON Orders.Shipper_ID = shippers.Shipper_ID ;
    WHERE Orders.Order_Date = ;
      (SELECT MAX(Order_Date) ;
            FROM Orders Ord WHERE Orders.Customer_ID=Ord.Customer_ID );
       ORDER BY Cust_Name ;
    INTO CURSOR MostRecentOrders

Листинг 6. Тем не менее, другой способ находить данные, связанные с составным результатом, состоит в том, чтобы использовать подзапрос в предложении WHERE. Возникающий в результате запрос должен жёстко поддерживаться из-за странного выражения, используемого, чтобы соответствовать данным результирующего подзапроса.
SELECT Orders.Order_ID, Customer.Company_Name as Cust_Name, ;
         Shippers.Company_Name AS Ship_Name, Orders.Order_Date FROM Orders ;
      JOIN Customer ON Orders.Customer_ID = Customer.Customer_ID ;
      JOIN Shippers ON Orders.Shipper_ID = shippers.Shipper_ID ;
    WHERE Orders.Customer_ID + DTOS(Orders.Order_Date) IN ;
      (SELECT Customer_ID + DTOS(MAX(Order_Date)) FROM Orders GROUP BY Customer_ID );
    ORDER BY Cust_Name ;
    INTO CURSOR MostRecentOrders

VFP 9 дает вам лучший вариант. Вы можете выполнять подзапрос на лету в правой части предложении объединения. Помещение подзапроса в предложение обьединения означает, что он не должно быть соотнесённым; в некоторых случаях это приводит к тому, что запрос выполняется быстрее. Запрос в листинге 7 использует этот подход. Здесь подзапрос перемещён в предложение JOIN, и не должен объединять ID заказчика и самую последнюю дату. Вместо этого, ON часть JOIN сравнивает два поля по отдельности, что очень похоже на решение с двумя запросами в листинге 5.

Листинг 7. Этот запрос использует подзапрос в предложении FROM (производная таблица), чтобы решить проблему нахождения агрегатированных данных.
SELECT Orders.Order_ID, Customer.Company_Name as Cust_Name, ;
         Shippers.Company_Name AS Ship_Name, Orders.Order_Date ;
    FROM Orders ;
      JOIN (SELECT Customer_ID, MAX(Order_Date) as Order_Date ;
          FROM Orders CheckOrderDate GROUP BY 1) RecentOrder ;
        ON Orders.Customer_ID = RecentOrder.Customer_ID ;
        AND Orders.Order_Date = RecentOrder.Order_Date ;
      JOIN Customer ON Orders.Customer_ID = Customer.Customer_ID ;
      JOIN Shippers ON Orders.Shipper_ID = shippers.Shipper_ID ;
    ORDER BY Cust_Name ;
    INTO CURSOR MostRecentOrders

Имейте в виду, что подзапрос в предложении FROM не может быть коррелированным; это означает, что он не может обратиться к полям таблиц, используемых в основном запросе, а только к полям таблиц, которые он перечисляет. Происходит это потому, что все подзапросы в предложении FROM вычисляются прежде, чем выполняются обьединения, таким образом еще не ясно, какие записи находятся в результате.

Вычисляемые поля с подзапросом

В дополнение к поддержке производных таблиц, VFP 9 позволяет вам помещать подзапросы в список полей запроса. То есть вы можете использовать подзапрос, чтобы вычислить поле, которое должно появиться в результате. Подзапрос, использующий этот способ, должен возвратить одиночное поле и не больше, чем одиночная запись. Если не имеется никаких записей в результирующем наборе для специфической записи, то значение поля устанавливается равным NULL.
Для чего это нужно? Почему бы не включить выражение в основной запрос и добавить любые необходимые таблицы? Как и с производными таблицами, этот подход удобен, когда используется группировка. Допустим, что вы хотите найти общее количество заказов, размещённых каждым заказчиком в конкретном году. Вместе с этим вы хотите иметь больше информации о заказчике, которая включала бы его адрес, номер телефона и факса.
Несомненно, для того, чтобы вычислить общее количество заказов для каждого заказчика, вы должны группировать данные из Order_Line_Items по заказчикам. Вы можете извлекать множественные поля из таблицы Customer обычным способом, указывая их в предложении GROUP BY. (До VFP 8, вы могли указывать в запросе дополнительные поля, даже не перечисляя их в предложении GROUP BY. В VFP 8, Вы можете сделать то же самое,  выдав команду SET ENGINEBEHAVIOR 70) В листинге 8 показан запрос, который отыскивает id заказчика, название компании, адрес, телефон и факс наряду с общим значением количества заказов каждого заказчика.


Листинг 8. В запрос, группирующий поле или поля дочерней таблицы, вы можете добавлять поля из родительской таблицы, перечисляя их в предложении GROUP BY. Не забудьте определить значение nYear перед выполнением этого запроса.
SELECT Customer.Customer_ID, Customer.Company_Name, ;
         Customer.Address, Customer.City, Customer.Region, ;
         Customer.Postal_Code, Customer.Phone, Customer.Fax, ;
         SUM(quantity*unit_price) AS yTotal ;
  FROM Customer ;
    LEFT JOIN Orders ;
      JOIN Order_Line_Items;
        ON Orders.Order_ID = Order_Line_Items.Order_ID ;
      ON Customer.Customer_ID = Orders.Customer_ID ;
      AND BETWEEN(Order_Date,DATE(m.nYear,1,1),DATE(m.nYear,12,31)) ;
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8;
  INTO CURSOR CustomerTotal

Однако, добавление такого множества полей в предложение GROUP BY замедляет запрос. Другой вариант, который может использоваться в этой ситуации, должен удалить поля из предложения GROUP BY и перенести их в агрегатные функции  MAX() или MIN(). Как и в случае с группировкой, так как эти поля - те же самые для всех записей в группе, использование MAX() или MIN() не изменяет результат. Эта версия, показанный в листинге 9, является несколько более быстрой, чем перечисление всех полей в предложении GROUP BY.

Листинг 9. Второй вариант выборки родительских данных в сгруппированном запросе должен перенести все дополнительные поля в агрегатные функции MIN() или MAX(). Как обычно, не забудьте определить значение nYear перед выполнением этого запроса.
SELECT Customer.Customer_ID, MAX(Customer.Company_Name), ;
         MAX(Customer.Address), MAX(Customer.City), MAX(Customer.Region), ;
         MAX(Customer.Postal_Code), MAX(Customer.Phone), MAX(Customer.Fax), ;
         SUM(quantity*unit_price) AS yTotal ;
  FROM Customer ;
    LEFT JOIN Orders JOIN Order_Line_Items ON Orders.Order_ID = Order_Line_Items.Order_ID ;
      ON Customer.Customer_ID = Orders.Customer_ID ;
      AND BETWEEN(Order_Date,DATE(m.nYear,1,1),DATE(m.nYear,12,31)) ;
    GROUP BY 1;
    INTO CURSOR CustomerTotal

Однако, способность использовать подзапрос в списке поля обеспечивает гораздо более простое и более эффективное решение для этой проблемы. Вы можете вычислять общее количество заказов в подзапросе, позволяя основному запросу, обращаться только к родительской таблице. Вы не только устраняете дополнительные поля в GROUP BY и дополнительные обращения к агрегатным функциям, но вы можете также не использовать внешнее обьединение. Такой запрос показан в листинге 10.

Листинг 10. Использование подзапроса в списке полей упрощает проблему выборки дополнительных полей из родительской таблицы, когда объединение данных основано на дочерней таблице. Убедитесь, что присвоили nYear значение перед выполнением этого запроса.
SELECT Customer.Customer_ID, Customer.Company_Name, ;
         Customer.Address, Customer.City, Customer.Region, ;
         Customer.Postal_Code, Customer.Phone, Customer.Fax, ;
         (SELECT SUM(quantity * unit_price) ;
            FROM Orders ;
              JOIN Order_Line_Items;
                ON Orders.Order_ID = Order_Line_Items.Order_ID ;
            WHERE BETWEEN(Order_Date,DATE(m.nYear,1,1),DATE(m.nYear,12,31)) ;
              AND Customer.Customer_ID=Orders.Customer_ID ) as yTotal ;
    FROM Customer ;
    INTO CURSOR CustomerTotal
В наших тестах, используя показанные запросы, подзапрос в последнем варианте (листинг 10) выполнился приблизительно на 12% быстрее, чем в варианте с обращениями к MAX() (листинг 9), который, в свою очередь, выполнился приблизительно на 12% быстрее, чем запрос с перечислением всех родительских полей в предложении GROUP BY (листинг 8).

Вычисленние значения замены в UPDATE

Третье новое место, где вы можете использовать подзапросы — это предложение SET команды UPDATE. То есть вы можете использовать подзапрос, чтобы вычислить значение, в которое должно быть установлено поле. Однако, когда Вы используете такой подход, команда UPDATE не может включать подзапрос в предложение WHERE. Кроме того, вы ограничены одиночным подзапросом в предложении SET, так что вы не можете использовать этот подход, чтобы вычислить значения множественных полей.
Для примера предположите, что вы имеете данные по складу (SalesByProduct) для базы данных TasTrade, которые сообщают вам, сколько и какие продукты были проданы и количество этого сбыта в  долларах. Это разработано, чтобы хранить данные в течение одиночного месяца, и Вы хотите изменить их в конце месяца.
Чтобы модифицировать данные, используйте следующие команды UPDATE. Установите nMonth и nYear к значениям месяца и года для данных, которые вы собрали перед выполнением кода из листинга 11. (“Соотнесенные изменения”, рассматривающиеся позже в этой главе, обеспечивают лучшее решение для этой проблемы).


Листинг 11. Вы можете использовать подзапрос в предлжении SET команды UPDATE SQL, чтобы вычислить новые значения поля на лету.
UPDATE SalesByProduct SET TotalSales = ;
      (SELECT NVL(SUM(quantity * unit_price), $0) ;
        FROM Order_Line_Items ;
          JOIN Orders ON Order_Line_Items.Order_ID = Orders.Order_ID ;
        WHERE MONTH(Order_Date) = nMonth AND YEAR(Order_Date) = nYear ;
          AND Order_Line_Items.Product_ID = SalesByProduct.Product_ID)

  UPDATE SalesByProduct SET UnitsSold = ;
      (SELECT CAST(NVL(SUM(quantity),0) AS N(12)) ;
        FROM Order_Line_Items ;
          JOIN Orders ON Order_Line_Items.Order_ID = Orders.Order_ID ;
        WHERE MONTH(Order_Date) = nMonth AND YEAR(Order_Date) = nYear ;
          AND Order_Line_Items.Product_ID = SalesByProduct.Product_ID)

Во втором примере UPDATE используется новая функция CAST(), которая позволяет вам изменять типы данных на лету.

Соотнесенные подзапросы и группировка

Соотнесенный подзапрос - это запрос, который обращается к одному или большему количеству полей основного запроса, обычно в предложении WHERE подзапроса. VFP 8 запрещает использование предложения GROUP BY в соотнесенных подзапросах. Поскольку корреляция во многих случаях может давать тот же самый эффект, как группировка (см. например листинг 4), вы, возможно, столкнулись с этим ограничением.
Но имеется ряд ситуаций, где способность использовать GROUP BY в соотнесенном подзапросе позволяет гораздо проще получить желательные результаты. К счастью, VFP 9 разрешает группировки в соотнесенном подзапросе.
Например, рассмотрим случай, когда вы хотите получить список заказчиков, которые разместили в итоге по крайней мере один на один заказ больше, чем указанное значение, и имелись такие заказы, отправленные не в их адрес. (Вы могли бы проверять здесь подозрительные транзакции.) В VFP 8 и более ранних версиях для извлечения этой информации потребовалось бы выполнить два запроса: первый собирал бы информацию относительно заказов с определенной суммой, в то время как второй сравнивал бы адреса отправки с адресами заказчиков и извлекал информацию о заказчиках. Листинг 12 показывает один из способов сделать это, с пороговым набором значений суммы от $4000.


Листинг 12. В VFP 8 и более ранних версиях требовалось два запроса для получения списока больших заказов, отправленных куда-то, но не в штаб-квартиру компании, наряду с информацией о заказчике.
SELECT Orders.Customer_ID, Orders.Ship_to_Address, Orders.Order_ID, Orders.Order_Date ;
    FROM Orders JOIN Order_line_items ON Orders.Order_ID=Order_Line_Items.Order_ID ;
    GROUP BY 1, 2, 3, 4 HAVING SUM(Quantity * Unit_Price)> 4000 ;
    INTO CURSOR BigOrders

  SELECT Company_Name, Order_ID, Order_Date FROM Customer ;
      JOIN BigOrders ON Customer.Customer_ID = BigOrders.Customer_ID ;
    WHERE BigOrders.Ship_to_Address <> Customer.Address INTO CURSOR Suspicions

В VFP 9 способность группировки в соотнесенных подзапросах означает, что вы можете получить такой же результат в одном, более читаемом запросе. В листинге 13 показан вариант с одним запросом.

Листинг 13. Использование GROUP BY в соотнесенном подзапросе в VFP делает возможным найти заказчиков с подозрительными заказами в одиночном запросе.
SELECT Company_Name, Ord.Order_ID, Ord.Order_Date FROM Customer ;
     JOIN Orders Ord ON Customer.Customer_ID = Ord.Customer_ID ;
    WHERE Ord.Order_ID in ;
      (SELECT Orders.Order_ID FROM Orders ;
        JOIN Order_Line_Items ON Orders.Order_ID=Order_Line_Items.Order_ID ;
          AND Orders.Customer_ID=Customer.Customer_ID ;
          AND Orders.Ship_to_Address <> Customer.Address ;
      GROUP BY Orders.Order_ID ;
      HAVING SUM(Quantity * Unit_Price)> 4000) ;
    INTO CURSOR Suspicions

Как и в ряде других случаев, когда одиночный запрос заменяет два запроса, вариант, показанный в листинге 13, работает  быстрее, чем вариант в листинге 12.

Использование предложения TOP N в подзапросах

Предложение TOP N в SELECT возвращает в результирующем наборе только первые N записей (или первые N% записей). В то время как функции MIN() и MAX() позволяют вам выбирать одно самое маленькое или самое большое значение в данном поле, TOP N позволяет вам выбирать множественные значения. Вы можете использовать его, чтобы видеть вещи подобно 10 самым последним заказам или 30 наиболее дорогим изделиям. Например, следующий запрос возвратит в результате 10% изделий с наименьшей общей стоимостью.

SELECT TOP 10 PERCENT Product_ID, SUM(Quantity) AS nSales FROM Order_Line_Items ;
   GROUP BY Product_ID ORDER BY nSales INTO CURSOR LowSales

В VFP 8 и более ранних версиях вы не могли использовать предложение TOP N в подзапросах. VFP 9 разрешает использование TOP N в подзапросах, если подзапрос не соотнесен. Когда вы используете TOP N в подзапросе, вы должны обязательно включить в этот подзапрос предложение ORDER BY.
Представьте себе, что вы рассматриваете вопрос прекращения поставок изделий, извлеченных предыдущим запросом  (наиболее плохо продающиеся изделия). Возможно, вы захотите установить контакт с теми заказчиками, которые приобрели эти изделия, чтобы удостовериться, что это не создало для них проблемы. Запрос в листинге 14 использует предыдущий запрос как подзапрос для создания такого списка заказчиков.

Листинг 14. Здесь подзапрос находит самые плохо продаваемые 10% изделий. Основной запрос использует эту информацию для получения списка заказчиков, купивших эти изделия.
SELECT DISTINCT Company_Name, English_Name FROM Customer ;
     JOIN Orders ON Customer.Customer_ID = Orders.Customer_ID ;
     JOIN Order_Line_Items ON Orders.Order_ID = Order_Line_Items.Order_ID ;
     JOIN (SELECT TOP 10 PERCENT Product_ID, SUM(Quantity) AS nSales FROM Order_Line_Items ;
              GROUP BY Product_ID ORDER BY nSales ) AS LowSales ;
        ON Order_Line_Items.Product_ID = LowSales.Product_ID ;
     JOIN Products ON LowSales.Product_ID = Products.Product_ID ;
     ORDER BY English_Name, Company_Name INTO CURSOR BoughtLowSellers

Соотнесенные модификации

В дополнение к поддержке подзапросов в предложении SET, команда UPDATE SQL в VFP 9 имеет новое предложение FROM, которое позволяет Вам получать данные модификации из другой таблицы. Это позволяет вам использовать соотнесенные модификации.
Пример в листинге 11 имеет один серьезный недостаток. Вы должны использовать отдельную команду UPDATE для каждого поля, которое вы хотите изменить. При использовании предложения FROM вы можете получить тот же самый результат с запросом, сопровождаемым командой UPDATE. Код в листинге 15 вычисляет новые значения, сохраняет их в курсоре, и затем ссылается на этот курсор в команде UPDATE.


Листинг 15. Новое предложение FROM в UPDATE SQL позволяет вам получать значения для замены из другой таблицы.
SELECT Order_Line_Items.Product_ID, ;
         SUM(Quantity*Order_Line_Items.Unit_Price) as TotalSales, ;
         SUM(Quantity) AS UnitsSold ;
    FROM Order_Line_Items ;
      JOIN Orders ON Order_Line_Items.Order_ID = Orders.Order_ID ;
        AND MONTH(Order_Date) = nMonth AND YEAR(Order_Date) = nYear ;
    GROUP BY 1 ;
    INTO CURSOR MonthlySales

  UPDATE SalesByProduct ;
    SET SalesByProduct.TotalSales = NVL(MonthlySales.TotalSales, $0), ;
        SalesByProduct.UnitsSold = NVL(MonthlySales.UnitsSold, 0) ;
    FROM SalesByProduct ;
      LEFT JOIN MonthlySales ;
        ON SalesByProduct.Product_ID = MonthlySales.Product_ID

Наряду с определением того, что значения берутся из другой таблицы, вы можете фактически выполнять объединения в предложении FROM, чтобы получить список значений. В листинге 15 внешнее обьединение гарантирует, что записи для изделий, не проданных в указанном месяце, установлены в 0.
Фактически, предложение FROM в UPDATE поддерживает подзапросы (производные таблицы), так что вы можете выполнить всю эту операцию в одиночной команде UPDATE, как показано в листинге 16.

Листинг 16. Вместо того, чтобы выполнять запрос до того, как получены результаты, вы можете использовать полученную таблицу в предложении FROM команды UPDATE.
UPDATE SalesByProduct ;
    SET SalesByProduct.TotalSales = NVL(MonthlySales.TotalSales, $0), ;
        SalesByProduct.UnitsSold = NVL(MonthlySales.UnitsSold, 0) ;
    FROM SalesByProduct ;
      LEFT JOIN (;
        SELECT Order_Line_Items.Product_ID, ;
               SUM(Quantity*Order_Line_Items.Unit_Price) as TotalSales, ;
               SUM(Quantity) AS UnitsSold ;
          FROM Order_Line_Items ;
            JOIN Orders ;
              ON Order_Line_Items.Order_ID = Orders.Order_ID ;
              AND (MONTH(Order_Date) = nMonth AND YEAR(Order_Date) = nYear) ;
          GROUP BY 1) AS MonthlySales ;
        ON SalesByProduct.Product_ID = MonthlySales.Product_ID

Соотнесённое удаление

В VFP 8 и в более раних версиях, команда DELETE SQL позволяла вам указывать только одну таблицу. В то время как вы могли использовать подзапросы в предложении WHERE, удаление записей, основанных на информации из других таблиц, было сложным. VFP 9 позволяет Вам перечислять множественные таблицы в предложении DELETE's FROM, соединяя их согласно обычным правилам. Это обеспечивает намного более качественный способ выполнить соотнесенное удаление, удаляя строки из одной таблицы, основанной на данных из одной или большего количества других таблиц.
В синтаксисе для соотнесенного DELETE существует небольшая путаница. Если предложение FROM в DELETE содержит больше чем одну таблицу, вы должны определить целевую таблицу для удаления записей между DELETE и FROM:

DELETE [Адресат] FROM Table1 [JOIN Table2 …]

Используйте локальный псевдоним целевой таблицы между DELETE и FROM. Это может быть имя таблицы, но если вы назначаете локальный псевдоним к таблице в предложении FROM, используйте его вместо имени. (Обратите внимание, что те же самые правила относятся к UPDATE, когда модифицируемая таблица также включена в предложение FROM, как в листинге 16.)
База данныхTasTrade не содержит в себе примеров удаления такого типа; она разработана с учётом того, что записи отмечены неактивными скорее чем удаленными. Тем не менее, предположите, что вы имеете таблицы Products (Продукты) и Suppliers (Поставщики), подобные таким же таблицам в TasTrade, с первичным ключом Supplier, используемым как внешний ключ в Products. Предположите, что имеется проблема при получении изделий из Австралии, и вы решаете убрать из вашего списка изделий все изделия, которые приходят от поставщиков из Австралии. Чтобы выполнять такое удаление в VFP 8, вы используете подзапрос, подобный показанному в листинге 17. В VFP 9 вы можете вместо этого использовать условие JOIN. листинге 18 показана команда DELETE, удаляющая эти изделия из таблицы Products.


Листинг 17. В VFP 8 и более ранних версиях использовался такой способ удаления записей из таблицы, основанной на данных,  полученных в другом подзапросе.
DELETE FROM Products ;
            WHERE Supplier_ID IN ( ;
                    SELECT Supplier_ID ;
                    FROM Supplier ;
                          WHERE UPPER(Supplier.Country) = "AUSTRALIA" )

Листинг 18. Команда DELETE теперь поддерживает множественные таблицы в предложении FROM. Условия обьединения определяют, какие записи должны быть удалены.
DELETE Products FROM Products ;
       JOIN Supplier ON Products.Supplier_ID = Supplier.Supplier_ID ;
       WHERE UPPER(Supplier.Country) = "AUSTRALIA"

Дальнейшее усовершенствование UNION

Предложение UNION в SELECT позволяет Вам объединять результаты нескольких запросов в одиночный набор результатов. В VFP 8, правила для UNION были ослаблены, делая более легким использование этого предложения. VFP 9 предлагает еще два усовершенствования в UNION и одно ограничение.

Использование имён в UNION

В более ранних версиях VFP, когда Вы использовали  предложение UNION для объединения множества запросов в одиночный результат, предложение ORDER BY могло перечислять только позиции полей из списка полей. Вы не могли обратиться к полям по их именам, даже если поле имело одно и то же имя в каждом запросе в UNION. Это делало такие запросы трудно читаемыми и трудно поддерживаемыми, потому список в ORDER BY должен быть откорректирован, если появлялись изменения в порядке указания полей.
В VFP 9 вы можете использовать имена полей в предложении ORDER BY объединяющего запроса. Имена поля, которые вы используете - те же, что и в результирующем наборе. Нужно иметь ввиду, что, когда имена соответствующих полей в UNION различны, в результирующем наборе будет использоваться имя поля из последнего запроса в UNION. Лучшим подходом, чем зависимость от порядка запросов, было бы использование предложения AS, чтобы гарантировать, что соответствующие поля имеют то же самое имя в каждом запросе в UNION.
В листинге 19 показан простой пример, реализующий эту возможность. Даже в этом простом запросе использование имён полей значительно увеличивает читабельность.


Листинг 19. Имена полей в ORDER BY — VFP 9 позволяет вам использовать имена полей из результирующего набора в предложении ORDER BY объединяемых в UNION запросов.
SELECT Company_Name, Address, City, Region, Postal_Code, Country FROM Customer ;
UNION ;
SELECT Company_Name, Address, City, Region, Postal_Code, Country FROM Supplier ;
ORDER BY Country, City ;
INTO CURSOR AllCompanies

Вставка данных из объединённых запросов

VFP 8 представил возможность заполнять таблицу или курсор непосредственно из результата запроса, используя синтаксис INSERT INTO … SELECT. Это сделало возможным получать результаты и добавлять их за один шаг.
VFP 9 добавляет дополнительную возможность в этот синтаксис: используемый запрос может включать предложение UNION. Это означает, что Вы можете консолидировать данные и добавлять их в таблицу или курсор за один шаг.
Например, предположите, что вы имеете данные по складу для TasTrade, содержащие ежегодную продажу каждым служащим товаров, а так же годовой итог по каждому служащему. (Обратите внимание, что это - другие данные со склада, чем те, которые описаны в разделе "Вычисленние значения замены в UPDATE" ранее в этой главе.)
Вы можете вычислять продажи каждого товара каждым служащим за указанный год в одном запросе; точно так же вы можете вычислять в одном запросе общие количества продаж для каждого служащего в течение года. Однако, сбор специфических и общих данных для товара требует использования или двух запросов или запроса, включающего UNION. Если Вы хотите добавлять годовое состояние данных по складу, вы можете делать это в INSERT, как показано в листинге 20.


Листинг 20. VFP 8 добавил возможность использования в INSERT непосредственно результата запроса; VFP 9 расширяет эту возможность, позволяя использовать объединение запросов. Убедитесь, что присвоили значение к переменной nYear перед выполнением этого примера.
INSERT INTO Warehouse ;
  SELECT CrossProd.Product_ID, ;
         CrossProd.Employee_ID, ;
         m.nYear as nYear, ;
         NVL(nUnitsSold, 0), ;
         NVL(nTotalSales, $0);
     FROM (SELECT Employee.Employee_ID, Products.Product_ID ;
        FROM Employee, Products) AS CrossProd ;
       LEFT JOIN ( ;
        SELECT Product_ID, Employee_ID, ;
               SUM(Quantity) AS nUnitsSold, ;
               SUM(Quantity * Unit_Price) AS nTotalSales ;
           FROM Orders ;
             JOIN Order_Line_Items ON Orders.Order_ID = ;
               Order_Line_Items.Order_ID ;
           WHERE YEAR(Order_Date) = m.nYear ;
           GROUP BY Product_ID, Employee_ID ) ;
           AS AnnualSales ;
        ON CrossProd.Employee_ID = AnnualSales.Employee_ID ;
        AND CrossProd.Product_ID = AnnualSales.Product_ID ;
  UNION ;
  SELECT "Total" AS Product_ID, Employee.Employee_ID, ;
         m.nYear AS nYear, ;
         CAST(NVL(SUM(Quantity),0) as N(12)) ;
           AS nUnitsSold, ;
         NVL(SUM(Quantity * Unit_Price), $0) ;
           AS nTotalSales ;
     FROM Orders ;
      JOIN Order_Line_Items ;
         ON Orders.Order_ID = Order_Line_Items.Order_ID ;
        AND YEAR(Order_Date) = m.nYear ;
       RIGHT JOIN Employee ;
         ON Orders.Employee_ID = Employee.Employee_ID ;
     GROUP BY Employee.Employee_ID ;
  ORDER BY 2, 1

Использование круглых скобки в UNION теперь не разрешается

Хотя это действительно не было синтаксически правильно, более ранние версии VFP не создавали объект, если запросы в UNION были включены в круглые скобки. В VFP 9 одиночный запрос в UNION может быть окружен круглыми скобками, но круглые скобки, помещённые вокруг множественных запросов в UNION, генерируют новую ошибку с номером 2196.
Листинг 21 демонстрирует запрос, который работает в VFP 8, но терпит неудачу в VFP 9 из-за этого нового правила.


Листинг 21. Размещение круглых скобок вокруг множественных запросов в UNION приводит к возникновению ошибки.

SELECT Company_Name, Address, City, Region, Postal_Code, Country FROM Customer ;
UNION ;
(SELECT Company_Name, Address, City, Region, Postal_Code, Country FROM Supplier ;
 UNION ;
 SELECT Company_Name, "", "", "", "", "" FROM Shippers)

Согласно рекомендации группы Fox team, использование круглых скобок для выделения множественных объединений может привести к неправильным результатам.

Комбинирование DISTINCT и ORDER BY

VFP позволяет Вам упорядочивать результаты запроса по любому полю из исходных таблиц; поля, указанные в списке ORDER BY, могут отсутствовать в списке полей запроса. В VFP 9 это больше не работает для запросов, которые используют SELECT DISTINCT. Например, следующий запрос выполняется в VFP 8, но приводит к ошибке 1808 (“SQL: предложение ORDER BY недопустимо”) в VFP 9:
SELECT Distinct Customer_ID    FROM Orders ORDER BY Order_Date

На это поведение воздействует установка SET ENGINEBEHAVIOR. (См. “Отключение нового поведения” позже в этой главе).

Изменения в оптимизации

VFP 9 содержит несколько изменений, улучшающих эффективность ваших запросов, а также новую функцию, которая делает  тестирование оптимизации проще.

Полностью оптимизирован оператор LIKE с “%”

Оператор LIKE позволяет вам сравнивать строки. Если условие в команде SQL включает cField LIKE cString, указанное поле сравнивается с указанной символьной строкой на базе символ - символ. В отличие от оператора "=", если cString короче, чем cField, то они не соответствуют условию, если не используется группирующий символ. Оператор LIKE поддерживает два группирующих символа: “_”, чтобы представить одиночный неизвестный символ, и “%”, чтобы представить 0 или большее количество неизвестных символов. Например, вы можете найти всех заказчиков в TasTrade, чьи имена начинаются с символа “P”, используя следующий запрос:
SELECT Customer_ID, Company_Name FROM Customer ;
  WHERE UPPER(Company_Name) LIKE "P%" ;
  INTO CURSOR PCompanies

Более ранние версии VFP не могли полностью оптимизировать этот запрос. Выражение LIKE “string%” могло быть только частично оптимизировано. VFP 9 полностью оптимизирует такие выражения. (Полная оптимизация применяется только, когда групповой символ % расположен в конце символьной строки.)
Наши тесты показали смешанные результаты относительно эффекта этой оптимизации. Для многих запросов, версии с использованием LIKE и "=" выполнялись одинаково быстро и в VFP 8, и в VFP 9. Однако в ситуации, возникающей, когда VFP берет сокращение и просто фильтрует первоначальную таблицу, оптимизация LIKE сделанного над значительным различием. VFP берет это сокращение с любым запросом, который включает одиночную таблицу, не имеет никаких расчетных полей и - полностью оптимизирует. Фильтрация исходной таблицы прежде чем создание фактического файла на диске экономит значительное время. Оптимизация LIKE означает, что движок  VFP может принимать этот подход с некоторыми дополнительными запросами. (Вы можете выключить это сокращение включением предложения NOFILTER в запросе.)

Улучшение быстродействия для TOP N

Когда вы используете предложение TOP N или TOP N PERCENT, чтобы возвратить только подмножество записей, которые так или иначе соответствуют условиям запроса, VFP должен выяснить, какие записи имеются наверху списка. Когда вы определяете TOP N для большого набора, тот процесс может потребовать значительного времи. VFP 9 улучшает эффективность в этой ситуации.
В наших тестах мы не замечали различий, пока мы работали с не очень большой таблицей. При выборе TOP 20 из таблицы c почти 75,000 записей различия практически отсутствовали. Но когда мы сделали TOP 20 для таблицы, содержащей более чем миллион записей, то VFP 9 закончил выборку приблизительно в три раза быстрее, чем VFP 8.
Помимо уменьшения времени выборки с TOP N, немного изменилось и поведение запросов с TOP N. В более ранних версиях VFP запрос с предложением TOP N мог возвращать больше чем N записей из-за связей в данных. VFP 9 никогда не возвращает больше, чем точное число записей, определенных в предложении TOP N. (См. “Отключение нового поведения” позже в этой главе для обзора исключений из этого правила.) Когда имеются связи, оказывается, что VFP выбирает записи в физическом порядке из группы с тем же самым значением.
Эти изменения относятся к тем, на которые воздействует новая установка SET ENGINEBEHAVIOR TO 90.  Для подробной информации см. “Отключение нового поведения” позже в этой главе.

Улучшение эффективности с OR

В более ранних версиях, когда запрос использовал оператор OR для объединения условий, включающих различные таблицы, результат не мог быть всегда оптимизирован. Изменения в движке SQL для VFP 9 позволяют оптимизировать такие условия, если индивидуальные условия - оптимизируемы.
В нашем тестировании не каждый запрос, использующий OR с условиями, основанными на различных таблицах, показал улучшенное быстродействие, но в некоторых случаях мы смогли заметить различие. Например, следующий запрос (используется база данных Northwind) в VFP 9 выполнился почти четыре раза быстрее:
SELECT Orders.OrderId, ProductId FROM Orders ;
     JOIN OrderDetails ON Orders.OrderId=OrderDetails.OrderId  ;
 WHERE ;
   (Orders.OrderDate=DATE(1997,9,1) AND OrderDetails.Quantity>2);
    OR ;
   (Orders.OrderDate=DATE(1997,9,2) AND OrderDetails.Quantity>3);
 INTO CURSOR Result

Вы можете также заметить некоторые усовершенствования в использовании OR в подзапросах.

Фильтрация и временные индексы

Когда вы объединяете в запросе две таблицы, VFP выбирает индексный тэг, подходящий для соответствующей записи. Если рассматриваемые поля не индексированы, или существующий тэг не способен сильно помочь (например, когда обьединение включает маленькую таблицу и намного большую таблицу, и имеется только тэг для меньшей таблицы), VFP создает индекс на лету. При вызове функции SYS(3054) это обьединение показывается как “использующее временный индекс” (“using temp index”).
VFP 9 ускоряет выполнение запросов, которые формируют временный индекс и имеют не оптимизируемый фильтр для этой  таблицы (той, для которой индекс сформирован). Эффект проявляется наиболее сильно для больших таблиц и в случаях, где не оптимизированный фильтр маскирует много записей. Мы предполагаем, что двигатель делает фильтрацию перед формированием временного индекса, что ускоряет и формирование временного индекса и использование существующего.
В наших тестах ни одна из типовых таблиц, которые приходят с VFP, не была достаточно большой, чтобы наблюдать этот эффект. Мы смогли продемонстрировать это усовершенствование, используя таблицу с приблизительно 75,000 записей. Наш тестовый запрос соединил таблицу саму с собой на неиндексированном поле, используя условие фильтра, которое выбрало только приблизительно 250 записей. В VFP 9 запрос потребовал две секунды. В VFP 8 тот же самый запрос выполнялся почти 50 секунд.

Ускорение соотнесений (корелляции)

Вы можете никогда не столкнуться с другой областью, где эффективность была так улучшена. Если вы имеете запрос с соотнесенным подзапросом, и запрос также включает фильтр в таблице от основного запроса, которая используется в подзапросе, VFP 9 выполняет измерения лучше, чем VFP 8.
Мы создали следующий запрос, используя данные из TasTrade, и обнаружили, что VFP 9 отработал приблизительно в четыре раза быстрее:
SELECT Orders.Order_ID, Customer.Company_Name as Cust_Name, ;
       Shippers.Company_Name AS Ship_Name, Orders.Order_Date ;
  FROM Orders ;
    JOIN Customer ;
      ON Orders.Customer_ID = Customer.Customer_ID ;
    JOIN Shippers ;
      ON Orders.Shipper_ID = shippers.Shipper_ID ;
  WHERE Orders.Discount>0 AND ;
    Orders.Order_Date = (SELECT MAX(Order_Date) ;
       FROM Orders Ord ;
       WHERE Orders.Customer_ID=Ord.Customer_ID );
  ORDER BY Cust_Name ;
  INTO CURSOR MostRecentOrders

Регистрация результатов оптимизации

Функция SYS(3054) появилась в VFP 5. Она предоставляла вам информацию относительно того, как FoxPro оптимизирует запрос. Она несколько раз улучшалась и теперь предоставляет множество данных относительно процесса оптимизации. Однако, нужно все еще интенсивно использовать SYS(3054), чтобы собрать информацию относительно эффективности запроса для всей программы или приложения.
Вызовите SYS(3092). Эта новая функция позволяет вам направлять вывод от функции SYS(3054) в регистрационный файл. Само собой, функция SYS(3054) может посылать вывод только активному окну или переменной. С SYS(3092) вы можете собирать данные относительно целого ряда запросов и исследовать их в свободное время.
Синтаксис:
CLogFile = SYS(3092 [, cFileName [, lAdditive]])

Параметр cFileName определяет имя (включая путь) регистрационного файла. Используйте lAdditive, чтобы определить, нужно ли записывать информацию поверх существующей. Значение по умолчанию перезавписывает существующий файл.
Выключить регистрацию и сделать файл регистрации доступный для чтения можно, передав пустую строку в качестве параметра cFileName.
Функция возвращает имя активного регистрационного файла. Обратите внимание, что новый регистрационный файл создаётся прежде, чем какое-либо значение возвращено, поэтому для сохранения старого регистрационного файла перед его изменением вы должны вызвать функцию один раз без параметров, и затем вызывите её снова.
Когда Вы включаете регистрацию функцией SYS(3092), вывод из SYS(3054) все еще отображается на экране, в активном окне или сохраняется в указанной переменной.
Как только вы указываете регистрационный файл в SYS(3092), используйте SYS(3054) по мере того, как вы хотите, и выполните запросы, которые вы хотите проверить. Когда проверки выполнены, сбросьте SYS(3054), и затем выдайте SYS(3092, " ") чтобы прекратить регистрацию. Вы можете затем исследовать регистрационный файл, чтобы просмотреть ваши результаты оптимизации.
Информация в регистрационном файле наиболее полезна, если вы передаете значение 2 или 12 как второй параметр для SYS(3054). Добавленная в VFP 7, эта установка включают запрос непосредственно в вывод перед сообщением относительно оптимизации.

Выборка из буферизированных таблиц

Ещё со времён VFP 3 разработчики FoxPro были расстроены поведением SELECT по отношению к буферизированным курсорам. Когда в запросе  используется буферизированная таблица, VFP фактическую использует таблицу на диске, а не открытую буферизированную версию. Это означает, что результаты запроса не отражают несохранённые изменения данных.
Это поведение естественным образом следует из нормального поведения запросов. Независимо от того, открыта таблица или нет, когда она указана в предложении FROM запроса, VFP открывает её заново в новой рабочей области. В некоторых случаях было бы действительно удобно иметь возможность переместить данные из буферизированной таблицы в запрос. В VFP 8 и более ранних версиях вместо этого вы должны были обращаться к командам Xbase (типа  CALCULATE).
VFP 9 предоставляет вам опцию просмотра буферизированных данных. Добавьте новое предложение WITH (Buffering=.T.) к запросу, и он использует доступный буфер вместо таблицы, сохранённой на диске. В листинге 22 показан запрос, который подсчитывает количество заказчиков в каждой стране, используя буферизированные данные.


Листинг 22. Вы можете делать выборку из буферизированных данных, используюя новое предложение WITH (Buffering=.T.)

SELECT Country, CNT(*) FROM Customer WITH (Buffering = .T.) ;
   GROUP BY Country INTO CURSOR BufferedCount

Предложение WITH применяется к одиночной таблице. Если запрос перечисляет множественные таблицы, для которых вы хотите использовать буферизированные данные, включите утверждение WITH для каждой таблицы.
Одно большое предупреждение. Если вы используете буферизацию строки, запрос выполняет изменения для текущей строки. Это фактически имеет смысл, поскольку запрос перемещает указатель записи в буферизированной таблице. В более ранних версиях, где запросы запрашивали данные с диска, указатель записи в буфере не двигался, но когда вы делаете запрос  непосредственно к буферу, указатель записи перемещается.
Вы можете также управлять поведением запросов с буферизированными таблицами глобально. Новая команда SET SQLBUFFERING позволяет вам определять, получают ли запросы данные по умолчанию с диска или из буферов. Предложение WITH (Buffering = lExpr) отменяет текущую установку для специфицированной таблицы и запроса. SET SQLBUFFERING действует в текущем сеансе данных. Используйте SET(“SQLBUFFERING”), чтобы узнать текущую установку.

Отключение нового поведения

Значительные изменения для двигателя SQL в VFP, появившиеся в VFP 8, вызвали ряд проблем для некоторых существующих приложений. Для того, чтобы не заставлять силой разработчиков изменять рабочий код в VFP 7 более ранних версиях, группа Fox team добавила команду SET ENGINEBEHAVIOR, которая позволяет вам отключать эти изменений в VFP 8. В то же время это не лучшее решение - использовать эту отмену постоянно, тем не менее команда обеспечила гибкое решение для уже существующих приложений.
Большинство изменений, появившихся в движке SQL в VFP 9, вряд ли вызовут проблемы совместимости. Однако, имеются несколько моментов, которые могут быть вызвать такие проблема в некоторых приложений, так что группа Fox team добавила новую установку для SET ENGINEBEHAVIOR.
Как отмечено в “Улучшении быстродействия для TOP N” ранее в этой главе, в VFP 9 запрос с TOP N теперь возвращает точно N записей; в случае связей между таблицами, это может привести к потере некоторых из связанных результатов.
Когда запрос включает одну из агрегатных функций (CNT(), SUM(), AVG(), MIN() or MAX())), но не содержит предложения GROUP BY, VFP 9 всегда возвращает одину запись. Если никакие записи не соответствуют условиям объединения и фильтрации, то результирующая запись содержит значение NULL для всех полей. В более ранних версиях такой запрос возвратил бы пустой результат (_TALLY = 0). 
И, наконец, все поля, перечисленные в предложении ORDER BY запроса, использующего SELECT DISTINCT, должны быть включены в список полей запроса.
Чтобы отключить такое поведение, установите ENGINEBEHAVIOR в 80 или 70. По умолчанию значение ENGINEBEHAVIOR установлено в  90, что делает возможным новое поведение.
Заключение
Многие изменения в диалекте SQL для VFP 9 увеличивают его совместимость со стандартом SQL-92. Они также обеспечивают большее количество необходимых инструментальных средств для управления вашими данными. В то время как одни из изменений, вероятно,  не будут сильно воздействать на вашу ежедневную работу, вы, вероятно найдете, что другие удобны и могут многократно использоваться. Способность извлекать некоторые данные в одиночном запросе там, где ранее были необходимы два запроса, не только имеет тенденцию к уменьшению времени на выполнение, но также делает код запроса проще.


Новые возможности команд SQL

Команда SELECT...SQL и другие команды SQL в девятой версии Visual FoxPro были значительно расширены. В этой главе рассматриваются расширения существующих команд и новые команды, повышающие эффективность применения SQL.

Новые возможности

В Visual FoxPro 9.0 некоторые ограничения команды SELECT...SQL были отменены или дополнены. Эти изменения показаны в следующей таблице.


Изменения

Описание

Количество объединений и подзапросов в команде SELECT

В Visual FoxPro 9.0 отменено ограничение общего числа предложений обьединения и подзапросов. В предыдущих версиях  ограничение было равно девяти.

Количество предложений UNION в команде SELECT

В Visual FoxPro 9.0 отменено ограничение количества предложений UNION. В предыдущих версиях ограничение было равно девяти.

Количество таблиц, на которые ссылалась команда SELECT

В Visual FoxPro 9.0 отменено ограничение на количество таблиц (псевдонимов), вызваемых в SQL SELECT. Предыдущее ограничение было равно 30.

Количество параметров в предложении IN ()

В Visual FoxPro 9.0 отменено ограничение на  24 значения в предложении IN () для WHERE. Однако, количество значений по прежнему может устанавливаться функцией SYS(3055) - для сложных предложений FOR и WHERE. Подробнее об изменении функциональных возможностей предложения IN см. «Изменения функциональных возможностей в текущей реализации».

Расширенные подзапросы

В Visual FoxPro 9.0 обеспечивает значительно более гибкие подзапросы. Например, теперь поддерживаются множественные подзапросы. Ниже рассматриваются расширения подзапросов в Visual FoxPro 9.0.

Составные подзапросы

Visual FoxPro 9.0 поддерживает множественное вложение подзапросов, с корреляцией, допускаемой непосредственным родителем. Ограничения по глубине вложения отсутствуют. В Visual FoxPro 8.0 исключение 1842 (SQL: превышен уровень вложенности подзапросов) генерировалось, когда имелось более одного уровня вложенности подзапроса.
Ниже показан общий синтаксис для множественных подзапросов:
SELECT … WHERE … (SELECT … WHERE … (SELECT …) …) …
Следующие запросы, выполнение которых приводило к возникновению исключения в Visual FoxPro 8.0, теперь выполняются правильно:
CREATE CURSOR MyCursor (field1 I)
INSERT INTO MyCursor VALUES (0)
CREATE CURSOR MyCursor1 (field1 I)
INSERT INTO MyCursor1 VALUES (1)
CREATE CURSOR MyCursor2 (field1 I)
INSERT INTO MyCursor2 VALUES (2)
SELECT * FROM MyCursor T1 WHERE EXISTS ;
   (SELECT * from MyCursor1 T2 WHERE NOT EXISTS ;
   (SELECT * FROM MyCursor2 T3))
*** Второй множественный подзапрос, вложенный в пример ***
SELECT * FROM table1 WHERE table1.iid IN ;
   (SELECT table2.itable1id FROM table2 WHERE table2.iid IN ;
   (SELECT table3.itable2id FROM table3 WHERE table3.cValue = "value"))

GROUP BY в соотнесенном подзапросе

Многие запросы могут быть оценены,  выполняя подзапрос один раз и заменяя возникающим в результате значением или значениями в предложение WHERE внешнего запроса. В запросах, которые включают,  соотнесенный подзапрос (также известный как повторяющийся подзапрос), подзапрос зависит от внешнего запроса для значений. Это означает, что подзапрос выполнен неоднократно, один раз для каждой строки, которая могла бы быть выбрана внешним запросом.
Visual FoxPro 8.0 не позволял использовать GROUP BY в соотнесенном подзапросе (генерировалось исключение 1828 (SQL: Использование GROUP BY в подзапросе запрещено). В Visual FoxPro 9.0 это ограничение отменего; теперь GROUP BY поддерживается для соотнесенных подзапросов, возвращающих больше чем одну запись.
Ниже показан общий синтаксис для предложения GROUP BY в соотнесенном подзапросе.
SELECT … WHERE … (SELECT … WHERE … GROUP BY …) …
Следующий пример генерировал исключение в Visual FoxPro 8.0; теперь,  в Visual FoxPro 9.0, он выполняется правильно.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor1 (field1 I, field2 I, field3 I)
INSERT INTO MyCursor1 VALUES(1,2,3)
CREATE CURSOR MyCursor2 (field1 I, field2 I, field3 I)
INSERT INTO MyCursor2 VALUES(1,2,3)
SELECT * from MyCursor1 T1 WHERE field1;
   IN (SELECT MAX(field1) FROM MyCursor2 T2 ;
   WHERE T2.field2=T1.FIELD2 GROUP BY field3)

Предложение TOP N в не-соотнесенном подзапросе

Visual FoxPro 9.0 поддерживает предложениеTOP N в не-соотнесенном подзапросе. Предложение ORDER BY должно присутствовать, если предложение TOP N используется, и это — единственный случай, допустимый в подзапросе.
Ниже показан общий синтаксис для предложения TOP N в не-соотнесенном подзапросе.
SELECT … WHERE … (SELECT TOP nExpr [PERCENT] … FROM … ORDER BY …) …
Следующий пример генерировал исключение в Visual FoxPro 8.0; теперь,  в Visual FoxPro 9.0, он выполняется правильно.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor1 (field1 I, field2 I, field3 I)
INSERT INTO MyCursor1 VALUES(1,2,3)
CREATE CURSOR MyCursor2 (field1 I, field2 I, field3 I)
INSERT INTO MyCursor2 VALUES(1,2,3)
SELECT * FROM MyCursor1 WHERE field1 ;
   IN (SELECT TOP 5 field2 FROM MyCursor2 order by field2)

Подзапросы в cписке полей команды SELECT

Visual FoxPro 9.0 позволяет указывать подзапрос как результирующее поле или часть выражения в проекции. Подзапрос в проекции должен удовлетворять тем же самым требованиям, как и подзапрос, используемый в операции сравнения. Если подзапрос не возвращает никакие записи, то возвращается значение NULL.
В Visual FoxPro 8.0, попытка использовать такую конструкцию вызывала исключение 1810 (SQL: Недопустимое использование подзапроса).
Ниже показан общий синтаксис для подзапроса в списке полей команды SELECT
SELECT … (SELECT …) … FROM …
Следующий пример генерировал исключение в Visual FoxPro 8.0; теперь,  в Visual FoxPro 9.0, он выполняется правильно.
SELECT T1.field1, (SELECT field2 FROM MyCursor2 T2;
   WHERE T2.field1=T1.field1) FROM MyCursor1 T1

Агрегатные функции в списке полей SELECT подзапроса

В Visual FoxPro 9.0 агрегатные функции теперь поддерживаются в списке SELECT сравниваемого подзапроса, используя операторы сравнения <, <=, >, >=, сопровождаемые  ALL, ANY, или SOME. См. «Cоображения о команде SQL SELECT» для получения подробной информации об агрегатных функциях.

Следующий пример показывает использование агрегатной функции COUNT в списке SELECT подзапроса.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor (FIELD1 i)
INSERT INTO MyCursor VALUES (6)
INSERT INTO MyCursor VALUES (0)
INSERT INTO MyCursor VALUES (1)
INSERT INTO MyCursor VALUES (2)
INSERT INTO MyCursor VALUES (3)
INSERT INTO MyCursor VALUES (4)
INSERT INTO MyCursor VALUES (5)
INSERT INTO MyCursor VALUES (-1)
CREATE CURSOR MyCursor2 (FIELD2 i)
INSERT INTO MyCursor2  VALUES (1)
INSERT INTO MyCursor2  VALUES (2)
INSERT INTO MyCursor2  VALUES (2)
INSERT INTO MyCursor2  VALUES (3)
INSERT INTO MyCursor2  VALUES (3)
INSERT INTO MyCursor2  VALUES (3)
INSERT INTO MyCursor2  VALUES (4)
INSERT INTO MyCursor2  VALUES (4)
INSERT INTO MyCursor2  VALUES (4)
INSERT INTO MyCursor2  VALUES (4)
SELECT * FROM MYCURSOR WHERE field1 ;
   < ALL (SELECT count(*) FROM MyCursor2 GROUP BY field2) ;
   INTO CURSOR MyCursor3
BROWSE

Соотнесенные подзапросы позволяют сложным выражениям быть сравненимыми с соотнесенным полем

В Visual FoxPro 8.0, соотнесённые поля могли быть вызваны только в следующих форматах:
Соотнесённое поле <сравнение> локальное поле
-или-
Локальное поле <сравнение> соотнесённое поле
В Visual FoxPro 9.0 соотнесенные поля поддерживают сравнение с локальными выражениями (как показано в следующих форматах):
Соотнесенное поле <сравнение> локальное выражение
-или-
Локальное выражение <сравнение> соотнесённое поле
Локальное выражение должно использовать по крайней мере одно локальное поле и не может ссылаться на любое внешнее (соотнесенное) поле.
В следующем примере локальное выражение (Mycursor2. field2 / 2) сравнивается с соотнесённым полем (Mycursor. field1).
SELECT * FROM MyCursor ;
   WHERE EXISTS(SELECT * FROM MyCursor2  ;
   WHERE MyCursor2.field2 / 2 > MyCursor.field1)

Изменения для выражений по сравнению с подзапросами.

В Visual FoxPro 8.0 левая часть выражения, использующего операторы сравнения [NOT] IN <, <=, =, ==, < >, !=, >, =>, ALL, ANY или SOME с подзапросом, должны были ссылаться на одну и только одну таблицу из предложения FROM. В случае сравнения с соотнесенным подзапросом, таблица должна также быть соотнесённой таблицей.
В Visual FoxPro 9.0, сравнения обрабатываются следующими способами:

  • Выражение слева от IN должно сравниваться со ссылкой по крайней мере на одну таблицу из предложения FROM.
  • Левая часть для условий =, ==, <>, !=, сопровождаемая предложениями ALL, SOME, или ANY, должна ссылаться по крайней мере на одну таблицу из предложения FROM.
  • Левая часть для условия >, >=, <, <=, сопровождаемая предложениями ALL, SOME, или ANY (SELECT TOP…), должна ссыслаться по крайней мере на одну таблицу из предложения FROM.
  • Левая часть для условия >, >=, <, <=, сопровождаемая предложениями ALL, SOME, или ANY (SELECT <агрегатная функция>…), должна ссыслаться по крайней мере на одну таблицу из предложения FROM.
  • Левая часть для условия >, >=, <, <=, сопровождаемая предложениями ALL, SOME, or ANY (подзапрос с GROUP BY и/или HAVING), должна ссылаться по крайней мере на одну таблицу из предложения FROM.

В Visual FoxPro 9.0 левая часть выражения сравнения, которое не начинается со списка (например, предложения ALL, SOME, or ANY не указано), не должна ссылаться на какую-любую таблицу из предложения FROM.
Во всех случаях, в левой части выражения сравнения допускается ссылаться на более чем одну таблица из предложения FROM. Для соотнесенного подзапроса, левая часть выражения сравнения не должна сослаться на соотнесённую таблицу.

Подзапросы в команде UPDATE...SQL

В Visual FoxPro 9.0 команда UPDATE...SQL теперь поддерживает подзапрос в предложении SET.
К подзапросу в предложении SET предъявляются те же требования, как и к подзапросу, используемому в операции сравнения. Если подзапрос не возвращает никакие записи, то возвращается значение NULL.
В предложении SET допускается использовать только один подзапрос. Если имеется подзапрос в предложении SET, то использовать подзапросы в предложении WHERE не разрешается.
Ниже показан общий синтаксис для подзапроса в предложении SET.
UPDATE … SET … (SELECT …) …
В следующем примере показано использование подзапроса в предложении SET.
CLOSE DATA
CREATE CURSOR MyCursor1 (field1 I, field2 I NULL)
INSERT INTO MyCursor1 VALUES (1,1)
INSERT INTO MyCursor1 VALUES (2,2)
INSERT INTO MyCursor1 VALUES (5,5)
INSERT INTO MyCursor1 VALUES (6,6)
INSERT INTO MyCursor1 VALUES (7,7)
INSERT INTO MyCursor1 VALUES (8,8)
INSERT INTO MyCursor1 VALUES (9,9)
CREATE CURSOR MyCursor2 (field1 I, field2 I)
INSERT INTO MyCursor2 VALUES (1,10)
INSERT INTO MyCursor2 VALUES (2,20)
INSERT INTO MyCursor2 VALUES (3,30)
INSERT INTO MyCursor2 VALUES (4,40)
INSERT INTO MyCursor2 VALUES (5,50)
INSERT INTO MyCursor2 VALUES (6,60)
INSERT INTO MyCursor2 VALUES (7,70)
INSERT INTO MyCursor2 VALUES (8,80)
UPDATE MyCursor1 SET field2 = 100 + (SELECT field2 FROM MyCursor2 ;
  WHERE MyCursor2.field1 = MyCursor1.field1) WHERE field1 > 5
SELECT MyCursor1
LIST

Подзапросы в предложении FROM

Подзапрос в предложении FROM обычно обозначается как полученная таблица. Полученная таблица - это команда SELECT в предложении FROM, упоминаемая под псевдонимом или определенным пользователем именем. Набор результатов SELECT в предложении FROM создает таблицу, используемую внешней командой SELECT. Visual FoxPro 9.0 разрешает использование подзапроса в предложении FROM.
Подзапрос должен быть заключен в круглые скобки, и обязательно связан с псевдонимом. Корреляция не обеспечивается. К подзапросу применяются те же ограничения синтаксиса, как и для обычной команды SELECT, не ограниченые синтаксисом ранее рассмотренных подзапросов. Все такие подзапросы выполненяются до выполнения самого верхнего SELECT.
Ниже показан общий синтаксис подзапроса в предложении FROM.
SELECT … FROM (SELECT …) [AS] Alias…
В следующем примере демонстрируется использование подзапроса в предложении FROM.
SELECT * FROM (SELECT * FROM MyCursor T1;
   WHERE field1 = (SELECT T2.field2 FROM MyCursor1 T2;
   WHERE T2.field1=T1.field2);
   UNION SELECT * FROM MyCursor2;
   ORDER BY 2 desc) AS subquery

*** Следующий код генерирует ошибку ***
SELECT * FROM (SELECT TOP 5 field1 FROM MyCursor) ORDER BY field1

GROUP BY с именами полей в предложении UNION

При использовании предложения UNION в Visual FoxPro 8.0 вы должны были использовать числовые ссылки в предложении ORDER BY. В Visual FoxPro 9.0, это ограничение было отменено, и теперь допускается использовать имена полей.
Ссылочные поля должны присутствовать в списке SELECT (проекция) для последнего SELECT в UNION; эта проекция используется для операции ORDER BY.
Следующий пример показывает использование имен полей в предложении ORDER BY.
CLOSE DATABASES all
CREATE CURSOR MyCursor(field1 I, field2 I)
INSERT INTO MyCursor values(1,6)
INSERT INTO MyCursor values(2,5)
INSERT INTO MyCursor values(3,4)
SELECT field1, field2, .T. AS FLAG, 1 FROM MyCursor;
   WHERE field1 = 1;
   UNION ;
   SELECT field1, field2, .T. AS FLAG, 1 FROM MyCursor;
   WHERE field1 = 3;
   ORDER BY field2 ;
   INTO CURSOR TEMP READWRITE
BROWSE NOWAIT

Эффективное выполнение TOP N

В Visual FoxPro 8.0 и более ранних версиях при использовании в TOP N предложения [PERCENT] все записи сортируются и затем TOP N записей извлекается. В Visual FoxPro 9.0 эффективность выполнения была улучшена за счёт удаления записей, которые не удовлетворяют требованиям TOP N из процесса сортировки как можно раньше.
Оптимизация TOP N выполняется только тогда, когда если SET ENGINEBEHAVIOR установлена в 90.
Оптимизация требует, чтобы TOP N возвратила не больше, чем N записей (это не так для Visual FoxPro 8.0 и более ранних версий).
TOP N PERCENT не может быть оптимизирована, если весь результирующий набор записей не может быть считан в память сразу.

Улучшенная оптимизация для множественной таблицы с условием OR

Visual FoxPro 9.0 применяет улучшенную Rushmore оптимизацию при выборке из связанных по условию OR таблиц. Visual FoxPro оптимизирует условия фильтра для таблицы, пока обе стороны условия могут быть оптимизированы. Следующий пример показывает это:
CLEAR
CREATE CURSOR Test1 (f1 I)
FOR i=1 TO 20
  INSERT INTO Test1 VALUES (I)
NEXT
INDEX ON f1 TAG f1
CREATE CURSOR Test2 (f2 I)
FOR i=1 TO 20
  INSERT INTO Test2 VALUES (I)
NEXT
INDEX ON f2 TAG f2
SYS(3054,12)
SELECT * from Test1, Test2 WHERE (f1 IN (1,2,3) AND f2 IN (17,18,19)) OR ;
  (f2 IN (1,2,3) AND f1 IN (17,18,19)) INTO CURSOR Result
SYS(3054,0)
В этом сценарии, таблица Test1 может быть оптимизирована Rushmore, используя следующее условие:

( F1 В (1,2,3) ИЛИ f1 В (17,18,19)) и таблица Test2 со следующими условиями:
( F2 В (17,18,19) ИЛИ f2 В (1,2,3))

Поддержка для локальных буферизированных данных

Время от времени может быть полезно использовать SELECT...SQL, чтобы выбрать записи из локального буферизированного курсора, в котором таблица не модифицировалась. Много раз при создании средств управления подобно сеткам, окнам списка, и комбинированным спискам необходимо рассмотреть недавно добавленные записи, которые еще не были сохранены на диске. В настоящее время команды SQL основаны на содержании, которое уже сохранено на диске.
Visual FoxPro 9.0 обеспечивает расширения языка, которые позволяют вам определять, основаны ли данные, возвращенные командой SELECT...SQL на буферизированных данных или данных, сохранённых на диске.
Команда SELECT...SQL теперь поддерживает предложение WITH...BUFFERING, которое позволяет вам определять, основаны ли извлекаемые данные на буферизированных данных или данных, уже сохранённых на диске. Для подробной информации, см. «Команда SELECT...SQL c предложением WITH».
Если Вы не включаете предложение BUFFERING, характер извлекаемых данных определяется установкой команды SET SQLBUFFERING. Для подробной информации, см. «Команда SET SQLBUFFERING».

Расширения других SQL команд

Следующие разделы описывают расширения, сделанные в командах  INSERT...SQL, UPDATE...SQL и DELETE...SQL Visual FoxPro 9.0.

Предложение UNION в команде INSERT...SQL

В Visual FoxPro 9.0 предложение UNION теперь поддерживается в команде INSERT...SQL
Ниже показан общий синтаксис для предложения UNION.
INSERT INTO … SELECT … FROM … [UNION SELECT … [UNION …]]
Следующий пример показывает использование предложения UNION в INSERT...SQL.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor (field1 I,field2 I)
CREATE CURSOR MyCursor1 (field1 I,field2 I)
CREATE CURSOR MyCursor2 (field1 I,field2 I)
INSERT INTO MyCursor1 VALUES (1,1)
INSERT INTO MyCursor2 VALUES (2,2)
INSERT INTO MyCursor SELECT * FROM MyCursor1 UNION SELECT * FROM MyCursor2
SELECT MyCursor
LIST

Соотнесенная команда UPDATE...SQL

Visual FoxPro 9.0 теперь поддерживает соотнесенные модификации в команде UPDATE...SQL.
Если в команду UPDATE...SQL включено предложение FROM, то имя после ключевого слова UPDATE определяет адресата для операции модификации. Это может быть имя таблицы, псевдоним или имя файла. Для того, чтобы выбрать выходную таблицу,  используется следующая логика:

  • Если имя в предложении FROM соответствует неявному или явному псевдониму для таблицы, то таблица используется как адресат для операции модификации.
  • Если имя соответствует псевдониму курсора в текущем сеансе данных, то курсор используется как адресат.
  • Таблица или файл с тем же самым именем используется как адресат.

Команда UPDATE...SQL в предложении FROM имеет тот же самый синтаксис, как предложение FROM в команде SELECT...SQL со следующими ограничениями:

  • Выходная таблица или курсор не может быть включено во внешнем объединении (OUTER JOIN) как вторичная таблица.
  • Выходной курсор не может быть результом подзапроса.
  • Все другие объединения могут быть оценены перед соединением выходной таблицы.

Ниже показан общий синтаксис для соотнесенной команды UPDATE.
UPDATE … SET … FROM … WHERE …
Следующий пример показывает соотнесенную модификацию, используя команду UPDATE...SQL.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor1 (field1 I , field2 I NULL,field3 I NULL)
INSERT INTO MyCursor1 VALUES (1,1,0)
INSERT INTO MyCursor1 VALUES (2,2,0)
INSERT INTO MyCursor1 VALUES (5,5,0)
INSERT INTO MyCursor1 VALUES (6,6,0)
INSERT INTO MyCursor1 VALUES (7,7,0)
INSERT INTO MyCursor1 VALUES (8,8,0)
INSERT INTO MyCursor1 VALUES (9,9,0)
CREATE CURSOR MyCursor2 (field1 I , field2 I)
INSERT INTO MyCursor2 VALUES (1,10)
INSERT INTO MyCursor2 VALUES (2,20)
INSERT INTO MyCursor2 VALUES (3,30)
INSERT INTO MyCursor2 VALUES (4,40)
INSERT INTO MyCursor2 VALUES (5,50)
INSERT INTO MyCursor2 VALUES (6,60)
INSERT INTO MyCursor2 VALUES (7,70)
INSERT INTO MyCursor2 VALUES (8,80)
CREATE CURSOR MyCursor3 (field1 I , field2 I)
INSERT INTO MyCursor3 VALUES (6,600)
INSERT INTO MyCursor3 VALUES (7,700)
UPDATE MyCursor1 SET MyCursor1.field2=MyCursor2.field2, field3=MyCursor2.field2*10 ;
FROM MyCursor2 ;
  WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1
SELECT MyCursor1
LIST
UPDATE MyCursor1 SET MyCursor1.field2=MyCursor3.field2 FROM MyCursor2, MyCursor3  ;
  WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1 AND
  myCursor2.field1=MyCursor3.field1
SELECT MyCursor1
LIST

Соотнесенная команда DELETE...SQL

Visual FoxPro 9.0 теперь поддерживает соотнесенное удаление в команде DELETE...SQL.
Если в предложении FROM указана больше чем одна таблица, требуется имя после ключевого слова DELETE; это имя определяет адресата для операции удаления . Это имя может быть имя таблицы, псевдоним или имя файла. Для выбора целевой таблицы используется следующая логика:

  • Если имя соответствует неявному или явному псевдониму таблицы в предложении FROM, то таблица используется как адресат для операции модификации.
  • Если имя соответствует псевдониму для курсора в текущем сеансе данных, то курсор используется как адресат.
  • Таблица или файл с тем же самым именем используется как адресат.

Команда DELETE...SQL в предложении FROM имеет тот же самый синтаксис, как предложение FROM в команде SELECT...SQL со следующими ограничениями:

  • Выходная таблица или курсор не может быть включена во внешнее объединение как вторичная таблица.
  • Выходной курсор не может быть результат подзапроса.
  • Должна быть возможность оценить все другие объединения перед соединением выходной таблицы.

Ниже показан общий синтаксис для соотнесенной команды DELETE.
DELETE [alias] FROM alias1 [, alias2 … ] … WHERE …
Следующий пример показывает соотнесенное удаление с использованием команды DELETE...SQL.
CLOSE DATABASES ALL
CREATE CURSOR MyCursor1 (field1 I , field2 I NULL,field3 I NULL)
INSERT INTO MyCursor1 VALUES (1,1,0)
INSERT INTO MyCursor1 VALUES (2,2,0)
INSERT INTO MyCursor1 VALUES (5,5,0)
INSERT INTO MyCursor1 VALUES (6,6,0)
INSERT INTO MyCursor1 VALUES (7,7,0)
INSERT INTO MyCursor1 VALUES (8,8,0)
INSERT INTO MyCursor1 VALUES (9,9,0)
CREATE CURSOR MyCursor2 (field1 I , field2 I)
INSERT INTO MyCursor2 VALUES (1,10)
INSERT INTO MyCursor2 VALUES (2,20)
INSERT INTO MyCursor2 VALUES (3,30)
INSERT INTO MyCursor2 VALUES (4,40)
INSERT INTO MyCursor2 VALUES (5,50)
INSERT INTO MyCursor2 VALUES (6,60)
INSERT INTO MyCursor2 VALUES (7,70)
INSERT INTO MyCursor2 VALUES (8,80)
CREATE CURSOR MyCursor3 (field1 I , field2 I)
INSERT INTO MyCursor3 VALUES (6,600)
INSERT INTO MyCursor3 VALUES (7,700)
DELETE MyCursor1 FROM MyCursor2  ;
  WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1
SELECT MyCursor1
LIST
RECALL ALL
DELETE MyCursor1 FROM MyCursor2, MyCursor3  ;
  WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1 AND
  myCursor2.field1=MyCursor3.field1
SELECT MyCursor1
LIST
RECALL ALL
DELETE FROM MyCursor1 WHERE MyCursor1.field1 > 5
SELECT MyCursor1
list
RECALL ALL
DELETE MyCursor1 from MyCursor1 WHERE MyCursor1.field1 > 5
RECALL ALL IN MyCursor1
DELETE T1 ;
  FROM MyCursor1 T1 JOIN MyCursor2 ON T1.field1>5 AND MyCursor2.field1=T1.field1,
  myCursor3  ;
  WHERE MyCursor2.field1=MyCursor3.field1
RECALL ALL IN MyCursor1

Обновляемые поля в команде UPDATE...SQL

Число полей, которые могут модифицироваться командой UPDATE...SQL,  больше не ограничивается 128, как в предыдущих версиях Visual FoxPro. Новое ограничение — 255 полей, т.е. равно числу полей, доступных в таблице.

SET ENGINEBEHAVIOR

Команда SET ENGINEBEHAVIOR имеет в Visual FoxPro 9.0 новую опцию, 90, которая воздействует поведение команды SELECT...SQL для предложения TOP N и агрегатных функций. Для дополнительной информации см. «Команда SET ENGINEBEHAVIOR».

Преобразование типов данных

Преобразование между типами данных (например, преобразование между полями memo и character) в Visual FoxPro 9.0 было улучшено. Это усовершенствованное преобразование применяется в команде ALTER TABLE...SQL с опцией COLUMN также, как и структурные изменения, сделанные в Конструкторе Таблиц.

Особенности использования команды SELECT… SQL

При создании запросов и просмотров командами SQL SELECT вы должны учитывать следующие утверждения, соглашения, и ограничения.

  • Определяемые пользователем функции в командах SELECT...SQL
  • Агрегатные Функции
  • Правила по именованию столбцов
  • Операции объединения (UNION)

Определяемые пользователем функции в командах SELECT...SQL

Вы можете определять выражения, содержащие определяемые пользователем функции для Select_Item в утверждениях SQL SELECT. Однако обратите внимание на следующие рекомендации и ограничения при использовании определяемых пользователем функций в утверждениях SQL SELECT:

  • Используйте API и определяемые пользователем функции, написанные на C или ассемблере вместо того, чтобы выполнять манипулирование с большими вычислительными объемами в определяемых пользователем функциях Visual FoxPro. Быстродействие, с которым выполняются определяемые пользователем функции, может ограничивать быстродействие операций, выполняемых с SQL SELECT.
  • Не предпринимайте ничего относительно ввода-вывода Visual FoxPro или среды таблицы при использовании определяемых пользователем функций в SQL SELECT. Вообще, в этом случае вы, скорее всего, не знаете, какая рабочая область выбрана, какое имя у текущей таблицы, какие имена имеют обрабатываемые полея. Значение этих переменных зависит от точного расположения в процессе оптимизации, в котором вызывается определяемая пользователем функция.
  • Не изменяйте Visual FoxPro ввод-вывод или среду таблицы в определяемых пользователем функциях, вызваемых в SQL SELECT. Результаты могут быть непредсказуемы.
  • Используйте список параметров, передаваемых функции, только когда она вызывается как единственый надежный способ передать значения к определяемым пользователем функциям в SQL SELECT.
  • Вы должны понимать, что "запрещенные" манипуляции могут обеспечивать правильные результаты для одной из версий Visual FoxPro, но не cмогли бы работать в более поздних версиях.

Вне этих ограничений, определяемые пользователем функции приемлемы в командах SQL SELECT. Однако помните, что их использование в SQL SELECT способно резко уменьшить эффективность выполнения запроса.

Агрегатные функции

Вы можете использовать агрегатные функции с Select_Item, которые является полем или выражением, включающим поле, или внутри условия фильтра в предложении HAVING. Однако, Вы не можете вкладывать агрегатные функции одна в другую.
В следующай таблице перечислены агрегатные функции, которые вы можете использовать в команде SELECT...SQL.


Агрегатная функция

Назначение

AVG()

Возвращает среднее значение для столбца данных

COUNT( ) или CNT( )

Считает число единиц выбора в столбце. COUNT (*) считает число строк в выводе запроса

MIN()

Возвращает минимальное значение для столбца данных

MAX()

Возвращает максимальное значение для столбца данных

SUM()

Возвращает сумму значений для столбца данных

Следующий пример создает запрос, который назначает имена столбцам в результирующей выборке в соответствии с описанием:
CLEAR ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT AVG(TAlias1.order_amt), MIN(TAlias1.order_amt) FROM Orders AS TAlias1

Правила присваивания имён столбцам

Следующие правила применяются, когда вы определяете столбец с предложением AS, чтобы отобразить результаты в отдельном столбце:

  • Если Select_Item - поле с уникальным именем, имя выводимого столбца совпадает с именем поля.
  • Если больше чем один Select_Item имеют одинаковые имена, то к концу имени выводимого столбца добавляется символ подчеркивания (_) и номер (символ).

Например, если команда SELECT SQL определяет, что должны быть отображены поля Cust_ID для двух таблиц, Customer и Orders, в выборке эти столбцы будут иметь имена Cust_ID_a и Cust_Id_b. Следующий пример создает запрос, который отображает имена столбцов как описано:
CLEAR ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT TAlias1.cust_id, TAlias2.cust_id ;
   FROM Customer AS TAlias1, Orders AS TAlias2 ;
   WHERE TAlias1.cust_id = TAlias2.cust_id
При выборке из свободных таблиц, имеющих одинаковые имена полей длиной 10 символов (максимальная длина имени поля для свободных таблиц), в результирующей выборке второе и все последующие имена усекаются и добавляется числовой идентификатор. Например, имя столбца Department появилось бы как Department и Departmen2.

  • Если Select_Item - выражение, и предложение AS не используется, то имя столбца вывода формируется как Exp_1. Имена дополнительных столбцов, определяемых выражениями), формируются как Exp_2, Exp_3, и так далее. Следующий пример создает запрос, который отображает имена столбцов как описано:

CLEAR ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT TAlias1.postalcode+"-1234", TAlias2.postalcode+"-5678" ;
   FROM Customer AS TAlias1, Orders AS TAlias2 ;
   WHERE TAlias1.cust_id = TAlias2.cust_id

  • Если с Select_Item используется агрегатная функция, типа COUNT(),  выводимый столбец именован как Cnt_FieldName. Если больше чем один Select_Item используются с составными функциями, столбцы вывода именуются в соответствии со следующим соглашением: ИмяАгрегатнойФункции_ИмяПоля; при необходимости имена сокращаются. Следующий пример создает запрос, который отображает имена выводимых столбцов в соответсвии с выше сказанным:

CLEAR ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT COUNT(order_id), SUM(order_net)FROM Orders

Операция объединения (UNION)

При выполнении операций объединения между командами SELECT...SQL, использующими предложение UNION, необходимо знать и учитывать следующие соображения и ограничения:

  • Вы не можете использовать UNION для объединения подзапросов.
  • Объединяемые команды SELECT должны иметь то же самое количество столбцов, как в результирующем наборе.
  • Только конечная (заключительная) команда SELECT может иметь предложение ORDER BY, которое должно обращаться к выводимым столбцам по их номерам. Если предложение ORDER BY включено, то оно воздействует на весь результирующий набор.
  • Когда два столбца различных типов данных включаются в операции UNION, тип данных с более низким старшинством преобразуется в тип данных с более высоким старшинством.

Преобразование типов данных и старшинство в операциях UNION

До Visual FoxPro 8.0 вам нужно было выполнять явное преобразование типов данных при выполнении операций UNION в командах  SELECT...SQL над двумя объединяемыми полями различных типов. Теперь Visual FoxPro поддерживает неявное преобразование типа данных для типов данных, которые поддерживают такое преобразование.
Явное преобразование типа данных требует, чтобы вы использовали функции преобразования Visual FoxPro, типа CTOD(), в то время как неявные преобразования не требуют, чтобы Вы использовали такие функции преобразования.
Когда Visual FoxPro объединяет в операции UNION два столбца с различными типами данных, тип данных с более низким старшинством преобразован в тип данных с более высоким старшинством. Для реквизитов поля, NULL имеет более высокое старшинство над NOT NULL.
Следующая таблица показывает все явные и неявные преобразования типа данных, разрешенные для типов данных таблиц Visual FoxPro.


Первый тип данных

Второй тип данных

Ожидаемый тип данных

Character (N) 

Character (X)

Character (MAX(N,X))

Character (N)

Character Binary (X)

Character Binary (MAX(N,X))

Character (N)

Memo

Memo

Character Binary (N)

Character Binary (X)

Character Binary (MAX(N,X))

Character Binary (N)

Memo

Memo

Date  

DateTime

DateTime

Double (N)

Float (X,Y)

Float (MAX(MAX(8,Y),2))

Double (N) 

Integer

Double (N)

Double (N) 

Numeric (X,Y) 

Double (MAX(MAX(8,Y),2))

Double (X)   

Double (Y)

Double (MAX(X,Y))

Float (N,M) 

Double (X) 

Float (20, MAX(M,X))

Float (N,M)

Float (X,Y) 

Float (MAX(N,M), MAX(X,Y))

Float (N,M) 

Numeric (X,Y) 

Float (MAX (N,X), MAX(M,Y))

Integer

Currency

Currency

Integer   

Double (X)

Double (X)

Integer 

Float (X,Y) 

Float (MAX(11,X), Y)

Integer 

Numeric (X,Y) 

Numeric (MAX(11,X), Y)

Numeric (N,M) 

Double (X) 

Numeric (20, MAX(M,X))

Numeric (N,M) 

Float (X,Y) 

Float (MAX(N,X), MAX(M,Y))

Numeric (N,M)

Numeric (X,Y)

Numeric (MAX(N,X), MAX(M,Y))

Varchar (X) 

Character (Y) 

Varchar (MAX(X,Y))

Varchar Binary (X) 

Character Binary (Y) 

Varchar Binary (MAX(X,Y))

Команда SELECT...SQL c предложением WITH

Предложение WITH позволяет Вам применять одиночную команду к существующим утверждениям SELECT без необходимости изменять эти утверждения.
[WITH (BUFFERING = lExpr) ]
Параметры
lExpr
Логический тип данных. В следующей таблице перечислены значения для lExpr.


lExp

Установка

Истина (.T.)

Данные в команде SQL...SELECT основаны на буферизированных данных, если они доступны.

Ложь (.F.)

(Значение по умолчанию) Данные в команде SQL-SELECT основаны только на тех данных, которые записаны на диске.

Если вы устанавливаете BUFFERING в истину (.T.), то можете сделать запрос данных из локального буферизированного курсора, который может включать записи, которые модифицировались, но не сохранены на диске. Иначе, результат выборки будет  включать только те записи, которые сохранены на диске.
Если курсор использует буферизацию строки, то текущая запись сохраняется до того, как команда выполнена.
Если курсор использует буферизацию таблицы, то изменяемая текущая запись сохранена в буфере.
Предложение WITH, устанавливающее BUFFERING, поддерживается только на локальных данных Visual FoxPro, и не поддерживается на данных из баз данных внешних компонентов (например, на SQL Server).
Если вы устанавливаете BUFFERING в Ложь (.F.), то команда SELECT выбирает данные из курсора на диске (который может быть отличен от того, который находится в буфере). Если Вы не включаете утверждение BUFFERING, то ваши результаты основаны на установке для команды SET SQLBUFFERING. По умолчанию, ваши запросы выполняют выборку данных с диска. Команда SELECT с WITH BUFFERING, установленным в Истину (.T.), отменяет установку SET SQLBUFFERING.
Опция WITH BUFFERING эффективна в следующих случаях:

  • Рабочая область не имеет псевдонима, определенного, чтобы поддерживать курсор, на который ссылается команда SELECT.
  • Курсор не буферизирован.
  • Курсор действует к как адресат для операций модификации или удаления.

Следующий пример демонстрирует простую команду SELECT, ссылающуюся на одиночную таблицу. Чтобы использование предложения WITH было эффективным, должна существовать рабочая область с псевдоним Customers.
SELECT * FROM customers WITH (BUFFERING=.T.)
Следующий пример показывает, что каждая таблица, указанная в предложении FROM, нуждается в предложении WITH BUFFERING.
SET MULTILOCKS ON
CREATE TABLE Table1 (Field1 N(10), Field2 N(10))
CREATE TABLE Table2 (Field3 N(10), Field4 N(10))
CURSORSETPROP("Buffering", 5, "Table1")
CURSORSETPROP("Buffering", 5, "Table2")
INSERT INTO Table1 VALUES (1, 1)
INSERT INTO Table2 VALUES (0, 100)
SELECT Table1.*, Table2.* FROM FORCE Table1 ;
      JOIN Table2 ON Field1 >= Field3 AND Field2 > 0 AND Field4 < 100
REPLACE Field4 WITH 99 IN Table2
SELECT Table1.*, Table2.* FROM FORCE Table1 ;
      JOIN Table2 ON Field1 >= Field3 AND Field2 > 0 AND Field4 < 100
SELECT Table1.*, Table2.* FROM FORCE Table1 WITH (BUFFERING=.T.) ;
      JOIN Table2 WITH (BUFFERING=.T.) ;
      ON Field1 >= Field3 AND Field2 > 0 AND Field4 < 100

Команда SET SQLBUFFERING

Определяет, основаны ли данные в SQL - команде SELECT на буферизированных данных или данных, записанных в диск.
SET SQLBUFFERING ON | OFF

Параметры
ON
Определяет, что команда SELECT...SQL использует локальные буферизированные данные, если они доступны. Если данные не буферизированы, то выбираются данные с диска.
OFF
(Значение по умолчанию) Определяет, что команда SELECT...SQL использует данные, сохранённые на диске.

(Перевод Вячеслав Клепинин)

Автор публикации

не в сети 3 месяца

admin

Комментарии: 1Публикации: 123Регистрация: 10-12-2000
Материалы по теме
Оставить комментарий
//////////////// ///////////////
Авторизация
*
*
Генерация пароля