Опубликован: 10.10.2005 | Уровень: специалист | Доступ: платный | ВУЗ: Московский государственный университет имени М.В.Ломоносова
Лекция 7:

Средства манипулирования данными

Аннотация: Название этой лекции не совсем правильно отражает ее реальное содержание. Собственно средствам языка SQL, предназначенным для обновления базы данных, посвящается только один из трех основных разделов. Однако и следующие два раздела непосредственно примыкают к этой теме. В разделе "Представления, над которыми возможны операции обновления" рассматриваются возможности выполнения операций обновления базы данных через представляемые таблицы. Обсуждается проблема в целом, подход к ее решению, применявшийся в ранних вариантах стандарта SQL, и решение, принятое в стандарте SQL:1999. Последний основной раздел лекции посвящается механизму триггеров, специфицированному в стандарте SQL:1999. Здесь тоже имеется явная связь с основной темой лекции, потому что главным условием срабатывания триггера является выполнение некоторой операции обновления базы данных, и действия, выполняемые в триггерной процедуре, как правило, тоже связаны с обновлением базы данных.
Ключевые слова: базы данных, ПО, OLTP, операции, SQL, динамический SQL, синтаксис, базовые таблицы, стандарт языка, операторы, представление, триггер, связь, оператор UPDATE, оператор DELETE, оператор INSERT, скалярное выражение, вставка всех строк указанной таблицы, список заданий, EMP, PRO, первичный ключ, ограничение первичного ключа, ограничение целостности, вставка явно заданного набора строк, триггеры ROW и STATEMENT, заработная плата, вставка строк результата запроса, булевское выражение, модифицируемость, подзапрос, скалярный подзапрос, материализация представления, представление, над которым возможна операция обновления (updatable view), семантика, место, представление, допускающее применение операций обновления, в стандарте SQL/92, спецификация запроса, distinction, эквивалентная операция, стандарт SQL:1999, представление, допускающее применение операций обновления, в стандарте SQL:1999, потенциальная применимость операций обновления, применимость операций обновления, простая применимость операций обновления, применимость операции вставки, строка данных, UNION, INTERSECT, функциональные зависимости, аксиомы армстронга, SSS, аксиоматическая функциональная зависимость, известная функциональная зависимость, виртуальная таблица, C2, возможный ключ, неопределенное значение, BUC-множество столбцов, акроним, Мультимножество, BPK-множество столбцов, естественное соединение, natural, joining, SL, декартово произведение, таблица разделов, предикат сравнения, условное выражение, логические операции, рекурсивный запрос, здравый смысл, раздел WITH CHECK OPTION определения представления, режимы проверки CASCADED и LOCAL, CASCADE, пользователи базы данных, реляционная база данных, реляционная алгебра, кортеж, международный стандарт, логические выражения, БД, system, СУБД, SQL/92, триггерная процедура, активная база данных, предметная таблица (subject table), операторы SQL, инициирующий оператор SQL (triggering SQL statement), изменение таблицы, безопасность данных, идентификатор пользователя, механизм триггеров в SQL:1999, DROP, triggering, ALTER, триггеры BEFORE и AFTER, триггеры INSERT, UPDATE и DELETE, ссылочное действие, журнализация, раздел WHEN определения триггера, предикат, инициируемый SQL-оператором (triggered_SQL_statement), atom, SQL/PSM, компания oracle, управляющая конструкция, ветвление, составной инициируемый SQL-оператор, составной оператор, выполнение триггеров, контекст выполнения триггера, атомарность, руководитель проекта, псевдоним, переходная таблица, предсказуемость, порядок выполнения триггеров, поддержка ссылочной целостности, внешний ключ, ссылочная целостность, базовая, отношение

Введение

Базы данных, по крайней мере, в приложениях категории OLTP, являются высоко динамичными объектами. В таких приложениях на две операции выборки данных в среднем приходится одна операция обновления содержимого базы данных (добавления новых данных, удаления или модификации существующих данных). Поэтому для пользователей и разработчиков OLTP-приложений средства манипулирования данными по важности находятся на втором месте после средств выборки данных.

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

Лекция состоит из трех основных разделов. В первом разделе мы обсудим синтаксис и семантику операторов манипулирования данными, полагая, что они действуют над базовыми таблицами. Во втором разделе будет продемонстрировано, что в ряде случаев, специфицированных в стандарте языка SQL, операторы манипулирования данными можно применять к порождаемым таблицам и представлениям с однозначным отображением результатов действия этих операторов на соответствующие базовые таблицы. Третий раздел посвящен механизму триггеров, которые, по существу, представляют собой "хранимые процедуры", автоматически вызываемые при возникновении соответствующих условий. Триггеры не обязательно связываются с действиями, производимыми при манипулировании данных, но, поскольку одно из основных функций этого механизма состоит в поддержании целостности баз данных, как правило, такая связь имеется. Поэтому мы включили обсуждение механизма триггеров в соответствии со стандартом SQL именно в данную лекцию.

Базовые средства манипулирования данными

К базовым средствам манипулирования данными языка SQL относятся "поисковые" варианты операторов UPDATE и DELETE . Эти варианты называются поисковыми, потому что при задании соответствующей операции задается логическое условие, налагаемое на строки адресуемой оператором таблицы, которые должны быть подвергнуты модификации или удалению. Кроме того, в такую категорию языковых средств входит оператор INSERT , позволяющий добавлять строки в существующие таблицы. Логично начать изложение именно с оператора INSERT , поскольку, для того чтобы можно было что-либо модифицировать в таблицах или удалять из таблиц, нужно, чтобы в таблицах содержались какие-то строки.

Оператор INSERT для вставки строк в существующие таблицы

Общий синтаксис оператора INSERT выглядит следующим образом:

INSERT INTO table_name
     { [ (column_commalist) ] query_expression
     | DEFAULT VALUES

На вид синтаксические правила кажутся очень простыми, пока не вспомнишь, что обозначает синтаксическая категория query_expression (см. раздел "Общие синтаксические правила построения скалярных выражений" лекции 13). Даже если ограничиться простейшей составляющей этой конструкции ( simple_table ), то мы имеем следующие возможности:

simple_table ::= query_specification
     | table_value_constructor
     | TABLE table_name
Вставка всех строк указанной таблицы

Тем самым, стандарт допускает вставку в указанную таблицу всех строк некоторой другой таблицы (вариант table_name ). Эта другая таблица может быть как базовой, так и представляемой. Естественно, что в последнем случае в определении представления не должны присутствовать ссылки на таблицу, в которую производится вставка. При использовании данного варианта оператора вставки число столбцов вставляемой таблицы должно совпадать с числом столбцов таблицы, в которую производится вставка, или с числом столбцов, указанных в списке column_commalist, если этот список задан. Типы данных соответствующих столбцов вставляемой таблицы и таблицы, в которую производится вставка, должны быть совместимыми. Если в операции задан список column_commalist и в нем содержатся не все имена столбцов таблицы, в которую производится вставка, то в оставшиеся столбцы во всех строках заносятся значения столбцов по умолчанию. Если для какого-либо из оставшихся столбцов значение по умолчанию не определено, при выполнении операции вставки фиксируется ошибка.

Чтобы привести пример этого варианта операции INSERT (пример 17.1), предположим, что в базе данных EMP-DEPT-PRO имеется еще одна промежуточная таблица EMP_TEMP, в которой временно хранятся данные о служащих, проходящих испытательный срок. Пусть эта таблица имеет следующий заголовок:

EMP_TEMP:
EMP_NO : EMP_NO
EMP_NAME : VARCHAR
EMP_BDATE : DATE

В таблице EMP_TEMP хранятся не полные сведения о служащих, а именно те, которые требуются на время испытательного срока. Если выполнить операцию

INSERT INTO EMP (EMP_NO, EMP_NAME, EMP_BDATE) TABLE EMP_TEMP;
Пример 17.1.

то в основной таблице EMP появятся строки, соответствующие служащим, проходившим испытательный срок. При этом в столбцах EMP_NO, EMP_NAME, EMP_BDATE этих строк будут содержаться данные, взятые из таблицы EMP_TEMP, а в столбцах EMP_SAL, DEPT_NO, PRO_NO будут находиться значения, определенные для данных столбцов по умолчанию. Конечно, поскольку столбец EMP_NO является первичным ключом таблицы EMP (по всей видимости, и таблицы EMP_TEMP ), операция вставки будет успешно выполнена только в том случае, когда ограничение первичного ключа таблицы EMP не будет нарушено (конечно же, требуется выполнение и всех других ограничений целостности, определенных для таблицы EMP ).

Вставка явно заданного набора строк

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

table_value_constructor ::= 
    VALUES row_value_constructor_comma_list
row_value_constructor ::= row_value_constructor_element
    | [ ROW ] (row_value_constructor_element_comma_list)
    | row_subquery
row_value_constructor_element ::= value_expression 
    | NULL | DEFAULT

Самый простой пример использования этого варианта оператора вставки состоит в занесении в таблицу EMP явно задаваемых данных о новом служащем (пример 17.2):

INSERT INTO EMP
   ROW (2445, 'Brown', '1985-04-08', 16500.00, 630, 772);
Пример 17.2.

В этом примере явно заданы значения всех столбцов заносимой строки (как показывают синтаксические правила, ключевое слово ROW можно опустить). Возможен и такой вариант (пример 17.2a):

INSERT INTO EMP
   ROW ( 2445, DEFAULT, NULL, DEFAULT, NULL, NULL);
Пример 17.2a.

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

Если обладать полной информацией об определении таблицы EMP, то формулировку операции примера 17.2a можно переписать короче следующим эквивалентным образом (пример 17.2b):

INSERT INTO EMP (EMP_NO) 2445;
Пример 17.2b.

Вспомним теперь, что одной из разновидностей value_expression_primary является scalar_subquery (см. раздел "Скалярные выражения" лекции 13). Это означает, что в список элементов конструктора строки могут входить скалярные запросы, т. е. запросы, результат выполнения которых состоит из единственной строки, включающей единственный столбец. Поэтому допустима, например, такая операция вставки (пример 17.3):

INSERT INTO EMP VALUES
           ROW (2445, (SELECT EMP_NAME
                        FROM EMP
                        WHERE EMP_NO = 2555),
                   '1985-04-08',
                   SELECT EMP_SAL
                        FROM EMP
                        WHERE EMP_NO = 2555),
                   NULL, NULL ),
           ROW (2446, (SELECT EMP_NAME
                        FROM EMP
                        WHERE EMP_NO = 2556),
                   '1978-05-09',
                   (SELECT EMP_SAL
                        FROM EMP
                        WHERE EMP_NO = 2556),
                   NULL, NULL );
Пример 17.3.

После выполнения этой операции в таблице EMP появятся две новые строки для служащих с уникальными идентификаторами 2445 и 2446, причем первому из них будет присвоено имя и размер заработной платы служащего с уникальным идентификатором 2555, а второму - аналогичные данные о служащем с уникальным идентификатором 2556.

Вставка строк результата запроса

Наконец, обсудим вариант оператора вставки, когда набор вставляемых строк определяется через спецификацию запроса. Предположим, например, что требуется сохранить в отдельной таблице DEPT_SUMMARY сведения о числе служащих каждого отдела, их максимальной, минимальной и суммарной заработной плате. Пусть таблица DEPT_SUMMARY уже создана и имеет следующий заголовок1Мы не будем приводить полное определение таблицы, включающее требуемые ограничения целостности.:

DEPT_SUMMARY:
DEPT_NO : DEPT_NO
DEPT_EMP_NO : INTEGER
DEPT_MAX_SAL : SALARY
DEPT_MIN_SAL : SALARY
DEPT_TOTAL_SAL : SALARY

Тогда заполнить таблицу можно с помощью следующей операции вставки (пример 17.4):

INSERT INTO DEPT_SUMMARY
   (SELECT DEPT_NO, COUNT(*), MAX (EMP_SAL),
        MIN (EMP_SAL), SUM (EMP_SAL)
     FROM EMP
     GROUP BY DEPT_NO);
Пример 17.4.
Алексей Ковтун
Алексей Ковтун

При попытке исполнения запроса:

CREATE DOMAIN EMP_NO AS INTEGER

    CHECK (VALUE BETWEEN 1 AND 10000);

Выдается ошибка: Неизвестный тип объекта "DOMAIN" в интсрукции CREATE, DROP или ALTER. 

Используется SQL Server MS SQL 2008R2

Александра Каева
Александра Каева
Евгений Вершинин
Евгений Вершинин
Россия, Нижний Новгород, Нижегородский государственный технический университет, 2008
Aleksandr Arshinskyi
Aleksandr Arshinskyi
Россия