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

1. Виконаємо функцію REPLACE, щоб видалити всі входження символу «а». Довжина отриманого рядка є довжина вихідного рядка після

видалення з неї всіх символів «а».
SQL>		-------------------
SQL> --		Вправа №17
SQL> -------------------
SQL> SELECT отдел_имя, LENGTH(отдел_имя),
2		LENGTH(REPLACE(отдел_имя, 'а'))
3	FROM отдел;

ОТДЕЛ_ИМЯ LENGTH(ОТДЕЛ_ИМЯ) LENGTH(REPLACE(ОТДЕЛ_ИМЯ,'А'))

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

учет	4	4
исследования	12	11
реализация	10	8
операции	8	7
2. Віднімемо цю довжину від довжини вихідного рядка.
SQL> ------------------
SQL> --	Вправа №18
SQL> ------------------

SQL> SELECT отдел_имя, LENGTH(отдел_имя),

2LENGTH(отдел_имя) - LENGTH(REPLACE(отдел_имя, 'а'))

3"Буква 'а'"

4FROM отдел;

ОТДЕЛ_ИМЯ LENGTH(ОТДЕЛ_ИМЯ) Буква 'а'

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

учет	4	0
исследования	12	1
реализация	10	2
операции	8	1

3. Одержимо кількість входжень символу «а» у вихідний рядок.

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

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

Функція ROUND

ROUND({ стовпецьзначення }, n );

Округляє числове значення до n-го знака після коми. Якщо nне задано або дорівнює 0, то округлення відбувається до цілого числа. Якщо

n негативне, то – до відповідного розряду.

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

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

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

SQL> SELECT ROUND(45.923, 1), ROUND(45.923), 2 ROUND(45.323, 1), ROUND(42.323, -1),

3 ROUND(оклад/33, 2)

4 FROM служ

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

ROUND(45.923,1) ROUND(45.923) ROUND(45.323,1) ROUND(42.323,-1) ROUND(ОКЛАД/33,2)

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

45,9	46	45,3	40	74,24
45,9	46	45,3	40	151,52
45,9	46	45,3	40	24,24

Функція TRUNC

TRUNC({ стовпецьзначення }, n );

Відкидає молодші розряди числа до n-го знака. Якщо n не задано або дорівнює 0, то відкидається вся дробова частина числа. Якщо n негативне, то n молодших розрядів числа обнуляються.

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

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

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

SQL> SELECT TRUNC(45.923, 1), TRUNC(45.923), 2 TRUNC(45.323, 1), TRUNC(42.323, -1),

3 TRUNC(оклад/33, 2)

4 FROM служ

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

TRUNC(45.923,1) TRUNC(45.923) TRUNC(45.323,1) TRUNC(42.323,-1) TRUNC(ОКЛАД/33,2)

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

45,9	45	45,3	40	74,24
45,9	45	45,3	40	151,51
45,9	45	45,3	40	24,24

Функція CEIL

CEIL({		стовпець значення });
Знаходить мінімальне ціле, більше або рівне даному числу.
SQL> ------------------
SQL> --		Вправа №21
SQL> ------------------
SQL> SELECT CEIL(оклад), CEIL(99.9), CEIL(101.76), CEIL(-11.1)
2	FROM служ
3	WHERE оклад BETWEEN 3000 AND 5000;

CEIL(ОКЛАД) CEIL(99.9) CEIL(101.76) CEIL(-11.1)

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

3000	100	102	-11
5000	100	102	-11
3000	100	102	-11

Функція FLOOR

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

Знаходить максимальне ціле, менше або рівне даному числу.

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

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

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

SQL> SELECT FLOOR(оклад), FLOOR(99.9), FLOOR(101.76), FLOOR(-11.1)

2 FROM служ

3 WHERE оклад BETWEEN 3000 AND 5000;

FLOOR(ОКЛАД) FLOOR(99.9) FLOOR(101.76) FLOOR(-11.1)

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

3000	99	101	-12
5000	99	101	-12
3000	99	101	-12

Функція POWER
POWER({ стовпець значення },	n );
Зводить число в ступінь n , де	n може бути не ціле або навіть

негативне. Однак, якщо значення, що зводиться в ступінь, негативне, то n повинне бути цілим.

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

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

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

SQL> SELECT оклад, POWER(оклад, 2), POWER(оклад, 3),

POWER(50, 5)
2	FROM служ
3	WHERE отдел_номер = 10;
ОКЛАД POWER(ОКЛАД,2) POWER(ОКЛАД,3) POWER(50,5)
	------------ ------------------------ -------------------------- -------------------
	2450	6002500	1,471E+10	312500000
	5000	25000000	1,250E+11	312500000
	800	640000	512000000	312500000

Функція EXP

EXP( n );

Обчислює значення числа «е» у ступені n , де е = 2,71828183...

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

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

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

SQL> SELECT EXP(3) 2 FROM отдел;

EXP(3)

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

20,085537

20,085537

20,085537

20,085537

Функція SQRT

SQRT({		стовпець значення });
Обчислює квадратний корінь від числа. Якщо число не визначене або
негативне, то результат буде NULL.
SQL> ------------------
SQL> --		Вправа №25
SQL> ------------------
SQL> SELECT оклад, SQRT(оклад), SQRT(40), SQRT(премия)
2	FROM служ
3	WHERE премия > 0;

ОКЛАД SQRT(ОКЛАД) SQRT(40) SQRT(ПРЕМИЯ)

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

1600	40	6,3245553	17,320508
1250	35,355339	6,3245553	22,36068
1250	35,355339	6,3245553	37,416574

Функція SIGN

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

Повертає -1, якщо число негативне; 0, якщо число дорівнює нулю й 1, якщо число позитивне.

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

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

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

SQL> SELECT оклад - премия, SIGN(оклад - премия),

2премия - оклад, SIGN(премия - оклад)

3FROM служ

4WHERE отдел_номер = 30 AND премия IS NOT NULL;

ОКЛАД-ПРЕМИЯ SIGN(ОКЛАД-ПРЕМИЯ) ПРЕМИЯ-ОКЛАД SIGN(ПРЕМИЯ-ОКЛАД)

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

1300	1	-1300	-1
750	1	-750	-1
-150	-1	150	1
1500	1	-1500	-1

Часто функція SIGN використовується для визначення, яке з двох чисел більше. У наступному прикладі обрані всі службовці, в яких зар-плата більша за премію.

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

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

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

SQL> SELECT служ_имя, оклад, премия

2	FROM служ
3	WHERE SIGN(оклад - премия) = 1;
СЛУЖ_ИМЯ		ОКЛАД	ПРЕМИЯ
------------------		---------	--------------
Иванова		1600	300
Черничная		1250	500
Воронина		1500	0

Функція ABS

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

Повертає значення числа за модулем.
SQL> ------------------
SQL> -- Вправа №28
SQL> ------------------
SQL> SELECT оклад, премия, премия - оклад,
2	ABS(премия - оклад), ABS(-35)
3	FROM служ
4	WHERE отдел_номер = 30;
ОКЛАД	ПРЕМИЯ ПРЕМИЯ-ОКЛАД ABS(ПРЕМИЯ-ОКЛАД) ABS(-35)
---------	-------------- ------------------------ --------------------------------- ---------
1600	300	-1300	1300	35
1250	500	-750	750	35
1250	1400	150	150	35
2850				35
1500	0	-1500	1500	35
950				35

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

Функція MOD

MOD( значення1 , значення2 );

Визначає частку від ділення першого числа на друге, тобто перше число за модулем другого.

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

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

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

SQL> SELECT оклад, премия, MOD(оклад, премия), MOD(100, 40)

2FROM служ

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

4ORDER BY премия;

ОКЛАД ПРЕМИЯ MOD(ОКЛАД,ПРЕМИЯ) MOD(100,40)

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

1500 0 1500 20

1600	300	100	20
1250	500	250	20
1250	1400	1250	20
2850			20
950			20

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

До числових функцій також належать:

LOG (	m , n )	– логарифм n за основою m
SIN (	n )	– синус n (у радіанах)
SINH ( n )		– синус гіперболічний n
TAN (	n )	– тангенс n
TANH ( n )		– тангенс гіперболічний	n
COS ( n )		– косинус n (у радіанах)
COS ( n )		– косинус гіперболічний	n

7.3.2.4. Функції над датами

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

Зберігання дат в Oracle

Oracle зберігає дати у внутрішньому числовому форматі, що включає: Століття, Рік, Місяць, День, Години, Хвилини, Секунди.

Формат представляється будь-якою датою за замовчуванням (див. маски форматування нижче). Oracle може зберігати дати в діапазоні від 1 січня 4712 року до н. е. до 4712 року н. е.

Псевдостовпець SYSDATE і таблиця DUAL

SYSDATE – це псевдостовпець, що містить поточну дату й час. Стовпець SYSDATE можна використовувати як і будь-який інший стовпець. Наприклад, можна видати поточну дату шляхом вказівки поля SYSDATE у реченні SELECT. Зручно для такого запиту взяти допоміжну таблицю з ім'ям DUAL. Ця таблиця належить користувачеві з ім'ям SYS (системному адміністраторові) і доступна всім користувачам тільки на читання. Вона містить один стовпець DUMMY і один рядок зі значенням «X». Таблиця DUAL зручна для завдання в запитах, коли ви хочете одержати одиничне

значення, не пов'язане з таблицями БД, наприклад, значення константи, псевдостовпця або арифметичного виразу, що не містить імен стовпців БД.

Щоб одержати поточну дату, виконайте:

SQL> SELECT SYSDATE

2 FROM SYS.DUAL;

SYSDATE

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

22.10.2006

Можна також вибрати стовпець SYSDATE з таблиці «служ», але в цьому випадку ви одержите 14 однакових значень – по одному в кожному рядку цієї таблиці. Саме тому в подібних випадках переважніше використати таблицю DUAL.

Застосування арифметичних операторів в операціях над датами

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

Розглянемо оператори, які допускаються при роботі з датами.

	Таблиця 7.9
	Дії над датами
Оператори	Зміст дії
дата + число	Додає до дати певну кількість днів, повертаючи нову дату
дата - число	Віднімає з дати певну кількість днів, повертаючи нову
	дату
дата - дата	Віднімає з однієї дати іншу, повертаючи число, що
	позначає кількість днів між ними
дата + число / 24	Додає до дати певну кількість годин, повертаючи нову
	дату

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

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

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

SQL> SELECT оформлен, оформлен+7, оформлен-7, SYSDATEоформлен

2 FROM служ

3 WHERE оформлен LIKE '%_02_%';

ОФОРМЛЕН ОФОРМЛЕН+7 ОФОРМЛЕН-7 SYSDATE-ОФОРМЛЕН

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

20.02.1999	27.02.1999	13.02.1999	610,61269
22.02.1999	01.03.1999	15.02.1999	608,61269

Віднімання стовпця «оформлений» (дати зарахування на роботу) з SYSDATE дозволяє визначити, скільки днів знаходиться на роботі кожний із службовців компанії.

Функція MONTHS_BETWEEN

MONTHS_BETWEEN( дата1 , дата2 );

Обчислює кількість місяців між двома датами. Результат може бути позитивним або негативним, залежно від того, яка дата на якому місці стоїть. Якщо дата1більш пізня, ніж дата2 , то результат позитивний. При цьому дробова частина результату відбиває залишок місяця.

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

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

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

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

2MONTHS_BETWEEN('01.06.1995', '05.11.2001')

3FROM служ

4WHERE MONTHS_BETWEEN(SYSDATE, оформлен) > 13;

MONTHS_BETWEEN(SYSDATE,ОФОРМЛЕН) MONTHS_BETWEEN('01.06.1995','05.11.2001')

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

22,181413	-77,12903
20,084639	-77,12903
20	-77,12903
24,826574	-77,12903
17,697542	-77,12903
40,439477	-77,12903
13,471735	-77,12903
15,633026	-77,12903

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