6. Лекция: Работа с представлениями. Типы данных

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

Создание представлений

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

Представление является объектом схемы и используется как логическая таблица базы данных.

Для определения представления применяется оператор CREATE VIEW .

Оператор CREATE VIEW

Оператор CREATE VIEW имеет в стандарте SQL-92 следующее формальное описание:

CREATE VIEW table_name [(field .,:) ]
   AS (SELECT_operator
       [WITH [CASCADED | LOCAL] 
        CHECK OPTION ] );

Список полей (field), указываемый после имени представления, позволяет переименовать столбцы основных таблиц, используемых в запросе. Это может потребоваться в случае совпадения имен столбцов при запросах, использующих объединение таблиц; для именования вычислимых столбцов; для именования объединенных столбцов, полученных посредством соединения столбцов из двух таблиц, имеющих различные имена полей.

Оператор запроса SELECT , использующийся для построения представления, может иметь две формы:

  • расширяемую;
  • постоянную.

Расширяемая форма оператора SELECT задается как конструкция SELECT *, не ограничивая жестко список полей, извлекаемых в запрос. Это позволяет не менять синтаксис представления при изменении оператором ALTER TABLE структуры таблицы: добавлении новых столбцов или удалении столбцов. Однако это также может являться и недостатком, если SQL-операторы, использующие представление, зависят от числа, типа и имен столбцов.

Постоянная форма оператора SELECT задается как конструкция SELECT список_столбцов, жестко фиксируя имена столбцов, входящих в запрос.

Как будет влиять изменение основных таблиц на представление, можно указать в операторе ALTER TABLE :

  • фраза RESTRICT определяет ограничение, отменяющее изменение таблицы, если на данный столбец есть ссылки в представлениях (а также в ограничениях и предикатах);
  • фраза CASCADE указывает, что все представления, использующие удаляемый столбец, также будут удалены (а также все внешние ключи, имеющие ссылки на удаляемый столбец или ограничения FOREIGN KEY).

Оператор ALTER TABLE имеет в стандарте SQL-92 следующее формальное описание:

ALTER TABLE table_name
  { ADD [COLUMN] column_name column_type 
        [(size)]
        [column_ constraint] }
  | { ALTER [COLUMN]column_name 
        { SET DEFAULT value } | DROP DEFAULT }
  | { DROP [COLUMN] column_name 
                    RESTRICT | CASCADE }
  | { ADD table_ constraint }
  | { DROP CONSTRAINT constraint_name 
                      RESTRICT | CASCADE };

Поддержка оператора ALTER TABLE необходима только для полного уровня соответствия стандарту, однако, большинство коммерческих СУБД реализует этот оператор, но с некоторыми изменениями и расширениями.

Следующий оператор иллюстрирует изменение таблицы, приводящее к удалению всех представлений, ссылающихся на столбец f2 изменяемой таблицы:

ALTER TABLE tbl1 DROP COLUMN f2 CASCADE; 

Изменение данных в представлениях

Если для представления указывается оператор DELETE, INSERT или UPDATE, то все изменения происходят как над представлением, так и над основными таблицами, используемыми для создания представления. Не во все представления можно внести изменения. Так, представления могут быть изменяемыми или постоянными.

Стандарт позволяет внесение изменений всегда только в одну основную таблицу. Однако большинство коммерческих СУБД позволяют вносить изменения и в две связанные между собой таблицы, но с некоторыми оговорками.

Стандарт SQL-92 определяет, что представление является изменяемым, если выполнены следующие условия:

  • запрос, используемый для создания представления, извлекает данные только из одной таблицы;
  • если в запросе, используемом для создания таблицы, в качестве таблицы выступает представление, то оно также должно быть изменяемым;
  • не разрешается никаких объединений таблиц, даже самой с собой;
  • запрос, используемый для создания представления, не должен содержать вычислимых столбцов, агрегирующих функций и фраз DISTINCT, GROUP BY и HAVING;
  • в запросе, используемом для создания представления, нельзя ссылаться дважды на один и тот же столбец.

Опции [WITH [CASCADED | LOCAL] CHECK OPTION

Для изменяемого представления можно указывать фразу WITH CHECK OPTION, позволяющую предотвращать "потерю строк" в представлениях. Так, если эта фраза указана, то при внесении изменений в таблицу будет проверен предикат, указанный в запросе, использованном для создания таблицы. Если предикат не возвращает значение TRUE, то изменения не будут внесены.

Например, если запрос создан оператором

CREATE VIEW v_tbl1 AS 
   (SELECT f1,f2, f3 FROM tbl1 WHERE f2>100) 
   WITH CHECK OPTION;,

то вставка строки не будет произведена:

INSERT INTO v_tbl1 (f1,f2,f3) 
       VALUES (1,50,'abc'); 

Фраза WITH CHECK OPTION может быть расширена до:

  • WITH CASCADED CHECK OPTION - предикаты проверяются во всех вложенных запросах;
  • WITH LOCAL CHECK OPTION - предикаты проверяются только в запросе, использованном для создания данного представления;

Так, для представления, созданного операторами

CREATE VIEW v_1 AS 
   (SELECT f1,f2, f3 FROM tbl1 WHERE f2>100);,
CREATE VIEW v_2 AS 
   (SELECT f1,f2, f3 FROM v_1 WHERE f2>50) 
   WITH LOCAL CHECK OPTION;,

добавление строки будет выполнено:

INSERT INTO v_2 (f1,f2,f3) 
       VALUES (1,30,'abc');.

Эта строка будет добавлена в основную таблицу, но не будет видна в представлении, посредством которого она была добавлена.

По умолчанию предполагается, что для WITH CHECK OPTION используется фраза CASCADED.

Типы данных

Каждый столбец базы данных имеет свой тип, указываемый при создании столбца.

В стандарте SQL-92 определены следующие типы:

  • символьные:
    • CHARACTER (len);
    • CHAR (len);
    • CHARACTER VARYING (len);
    • CHAR VARYING (len);
    • VARCHAR (len);
    • NATIONAL CHARACTER (len);
    • NATIONAL CHAR (len);
    • NCHAR (len);
    • NATIONAL CHARACTER VARYING (len);
    • NATIONAL CHAR VARYING (len);
    • NCHAR VARYING (len);
  • двоичные:
    • BIT (len);
    • BIT VARYING (len);
  • числовые:
    • NUMERIC;
    • DECIMAL;
    • DEC;
    • INTEGER;
    • INT;
    • SMALLINT;
    • FLOAT;
    • REAL;
    • DOUBLE PRECISION;
  • даты/времени:
    • DATE;
    • TIME;
    • TIME WITH TIME ZONE;
    • TIMESTAMP;
    • TIMESTAMP WITH TIME ZONE;
  • интервалы:
    • INTERVAL.

Отметим, что параметры типа, указываемые в скобках, в большинстве случаев можно при необходимости опускать.

Для символьных типов возможно указание фразы CARACTER SET { set_name | using_form}, устанавливающей используемый набор символов.

Приведем описание наиболее часто используемых типов данных:

  • CHAR (num) - текстовая строка фиксированной длины (на диске сразу выделяется место под всю строку);
  • VARCHAR (num) - текстовая строка переменной длины, содержащая не более num символов (на диске выделяется место в зависимости от длины строки);
  • INTEGER или INT - целое;
  • NUMERIC - число с плавающей точкой, возможно определение числа знаков после запятой;
  • DECIMAL или DEC - число с плавающей точкой, возможно задание минимального значения точности;
  • FLOAT - число с плавающей точкой, позволяющее задавать точность (количество знаков после запятой);
  • REAL - число с плавающей точкой, точность которого определяется реализацией;
  • DATE - тип даты;
  • TIME WITH TIME ZONE - тип времени, содержащий поля, описывающие сдвиг зонального времени.

Типы данных, описывающие дату и время, состоят из нескольких полей, в которых хранятся части даты времени. Так, тип DATE содержит поля YEAR, MONTH и DAY. Тип TIME содержит поля HOUR, MINUTE и SECOND.

Тип TIMESTAMP содержит как поля даты, так и поля времени.

Значение типа TIMESTAMP записывается следующим образом: '10-12-2003 08:30:00'. Порядок следования полей при написании даты, как правило, определяется установками компьютера.

Функции даты/времени

Для работы с данными, имеющими тип даты/времени в языке SQL предусмотрены следующие функции:

CURRENT_TIME - определяет текущее время;

CURRENT_DATE - определяет текущую дату;

CURRENT_TIMESTAMP - определяет текущие дату и время.

Например:

INSERT INTO tbl1 (f1,f2,f3,f4)
     VALUES (1,100,'abc', CURRENT_DATE);