SQL и процедурно-ориентированные языки

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

Разбить на страницы
Показывать лекцию целиком

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

Представление (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.

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

    Для символьных типов возможно указание фразы CHARACTER 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);
    Страницы:

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

    Представление (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.

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

    Для символьных типов возможно указание фразы CHARACTER 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);
    Предыдущее занятие Следующее занятие
    Вернуться к учебному плану
    ^