13. Представления, функции, хранимые триггеры(DDL,DML), курсоры - KattyOG/Database GitHub Wiki

Представления

Представление (View) – это виртуальная таблица, содержимое которой (столбцы и строки) определяется запросом.
Представление можно использовать в следующих целях:

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

К представлению можно обращаться как к обычной таблице и извлекать данные из него.

Одним из главных достоинством представлений является то, что они сильно упрощают взаимодействие с данными в базе данных. Допустим, Вам необходимо каждый раз делать сложную по своей структуре выборку, а как Вы знаете, запрос на выборку может быть, ну просто очень сложный и этому нет предела. И если не было бы представлений, то Вам приходилось бы каждый раз запускать этот запрос, или даже его модифицировать, например, для вставки условий. А так как у нас есть такие объекты как представления, нам этого делать не придется. Мы просто на всего создадим одну VIEWS, и потом уже к ней будем обращаться с помощью уже простых запросов.

Синтаксис

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW имя [ ( имя_столбца [, ...] ) ]
    [ WITH ( имя_параметра_представления [= значение_параметра_представления] [, ... ] ) ]
    AS запрос
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

CREATE VIEW создаёт представление запроса. Создаваемое представление лишено физической материализации, поэтому указанный запрос будет выполняться при каждом обращении к представлению.

Команда CREATE OR REPLACE VIEW действует подобным образом, но если представление с этим именем уже существует, оно заменяется.

Если задано имя схемы (например, CREATE VIEW myschema.myview ...), представление создаётся в указанной схеме, в противном случае — в текущей. Временные представления существуют в специальной схеме, так что при создании таких представлений имя схемы задать нельзя. Имя представления должно отличаться от имён других представлений, таблиц, последовательностей, индексов или сторонних таблиц в этой схеме.

Параметры

TEMPORARY или TEMP
С таким указанием представление создаётся как временное. Временные представления автоматически удаляются в конце сеанса. Существующее постоянное представление с тем же именем не будет видно в текущем сеансе, пока существует временное, однако к нему можно обратиться, дополнив имя указанием схемы.

Если в определении представления задействованы временные таблицы, представление так же создаётся как временное (вне зависимости от присутствия явного указания TEMPORARY).

RECURSIVE
Создаёт рекурсивное представление.

имя
Имя создаваемого представления (возможно, дополненное схемой).

имя_столбца
Необязательный список имён, назначаемых столбцам представления. Если отсутствует, имена столбцов формируются из результатов запроса.

запрос
Команда SELECT или VALUES, которая выдаёт столбцы и строки представления.

WITH [ CASCADED | LOCAL ] CHECK OPTION
Это указание управляет поведением автоматически изменяемых представлений. Если оно присутствует, при выполнении операций INSERT и UPDATE с этим представлением будет проверяться, удовлетворяют ли новые строки условию, определяющему представление (то есть, проверяется, будут ли новые строки видны через это представление). Если они не удовлетворяют условию, операция не будет выполнена. Если указание CHECK OPTION отсутствует, команды INSERT и UPDATE смогут создавать в этом представлении строки, которые не будут видны в нём.

Обновляемые представления

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

Любые изменения, в том числе инструкции UPDATE, INSERT и DELETE, должны ссылаться на столбцы только одной базовой таблицы.
Изменяемые в представлении столбцы должны непосредственно ссылаться на данные столбцов базовой таблицы. Столбцы нельзя сформировать каким-либо другим образом, в том числе:

o  при помощи агрегатной функции: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR и
VARP;
o на основе вычисления. Столбец нельзя вычислить по выражению, включающему другие столбцы. Столбцы,
сформированные при помощи операторов UNION, UNION ALL, CROSSJOIN, EXCEPT и INTERSECT,
считаются вычисляемыми и также не являются обновляемыми.
o Предложения GROUP BY, HAVING и DISTINCT не влияют на изменяемые столбцы.
o Предложение TOP не используется нигде в инструкции select представления вместе с предложением WITH
CHECK OPTION.

Вышеназванные ограничения относятся ко всем подзапросам представления в предложении FROM, равно как и к самому представлению. Как правило, компонент Database Engine должен иметь возможность однозначно проследить изменения от определения представления до одной базовой таблицы. Если вышеуказанные ограничения не позволяют изменить данные через представление напрямую, используйте триггер INSTEAD OF.

Триггер INSTEAD OF

Чтобы сделать представление обновляемым, для него можно создать триггеры INSTEAD OF. Триггер INSTEAD OF выполняется вместо инструкции модификации данных, для которой он определен. Этот триггер позволяет пользователю указать набор действий, которые должны быть выполнены для обработки инструкции модификации данных. Таким образом, если для представления создан триггер INSTEAD OF, связанный с конкретной инструкцией модификации данных (INSERT, UPDATE или DELETE), соответствующее представление можно обновлять при помощи этой инструкции.

Функции

SQL Server содержит набор встроенных функций и предоставляет возможность создавать пользовательские функции (User Defined Function – UDF). Различают детерминированные и недетерминированные функции.

Функция является детерминированной, если при одном и том же заданном входном значении она всегда возвращает один и тот же результат. Например, встроенная функция DATEADD является детерминированной; она возвращает новое значение даты, добавляя интервал к указанной части заданной даты.

Функция является недетерминированной, если она может возвращать различные значения при одном и том же заданном входном значении. Например, встроенная функция GETDATE является недетерминированной; при каждом вызове она возвращает различные значения даты и времени компьютера, на котором запущен экземпляр SQL Server.

Все функции конфигурации, курсора, метаданных, безопасности и системные статистические – недетерминированные.

Пользовательские функции в зависимости от типа данных возвращаемых ими значений могут быть скалярными и табличными. Табличные пользовательские функции бывают двух типов: подставляемые и многооператорные. Скалярные пользовательские функции обычно используются в списке столбцов инструкции SELECT и в предложении WHERE. Табличные пользовательские функции обычно используются в предложении FROM, и их можно соединять с другими таблицами и представлениями.

Любой набор команд на языке SQL можно скомпоновать вместе и обозначить как функцию. Помимо запросов SELECT, эти команды могут включать запросы, изменяющие данные (INSERT, UPDATE и DELETE), а также другие SQL-команды. (В SQL-функциях нельзя использовать команды управления транзакциями, например COMMIT, SAVEPOINT) Однако последней командой должна быть SELECT или команда с предложением RETURNING, возвращающая результат с типом возврата функции. Если же вы хотите определить функцию SQL, выполняющую действия, но не возвращающую полезное значение, вы можете объявить её как возвращающую тип void.

CREATE FUNCTION

CREATE [ OR REPLACE ] FUNCTION
    имя ( [ [ режим_аргумента ] [ имя_аргумента ] тип_аргумента [ { DEFAULT | = } выражение_по_умолчанию ] [, ...] ] )
    [ RETURNS тип_результата
      | RETURNS TABLE ( имя_столбца тип_столбца [, ...] ) ]
  { LANGUAGE имя_языка
    | TRANSFORM { FOR TYPE имя_типа } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | COST стоимость_выполнения
    | ROWS строк_в_результате
    | SET параметр_конфигурации { TO значение | = значение | FROM CURRENT }
    | AS 'определение'
    | AS 'объектный_файл', 'объектный_символ'
  } ...
    [ WITH ( атрибут [, ...] ) ]

Команда CREATE FUNCTION определяет новую функцию. CREATE OR REPLACE FUNCTION создаёт новую функцию, либо заменяет определение уже существующей.

Если указано имя схемы, функция создаётся в заданной схеме, в противном случае — в текущей. Имя новой функции должно отличаться от имён существующих функций с такими же типами аргументов в этой схеме. Однако функции с аргументами разных типов могут иметь одно имя (это называется перегрузкой).

Чтобы заменить текущее определение существующей функции, используйте команду CREATE OR REPLACE FUNCTION. Но учтите, что она не позволяет изменить имя или аргументы функции (если попытаться сделать это, на самом деле будет создана новая, независимая функция). Кроме того, CREATE OR REPLACE FUNCTION не позволит изменить тип результата существующей функции. Чтобы сделать это, придётся удалить функцию и создать её заново. (Это означает, что если функция имеет выходные параметры (OUT), то изменить типы параметров OUT можно, только удалив функцию.)

Скалярная функция

Функция, которая возвращает одно значение(одну строку и один столбец).

Табличная функция

Для табличной функции предложение RETURNS задает TABLE без указания списка столбцов. Столбцы, включающие типы данных таблицы, возвращаемой функцией, определяются списком SELECT оператора SELECT, который определяет функцию.

Многооператорные табличные функции

Если предложение RETURNS задает тип TABLE с определением столбцов и их типов данных, функция является многооператорной табличной функцией. В многооператорной функции должно быть несколько select.

Триггеры

Триггер — хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных.

DDL

DDL: CREATE, ALTER, DROP
Триггеры DDL — это особый вид триггеров, которые срабатывают при выполнении инструкций языка описания данных DDL. Они могут применяться при выполнении административных задач (например, для аудита и регулирования операций в базе данных).

DML

DML: INSERT, UPDATE, DELETE
Триггеры DML — это хранимые процедуры особого типа, автоматически вступающие в силу, если происходит событие языка обработки данных DML, которое затрагивает таблицу или представление, определенное в триггере.

CREATE TRIGGER

CREATE [ CONSTRAINT ] TRIGGER имя { BEFORE | AFTER | INSTEAD OF } { событие [ OR ... ] }
    ON имя_таблицы
    [ FROM ссылающаяся_таблица ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( условие ) ]
    EXECUTE PROCEDURE имя_функции ( аргументы )

Здесь допускается событие:

    INSERT
    UPDATE [ OF имя_столбца [, ... ] ]
    DELETE
    TRUNCATE

CREATE TRIGGER создаёт новый триггер. Триггер будет связан с указанной таблицей, представлением или сторонней таблицей и будет выполнять заданную функцию имя_функции при определённых событиях.

Триггер можно настроить так, чтобы он срабатывал до операции со строкой (до проверки ограничений и попытки выполнить INSERT, UPDATE или DELETE) или после её завершения (после проверки ограничений и выполнения INSERT, UPDATE или DELETE), либо вместо операции (при добавлении, изменении и удалении строк в представлении). Если триггер срабатывает до или вместо события, он может пропустить операцию с текущей строкой, либо изменить добавляемую строку (только для операций INSERT и UPDATE). Если триггер срабатывает после события, он «видит» все изменения, включая результат действия других триггеров. Триггер и инструкция, при выполнении которой он срабатывает, считаются одной транзакцией, которую можно откатить назад внутри триггера. При обнаружении серьезной ошибки (например, нехватки места на диске) вся транзакция автоматически откатывается назад.

Триггер с пометкой FOR EACH ROW вызывается один раз для каждой строки, изменяемой в процессе операции.

Триггеры, срабатывающие в режиме INSTEAD OF, должны быть помечены FOR EACH ROW и могут быть определены только для представлений.

В триггерах FOR EACH ROW условие WHEN может ссылаться на значения столбца в старой и/или новой строке, в виде OLD.имя_столбца и NEW.имя_столбца, соответственно. Разумеется, триггеры INSERT не могут ссылаться на OLD, а триггеры DELETE не могут ссылаться на NEW.

Также пользователь должен иметь право EXECUTE для триггерной функции. Разрешения на хранимые процедуры: EXECUTE.

Курсоры

Курсор — ссылка на контекстную область памяти. Получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи.

Курсор — это виртуальная таблица которая представляет собой альтернативное хранилище данных. При этом курсор, позволяет обращаться к своим данным, как к данным обычного массива.

Курсор PL/pgSQL позволяет вам инкапсулировать запрос и обрабатывать каждую отдельную строку за раз.

На следующей диаграмме показано, как использовать курсор в PostgreSQL:

Сначала объявите курсор.

declare my_cursor cursor

Далее откройте курсор.

open my_cursor;

Затем выберите строки из набора результатов в целевой объект. Для получения записи нужно использовать FETCH.

 loop
     fetch my_cursor into temp;
     exit when not found;
     ...(какие-то действия для выборки данных)
end loop;

Наконец, закройте курсор.

close my_cursor;

По умолчанию курсор получает следующую строку, если вы не укажете направление явно. Для курсора допустимо следующее: NEXT, LAST, PRIOR, FIRST и тд.

Для цикла по диапазону чисел автоматически объявляется целочисленная переменная, а для цикла по результатам курсора - переменная типа record. Вы можете использовать Record как переменную, которая может содержать строку таблицы или некоторые столбцы (поля) из строки таблицы.