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

Функція ADD_MONTHS

ADD_MONTHS( дата , n );

Додає до дати nмісяців, де nповинне бути цілим і може бути негативним.

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

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

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

SQL> SELECT оформлен, ADD_MONTHS(оформлен, 3),

ADD_MONTHS(оформлен, -3)

2 FROM служ

3 WHERE отдел_номер = 20;

ОФОРМЛЕН ADD_MONTHS ADD_MONTHS

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

17.12.1998	17.03.1999	17.09.1998
02.04.2001	02.07.2001	02.01.2001
09.12.2000	09.03.2001	09.09.2000
12.01.2001	12.04.2001	12.10.2000
03.07.1999	03.10.1999	03.04.1999

Функція NEXT_DAY

NEXT_DAY( дата1 , рядок );

Повертає дату, що йде після заданої й відповідає дню тижня, обумовленому рядком , що може бути номером (день тижня по порядку) або назвою дня тижня. Варто звернути увагу, що першим по порядку днем тижня стоїть неділя. При установці на ядрі системи підтримки російської мови першим днем тижня буде понеділок.

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

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

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

SQL> SELECT оформлен, NEXT_DAY(оформлен, 'пятница'), NEXT_DAY(оформлен, 5)

2FROM служ

3WHERE отдел_номер = 10;

ОФОРМЛЕН NEXT_DAY(О NEXT_DAY(О

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

09.06.1997	13.06.1997	13.06.1997
17.11.1999	19.11.1999	19.11.1999
23.08.2000	25.08.2000	25.08.2000

Функція LAST_DAY

LAST_DAY( дата1 , рядок );

Повертає останній день місяця, що відповідає даній даті.

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

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

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

SQL> SELECT SYSDATE, LAST_DAY(SYSDATE), оформлен,

2	LAST_DAY(оформлен), LAST_DAY('15.02.2001')
3	FROM служ
4	WHERE отдел_номер = 20;

SYSDATE LAST_DAY(S ОФОРМЛЕН LAST_DAY(О LAST_DAY('

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

22.10.2000	31.10.2000	17.12.1998	31.12.1998	28.02.2001
22.10.2000	31.10.2000	02.04.2001	30.04.2001	28.02.2001
22.10.2000	31.10.2000	09.12.2000	31.12.2000	28.02.2001
22.10.2000	31.10.2000	12.01.2001	31.01.2001	28.02.2001
22.10.2000	31.10.2000	03.07.1999	31.07.1999	28.02.2001

Функція ROUND

ROUND( дата );

Округляє датудо півночі. Це зручно для порівняння двох дат з різним значенням годин/хвилин/секунд.

ROUND( дата , 'MONTH');

Округляє дату до найближчого 1-го числа.

ROUND( дата , 'YEAR');

Повертає найближчу до дати 1 січня.

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

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

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

SQL> SELECT SYSDATE, ROUND(SYSDATE, 'MONTH'), ROUND(SYSDATE, 'YEAR')

2 FROM SYS.DUAL;

SYSDATE ROUND(SYSD ROUND(SYSD

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

22.10.2000 01.11.2000 01.01.2001

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

Функція TRUNC

TRUNC( дата , рядок );

Працює аналогічно функції ROUND, але обрізає значення, а не округляє його. Якщо в якості рядка стоїть MONTH, то повертається перше число даного місяця; якщо YEAR – то 1 січня даного року й т. д.

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

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

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

SQL> SELECT SYSDATE, TRUNC(SYSDATE, 'MONTH'), TRUNC(SYSDATE, 'YEAR')

2 FROM SYS.DUAL;

SYSDATE TRUNC(SYSD TRUNC(SYSD

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

22.10.2000 01.10.2000 01.01.2000

Функція TRUNC зручна, коли, наприклад, потрібно видалити з дати інформацію про години/хвилини/секунди. Компонента часу дня фактично видаляється з дати за замовчуванням.

7.3.2.5. Функції конвертування

У мову SQL входить набір функцій для конвертування типів даних. Ці функції перетворюють значення одного типу в значення іншого типу. Перелік функцій конвертування включає:

TO_CHAR TO_NUMBER TO_DATE

Функція TO_CHAR

Функція TO_CHAR часто використовується для конвертування дат у символьні рядки різного виду.

TO_CHAR( дата [, формат подання ]);

Специфікує формат рядка, у якому повинна бути представлена дата. Наприклад, щоб перетворити поточну дату, представлену за

замовчуванням у форматі «DD-MM-YYYY» (при настроюванні на російську мову й український регіон) в інший вид, наберіть команду, подібну наступній:

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

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

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

SQL> SELECT TO_CHAR(SYSDATE, 'DAY, DD MONTH YYYY') 2 FROM SYS.DUAL;

TO_CHAR(SYSDATE,'DAY,DDMONTHY

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

ВОСКРЕСЕНЬЕ, 21 ОКТЯБРЬ 2007

Необхідно зазначити, що:

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

DAY і MONTH у рядку результатів автоматично додаються пробілами праворуч до максимального розміру в домені припустимих значень.

Щоб виключити пробіли праворуч, додайте до відповідної маски префікс FM (fill mode):

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

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

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

SQL> SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH YYYY') 2 FROM SYS.DUAL;

TO_CHAR(SYSDATE,'FMDAY,DDMONT

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

ВОСКРЕСЕНЬЕ, 21 ОКТЯБРЬ 2007

Префікс FM може також використовуватися для прибирання нулів ліворуч у форматі DD, тобто 05 заміняється на 5.

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

Функція TO_CHAR може бути використана для одержання часу дня й видачі його в заданому форматі.

Наприклад, щоб видати поточний час, наберіть команду:

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

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

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

SQL> SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') 2 FROM SYS.DUAL;

TO_CHAR(

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

05:47:30

Функція TO_CHAR застосовується й для конвертування числових значень у символьні рядки, у наступному форматі:

TO_CHAR( дата [, формат подання ]);

Наприклад:

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

SQL> --і Вправа №40

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

SQL> SELECT TO_CHAR(оклад, '$9,999.99')

2FROM служ

3WHERE отдел_номер = 30;

TO_CHAR(ОК

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

$1,600.00 $1,250.00 $1,250.00 $2,850.00 $1,500.00 $950.00

6 строк выбрано.

Завдання маски форматування не обов'язкове. Якщо вона опущена, то перетворення в символьний формат відбувається за замовчуванням. Для дат – це формат DD-MM-YYYY. Для чисел формат за замовчуванням – це просте число у вигляді символьного рядка.

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

Формати дат

	Таблиця 7.10
	Елементи форматування масок дат
Маска	Значення
SCC або CC	Століття; префікс 'S' задає видачу дат до н. е. зі знаком '-'
YYYY або SYYYY	Рік; префікс 'S' задає видачу дат до н. е. зі знаком '-'
YYY або YY або Y	Останні 3, 2 або 1 розряд року
Y,YYY	Рік з комою після третього розряду
SYEAR або YEAR	Рік, що задає словами мовою національної підтримки.
	Префікс 'S' задає '-' для дат до н. е.
BC або AD	BC/AD індикатор ери (може бути заданий кожний із двох, що
	не впливає на результат)
B.C. або A.D.	BC/AD індикатор із крапками
Q	Квартал року (число від 1 до 4)
MM	Місяць по порядку від 1 до 12
MONTH	Назва місяця, що доповнюється пробілами до максимального
	розміру з домена значень
MON	Трьохсимвольна абревіатура назви місяця
WW або W	Тиждень року або місяця
DDD або DD або D	Порядковий день року, місяця або тижня
DAY	Назва дня тижня, що доповнюється до максимального
	розміру з домена значень
DY	Двохсимвольна абревіатура назви дня тижня
J	День по юліанському календарю, кількість днів від 31 грудня
	4713 р. до н.е.
AM або PM	Індикатор часу до або після полудня (може бути задана кожна
	з двох масок)
A.M. або P.M.	Індикатор часу до або після полудня з крапками
HH або HH12	Годинники від 1 до 12
HH24	Годинники від 0 до 23
MI	Хвилини
SS	Секунди
SSSSS	Кількість секунд, що пройшли починаючи з півночі
/ . , і т.д.	Знаки пунктуації, відтворені в результаті
“...”	Символьні рядки, взяті в лапки, також можуть бути включені в
	результат

Мова, на якій можна вводити маски «DAY», «MON» і «Month», залежить від поточних установок NLS – системи підтримки національних мов у середовищі Oracle8. Поточну установку мови для дат можна довідатися за допомогою наступної команди:

SQL> ------------------
SQL> -- Вправа №41
SQL> ------------------
SQL> SELECT *
2	FROM nls_session_parameters
3	WHERE parameter = 'NLS_DATE_LANGUAGE';
PARAMETER		VALUE
---------------------------		-----------
NLS_DATE_LANGUAGE		RUSSIAN

Префікс FM

Префікс FM (Fill Mode) може бути доданий до масок форматування ліворуч. У сполученні з масками MONTH і DAY прибирає пробіли праворуч, якщо ви хочете одержати рядки змінної довжини. З форматом «ddth» використовується для зменшення значущих нулів ліворуч. В інших масках форматування не застосовується. Друге входження «FM» повертає постановку пробілів.

Суфікси TH і SP

У наступній таблиці (табл. 7.11) наведені суфікси, які можуть додаватися до масок форматування праворуч.

		Таблиця 7.11
		Суфікси TH і SP
Суфікс		Значення
TH		Порядковий числівник (наприклад, «DDTH») дасть «4TH», тобто 4
		заміняється на 4-і
SP		Спелінг числа, тобто видача числа словами (тільки англійською
		мовою)
Наприклад:
SQL>	------------------
SQL> --	Вправа №42

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

SQL> SELECT TO_CHAR(SYSDATE, 'DDDSP') 2 FROM dual;

TO_CHAR(SYSDATE,'DDDSP')

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

TWO HUNDRED NINETY-SIX

Регістр, на якому задані маски форматування, впливає на результат, наприклад (табл. 7.12).

Таблиця 7.12

Вплив регістра в масках форматування

Приклад маски	Як видається значення
DAY	ПОНЕДІЛОК
Day	Понеділок
MONTH	ЛИПЕНЬ
Month	Липень
ddth	14th
ddTh	14Th
DDth	14TH

Елемент маски форматування RR

Елемент маски форматування RR можна розглядати як альтернативу елементу YY, що видає дві останні цифри року. Цей елемент також трактується як дві останні цифри року, але керуючись іншими правилами.

Якщо маска року має вигляд «YY», то при спробі виведення значення року в чотиризначній нотації, дати на стику сторіч трактуються неправильно. У цьому і є суть проблеми 2000 року. Частково вирішити цю проблему можна за допомогою маски «RR», але при розробці нових програм рекомендується використовувати маску «YYYY» для повного усунення проблемної ситуації.

У табл.7.13 показано, як у старих додатках можна вирішити проблему 2000 року за допомогою заміни маски «YY» на «RR», що обчислює сторіччя за наступними правилами:

Таблиця 7.13

Правила форматування маски «RR»

	Якщо дві останні цифри поточного року лежать у діапазоні
		00-49	50-99
Якщо дві останні
		Результат –	Результат –
цифри року	00-49	рік поточного	рік попереднього
перебувають в		сторіччя	сторіччя
діапазоні
		Результат –	Результат –
	50-99	рік попереднього	рік поточного
		сторіччя	сторіччя

Таблиця 7.14

Приклади дат у форматі RR

	Поточний рік	Дата у форматі	Рік, що інтерпретується
		(DD.MM.RR)
	1994	27.11.95	1995
	1994	27.11.17	2017
	2003	27.11.95	1995
	2003	27.11.17	2017

Числові формати

Нижче наведена табл. 7.15 припустимих масок форматування для чисел.

Таблиця 7.15

Елементи форматування числових масок

	Маска	Призначення	Приклад	Як
			маски	друкується
		Позиція числового розряду (кількість
	9	дев'яток визначає ширину числа, а	999999	1234
		розряди, яких бракує, при цьому не
		друкуються)
	0	Друкування відсутніх розрядів нулями	099999	001234

Закінчення таблиці 7.15