Понимание SQL. Глава 21. Изменение значений с помощью представлений. [SQL.RU]

21. ИЗМЕНЕНИЕ ЗНАЧЕНИЙ С ПОМОЩЬЮ ПРЕДСТАВЛЕНИЙ

Эта глава рассказывает о командах модификации ЯЗЫКА DML - ВСТАВИТЬ(INSERT), ИЗМЕНИТЬ(UPDATE), и УДАЛИТЬ(DELETE) - когда они применяются для представлений. Как упомянуто в предыдущей главе, использование команд модификации в представлениях - это косвенный способ использования их в ссылочных таб- лицах с помощью запросов представлений. Однако, не все представления могут модифицироваться. В этой главе, мы будем обсуждать правила определяющие, является ли представление модифицируемым. Кроме того, вы обучитесь использованию предложения WITH CHECK OPTION, которое управляет указанными значениями, которые можно вводить в таблицу с помощью представления. Как упомянуто в Главе 18, это, в некоторых случаях, может быть желательным вариантом непосредственного ограничения таблицы.

МОДИФИЦИРОВАНИЕ ПРЕДСТАВЛЕНИЯ

Один из наиболее трудных и неоднозначных аспектов представлений - непосредственное их использование с командами модификации DML. Как упомянуто в предыдущей главе, эти команды фактически воздействуют на значения в базовой таблице представления. Это является некоторым противоречием. Представление состоит из результатов запроса, и когда вы модифицируете представление, вы модифицируете набор результатов запроса. Но модификация не должна воз- действовать на запрос ; она должна воздействовать на значения в таблице к которой был сделан запрос, и таким образом изменять вывод запроса. Это не простой вопрос. Следующий оператор будет создавать представление показанное на Рисунке 21.1:

 
      CREATE VIEW Citymatch (custcity, salescity) 
          AS SELECT DISTINCT a.city, b.city 
             FROM Customers a, Salespeople b 
             WHERE a.snum = b.snum;

Это представление показывает все совпадения заказчиков с их продавца- ми так, что имеется по крайней мере один заказчик в городе_заказчика обслуживаемый продавцом в городе_продавца. Например, одна строка этой таблицы - London London - показывает, что имеется по крайней мере один заказчик в Лондоне, обслуживаемый продав- цом в Лондоне. Эта строка может быть произведена при совпадении Hoffmanа с его продавцом Peel, причем если оба они из Лондона.

 
            ===============  SQL Execution Log ============== 
           |                                                 | 
           | SELECT *                                        | 
           | FROM  Citymatch;                                | 
           | =============================================== | 
           |   custcity    salescity                         | 
           |  ---------    ---------                         | 
           |  Berlin       San Jose                          | 
           |  London       London                            | 
           |  Rome         London                            | 
           |  Rome         New York                          | 
           |  San Jose     Barselona                         | 
           |  San Jose     San Jose                          | 
           |                                                 | 
             ================================================

ОПРЕДЕЛЕНИЕ МОДИФИЦИРУЕМОСТИ ПРЕДСТАВЛЕНИЯ

МОДИФИЦИРУЕМЫЕ ПРЕДСТАВЛЕНИЯ И ПРЕДСТАВЛЕНИЯ ТОЛЬКО_ЧТЕНИЕ.

ЧТО ЯВЛЯЕТСЯ - МОДИФИЦИРУЕМЫМИ ПРЕДСТАВЛЕНИЕМ

Имеются некоторые примеры модифицируемых представлений и представлений только_чтение:

 
           CREATE VIEW Dateorders (odate, ocount) 
              AS SELECT odate, COUNT (*) 
                 FROM Orders 
                 GROUP BY odate;

Это - представление только_чтение из-за присутствия в нем агрегатной функции и GROUP BY.

 
              CREATE VIEW Londoncust 
                 AS SELECT * 
                    FROM Customers 
                    WHERE city = 'London';

А это - представление модифицируемое.

 
             CREATE VIEW SJsales (name, number, percentage) 
                 AS SELECT sname, snum, comm  100 
                    FROM Salespeople 
                    WHERE city = 'SanJose';

Это - представление только_чтение из-за выражения " comm * 100 " . При этом, однако, возможны переупорядочение и переименование полей. Некоторые программы будут позволять удаление в этом представлении или в порядках столбцов snum и sname.

 
             CREATE VIEW Salesonthird 
                AS SELECT * 
                  FROM Salespeople 
                  WHERE snum IN 
                    (SELECT snum 
                       FROM Orders 
                       WHERE odate = 10/03/1990);

Это - представление только_чтение в ANSI из-за присутствия в нем подзапроса. В некоторых программах, это может быть приемлемо.

 
            CREATE VIEW Someorders 
               AS SELECT snum, onum, cnum 
                  FROM Orders 
                  WHERE odate IN (10/03/1990,10/05/1990);

Это - модифицируемое представление.

ПРОВЕРКА ЗНАЧЕНИЙ ПОМЕЩАЕМЫХ В ПРЕДСТАВЛЕНИЕ

Другой вывод о модифицируемости представления тот, что вы можете вводить значения которые " проглатываются " (swallowed) в базовой таблице. Рассмотрим такое представление:

 
          CREATE VIEW Highratings 
             AS SELECT cnum, rating 
                FROM Customers 
                WHERE rating = 300;

Это - представление модифицируемое. Оно просто ограничивает ваш дос- туп к определенным строкам и столбцам в таблице. Предположим, что вы вставляете (INSERT) следующую строку:

 
       INSERT INTO Highratings 
          VALUES (2018, 200);

Это - допустима команда INSERT в этом представлении. Строка будет вставлена, с помощью представления Highratings, в таблицу Заказчиков. Однако когда она появится там, она исчезнет из представления, поскольку значение оценки не равно 300. Это - обычна проблема. Значение 200 может быть просто напечатано, но теперь строка находится уже в таблице Заказчиков где вы не можете даже увидеть ее. Пользователь не сможет понять, почему введя строку он не может ее увидеть, и будет не- способен при этом удалить ее. Вы можете быть гарантированы от модификаций такого типа с помощью включения WITH CHECK OPTION (С ОПЦИЕЙ ПРОВЕРКИ) в определение представления. Мы можем использовать WITH CHECK OPTION в определении представления Highratmgs.

 
          CREATE VIEW Highratings 
             AS SELECT cnum, rating 
                FROM Customers 
                WHERE rating = 300 
                WITH CHECK OPTION;

Вышеупомянутая вставка будет отклонена.

WITH CHECK OPTION - производит действие все_или_ничего (all-or-nothing). Вы помещаете его в определение представления, а не в команду DML, так что или все команды модификации в представлении будут проверяться, или ни одна не будет проверена. Обычно вы хотите использовать опцию проверки, используя ее в определении представления, что может быть удобно. В общем, вы должны использовать эту опцию, если у вас нет причины, разрешать представлению помещать в таблицу значения, которые он сам не может содержать.

ПРЕДИКАТЫ И ИСКЛЮЧЕННЫЕ ПОЛЯ

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

 
            CREATE VIEW Londonsta1t 
               AS SELECT snum, sname, comm 
                  FROM Salespeople 
                  WHERE city = 'London';

В конце концов, зачем включать значение city, если все значения city будут одинаковыми. А как будет выглядит картинка получаемая всякий раз, когда мы про- буем вставить строку. Так как мы не можем указать значение city как значение по умолчанию, этим значением вероятно будет NULL, и оно будет введено в поле city ( NULL используется если другое значение по умолчанию значение не было определено. См. Главу 18 для подробностей ). Так как в этом случае поле city не будет равняться значению London, вставляемая строка будет исключена из представления.

Это будет верным для любой строки которую вы попробуете вставить в просмотр Londonstaff. Все они должны быть введены с помощью представления Londonstaff в таблицу Продавцов, и затем исключены из самого представления( если определением по умолчанию был не London, то это особый случай). Пользователь не сможет вводить строки в это представление, хотя все еще неизвестно, может ли он вводить строки в базовую таблицу. Даже если мы добавим WITH CHECK OPTION в определение представления

 
       CREATE VIEW Londonstate 
          AS SELECT snum, sname, comm 
             FROM Salespeople 
             WHERE city = 'London' 
             WITH CHECK OPTION;

проблема не обязательно будет решена. В результате этого мы получим представление которое мы могли бы модифицировать или из которого мы могли бы удалять, но не вставлять в него. В некоторых случаях, это может быть хорошо; хотя возможно нет смысла пользователям имеющим доступ к этому представлению иметь возможность добавлять строки. Но вы долж- ны точно определить что может произойти прежде, чем вы создадите такое представление. Даже если это не всегда может обеспечить Вас полезной информацией, полезно включать в ваше представление все пол, на которые имеется ссылка в предикате. Если вы не хотите видеть эти пол в вашем выводе, вы всегда сможете исключить их из запроса в представлении, в противоположность запросу внутри представления. Другими словами, вы могли бы определить представление Londonstaff подобно этому:

 
           CREATE VIEW Londonstaff 
              AS SELECT * 
                 FROM Salespeople 
                 WHERE city = 'London' 
                 WITH CHECK OPTION;

Эта команда заполнит представление одинаковыми значениями в поле city, которые вы можете просто исключить из вывода с помощью запроса в котором указаны только те пол которые вы хотите видеть

 
           SELECT snum, sname, comm 
              FROM Londonstaff;

ПРОВЕРКА ПРЕДСТАВЛЕНИЙ КОТОРЫЕ БАЗИРУЮТСЯ НА ДРУГИХ ПРЕДСТАВЛЕНИЯХ

Еще одно надо упомянуть относительно предложения WITH CHECK OPTION в ANSI: оно не делает каскадированного изменения : Оно применяется только в представлениях в которых оно определено, но не в представлениях основанных на этом представлении. Например, в предыдущем примере

 
          CREATE VIEW Highratings 
             AS SELECT cnum, rating 
                FROM Customers 
                WHERE rating = 300 
                WITH CHECK OPTION;

попытка вставить или модифицировать значение оценки не равное 300 по- терпит неудачу. Однако, мы можем создать второе представление ( с идентичным содержанием ) основанное на первом:

 
          CREATE VIEW Myratings 
             AS SELECT * 
             FROM Highratings;

Теперь мы можем модифицировать оценки не равные 300:

 
           UPDATE Myratings 
              SET rating = 200 
              WHERE cnum = 2004;

 
             CREATE VIEW Myratings 
                AS SELECT * 
                   FROM Highratings 
                   WITH CHECK OPTION;

проблема не будет решена. Предложение WITH CHECK OPTION будет исследовать только предикат представления Myratings. Пока у Myratings, фактически, не имеется никакого предиката, WITH CHECK OPTION ни- чего не будет делать. Если используется предикат, то он будет проверять- с всякий раз, когда представление Myratings будет модифицироваться, но предикат Highratings все равно будет проигнорирован. Это - дефект в стандарте ANSI, который у большинство программ исправлен. Вы можете попробовать использовать представление наподобие последнего примера и посмотреть избавлена ли ваша система от этого дефекта. ( Попытка выяснить это самостоятельно может быть иногда быть проще и яснее, чем поиск ответа в документации системы. )

РЕЗЮМЕ

РАБОТА С SQL

1. Какое из этих представлений - модифицируемое ?

 
       #1 CREATE VIEW Dailyorders 
             AS SELECT DISTINCT cnum, snum, onum, 
             odate 
               FROM Orders; 
 
       #2 CREATE VIEW Custotals 
             AS SELECT cname, SUM (amt) 
                FROM Orders, Customers 
                WHERE Orders.cnum = customer.cnum 
                GROUP BY cname; 
 
       #3 CREATE VIEW Thirdorders 
             AS SELECT * 
                FROM Dailyorders 
                WHERE odate = 10/03/1990; 
 
       #4 CREATE VIEW Nullcities 
             AS SELECT snum, sname, city 
                FROM Salespeople 
                WHERE city IS NULL 
                   OR sname BETWEEN 'A' AND 'MZ';

2. Создайте представление таблицы Продавцов с именем Commissions (Комиссионные). Это представление должно включать только пол comm и snum. С помощью этого представления, можно будет вводить или изменять комиссионные, но только для значений между .10 и .20.

3. Некоторые SQL реализации имеют встроенную константу представляющую текущую дату, иногда называемую " CURDATE ". Слово CURDATE может следовательно использоваться в операторе SQL, и заменяться текущей датой, когда его значение станет доступным с по- мощью таких команд как SELECT или INSERT. Мы будем использовать представление таблицы Порядков с именем Entryorders для вставки строк в таблицу Порядков. Создайте таблицу порядков, так чтобы CURDATE автоматически вставлялась в поле odate если не указано другого значения. Затем создайте представление Entryorders, так чтобы значения не могли быть указаны.

( См.Приложение A для ответов. )