208.99K

Category:

database

8. Exception, Циклы, Курсоры, Директивы

1.

Обработка ошибок
По умолчанию любая возникающая ошибка прерывает выполнение функции на PL/pgSQL и
транзакцию,
в
которой
она
выполняется.
Использование
в
блоке
секции EXCEPTION позволяет перехватывать и обрабатывать ошибки.

2.

Обработка ошибок
Ошибки отлавливаются в пределах блока begin-end:
1. Сначала выполняется код между begin и exception
2. Если ошибок не произошло, тогда секция между exception и end ингорируется
3. Если в процессе выполнения кода происходит ошибка, выполнение останавливается и
переходит в блок exception.
4. Если в блоке находится обработчик для исключения, вызывается код после then
5. Если обработчик не найден, исключение выбрасывается за пределы блока begin-end

3.

4.

5.

6.

7.

8.

Предопределённые ошибки
В качестве условия может задаваться одно из имён, перечисленных в Документации.
Если задаётся имя категории, ему соответствуют все ошибки в данной категории. Специальному имени
условия OTHERS соответствуют все типы ошибок, кроме QUERY_CANCELED и ASSERT_FAILURE. (И эти два типа
ошибок можно перехватить по имени, но часто это неразумно) Имена условий воспринимаются без учёта
регистра. Условие ошибки также можно задать кодом SQLSTATE; например, эти два варианта равнозначны:
WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...

9.

Внутри блока…
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones’;
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'перехватили ошибку division_by_zero’;
RETURN x;
END;

10.

Внутри блока…
При выполнении команд в секции EXCEPTION локальные переменные функции на PL/pgSQL сохраняют те
значения, которые были на момент возникновения ошибки. Однако все изменения в базе данных,
выполненные в блоке, будут отменены.
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones’;
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'перехватили ошибку division_by_zero’;
RETURN x;
END;

11.

Наличие секции EXCEPTION значительно увеличивает накладные
расходы на вход-выход из блока, поэтому не
используйте EXCEPTION без надобности.

12.

Получение информации об ошибке
При обработке исключений часто бывает необходимым получить детальную информацию о
произошедшей ошибке. Для этого в PL/pgSQL есть два способа: использование специальных
переменных и команда GET STACKED DIAGNOSTICS.
Внутри секции EXCEPTION специальная переменная SQLSTATE содержит код ошибки, для которой
было вызвано исключение (список возможных кодов ошибок приведён в Документации).
Специальная переменная SQLERRM содержит сообщение об ошибке, связанное с исключением.
Эти переменные являются неопределёнными вне секции EXCEPTION.

13.

GET STACKED DIAGNOSTICS переменная { = | := } элемент [ , ... ];
Каждый элемент представляется ключевым словом, указывающим, какое значение состояния нужно присвоить
заданной переменной (она должна иметь подходящий тип данных, чтобы принять его).
Элемент
Тип
Описание
RETURNED_SQLSTATE
text
код исключения, возвращаемый SQLSTATE
COLUMN_NAME
text
имя столбца, относящегося к исключению
CONSTRAINT_NAME
text
имя ограничения целостности, относящегося к исключению
PG_DATATYPE_NAME
text
имя типа данных, относящегося к исключению
MESSAGE_TEXT
text
текст основного сообщения исключения
TABLE_NAME
text
имя таблицы, относящейся к исключению
SCHEMA_NAME
text
имя схемы, относящейся к исключению
PG_EXCEPTION_DETAIL
text
текст детального сообщения исключения (если есть)
PG_EXCEPTION_HINT
text
текст подсказки к исключению (если есть)
PG_EXCEPTION_CONTEXT
text
строки текста, описывающие стек вызовов в момент исключения

14.

Получение информации об ошибке
DECLARE
text_var1 text;
text_var2 text;
text_var3 text;
BEGIN
-- здесь происходит обработка, которая может вызвать исключение
...
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
text_var1 = MESSAGE_TEXT,
text_var2 = PG_EXCEPTION_DETAIL,
text_var3 = PG_EXCEPTION_HINT;
END;

15.

Ошибки и вложенные блоки
Если ошибка не обрабатывается в пределах блока begin ..end, она выбрасывается за его пределы. Далее эта
ошибка может быть обработана блоком exception внешнего блока. Если и там ошибка не обрабатывается,
она выбрасывается и за его пределы, и так далее.
declare
a number;
-- Внешний блок
begin
-- Вложенный блок
begin
a := 1 / 0;
-- Важно помнить, что после возникновения ошибки
-- выполнение кода в пределах блока прекращается.
raise notice('Этот код не будет выполнен’);
end;
exception
when zero_divide:
raise notice('Ошибка обработана внешним блоком');
end;

16.

Обработка собственных ошибок
Мы можем задать свою ошибку для обработки: RAISE SQLSTATE '22012';
При задании SQLSTATE кода необязательно использовать только список предопределённых кодов
ошибок. В качестве кода ошибки может быть любое пятисимвольное значение, состоящее из
цифр и/или ASCII символов в верхнем регистре, кроме 00000.
Не рекомендуется использовать коды ошибок, которые заканчиваются на 000, потому что так
обозначаются коды категорий. И чтобы их перехватить, нужно перехватывать целую категорию.
begin
...
IF v_err IS NOT NULL then
RAISE SQLSTATE 'URM01’;
end if;
exception
when sqlstate 'URM01' then
v_res.p_err := v_err;
v_res.p_res := false;
return v_res;
end;

17.

Для Oracle
Можно объявлять собственные исключения, давая им названия, которые полнее раскрывают их суть.
Область видимости собственного исключения в данном
случае - блок, в котором оно объявлено. Вне этого блока
обработать исключение не получится.

18.

А теперь о насущном…

19.

DECLARE
...
BEGIN
SELECT ... INTO ... FROM ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
...
WHEN TOO_MANY_ROWS THEN
...
END;

20.

DECLARE
...
BEGIN
SELECT ... INTO ... FROM ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
...
WHEN TOO_MANY_ROWS THEN
...
END;
DECLARE
...
BEGIN
SELECT ... INTO STRICT ... FROM ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
...
WHEN TOO_MANY_ROWS THEN
...
END;

21.

Директива Strict
STRICT
CREATE FUNCTION add_strict(a int, b int)
RETURNS int
LANGUAGE sql
STRICT
AS $$
SELECT a + b;
$$;
Директива STRICT (синоним: RETURNS NULL ON NULL INPUT) указывает, что функция
должна возвращать NULL, если любой из её входных параметров является NULL.
Это позволяет избежать выполнения функции с NULL значениями.

22.

DECLARE
...
BEGIN
SELECT ... INTO STRICT ... FROM ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
...
WHEN TOO_MANY_ROWS THEN
...
END;
NO_DATA_FOUND
SELECT * INTO r_movie FROM movies WHERE movie_id = p_id;
if not found then
p_err := CONCAT('Сообщение с swbID = ',pr_swbID,' не обнаружено');
raise sqlstate '57910';
end if;

23.

DECLARE
...
BEGIN
SELECT ... INTO STRICT ... FROM ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
...
WHEN TOO_MANY_ROWS THEN
...
END;
TOO_MANY_ROWS
a varchar;
a := cSmrMfo8 FROM SMR WHERE IDSmr = '1';
-a record;
a := (cSmrMfo8,cSmrKorAcc) FROM SMR WHERE IDSmr='1';

24.

Простые циклы
LOOP
-- здесь производятся вычисления
IF count > 0 THEN
EXIT; -- выход из цикла
END IF;
END LOOP;
LOOP
-- здесь производятся вычисления
EXIT WHEN count > 0; -- аналогично предыдущему примеру
END LOOP;

25.

Простые циклы
LOOP
-- здесь производятся вычисления
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
-- вычисления для count в диапазоне 50 .. 100
END LOOP;

26.

Простые циклы
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- здесь производятся вычисления
END LOOP;
WHILE NOT done LOOP
-- здесь производятся вычисления
END LOOP;

27.

Простые циклы
FOR i IN 1..10 LOOP
-- внутри цикла переменная i будет иметь значения 1,2,3,4,5,6,7,8,9,10
END LOOP;
FOR i IN REVERSE 10..1 LOOP
-- внутри цикла переменная i будет иметь значения 10,9,8,7,6,5,4,3,2,1
END LOOP;
FOR i IN REVERSE 10..1 BY 2 LOOP
-- внутри цикла переменная i будет иметь значения 10,8,6,4,2
END LOOP;

28.

Цикл по массиву
CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
s int := 0;
x int;
BEGIN
FOREACH x IN ARRAY $1 LOOP
s := s + x;
END LOOP;
RETURN s;
END;
$$ LANGUAGE plpgsql;

29.

Курсоры

30.

31.

Курсоры
Курсор (CURSOR) - это набор строк, возвращаемых запросом, может состоять из нуля, одной или
нескольких строк, в зависимости от того, сколько строк соответствует вашим критериям поиска. Когда
запрос возвращает несколько строк, вы можете явно объявить курсор для обработки строк. Более того, вы
можете объявить курсор в декларативной части любого PL/SQL-блока, подпрограммы или пакета.
КУРСОР БЕЗ ПАРАМЕТРОВ (ПРОСТОЙ)
Объявление курсора без каких-либо параметров, простой курсор.
CURSOR cursor_name
IS
SELECT_statement;
Например:
CURSOR c1
IS
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in;
Результирующий набор этого курсора все course_numbers, у
которых course_name соответствует переменной name_in.

32.

Курсор с параметрами
CURSOR cursor_name (parameter_list)
IS
SELECT_statement;
CURSOR c2 (subject_id_in IN varchar2)
IS
SELECT course_number
FROM courses_tbl
WHERE subject_id = subject_id_in;
Результирующий набор этого курсора все course_numbers, у
которых subject_id соответствует subject_id полученные курсором с помощью
параметра subject_id_in.
Курсор с возвращающим условием
CURSOR cursor_name
RETURN field%ROWTYPE
IS
SELECT_statement;
CURSOR c3
RETURN courses_tbl%ROWTYPE
IS
SELECT *
FROM courses_tbl
WHERE subject = 'Mathematics';
Результирующим набором этого курсора будут все столбцы course_tbl, у
которых subject будет 'Mathematics'.

33.

Управление курсором
Для управления курсором используется три команды: OPEN, FETCH и CLOSE.
Сначала вы инициализируете курсор с помощью оператора OPEN, который идентифицирует набор
результатов.
Затем вы можете выполнять FETCH несколько раз, пока не будут получены все строки, или вы можете
использовать предложение BULK COLLECT, чтобы получить все строки одновременно.
Когда последняя строка обработана, вы закрываете курсор с помощью оператора CLOSE. Вы можете
обрабатывать несколько запросов параллельно, объявляя и открывая несколько курсоров.
OPEN оператор
После того, как вы объявили курсор, следующим шагом для открытия курсора будет оператор OPEN.
OPEN имя_курсора;

34.

FETCH ОПЕРАТОР
Цель использования курсора, в большинстве случаев, это получение строк из курсора таким образом, чтобы
некоторый тип операций мог быть выполнен на данных. После объявления и открытия курсора, следующим
шагом является выборка строк из курсора с помощью оператора FETCH.
FETCH имя_курсора INTO variable_list;
CLOSE ОПЕРАТОР
Параметры или аргументы
имя_курсора наименование курсора, из которого вы хотите
извлечь строки.
variable_list список переменных, разделенных запятыми, в
которые вы хотите сохранить результирующий набор
курсора.
Заключительный этап работы с курсорами это закрытие курсора, после того, как вы закончите его использовать.
CLOSE имя_курсора;

35.

Пример курсора
DECLARE
CURSOR c_acc IS
SELECT iaccnum,
iacccus
FROM acc;
v_accnum
acc.iaccnum%TYPE;
v_acccus
acc.iacccus%TYPE;
BEGIN
OPEN c_acc;
LOOP
FETCH c_acc INTO v_accnum,
v_acccus;
EXIT WHEN c_acc%NOTFOUND;
END LOOP;
CLOSE c_acc;
END;
DO $$
DECLARE
c_acc CURSOR FOR
SELECT iaccnum,
iacccus
FROM acc;
v_accnum
acc.iaccnum%TYPE;
v_acccus
acc.iacccus%TYPE;
BEGIN
OPEN c_acc;
LOOP
FETCH c_acc INTO v_accnum,
v_acccus;
EXIT WHEN NOT FOUND;
END LOOP;
CLOSE c_acc;
END;
$$;

36.

Неявный курсор
DO $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT itrnnum, itrnanum, dtrntran
FROM trn
WHERE date_trunc('day', dtrntran) = current_date
LOOP
INSERT INTO some_log_table (itrnnum, itrnanum, dtrntran)
VALUES (rec.itrnnum, rec.itrnanum, rec.dtrntran);
END LOOP;
END;
$$

37.

Курсорные переменные
DO $$
DECLARE
c_acc
refcursor;
v_accnum
acc.iaccnum%TYPE;
v_acccus
acc.iacccus%TYPE;
BEGIN
OPEN c_acc FOR SELECT iaccnum,
iacccus
FROM acc;
LOOP
FETCH c_acc INTO v_accnum,
v_acccus;
EXIT WHEN NOT FOUND;
END LOOP;
CLOSE c_acc;
END;
$$

38.

Курсоры на основе динамического SQL
DECLARE
type refcur is ref cursor;
curs1
refcur;
tabname
varchar2(100) := 'acc';
keyvalue
number := 987000800111;
v_accnum
acc.iaccnum%TYPE;
v_acccus
acc.iacccus%TYPE;
BEGIN
OPEN curs1 FOR 'SELECT iaccnum,
iacccus
FROM ' || tabname ||
' WHERE iacccus = :keyvalue'
USING keyvalue;
LOOP
FETCH curs1 INTO v_accnum,
v_acccus;
EXIT WHEN curs1%NOTFOUND;
END LOOP;
DO $$
DECLARE
curs1
tabname
keyvalue
rec
v_accnum
v_acccus
BEGIN
refcursor := 'c_acc';
TEXT := 'acc';
NUMERIC := 987000800111;
record;
acc.iaccnum%TYPE;
acc.iacccus%TYPE;
OPEN curs1 FOR EXECUTE
format('SELECT iaccnum,
iacccus
FROM %I
WHERE iacccus = $1', tabname)
USING keyvalue;
LOOP
FETCH curs1 INTO v_accnum,
v_acccus;
EXIT WHEN NOT FOUND;
END LOOP;
CLOSE curs1;
END;
CLOSE curs1;
END;
$$;

39.

Атрибуты курсора
DECLARE
CURSOR c_acc is
SELECT iaccnum,
iacccus
FROM acc
where rownum = 1;
v_accnum
acc.iaccnum%TYPE;
v_acccus
acc.iacccus%TYPE;
BEGIN
OPEN c_acc;
LOOP
FETCH c_acc INTO v_accnum,
v_acccus;
dbms_output.put_line(c_acc%ROWCOUNT);
---------------------------------EXIT WHEN c_acc%NOTFOUND;
END LOOP;
IF c_acc%ISOPEN THEN
CLOSE c_acc;
END IF;
END;
DO $$
DECLARE
c_acc CURSOR FOR
SELECT iaccnum,
iacccus
FROM acc
LIMIT 1;
v_accnum
acc.iaccnum%TYPE;
v_acccus
acc.iacccus%TYPE;
rowcount INTEGER;
BEGIN
OPEN c_acc;
LOOP
FETCH c_acc INTO v_accnum,
v_acccus;
----эквивалент для %rowcount----GET DIAGNOSTICS rowcount = ROW_COUNT;
CALL tracepkg.txtout(rowcount::VARCHAR);
---------------------------------EXIT WHEN NOT FOUND;
END LOOP;
----эквивалент для %isopen----IF cursor_isopen('c_acc'::TEXT) THEN
CLOSE c_acc;
END IF;
END;
$$

40.

Курсоры для получения одной записи
FUNCTION getI22s_msgRow(p_ID IN i22s_msg.imsg_id%TYPE)
RETURN i22s_msg%ROWTYPE
IS
CURSOR c_i22msg IS
SELECT * FROM i22s_msg WHERE imsg_id = p_ID;
r_row i22s_msg%ROWTYPE;
BEGIN
OPEN c_i22msg;
FETCH c_i22msg INTO r_row;
CLOSE c_i22msg;
RETURN r_row;
END getI22s_msgRow;
create function getI22s_msgRow(p_ID i22s_msg.imsg_id%TYPE)
returns i22s_msg
language sql
stable
as $$
select * from i22s_msg WHERE imsg_id = p_ID;
$$

41.

SELECT FOR UPDATE оператор
Команда SELECT FOR UPDATE позволяет заблокировать записи в результирующем наборе курсора. Блокировка
записей снимается, когда выполняются следующие команды commit или rollback.
CURSOR имя_курсора
IS
select_statement
FOR UPDATE [OF column_list] [NOWAIT];
column_list столбцы в результирующего наборе курсора, который вы хотите обновить.
NOWAIT не является обязательным. Курсор не будет ждать ресурсов.
CURSOR c1
IS
SELECT course_number, instructor
FROM courses_tbl
FOR UPDATE OF instructor;

42.

43.

WHERE CURRENT OF ОПЕРАТОР
Если вы планируете обновлять или удалять записи, на которые ссылается оператор SELECT FOR UPDATE, вы можете
использовать оператор WHERE CURRENT OF.
UPDATE имя_таблицы
SET set_clause
WHERE CURRENT OF имя_курсора;
или
DELETE FROM имя_таблицы
WHERE CURRENT OF имя_курсора;
Оператор WHERE CURRENT OF позволяет обновить или удалить запись, которая была в курсоре последней
При использовании WHERE CURRENT OF в UPDATE нельзя обновлять поле, используя его текущее значение для
генерации обновлённого значения (например, SET Salary = Salary + 100 или SET Name = UPPER(Name)). Попытка это
сделать приведёт к ошибке SQLCODE -9

44.

Когда курсор позиционирован на строку таблицы, эту строку можно изменить или удалить
при помощи курсора. Есть ограничения на то, каким может быть запрос курсора (в частности, не
должно быть группировок), и крайне желательно использовать указание FOR UPDATE
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

45.

Возврат курсора из функции
Курсоры можно возвращать из функции
на PL/pgSQL. Это полезно, когда нужно вернуть
множество строк и столбцов, особенно если
выборки очень большие. Для этого, в функции
открывается курсор и его имя возвращается
вызывающему (или просто открывается курсор,
используя указанное имя портала, каким-либо
образом известное вызывающему). Вызывающий
затем может извлекать строки из курсора. Курсор
может быть закрыт вызывающим или он будет
автоматически закрыт при завершении
транзакции.

46.

47.

А как вернуть несколько курсоров из одной функции?
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM table_1;
RETURN NEXT $1;
OPEN $2 FOR SELECT * FROM table_2;
RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;
-- для использования курсоров необходимо начать транзакцию
BEGIN;
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;

48.

Директивы

49.

SECURITY INVOKER
SECURITY INVOKER или SECURITY DEFINER определяют с чьими правами будет выполняться подпрограмма.
CREATE FUNCTION get_user_data(user_id integer)
RETURNS text
LANGUAGE sql
SECURITY INVOKER
AS $$
SELECT data FROM users WHERE id = user_id;
$$;
SECURITY INVOKER выполняется с правами пользователя, который вызвал функцию.
Это поведение по умолчанию в PostgreSQL.

50.

SECURITY DEFINER
SECURITY INVOKER или SECURITY DEFINER определяют с чьими правами будет выполняться подпрограмма.
CREATE FUNCTION get_user_data(user_id integer)
RETURNS text
LANGUAGE sql
SECURITY DEFINER
AS $$
SELECT data FROM users WHERE id = user_id;
$$;
SECURITY DEFINER выполняется с правами пользователя, который создал функцию.
Это полезно для создания функций, которые должны иметь доступ к данным или выполнять
действия, для которых вызывающий пользователь не имеет прав.

51.

VOLATILE / STABLE / IMMUTABLE
Для каждой функции определяется характеристика изменчивости, с возможными вариантами: VOLATILE,
STABLE и IMMUTABLE. Если эта характеристика не задаётся явно в команде CREATE FUNCTION, по
умолчанию подразумевается VOLATILE.

52.

VOLATILE
Изменчивая функция может делать всё, что угодно, в том числе, модифицировать базу данных. В запросе,
использующем изменчивую функцию, она будет вычисляться заново для каждой строки, когда потребуется
её результат. PostgreSQL не будет кешировать результаты вызовов таких функций и будет вызывать их
каждый раз при выполнении запроса.
CREATE FUNCTION get_current_time()
RETURNS timestamptz
LANGUAGE sql
VOLATILE
AS $$
SELECT now();
$$;
Примеры VOLATILE функций:
• Функции, возвращающие текущее время ( now(), random() ).
• Функции, которые читают или изменяют данные в таблицах.

53.

STABLE
Функции, объявленные как STABLE, гарантируют одинаковый результат при каждом вызове в рамках
одного SQL-выражения, если параметры остаются неизменными. Такие функции не должны изменять
состояние базы данных, но могут читать данные из таблиц.
CREATE FUNCTION get_user_name(user_id integer)
RETURNS text
LANGUAGE sql
STABLE
AS $$
SELECT name FROM users WHERE id = user_id;
$$;
Примеры STABLE функций:
• Функции, выполняющие сложные вычисления на основе параметров.
• Функции, возвращающие значения из таблиц без изменения данных.

54.

IMMUTABLE
Функции, объявленные как IMMUTABLE, всегда возвращают один и тот же результат при одинаковых
параметрах, независимо от состояния базы данных или внешних факторов. Такие функции не зависят от
данных в таблицах и не изменяют их. PostgreSQL может кешировать результаты вызовов этих функций и
использовать их в индексах.
CREATE FUNCTION add_num(a integer, b integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS $$
SELECT a + b;
$$;
Примеры IMMUTABLE функций:
• Математические функции, такие как abs(), sqrt().
• Функции для форматирования строк или чисел.

55.

Что же выбрать?

56.

Что же выбрать?
• Если функция изменяет данные или зависит от времени или состояния базы
данных, она должна быть VOLATILE (можно не писать, стоит по умолчанию).
• Если функция только читает данные и возвращает предсказуемые результаты
в рамках одного SQL-выражения, но результаты могут изменяться между
разными выражениями, она должна быть STABLE.
• Если функция всегда возвращает одинаковый результат для одинаковых
параметров и не зависит от внешних факторов, она должна быть
IMMUTABLE.

English Русский Rules