Файл: Лекция 11. Функции языка sql. Создание функции в pl pgsql. Триггеры. Функции.docx

Лекция № 11.

Функции языка SQL. Создание функции в PL / pgSQL. Триггеры.

Функции

Функция аналогична оператору в том, что она манипулирует элементами данных и возвращает результат. Функции отличаются от операторов форматом, в котором они задаются со своими аргументами. Этот формат позволяет функциям оперировать на нуле, одном, двух или большем количестве аргументов:

function(argument, argument, ...)

Функции могут использоваться для выполнения расчетов с данными, преобразования типов данных, изменения форматов вывода дат и т.д. Функции SQL бывают двух основных типов:

• 
  однострочные (или скалярные) функции
  
• 
  групповые (или агрегатные) функции
  

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

Однострочные функции

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

• 
  числовые функции,
  
• 
  символьные функции,
  
• 
  функции для работы с датами,
  
• 
  функции преобразования.
  

Числовые функции

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

Функция	Описание
ABS(n)	Возвращает абсолютную величину n.
MOD(m, n)	Возвращает остаток от деления m на n. Если n=0, возвращает m.
POWER(m, n)	Возвращает m в степени n. Основание m и степень n могут быть любыми числами, но если m отрицательно, то n должно быть целым.
ROUND(m[, n])	Возвращает m, округленное до n позиций после десятичной точки; если n опущено, то до целого, если n отрицательно, округляется целая часть числа. n должно быть целым.
SQRT (n)	Возвращает квадратный корень из n. n должно быть неотрицательным.
TRUNC(m[, n])	Возвращает m, усеченное до n цифр после десятичной точки. Если n опущено, усечение выполняется до целого. n может быть отрицательным, что приводит к усечению (обнулению) n цифр слева от десятичной точки.

---

Примеры использования числовых функций

В примерах используется фиктивная таблица СУБД Oracle DUAL. Эта таблица принадлежит пользователю SYS и доступна всем пользователям. Она содержит один столбец с именем DUMMY и одну строку со значением X. Таблица DUAL полезна в случае, если требуется получить результат действия какой-либо однострочной функции, вызванной с аргументами, не связанными с данными БД.

Пример: Найти квадратный корень из 10.

SELECT SQRT(10)

FROM s_dept;

Результат:

SQRT(10)

---------

3,1622777

3,1622777

3,1622777

3,1622777

3,1622777

3,1622777

3,1622777

…

12 rows selected.

-- Мы получили 12 значений, так как в таблице s_dept 12 строк. Но так как -- вычисляемое значение никак не связано с данными таблицами, то все эти 12 -- значений одинаковы. Чтобы избежать подобных излишеств, и предназначена -- таблица DUAL:

SELECT SQRT(10)

FROM dual;

Результат:

SQRT(10)

---------

3,1622777

Пример использования ABS: Найти модуль чисел –13, 0 и 13.

SELECT ABS(-13), ABS(0), ABS(13)
FROM dual;

Результат:

ABS(-13) ABS(0) ABS(13)

-------- -------- --------

13 0 13

Пример использования MOD: Найти остаток от деления 18763298 на 813.

SELECT MOD(18763298, 813)
FROM dual;

Результат:

MOD(18763298,813)

-----------------

71

Пример использования ROUND: Округление числа 268,57 с различными значениями аргумента n.

SELECT ROUND(268.57, 5), ROUND(268.57, 1), ROUND(268.57),
ROUND(268.57, -2), ROUND(268.57, -3)
FROM dual;

Результат:

ROUND(268.57,5) ROUND(268.57,1) ROUND(268.57) ROUND(268.57,-2) ROUND(268.57,-3)

-------------- -------------- ------------ --------------- ---------------

268,57 268,6 269 300 0

Символьные функции

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

Функция	Описание
ASCII(char)	Возвращает ASCII-код первого символа строки char в наборе символов базы данных.
CHR(n)	Возвращает символ, имеющий двоичный код n в наборе символов базы данных.
CONCAT(char1, char2)	Возвращает строку char1, сцепленную со строкой char2. Эта функция эквивалентна оператору конкатенации (||).
INITCAP(char)	Возвращает строку char, каждое слово которой начинается с прописной буквы, а остальные буквы строчные.
INSTR(char1, char2[, n[,m]])	Просматривает строку char1, начиная с ее n-го символа, отыскивает m-е вхождение подстроки char2, и возвращает позицию первого символа в этом вхождении относительно начала строки char1. Если n отрицательно, то Oracle отсчитывает и просматривает в обратном направлении, начиная с конца строки char1. Значение m должно быть положительным. По умолчанию оба значения m и n равны 1, т.е. Oracle отыскивает первое вхождение подстроки char2, начиная поиск с первого символа char1. Возвращаемое значение всегда вычислено относительно начала строки char1, независимо от значения n, и выражено в символах. Если поиск безуспешен (т.е. если подстрока char2 не встречается m раз после n-го символа строки char1), то возвращается 0.
LENGTH(char)	Возвращает длину строки char в символах. Если char имеет тип данных CHAR, то эта длина включает все хвостовые пробелы. Если строка char пуста, то возвращается NULL.
LOWER(char)/ UPPER(char)	Возвращает строку char, все буквы которой строчные/прописные.
LPAD(char1, n[, char2])/ RPAD(char1, n[, char2])	Возвращает строку char, дополненную слева/справа до n символов цепочками символов char2. Если char2 опущено, подразумевается пробел. Если char1 длиннее, чем n, то возвращаются первые n символов строки char1.
SUBSTR(char, m[, n])	Возвращает часть строки char, начиная с символа с номером m и длиной n символов. Если m положительно, Oracle отсчитывает символы от начала строки char, если m отрицательно - от конца строки char. Значение m не может быть нулевым. Если n опущено, возвращаемая часть строки продолжается до конца char. Значение n не может быть меньше 1.

---

Примеры использования символьных функций

Пример использования LOWER, UPPER, INITCAP:

SELECT UPPER(last_name), LOWER(first_name), INITCAP(title)

FROM s_emp;

Результат:

UPPER(LAST_NAME) LOWER(FIRST_NAME) INITCAP(TITLE)

---------------- -------------- -------------------------

VELASQUEZ carmen President

NGAO ladoris Vp, Operations

NAGAYAMA midori Vp, Sales

QUICK-TO-SEE mark Vp, Finance

ROPEBURN audry Vp, Administration

URGUHART molly Warehouse Manager

…

25 rows selected.

Пример использования CONCAT: Для каждого служащего вывести его фамилию и имя через пробел в одном столбце с названием ‘employee’.

SELECT CONCAT(CONCAT(first_name,’ ‘), last_name) employee

FROM s_emp;

Результат:

EMPLOYEE

----------------------

PL/pgSQL – язык программирования, который используется в СУБД PostgreSQL для написания функций, триггеров и других управляющих конструкций.

Сразу скажу, что писать функции без базовых знаний SQL практически невозможно, поэтому советую ознакомиться с основами SQL, например, Вам помогут вот эти статьи:

• 
  SQL код – самоучитель по SQL для начинающих программистов
  
• 
  Основы языка запросов SQL
  
• 
  Строковые функции SQL
  
• 
  Как добавить новый столбец в таблицу на SQL?
  

Теперь давайте поговорим о том, для чего нам нужны эти самые функции. Как и в любом языке программирования есть «встроенные» функции, а также есть возможность писать свои «пользовательские» функции и PL/pgSQL не исключение, если быть точнее, то SQL не исключение, так как PL/pgSQL является расширением языка SQL.

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

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

---

В функциях в СУБД PostgreSQL можно использовать все операторы SQL такие как: INSERT, DELETE, UPDATE и другие. И Вы теперь представьте, что можно прописать в функции!? а потом просто  вызвать ее одной строкой.

Создание функций на PL/pgSQL

Теперь давайте будем учиться писать эти самые функции. И для начала ниже представлен общий синтаксис написания функции в PL/pgSQL:

CREATE OR REPLACE FUNCTION название функции (типы передаваемых данных через запятую)

RETURNS тип возвращаемого значения AS

$BODY$

DECLARE

Объявление переменных

BEGIN

Тело программы

RETURN возвращаемый результат;

END;

$BODY$

LANGUAGE язык, на котором написана функция (например, SQL или plpgsql) VOLATILE

Тип возвращаемого значения может быть разный, например, numeric, integer, text или, например void это тип, который не возвращает значение, а функция просто отрабатывает (например, добавляет новые строки).

Сейчас давайте напишем простенькую функцию, пока без использования plpgsql. Допустим, у нас есть две таблицы с одинаковой строуктурой: test и test1:

Test1

id	name	flag	znach
1	mike	1	10
2	peter	0	15

и Test2

id	number	name2	tarif
1	111	mike1	2
2	222	peter1	3

Например, мы часто используем в запросах объединение этих двух таблиц, для того чтобы подтянуть к таблице test1, столбец number из таблицы test2. Для упрощения всего этого давайте напишем простенькую функцию:

CREATE OR REPLACE FUNCTION "work".test_number(numeric)

RETURNS numeric AS

$BODY$

SELECT "number"

FROM "work"."test2"

WHERE id = $1;

$BODY$

LANGUAGE 'sql' VOLATILE
Где,

• 
  test_number — название функции;
  
• 
  numeric — тип входящего параметра (он у нас один, но их может быть много);
  
• 
  SELECT «number» FROM «work».»test2″ WHERE id = $1 – запрос, т.е. тело функции. $1 передаваемый параметр;
  
• 
  LANGUAGE ‘sql’ VOLATILE – язык, на котором написана функция.
  

Теперь давайте вызовем нашу функцию в запросе, это проще простого:

SELECT *, "work".test_number(id) AS Number

FROM import.test1
где, «work».test_number(id) – это и есть вызов нашей функции. Мы передали ему параметр id, т.е. целую колонку, результат будет таким:

---

Id	name	flag	znach	number
1	mike	1	10	111
2	peter	0	15	222

Если нужно кого-то конкретного подтянуть, можно написать вот так, т.е. передать параметр 1 (id=1).

SELECT *, "work".test_number(1) AS Number

FROM import.test1

WHERE id = 1
Таким образом, у нас выведется одна строка:

Id	name	flag	znach	number
1	mike	1	10	111

Здесь мы с Вами обошлись без всякого рода объединений, согласитесь так намного проще, запрос короче и выполняется быстрей.

Теперь попробуем написать функцию уже с использованием языка PL/pgSQL. Допустим, мы хотим знать, кто в нашей таблице соответствует тому или иному признаку. Например, кто из наших сотрудников работает. Определять будем по признаку flag (1 – работает; 0 – не работает). При этом нам не удобно вспоминать чему соответствует этот признак, например 1, поэтому давайте напишем функцию, чтобы видеть надпись, напротив того или иного сотрудника. Пишем функцию:

CREATE OR REPLACE FUNCTION "work".test_if(numeric)

RETURNS text AS

$BODY$

DECLARE

val ALIAS FOR $1;

val1 integer;

val2 text;

BEGIN

val1 :=1;

IF val1 = val THEN

val2 := 'Он работает';

ELSE

val2 := 'Не работает';

END IF;

RETURN val2;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE
Здесь мы уже используем объявление переменных, условие и возвращение значения.

Вызываем функцию.

SELECT *, "work".test_if(flag) AS Status

FROM import.test1
Получаем результат:

id	name	flag	znach	status
1	mike	1	10	Он работает
2	peter	0	15	Не работает

Как Вы уже, наверное, поняли, что возможности функций практически не ограничены, на языке PL/pgSQL можно написать очень серьезные функции, с трудными расчетами и так далее. 

---