Файл: Лекция 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,16227773,16227773,16227773,16227773,16227773,16227773,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 PresidentNGAO ladoris Vp, OperationsNAGAYAMA midori Vp, SalesQUICK-TO-SEE mark Vp, FinanceROPEBURN audry Vp, AdministrationURGUHART molly Warehouse Manager…25 rows selected.Пример использования CONCAT: Для каждого служащего вывести его фамилию и имя через пробел в одном столбце с названием ‘employee’.SELECT CONCAT(CONCAT(first_name,’ ‘), last_name) employeeFROM 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 NumberFROM import.test1WHERE 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$DECLAREval ALIAS FOR $1;val1 integer;val2 text;BEGIN val1 :=1;IF val1 = val THENval2 := 'Он работает'; ELSE val2 := 'Не работает';END IF;RETURN val2;END;$BODY$LANGUAGE 'plpgsql' VOLATILEЗдесь мы уже используем объявление переменных, условие и возвращение значения.Вызываем функцию.SELECT *, "work".test_if(flag) AS StatusFROM import.test1Получаем результат:

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

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

---