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

Введение

Базы данных, по крайней мере, в приложениях категории 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 хранятся не полные сведения о служащих, а именно те, которые требуются на время испытательного срока. Если выполнить операцию

INSERT INTO EMP (EMP_NO, EMP_NAME, EMP_BDATE) TABLE EMP_TEMP;

то в основной таблице 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);

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

INSERT INTO EMP
   ROW ( 2445, DEFAULT, NULL, DEFAULT, NULL, NULL);

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

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

INSERT INTO EMP (EMP_NO) 2445;

Вспомним теперь, что одной из разновидностей 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 );

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

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

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

Тогда заполнить таблицу можно с помощью следующей операции вставки (пример 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);