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

ШОФЕР

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

Функція INITCAP

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

Робить першу букву рядка прописною, інші – рядковими.

Наприклад, для одержання найменувань відділів з великої букви задайте наступну команду:

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

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

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

SQL> SELECT INITCAP(отдел_имя) 2 FROM отдел;

INITCAP(ОТДЕ

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

Учет

Исследования

Реализация

Операции

Функція CONCAT

CONCAT({(рядок1(, (рядок2(});

З'єднує два рядки в один (альтернативою може служити оператор ). SQL> ------------------

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

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

SQL> SELECT CONCAT(служ_им‘я, посада) " Должность"

2FROM служ

3WHERE служ_номер = 7900;

Должность

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

Зайцев повар

Функції LPAD і RPAD доповнюють символьні рядки до заданої довжини.

Функція LPAD

LPAD({(стовпець		значення , n , рядок });
Доповнює значення символьного поля або літерал ліворуч до <n>
символів. Додаткові символи заповнюються <рядком>. Якщо рядок <рядок>
не заданий, то заповнення ведеться пробілами.
SQL>	------------------
SQL> --	Вправа №5
SQL> ------------------
SQL> SELECT LPAD(отдел_имя, 25, '*'),
2	LPAD( отдел_имя, 15),
3	LPAD( отдел_номер, 25, '.')
4 FROM отдел;
LPAD(ОТДЕЛ_ИМЯ,25,'*') LPAD(ОТДЕЛ_ИМЯ,			LPAD(ОТДЕЛ_НОМЕР,25,'.')
-------------------------------------		------------------------------	-------------------------------------
**************************учет		учет .......................	10
***************исследования		исследования .......................	20
******************реализация		реализация .......................	30
********************операции		операции .......................	40

Зазначимо, що в другому стовпці значення полів доповнюються за замовчуванням пробілами і що в третьому стовпці аргументом є числове поле.

Функція RPAD

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

Доповнює значення символьного поля або літерал праворуч до <n>
символів. Додаткові символи заповнюються <рядком>. Якщо рядок <рядок>
не заданий, то заповнення ведеться пробілами.
SQL>	------------------
SQL> --	Вправа №6
SQL> ------------------
SQL> SELECT RPAD(отдел_имя, 25, '*'),
2	RPAD( отдел_имя, 15),
3	RP AD( отдел_номер, 25, '.')
4 FROM отдел;
RPAD(ОТДЕЛ_ИМЯ,25,'*') RPAD(ОТДЕЛ_ИМЯ, RPAD(ОТДЕЛ_НОМЕР,25,'.')
-------------------------------------- --------------------------------- -------------------------------------
учет********************************* учет		10.......................
исследования********************* исследования		20.......................
реализация************************ реализация		30.......................
операции************************** операции		40.......................

Цього разу значення другого стовпця доповнюється за замовчуванням пробілами праворуч.

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

Функція SUBSTR

SUBSTR({ стовпець	значення , позиція , n	});
Вибирає з рядка підрядок, що складається		з	n	символів	і
починається з заданої позиції. Якщо аргумент		n	не	заданий,	то
вибирається весь рядок, починаючи з заданої позиції.

У запропонованому прикладі формуються наступні підрядки: Чотири символи слова «Oracle», починаючи з другого.

Уміст стовпця «відділ_ім'я», починаючи з другого символу. П'ять символів стовпця «відділ_ім'я», починаючи з третього.

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

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

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

SQL> SELECT SUBSTR('Oracle', 2, 4),

2SUBSTR(отдел_имя, 2),

3SUBSTR(отдел_имя, 3, 5)

4FROM отдел;

SUBS SUBSTR(ОТДЕ SUBST

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

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

Зверніть увагу, що всі значення вирівняні вліво. Всі символьні стовпці в таблиці результатів вирівнюються вліво за замовчуванням.

Функція INSTR

INSTR({ стовпецьзначення }, рядок );

Знаходить позицію першого входження рядка в символьний рядок.

INSTR({	стовпець	значення }, рядок , позиція , n );
Знаходить позицію		n -го входження рядка в символьний рядок,
починаючи з заданої позиції в цьому рядку.
SQL> ------------------
SQL> --	Вправа №8
SQL> ------------------

SQL> SELECT отдел_имя, INSTR(отдел_имя, 'а'),

2INSTR(отдел_имя, 'ия'),

3INSTR(отдел_имя, 'с', 1, 2)

4FROM отдел;

ОТДЕЛ_ИМЯ INSTR(ОТДЕЛ_ИМЯ,'А') INSTR(ОТДЕЛ_ИМЯ,'ИЯ') INSTR(ОТДЕЛ_ИМЯ,'С',1,2)

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

учет	0	0	0
исследования	9	11	3
реализация	3	9	0

операции

5

0

0

Функція INSTR часто застосовується для визначення, чи містить рядок певну комбінацію символів. У наведеному вище прикладі значення функції INSTR(відділ_ім'я, 'ія') дорівнює 0 для відділів з іменами «облік» і «операції», оскільки в їхніх іменах не входить комбінація символів 'ія'.

Функції LTRIM і RTRIM видаляють задані символи з рядка відповідно ліворуч і праворуч.

Функція LTRIM

LTRIM({ стовпецьзначення }, 'символы');

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

SQL>		------------------
SQL> --		Вправа №9
SQL> ------------------
SQL> SELECT LTRIM(отдел_имя, 'учет'),
2		LTRIM( отдел_имя, 'исс'),
3		LTRIM( отдел_имя, 'о')
4	FROM отдел;

LTRIM(ОТДЕЛ_ LTRIM(ОТДЕЛ_ LTRIM(ОТДЕЛ_

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

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

Функція RTRIM

RTRIM({ стовпецьзначення }, 'символ');

Видаляє з рядка праворуч усі символи, що стоять у списку, аж до першого символу, що не входить у цей список. Якщо рядок символів не

заданий, то видаляються всі пробіли праворуч.
SQL>		------------------
SQL> --		Вправа №10
SQL> ------------------
SQL> SELECT отдел_имя, RTRIM(отдел_имя, 'ет'),
2		RTRIM( отдел_имя, 'я'),
3		RTRIM( отдел_имя, 'и')
4	FROM отдел;

ОТДЕЛ_ИМЯ	RTRIM(ОТДЕЛ_ RTRIM(ОТДЕЛ_ RTRIM(ОТДЕЛ_
---------------------	---------------------- ---------------------- ----------------------
учет	уч	учет	учет
исследования	исследования	исследовани исследования
реализация	реализация	реализаци	реализация
операции	операции	операции	операц

Функції RTRIM може бути корисна для відкидання праворуч зайвих пробілів у полях, наприклад, якщо при завантаженні даних у таблицю «служ» з файла або зовнішньої бази даних у стовпець «служ_ім'я» значення занеслися з непотрібними пробілами праворуч.

Функція SOUNDEX

SOUNDEX({ рядокзначення });

Повертає рядок, що кодує вимову значення стовпця або сим-вольного літерала. Ця функція дозволяє порівнювати значення сим-вольних англомовних рядків за співзвучністю.

Наприклад:

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

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

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

SQL> SELECT SOUNDEX('fred'), SOUNDEX('ford'), SOUNDEX('free')

2 FROM відділ;

SOUN SOUN SOUN

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

F630 F630 F600

F630 F630 F600

F630 F630 F600

F630 F630 F600

Функція LENGTH

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

Визначає довжину рядка (або числа) у символах.

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

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

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

SQL> SELECT LENGTH('Курс SQL'), LENGTH(отдел_номер),

LENGTH(отдел_имя)

2 FROM отдел;

LENGTH('КУРСSQL') LENGTH(ОТДЕЛ_НОМЕР) LENGTH(ОТДЕЛ_ИМЯ)

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

8	2	4
8	2	12
8	2	10
8	2	8

Функції INSTR і LENGTH повертають числове значення.

Функції TRANSLATE і REPLACE служать для заміни в рядках одних символів на ін.

Функція TRANSLATE

TRANSLATE({ стовпецьзначення }, рядок1 , рядок2 );

Заміняє символи рядка1 на символи рядка2 . У рядках може бути більше одного символу. При цьому всі входження першого символу

рядка1		заміняються на	перший	символ рядка2 ,	другий	символ
рядка1	– на другий символ рядка2 і т. д. Якщо				рядок2	коротше
рядка1	,	то всі символи	рядка1 ,	яким немає пари,	будуть	вилучені,
тобто замінені на ніщо.
SQL>		------------------
SQL>		-- Вправа №13
SQL>		------------------

SQL> SELECT служ_имя, TRANSLATE(служ_имя, 'о', '.'),

2должность, TRANSLATE(должность, 'ез', 'ив')

3FROM служ

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

СЛУЖ_ИМЯ TRANSLATE ДОЛЖНОСТЬ TRANSLATE(ДОЛ

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

Шевченко	Шевченк.	менеджер	миниджир
Король	К.р.ль	президент	привидинт
Болотова	Б.л.т.ва	горничная	горничная

Функція REPLACE

REPLACE({		стовпець	значення },		рядок1 , рядок2 );
Заміняє	всі	входження	рядка1	на	рядок2 . Якщо	рядок2	не
заданий, то	всі	входження	рядка1	віддаляються. Довжини		рядка1	і

рядка2 можуть бути довільними. Якщо не задані ні рядок1 , ні рядок2 , то система видасть помилку.

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

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

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

SQL> SELECT должность, REPLACE(должность, 'реализатор') "Замена1",

2служ_имя, REPLACE(служ_имя, 'о', '..') "Замена2"

3FROM служ

4WHERE служ_номер IN (7788, 7844, 7499, 7782);

ДОЛЖНОСТЬ Замена1 СЛУЖ_ИМЯ Замена2

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

менеджер	менеджер	Шевченко	Шевченк..
реализатор		Иванова	Иван..ва
реализатор		Воронина	В..р..нина
аналитик	аналитик	Мюллер	Мюллер

Функція REPLACE має більші можливості, ніж TRANSLATE. Функція TRANSLATE здійснює заміну символу на символ. REPLACE дозволяє заміняти один рядок на інший або видаляти символьні рядки (видаляються всі входження <рядка1>, якщо не заданий <рядок2>). Оскільки довжини рядків не регламентовані, то за допомогою функції REPLACE можна замінити й символ на символ, наприклад,

REPLACE(..., 'с', 'р').

Рядок, отриманий у результаті виконання функції TRANSLATE, дорівнює або коротше вихідного по довжині; результат виконання функції REPLACE може перевищувати вихідний рядок.

7.3.2.2. Вкладені функції

Функції над окремими рядками мають властивість вкладеності, тобто можна обчислювати значення однієї функції від результату виконання іншої. Кількість рівнів вкладеності практично не обмежено – до 117 рівнів. Порядок виконання функцій – від внутрішніх до зовнішніх.

Припустимо, необхідно визначити, скільки разів той або інший символ зустрічається в слові. Можна виконати функцію LENGTH від результату виконання функції TRANSLATE, як це показано в прикладі нижче. У даному прикладі визначається, скільки разів зустрічається буква «а» в назвах відділів.

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

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

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

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

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

10)

3 "Буква 'а'"

4 FROM отдел;

ОТДЕЛ_ИМЯ LENGTH(ОТДЕЛ_ИМЯ)		Буква 'а'
--------------------- ---------------------------------		------------
учет	4 0
исследования	12	1
реализация	10	2
операции	8 1

Нижче наведені кроки, які необхідно виконати для досягнення результату:

1.Використовуємо функцію LENGTH для визначення довжини найменування відділу.

2.Задаємо функцію TRANSLATE, щоб виключити всі букви «а» з назви відділу.

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

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

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

SQL> SELECT TRANSLATE(отдел_имя, 'ыа', 'ы') "Без 'а'" 2 FROM отдел;

Без 'а'

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

учет

исследовния

релизция

оперции

3.Зазначимо, що символ «ы» заміняється на «ы», а символ «а» видаляється, тому що йому немає відповідності. Символ «ы» служить як заповнювач.

4.Тепер віднімемо довжину рядка, отриману після виконання функції TRANSLATE, з довжини вихідного рядка.

LENGTH(TRANSLATE(отдел_имя, 'ыа', 'ы'))

5.У результаті одержуємо кількість входжень символу «а» в найменування відділу.

Альтернативою наведеному методу може служити використання функції REPLACE: