Lab_04_Oracle %28PL_SQL%29 / Додатково_Теорія_03_Вбудовані_функції

7.Вбудовані функції ORACLE.

7.1.Теоретичні відомості

7.1.1. Використання вбудованих функцій Oracle

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

Функції повертають значення, що є результатом їхньої роботи. Тип значення, який повертається, повинен бути доречний у даному контексті. Наприклад, якщо результат функції використовується в якості одного з операндів деякої арифметичної операції, то функція повинна повертати числове значення або результат повинен бути перетворений у числовий тип.

У наступних таблицях (табл. 7.1 і 7.2) «Тип» указує тип значення, що повертається функцією. Де можливо, тип позначається абревіатурою:

'C' означає «char» (символи); 'D' означає «date» (дата);

'N' означає «number» (числа);

'R' означає «rowID» (ідентифікатор рядка); 'W' означає «raw» (вихідні дані);

'*' означає тип даних, що залежить від контексту функції; n, m указують на чисельні вирази;

«символ», «символ1», «символ2», «набір» означають символьні вирази;

«вираз», «рядків», «результ» і «за_замовч» позначають вираз будьякого типу;

d,l позначає вираження типу дати.

Таблиця 7.1

Числові функції

Тип	Функції	Повертає значення
N	ABS(n)	Абсолютне значення n
N	CEIL(n)	Найменше ціле, не менше n
N	FLOOR(n)	Найбільше ціле, не більше n
N	MOD(m,n)	Частка від ділення m на n.
N	POWER(m,n)	m у ступені n. Якщо n не ціле, то воно усікається до
		цілого
N	ROUND(n[,m])	n, округлене до m-того десяткового знака; якщо m
		опущено, то воно приймається рівним 0. m м. б.
		негативним для округлення цифр ліворуч від
		десяткової крапки
N	SIGN(n)	Якщо n < 0, то -1; якщо n = 0, то 0; якщо n > 0, то 1
N	SQRT(n)	Корінь квадратний з n, якщо n < 0, то NULL
N	TRUNC(n[,m])	n, усічене до m 10-их знаків; якщо m опущено, то воно
		приймається рівним 0. m м. б. негативним для
		усікання (обнулення) цифр ліворуч від 10-ої крапки

		Таблиця 7.2
	Символьні функції
Тип	Функція	Повертає значення
N	ASCII(символ)	Код ASCII першого символу символьної змінної
З	CHR(n)	Символ, код ASCII якого дорівнює n
C	INITCAP(символ)	Символьна змінна з першими буквами слів, що
		починаються з великої букви
N	INSTR(символ1,	Позиція m-того включення «символ2» в
	символ2[,n[,m]])	«символ1» при початку пошуку з позиції n.
		Якщо m опущено, за замовчанням
		передбачається 1; аналогічно для n. Позиції
		даються відносно першого знака «символ1»,
		навіть якщо n > 1
N	LENGTH(символ)	Довжина в знаках символьної змінної "символ"
C	LOWER(символ)	«Символ», де всі букви встановлюються
		маленькими
C	LPAD(символ1	Рядок «символ1» ,доданий ліворуч до довжини
	n[,символ2])	n послідовністю знаків з рядка «символ2» з
		повторенням цієї послідовності стільки разів,
		скільки необхідно. Якщо «символ2» опущено,
		використовуються пробіли
C	LTRIM(символ, набір)	Рядок «символ», у якому викреслені початкові
		знаки аж до першого, не зустрічається в
		«набір» знака

З	RPAD(символ1,	Рядок «символ1»,	додана	праворуч символами
	n[,символ2])	«символ2», з повторенням, якщо необхідно;
		якщо «символ2» опущено, використовуються
		пробіли
C	SOUNDEX(символ)	Символьна змінна, що вставляє звуки слів в
		«символ»
C	SUBSTR(символ,	Підрядок, що одержується з «символу», який
	m[,n])	починається з символу m і довжини n символів
		(якщо n опущено, то до кінця «символ»)
C	TRANSLATE	Рядок «символ1», странсльований з безлічі
	(символ1, символ2,	символів «символ2» в безліч «символ3»;
	символ3)	кожний символ з «символ1» переводиться у
		відповідний символ з «символ3»
C	UPPFR(символ)	Рядок із символів верхнього регістра (заголовні
		букви)

		Закінчення таблиці 7.2
C	USERNV(символ)	Повертає інформацію про користувачів, що корисна
		для складання прикладних таблиць спостереження за
		закінченням якого-небудь процеса. Якщо «символ» є
		'ENTRYID', повертається доступний ідентифікатор
		елемента, за яким іде спостереження; якщо «символ»
		є 'SESSIONID', повертається ідентифікатор
		користувача, що перебуває у сеансі роботи з базою;
		якщо «символ» є 'TERMINAL', повертається
		ідентифікатор термінала користувача (у термінах
		операційної системи). LANGUAGE повертає мову, що
		використовується (наприклад, 'ENGLISH')

Групові функції

Групові функції мають значення тільки в запитах і під запитах (табл.

7.3).

DISTINCT приводить до групової функції, що розглядає тільки різні значення виразу; ALL примушує розглядати всі значення. Наприклад, DISTINCT при знаходженні середнього значення з 1,1,1, і 3 є 2, тоді як ALL при цій же операції є 1.5.

За замовчуванням завжди береться ALL.

Таблиця 7.3

Групові функції

Тип		Функція		Повертає значення
N	AVG([DISTINCT|ALL]n		Середнє значення n, з ігноруванням порожніх
	)		значень
N	COUNT({[DISTINCT|A		Кількість рядків, у яких «вираз» не є порожнім
		LL] вираз|*})	(NULL). *' позначає,що COUNT підраховує всі
			обрані рядки
N	MAX([DISTINCT|ALL]		Максимальне значення «вираз»
		вираз)
N	MIN([DISTINCT|ALL]		Мінімальне значення «вираз»
		вираз)
N	STDDEV([DISTINCT|		Середньоквадратичне (стандартне) відхилення
		ALL]n)	від n, з ігноруванням порожніх значень
N	SUM([DISTINCT|ALL]n		Cумма значень n
	)
N	VARIANCE([DISTINCT		Дисперсія n, з ігноруванням порожніх значень
		| ALL]n)
				Таблиця 7.4
		Функції перетворення
Тип		Функція		Результат
				(значення, що повертається)
C		CHARTOROWID(символ)		Перетворення рядка «символ» в
				ідентифікатор рядка
W		HEXTORAW(символ)		Перетворення рядка «символ», що містить
				16-і значення в рядок із двійковими
				значеннями (зручними для включення в
				RAW-стовпець (стовпець із вихідними
				даними))
C		RAWTOHEX(вих_дан)		Перетворення «вих_дан» (вихідні дані) у
				рядок, що містить16-і значення
C		ROWIDTOCHAR(ідент_р		Перетворення ідентифікатора рядка в
		яд)		рядок, довжиною 18 символів
C		TO_CHAR(n[,фмт])		Перетворення n або d у символьний вид за
C		TO_CHAR(d[,фмт])		форматом, що визначений за допомогою
				«фмт» (символьна змінна). Про «фмт»
				дивися «Моделі форматів» у цій главі.
				Якщо «фмт» опущено, n перетворюється в
				рядок такої довжини, що вміщує тільки
				значущі цифри; d перетвориться в рядок
				відповідно до умовчання ORACLE за
				датою: 'DD-MON-YY'.

D	TO_DATE("символ"[,фм	Перетворення дати в символьному вигляді
	т])	в значення дати; «фмт» – це рядок, що
		визначає формат рядка «символ». Про
		«фмт» дивися «Моделі Форматів» у цій
		главі. Якщо «фмт» опущено, «символ»
		повинен мати формат дати за
		замовчанням 'DD-MON-YY'
D	TO_DATE(n,[фмт])	Перетворення числа n у дату. «фмт» має
		бути такий же як і в TO_CHAR,(напр.,'I' або
		'MM')
N	TO_NUMBER(символ)	Перетворення рядка «символ», що
		становить число, у числові значення
		Таблиця 7.5

Функції роботи з датами

Тип	Функції	Результат
D	ADD_MONTHS(d,n)	Дата d плюс n місяців
D	LAST_DAY(d)	Дата останнього дня місяця, що містить d
N	MONTHS_BETWEEN(d,e)	Кількість місяців між d і e. Якщо d більш
		пізня дата, ніж e, результат позитивний, в
		протилежному випадку – негативний
D	NEW_TIME(d,a,b)	Дата й час у часовій зоні b, якщо в часовій
		зоні a вони рівні d. a і b символьні вирази
		наступних значень:
		AST, ADT Час за стандартом Атлантики,
		денний час.
		BST, BDT Час за стандартом Берінга,
		денний час.
		CST, CDT Час за Центральним стандартом,
		денний час.
		EST, EDT Час за Східним стандартом,
		денний час.
		GMT Середній час за Грінвічем.
		HST, HDT Час за стандартом Аляска-
		Гавайї, денний час.
		MST, MDT Час за стандартом Mountain,
		денний час.
		NST Час за стандартом Нью-Фаунленда.
		PST, PDT Тихоокеанський стандарт, денний
		час.

		YST, YDT Час за стандартом Юкону,
		денний час
D	NEXT_DAY(d,символ)	Дата першого з днів тижня позначеного
		«символ», що більше або дорівнює d
N	TRUNC(d)	d з усіченим часом доби

					Таблиця 7.6
			Інші функції
Тип		Функція			Результат
					(значення, що повертається)
*		DECODE(вираз,ряд1,			Якщо «вираз» дорівнює кожному з рядків
		результ1,ряд2,результ2,			«ряд1», повертає відповідний «результ»;
		...[за_замовч] )			якщо ні, то повертає «за_замовч». Якщо
					відповідності не знайдено, а «за_замовч»
					опущено, то повертається NULL (порожнє
					значення). «вираз» м. б. будь-якого типу;
					«ряд» повинен бути такого ж типу.
					Значення, що повертається завжди має
					той же тип даних, що й 1-ий «результ»
*		DUMP (вираз[,radix			Висвітлює значення виразу «вираз» у
		[,поч_позиція[,біти]]] )			внутpішньому фоpматі
*		GREATEST (			Повертає найбільше зі списку значень.
		вираз,вираз,...)			Пеpед порівнянням всі виpази зводяться
					до типу пеpшого виpазу
*		LEAST (вираз,вираз,...)			Повертає найменше зі списку значень.
					Пеpед порівнянням всі виpази зводяться
					до типу пеpшого виpазу
*		NVL (x, вираз)			Якщо х має порожнє значення (null),
					повертає «вираз»; якщо х має непусте
					значення, то повертається х. x і «вираз»
					м. б. будь-якого типу. Тип величини, що
					повертається, такий же, як для х
N		VSIZE (вираз)			Повертає кількість байтів, що займає
					«вираз» у внутрішньому поданні ORACLE
					Таблиця 7.7
		Псевдо-стовпці
Тип		Функція			Результат
					(значення, що повертається)
N		LEVEL		Повертає 1 для коpеня, 2 для дочіpнього
				рівня коpеня і т. д. Використовується в коман-
				ді SELECT ...CONNECT BY
-		NULL		Порожнє значення
R		ROWID		Повертає ідентифікатоp рядка. Використо-

			вуйте ідентифікатоp рядка в командах
			UPDATE...WHERE і SELECT ...FOR UPDATE
			для впевненості в тому, що команда буде
			коpектувати необхідний рядок, а не інші
N		ROWNUM	Повертає число, що вказує послідовність, у
			якій рядок був вибраний з таблиці або безлічі
			зв'язаних таблиць. Пеpший вибраний рядок
			має ROWNUM 1, другий 2, і т. д. ROWNUM в
			основному використовується для обмеження
			числа рядків, що повертаються в pезультаті
			запиту (напр.,...WHERE ROWNUM < 5 ).
			ROWNUM також може бути використаний для
			призначення унікальних значень кожному
			рядку в таблиці (напр., UPDATE table SET
			column = ROWNUM)
D		SYSDATE	Поточна дата й час
N		UID	Число, що ідентифікує даного користувача
			(на кожного користувача пpиходиться один
			ідентифікатоp)
C		USER	Ім'я поточного користувача мови SQL
7.3.2.	Організація самостійної роботи студентів

При вивченні функцій доцільно їх представити у вигляді наступних типів: символьні, числові, дати, конвертування типів даних, обробні всі типи даних на вході, групові.

Функції, що застосовуються для обробки значень даних, мають один або більше аргументів (тобто значень, що отримуються на обробку) і повертають одне значення як результат. Аргументом функції може служити константа, змінна або ім'я стовпця.

Загальний формат функції наступний:

<ім'я_функції> (<аргумент1>, [<аргумент2>, ...])

Функції можуть застосовуватися для: виконання обчислень над даними; корекції значень полів;

маніпулювання групами рядків при видачі результатів; зміни формату дат при їхній видачі; конвертування типів даних.

Одні функції оперують із окремими рядками, інші – з групами ряд-ків. У даній роботі наведені найбільш часто вживані функції. Повний перелік функцій можна знайти в стандартній документації Oracle.

Для функцій, що оперують із окремими рядками таблиці, характер-но наступне:

обробляють кожний рядок, що отримується за запитом; видають результат окремо для кожного рядка;

мають один або більше аргументів, що задаються користувачем; можуть мати вкладеність; можуть задаватися скрізь, де допускаються змінні, імена стовпців або

вирази, наприклад, у операторах SELECT, WHERE, ORDER BY.

У табл. 7.8 наведені умовні скорочення, що використовуються далі при описі форматів функцій.

Таблиця 7.8

	Умовні позначення в описах функцій
Скорочення		Значення
стовпець		будь-яке ім'я стовпця таблиці БД
значення		будь-яке значення (символьне/дата/числове)
N		числове значення
Рядок		символьний рядок
символи		набір заданих символів
Дата		ім'я стовпця або значення типу дата
позиція		число, що визначає порядковий номер символу в рядку

7.3.2.1. Символьні функції

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

Три функції змінюють регістр символів у рядку.

Функція LOWER

LOWER({(стовпецьзначення );

Переводить усі символи рядка на нижній регістр (маленькі букви).

Наприклад, для одержання найменувань відділів і рядка «Освоєння SQL» малими літерами задайте команду:

SQL> ------------------

SQL> -- Вправа №1

SQL> ------------------

SQL> SELECT LOWER(отдел_имя), LOWER('Освоение SQL')

2 FROM отдел;

LOWER(ОТДЕЛ_ LOWER('ОСВОЕ

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

учет	освоение sql
исследования	освоение sql
реализация	освоение sql
операции	освоение sql

Функція UPPER

UPPER({(стовпецьзначення );

Переводить усі символи рядка на верхній регістр (великі букви).

У запропонованому прикладі функція UPPER використовується для переводу списку посад на верхній регістр.

SQL> ------------------

SQL> -- Вправа №2

SQL> ------------------

SQL> SELECT DISTINCT UPPER(должность) 2 FROM служ;

UPPER(ДОЛЖНОС

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

АНАЛИТИК

ГОРНИЧНАЯ

МЕНЕДЖЕР

ПОВАР

ПРЕЗИДЕНТ

РЕАЛИЗАТОР

ТЕЛОХРАНИТЕЛЬ