У цій частині мова йтиме про функції роботи з текстовою інформацією, які можуть застосовуватися в запитах і програмному коді на мові PL / SQL.

Функція CONCAT (strl, str2)

Ця функція виконує конкатенацію рядків strl і str2. Якщо один з аргументів дорівнює NULL, то він сприймається як порожній рядок. Якщо обидва аргументи рівні NULL, то функція повертає NULL. приклад:

SELECT CONCAT ( "У попа \\" була собака ") x1,
CONCATCTest ", NULL) х2,
CONCAT (NULL, "Test") х3,
CONCAT (NULL, NULL) x4
FROM dual

У попа була собака

Для конкатенації рядків Oracle підтримує спеціальний оператор конкатенації «||», який працює аналогічно функції CONCAT, наприклад:

SELECT CONCAT ( "У попа \\" була собака ") x1," У попа "||" була собака "х2
FROM dual

Не слід плутати оператор конкатенації «||», еквівалентний виклику функції CONCAT, і оператор «+», застосовуваний в арифметичних операціях. У Oracle це різні оператори, але за рахунок автоматичного приведення типів можливі важковловимий помилки, наприклад:

SELECT "5" + "3" x1
FROM dual

В даному випадку повертається числове значення 8, а не текстовий рядок «53». Це пов'язано з тим, що, виявивши арифметичну операцію «+», Oracle автоматично намагається навести аргументи до типу NUMBER.

Функція LOWER (str)

Функція LOWER перетворює всі символи рядка str в рядкові. приклад:

SELECT LOWER ( "TeXt DATA") X
FROM dual

функціяUPPER (str)

Функція UPPER перетворює всі символи рядка str в прописні. приклад:

SELECT UPPER ( "TeXt DATA") X
FROM dual

Функція INITCAP (str)

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

SELECT INITCAPCІваноВ петро Сидорович ") X
FROM dual

функціїLTRIM (str [, set])іRTRIM (str [, set])

Функція LTRIM видаляє всі символи з початку рядка до першого символу, якого немає в наборі символів set. За замовчуванням set складається з одного пробілу і може не вказуватися. Функція RTRIM аналогічна LTRIM, але видаляє символи, починаючи від кінця рядка. Розглянемо кілька прикладів:

SELECT LTRIM ( "TeXt DATA") X1,
LTRIM ( "_ # TeXt DATA", "#_") X2,
LTRIM ( "1234567890 TeXt DATA", "1234567890") X3
FROM dual

Функція REPLACE (str, search_str, [, replace_str])

Функція REPLACE здійснює пошук зразка search_str в рядку str і кожне знайдене входження замінює на replace_str. За замовчуванням replace_str дорівнює порожній рядку, тому виклик функції REPLACE з двома аргументами призводить до видалення всіх знайдених входжень. Пошук підрядка ведеться з урахуванням регістру. приклад:

SELECT REPLACE ( "У попа була собака", "собака", "кішка") x1,
REPLACE ( "У попа була зла собака", "зла") х2,
REPLACE ( "У попа була собака", "Собака", "Кішка") х3
FROM dual

У попа була кішка	У попа була собака	У попа була собака

Функція TRANSLATE (str, from_mask, to_mask)

Функція TRANSLATE аналізує рядок str і замінює в ній всі символи, що зустрічаються в рядку from_mask, на відповідні символи з to_mask. Для коректної роботи функції рядка from_mask і to_mask повинні мати однакову довжину або рядок from_mask повинна бути довше, ніж to_mask. Якщо from_mask довше, ніж to_mask, і в процесі обробки рядка str виявляться символи, що відповідають одному із символів from_mask, і при цьому їм не знайдеться відповідності в to_mask, то такі символи будуть видалені з рядка str. Якщо передати from_mask або to_mask, рівне NULL, то функція поверне значення NULL. Порівняння проводиться з урахуванням регістру.

SELECT TRANSLATE ( "Test 12345", "е2 \\" Е! ") X1,
TRANSLATE ( "Test 12345", "e234", "E") x2
FROM dual

Ця функція зручна для вирішення ряду практичних завдань, пов'язаних з перекодуванням символів або з пошуком заборонених символів. Наприклад, необхідно проаналізувати пароль і з'ясувати, чи містить він хоча б одну цифру. Реалізація даної перевірки за допомогою TRANSLATE має вигляд:

IF TRANSLATE (PassWd, "0123456789", "*") \u003d PassWd THEN
ADD_ERR0R ( "Помилка - Пароль повинен містити хоча б одну цифру!");
RETURN 1;
END IF;

Інший приклад: йде підготовка числа до його перетворенню в NUMBER. Необхідно замінити роздільники десяткових знаків «,» і «.» на «.» і видалити пробіли. Реалізація даної операції за допомогою TRANSLATE має вигляд:

SELECT TRANSLATE ( "123 455,23", ".,", "..") X1,
TRANSLATE ( "- 123 455.23", ".,", "..") Х2
FROM dual

Функція SUBSTR (str, m [, n])

Функція SUBSTR повертає фрагмент рядка str, починаючи з символу m довжиною n символів. Довжину можна не вказувати - в цьому випадку повертається рядок від символу m і до кінця рядка str. Нумерація символів йде з 1. Якщо вказати m \u003d 0, то копіювання все одно почнеться з першого символу. Завдання від'ємного значення m призводить до того, що символи відраховуються від кінця рядка, а не від початку. Завдання значень m, що перевищують за абсолютним значенням довжину рядка, призводить до того, що функція повертає NULL.

SELECT SUBSTR ( "У попа була собака", 13) x1,
SUBSTR ( "У попа була собака", -6) х2,
SUBSTR ( "Еto тестовий текст", 5, 8) х3,
SUBSTR ( "У попа була собака", 150) х4
FROM dual

		текстовий

Функція INSTR (str, search_str [, n [, m]])

Функція INSTR повертає позицію першого символу m-ro фрагмента рядка str, що збігається з рядком search_str. Порівняння ведеться з n-го символу рядка str, при порівнянні враховується регістр. За замовчуванням n \u003d m \u003d 1, тобто пошук ведеться від початку рядка і повертається позиція першого знайденого фрагмента. У разі неуспішного пошуку функція повертає 0.

SELECT INSTR ( "y попа була собака", "собака") x1,
INSTR ( "y попа була собака", "кішка") х2,
INSTR ( "Це текст для демонстрації пошуку тексту", "текст", 1, 2) х3,
INSTR ( '11111000000001 "," 1 ", 7) х4
FROM dual

З даної функцій, так само як і з усіма іншими в Oracle, часто допускаються типові помилки, пов'язані з обробкою значення NULL. Якщо str \u003d NULL, то функція поверне NULL, а не нуль! Це необхідно враховувати при побудові різних умов. Наприклад, даний фрагмент програми на PL / SQL якраз не враховує цю особливість:

IF INSTR (TXT_VAR,"*") = 0 THEN
...
ENDIF;

В даному випадку правильно було б написати так:

IF NVL (INSTR (TXT_VAR, "*"), 0) \u003d 0 THEN
...
END IF;

Функції LENGTH (str) і LENGTHB (str)

Функція LENGTH (str) повертає довжину рядка str в символах. Для порожнього рядка і значення NULL функція повертає NULL, тому спільно з цією функцією рекомендується використовувати NVL.

SELECT LENGTH ( "У попа була собака") x1,
LENGTH ( "") х2,
LENGTH (NULL) х3,
NVL (LENGTH ( ""), 0) х4
FROM dual

Функція LENGTHB аналогічна функції LENGTH, але повертає довжину рядка в байтах.

Функція ASCII (str)

Повертає ASCII-код першого символу рядка str в разі застосування кодування ASCII і значення першого байта багатобайтові символу при використанні кодування на основі багатобайтові символів. приклад:

SELECT ASCII ( "Test") x1 FROM dual

Функція CHR (n)

Повертає символ за його кодом.

SELECT CHR (64) x1
FROM dual

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

1) Функція визначення довжини рядка LENGTH (рядок), повертає кількість символів в рядку, включаючи кінцеві прогалини.

SELECT LENGTH ( 'string') FROM DUAL поверне значення 7.

2) Функції перетворення регістрів символів UPPER (рядок), LOWER (рядок), INITCAP (рядок). Для перетворення символів до верхнього регістру використовується функція UPPER ().

SELECT UPPER ( 'string') FROM DUAL поверне STRING.

Якщо необхідно перетворити символи рядка до нижнього регістра використовується функція LOWER ().

SELECT LOWER ( 'STrinG') FROM DUAL поверне string.

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

SELECT INITCAP ( 'string1 string2') FROM DUAL поверне рядок String1 String2.

3) Функції для обрізання початкових і кінцевих пробілів LTRIM (рядок), RTRIM (рядок), TRIM (рядок). Відповідно перша функція обрізає всі початкові пробіли рядки, друга - все кінцеві, а третя все початкові і кінцеві.

SELECT LTRIM ( 'str1') FROM DUAL поверне рядок str1,
SELECT RTRIM ( 'str2') FROM DUAL поверне рядок str2,
SELECT TRIM ( 'str3') FROM DUAL поверне рядок str3.

4) Функція заміни частини рядка іншим рядком REPLACE (ісходная_строка, заменяемая_подстрока, заменяющая_подстрока). Для більшої ясності розглянемо приклад, в деякому текстовому полі таблиці зберігається число. Причому символ-роздільник між цілою і дробовою частиною в деяких полях «.», А нам для подальшої обробки даних потрібно, щоб він у всіх полях повинен бути «,». Для цього скористаємося функцією REPLACE наступним чином. REPLACE (field1, '.', ',') І всі символи «.» в поле field будуть замінені на символ «,».

SELECT REPLACE ( 'My_string', '_', '@') FROM DUAL поверне рядок [Email protected]

5) Функції перетворення даних до інших типів даних. TO_CHAR (число) перетворює число в текст. TO_NUMBER (рядок) перетворює текст в число. TO_DATE (рядок, формат_дати) перетворить рядок в дату певного формату.

SELECT TO_CHAR (123) FROM DUAL поверне рядок 123,
SELECT TO_NUMBER ( '12345') FROM DUAL поверне число 12345,
SELECT TO_DATE ('01 .01.2010 ',' dd.mon.yyyy ') FROM DUAL поверне дату 01.JAN.2010.

6) Функція визначення входження підрядка в рядок INSTR (ісходная_строка, подстрока, номер_сімвола). Даная функція дає змогу визначати номер символу в заданій стрічці з якого починається шукана підрядок (якщо така є). Інакше повертається 0. Наприклад нам потрібно визначити всі посади в таблиці Table1, в найменуванні яких зустрічається підрядок «менеджер». Для цього цілком підійде наступний оператор

SELECT * FROM TABLE1 WHERE INSTR (POST, 'менеджер', 1)\u003e 0.

Тобто оператор SELECT виведе тільки ті записи з таблиці TABLE1 де шукана підрядок «менеджер» буде знайдена. Причому пошук буде здійснюватися з першого символу. Якщо пошук потрібно здійснювати з іншої позиції, то номер символу для початку пошуку вказується в третьому параметрі.

SELECT INSTR ( 'Small string', 'string', 1) FROM DUAL поверне значення 7,
SELECT INSTR ( 'Small string', 'String', 1) FROM DUAL поверне значення 0.

7) Функція виділення в заданій стрічці подстроки SUBSTR (ісходная_строка, номер_начального_сімвола, кількість_символів). Розглянемо такий приклад, в призначеній для користувача таблиці зберігається адреса у вигляді найменування населеного пункту, назва вулиці, номер будинку. Причому ми точно знаємо, що для найменування населеного пункту відводиться строго 20 символів (якщо найменуванні населеного пункту менше ніж 20 символів, то інша частина заповнюється пробілами), для найменування вулиці 30 символів, для номера будинку 3 символу. Далі нам необхідно перенести всі адреси з нашої таблиці в іншу і при цьому все 3 компоненти адреси повинні бути в різних полях. Для виділення компонент адреси застосуємо функцію SUBSTR ().

SELECT SUBSTR (TABLE_1.ADDRESS, 1, 20) CITY, SUBSTR (TABLE_1.ADDRESS, 21, 30) STREET, SUBSTR (TABLE_1.ADDRESS, 52, 3) TOWN FROM TABLE_1

Звичайно для перенесення даних необхідно скористатися оператором INSERT, але для розуміння роботи функції SUBSTR цілком підійде розглянутий приклад.
SELECT SUBSTR ( 'My_string', 4, 3) FROM DUAL поверне рядок str.

Розглянуті вище функції можна використовувати у вхідних параметрах. Так якщо нам потрібно виділити всі символи, після якогось певного, то в функцію SUBSTR можна передати номер шуканого символу з функції INSTR. Наприклад якщо потрібно перенести всі символи з поля таблиці, які розташовані після «,» то можна використовувати таку конструкцію
SELECT SUBSTR (My_string, INSTR (My_string, ',', 1), LENGTH (My_string) - INSTR (My_string, ',', 1) +1) FROM DUAL.
Для визначення початкового символу ми викликаємо функцію INSTR (), яка поверне номер символу першого входження підрядка «,». Далі ми визначаємо кількість символів до кінця рядка як різницю довжини рядка і номера першого входження підрядка.

8) Для визначення коду символу використовується функція ASCII (рядок), яка повертає код 1 символу рядка. наприклад

SELECT ASCII (W) FROM DUAL поверне значення 87.

9) Зворотна функція перетворення коду символу в символ CHR (число).

SELECT CHR (87) FROM DUAL поверне символ W.

Функції для роботи з числами в Oracle.

У СУБД Oracle є ряд функцій для роботи з числами. До них відносяться функції зведення числа в ступінь POWER (), округлення ROUND () і т. Д.

1) Функція ABS (число) повертає абсолютне значення аргументу.
SELECT ABS (-3) FROM DUAL поверне значення 3.

2) Функція CEIL (число) повертає найменше ціле, більше або рівне переданому параметру.
SELECT CEIL (4.5) FROM DUAL поверне значення 5.

3) Функція FLOOR (число) повертає найбільше ціле, менше або рівне переданому параметру.
SELECT FLOOR (3.8) FROM DUAL поверне значення 3.

4) Функція MOD (чісло_1, чісло_2) повертає залишок від ділення першого параметра на другий.
SELECT MOD (5, 3) FROM DUAL поверне значення 2. Примітка. Якщо другий параметр дорівнює 0, то функція повертає перший параметр.

5) Функція округлення ROUND (чісло_1, чісло_2). Округлює перший переданий параметр до кількості розрядів, переданого в другому параметрі. Якщо другий параметр не вказано, то він приймається рівним 0, тобто округлення проводиться до цілого значення. приклади
SELECT ROUND (101.34) FROM DUAL поверне значення 101,
SELECT ROUND (100.1268, 2) FROM DUAL поверне значення 100.13
SELECT ROUND (1234000.3254, -2) FROM DUAL поверне значення 1234000,
SELECT ROUND (-100.122, 2) FROM DUAL поверне значення -100.12.

6) Функція усічення значення TRUNC (чісло_1, чісло_2). Повертає усеченное значення першого параметра до кількості десяткових розрядів, зазначеного в другому параметрі. приклади
SELECT TRUNC (150.58) FROM DUAL поверне значення 150
SELECT TRUNC (235.4587, 2) FROM DUAL поверне значення 235.45
SELECT TRUNC (101.23, -1) FROM DUAL поверне значення 100

7) У СУБД Oracle є ряд тригонометричних функцій SIN (число), COS (число), TAN (число) і зворотні їм ACOS (число), ASIN (число), ATAN (число). Вони повертають значення відповідної назвою тригонометричної функції. Для прямих функції параметром є значення кута в радіанах, а для зворотних - значення функції. приклади
SELECT COS (0.5) FROM DUAL поверне значення 0.877582561890373
SELECT SIN (0.5) FROM DUAL поверне значення 0.479425538604203
SELECT TAN (0.5) FROM DUAL поверне значення 0.546302489843791
SELECT ACOS (0.5) FROM DUAL поверне значення 1.0471975511966
SELECT ASIN (0.5) FROM DUAL поверне значення 0.523598775598299
SELECT ATAN (0.5) FROM DUAL поверне значення 0.463647609000806

8) Гіперболічні функції. SINH (число),
COSH (число), TANH (число). SINH () повертає гіперболічний синус переданого параметра, COSH () повертає гіперболічний косинус переданого параметра, TANH () повертає гіперболічний тангенс переданого параметра. приклади
SELECT COSH (0.5) FROM DUAL поверне значення 1.12762596520638
SELECT SINH (0.5) FROM DUAL поверне значення 0.521095305493747 SELECT TANH (0.5) FROM DUAL поверне значення 0.46211715726001

9) Функція піднесення до степеня POWER (чісло_1, чісло_2). приклади
SELECT POWER (10, 2) FROM DUAL поверне значення 100
SELECT POWER (100, -2) FROM DUAL поверне значення 0.0001

10) Логарифмічні функції. LN (число) повертає натуральний логарифм переданого параметра, LOG (чісло_1, чісло_2) повертає логарифм другого переданого параметра по підставі, переданому першому параметрі. Причому перший параметр повинен бути більше нуля і не дорівнює 1. Приклади
SELECT LN (5) FROM DUAL поверне значення 1.6094379124341
SELECT LOG (10, 3) FROM DUAL поверне значення 0.477121254719662

11) Функція витягання квадратного кореня SQRT (число). приклад
SELECT SQRT (4) FROM DUAL поверне значення 2.

12) Функція зведення числа е в ступінь EXP (число). приклад
SELECT EXP (2) FROM DUAL поверне значення 7.38905609893065.

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

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

1) ADD_MONTHS (дата, колічество_месяцев) повертає дату, віддалену від дати, переданої в першому параметрі на кількість місяців, зазначеному в другому параметрі. приклади
SELECT ADD_MONTHS ('01 -JAN-2010 ', 2) FROM DUAL поверне дату '01 .03.2010'
SELECT ADD_MONTHS ('01 -JAN-2010 ', -3) FROM DUAL поверне дату '01 .10.2009'
SELECT ADD_MONTHS ('30 -JAN-2010 ', 1) FROM DUAL поверне дату '28 .02.2010'

2) Для визначення поточної дати і часу застосовується функція SYSDATE. Область застосування даної функції набагато ширше ніж може здатися на перший погляд. В першу чергу це контроль за введенням даних в БД. У багатьох таблицях виділяється окреме поля для збереження дати останнього внесення змін. Також дуже зручно контролювати якісь вхідні параметри для звітів, особливо якщо вони не повинні бути більше ніж поточна дата. Крім дати дана функція повертає ще і час з точністю до секунд. приклад
SELECT SYSDATE FROM DUAL поверне дату '22 .05.2010 14:51:20 '

3) Якщо необхідно визначити останній день місяця, то для цього цілком підійде функції LAST_DAY (дата). Її можна використовувати для визначення кількості днів, що залишилися в місяці.
SELECT LAST_DAY (SYSDATE) - SYSDATE FROM DUAL.
В результаті виконання даного оператора буде виведено кількість днів від поточної дати до кінця місяця. приклад
SELECT LAST_DAY ('15 -FEB-2010 ') FROM DUAL поверне дату '28 .02.2010'.

4) Функція для визначення кількості місяців між датами MONTHS_BETWEEN (дата_1, дата_2). приклади
SELECT MONTHS_BETWEEN ('01 -JUL-2009 ', '01 -JAN-2010') FROM DUAL поверне значення -6
SELECT MONTHS_BETWEEN ('01 -JUL-2009 ', '10 -JAN-2010') FROM DUAL поверне значення -6.29032258064516.
Примітка. Якщо дні місяців збігаються, то функція повертає ціле число, в іншому випадку результат буде дробовим, причому кількість днів у місяці буде прийнято 31.

5) Функція NEXT_DAY (дата, день_неделі) дозволяє визначити наступну дату від дати, переданої в першому параметрі, яка відповідає дню тижня, переданому в другому параметрі. приклад
SELECT NEXT_DAY ('01 -JUL-2009 ',' mon ') FROM DUAL поверне дату '06 .07.2009', тобто наступного понеділка після 1 липня 2009 настав 6 числа.

6) Округлення дати ROUND (дата, формат). Другий параметр не обов'язковий, якщо його не вказувати, то він приймається за 'DD', тобто округлення буде вироблено до найближчого дня. приклади
SELECT ROUND (SYSDATE) FROM DUAL поверне дату '23 .05.2010 '
SELECT ROUND (SYSDATE, MONTH) FROM DUAL поверне дату '01 .06.2010 ', округляється до найближчого першого дня місяця.

7) Усікання дати. Функція TRUNC (дата, формат). Також як і розглянута вище може не мати другого параметра. В такому випадку усічення буде проводитися до найближчого дня. приклади
SELECT TRUNC (SYSDATE) FROM DUAL поверне дату '22 .05.2010 '
SELECT TRUNC (SYSDATE, 'WW') FROM DUAL поверне дату '01 .05.2010 '
SELECT TRUNC (SYSDATE, 'Day') FROM DUAL поверне дату '16 .05.2010 '.

Функції перетворення даних в Oracle

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

1) TO_CHAR (дані, формат). На перший погляд синтаксис досить простий, але за рахунок другого параметра можна дуже точно описати в який формат перетворити дані. Отже в рядок можна перетворити як дату, так і числове значення. Розглянемо варіант перетворення дати до рядка. Значення найпоширеніших форматів наведені в таблиці, більш повна інформація міститься в технічній документації.

Таблиця значень форматів для перетворення числа в рядок.

SELECT TO_CHAR (SYSDATE, 'D-MONTH-YY') FROM DUAL поверне рядок '7-MAY -10'
SELECT TO_CHAR (SYSDATE, 'DDD-MM-YYYY') FROM DUAL поверне рядок '142-05-2010'
SELECT TO_CHAR (SYSDATE, 'Q-D-MM-YYY') FROM DUAL поверне рядок '2-7-05-010'
SELECT TO_CHAR (1050, '9.99EEEE) FROM DUAL поверне рядок' 1.050E + 03 '
SELECT TO_CHAR (1400, '9999V999') FROM DUAL поверне рядок '1400000'
SELECT TO_CHAR (48, 'RM') FROM DUAL поверне рядок 'XLVIII'

2) Функція перетворення рядка в дату TO_DATE (рядок, формат). Можливі значення форматів вже розглянуті вище, тому наведу кілька прикладів використання даної функції. приклади
SELECT TO_DATE ('01 .01.2010 ',' DD.MM.YYYY ') FROM DUAL поверне дату '01 .01.2010'
SELECT TO_DATE ('01 .JAN.2010 ',' DD.MON.YYYY ') FROM DUAL поверне дату '01 .01.2009'
SELECT TO_DATE ('15 -01-10 ',' DD-MM-YY ') FROM DUAL поверне дату '15 .01.2010'.

3) Функція перетворення рядка в числове значення TO_NUMBER (рядок, формат). Найпоширеніші значення форматів перераховані в таблиці, тому розглянемо застосування даної функції на прикладах. приклади
SELECT TO_NUMBER ( '100') FROM DUAL поверне число 100
SELECT TO_NUMBER ( '0010.01', '9999D99') FROM DUAL поверне число 10.01
SELECT TO_NUMBER ( "500,000", "999G999") FROM DUAL поверне число 500000.

Вступ

oracle регістр строковий арифметичний

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

актуальність:Комп'ютер є не більше ніж пристроєм перетворення інформації. Якщо інформації не дуже багато, то основний час при переробці займає сам алгоритм перетворення. Якщо при цьому доводиться працювати з великими обсягами даних - ефективність обробки починає прямо залежати від ефективності отримання даних, їх фільтрації та ін. З метою спрощення процесу розробки, підвищення продуктивності роботи подібних систем, були створені різні СУБД. Вони мають свій власний формат зберігання даних, свої алгоритми їх пошуку і вилучення, але базова мова запитів у більшості з них один. І ця мова - SQL.

цілі:

1. Отримання теоретичних та практичних знань за функціями Oracle SQL;

2. Застосування теорії на практиці.

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

Функції Oracle SQL

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

function (argument, argument, ...)

Функції можуть використовуватися для виконання розрахунків з даними, перетворення типів даних, зміни форматів виведення дат і т.д. Функції SQL бувають двох основних типів:

1. однорядкові (або скалярні) функції;

2. групові (або агрегатні) функції.

Ці функції відрізняються кількістю рядків, на яких вони оперують. Однорядкова функція повертає єдине значення для кожного рядка таблиці, в той час як групова функція повертає єдине значення для цілої групи рядків.

однорядкові функції . Однорядкові функції можуть з'являтися в пропозиціях SELECT, WHERE і ORDER BY команди SELECT. В якості аргументів вони можуть приймати константи, задані користувачем, значення змінних, імена стовпців таблиці БД або виразу, складені за допомогою операторів і функцій.

список функцій

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

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

· Символьні функції;

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

· Функції перетворення.

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

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

Функції перетворення регістру символів

Функція визначення довжини рядка LENGTH (рядок), повертає кількість символів в рядку, включаючи кінцеві прогалини.

SELECT LENGTH ( string) FROM DUAL поверне значення 7.

Функції перетворення регістрів символів UPPER (рядок), LOWER (рядок), INITCAP (рядок). Для перетворення символів до верхнього регістру використовується функція UPPER ().

SELECT UPPER ( string) FROM DUALповерне STRING.

Якщо необхідно перетворити символи рядка до нижнього регістра використовується функція LOWER ().

SELECT LOWER ( String) FROM DUAL поверне STRING.

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

SELECT INITCAP (string1 string2) FROM DUAL поверне рядок STRING1 STRING2.

Функції для обрізання початкових і кінцевих пробілів LTRIM (рядок), RTRIM (рядок), TRIM (рядок). Відповідно перша функція обрізає всі початкові пробіли рядки, друга - все кінцеві, а третя все початкові і кінцеві.

SELECT LTRIM ( `str1") FROM DUAL поверне рядок str1,

SELECT RTRIM ( `str2") FROM DUAL поверне рядок str2,

SELECT TRIM ( `str3") FROM DUAL поверне рядок str3.

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

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

Для цього скористаємося функцією REPLACE наступним чином: REPLACE (field1, ".", ",") І все символи «.» в поле field будуть замінені на символ «,».

SELECT REPLACE ( `My_string", "_", "@") FROM DUAL поверне рядок [Email protected]

Функції перетворення даних до інших типів даних: TO_CHAR (число) перетворює число в текст, TO_NUMBER (рядок) перетворює текст в число, TO_DATE (рядок, формат дати) перетворить рядок в дату певного формату.

SELECT TO_CHAR (123) FROM DUAL поверне рядок 123,

SELECT TO_NUMBER ( `12345") FROM DUAL поверне число 12345,

Функція визначення входження підрядка в рядок INSTR (вихідна рядок, підрядок, номер символу). Ця функція дозволяє визначати номер символу в заданій стрічці, з якого починається шукана підрядок (якщо така є). Інакше повертається 0. Наприклад, нам потрібно визначити всі посади в таблиці Table1, в найменуванні яких зустрічається підрядок «менеджер». Для цього цілком підійде наступний оператор:

SELECT*FROMTABLE1 WHEREINSTR (POST, `менеджер", 1)\u003e 0.

Тобто оператор SELECT виведе тільки ті записи з таблиці TABLE1, де шукана підрядок «менеджер» буде знайдена. Причому пошук буде здійснюватися з першого символу. Якщо пошук потрібно здійснювати з іншої позиції, то номер символу для початку пошуку вказується в третьому параметрі.

SELECT INSTR ( `Small string", `string", 1) FROM DUAL поверне значення 7,

SELECT INSTR ( `Small string", `String", 1) FROM DUAL поверне значення 0.

Функція виділення в заданій стрічці подстроки SUBSTR (вихідна рядок, номер початкового символу, кількість символів). Розглянемо такий приклад, в призначеній для користувача таблиці зберігається адреса у вигляді найменування населеного пункту, назва вулиці, номер будинку. Причому ми точно знаємо, що для найменування населеного пункту відводиться строго 20 символів (якщо в найменуванні населеного пункту менше ніж 20 символів, то інша частина заповнюється пробілами), для найменування вулиці 30 символів, для номера будинку 3 символу. Далі нам необхідно перенести всі адреси з нашої таблиці в іншу, при цьому всі 3 компоненти адреси повинні бути в різних полях. Для виділення компонент адреси застосуємо функцію SUBSTR ().

SELECTSUBSTR (TABLE_1.ADDRESS, 1,20) CITY, SUBSTR (TABLE_1.ADDRESS, 21,30) STREET, SUBSTR (TABLE_1.ADDRESS, 52, 3) TOWN FROM TABLE_1;

Звичайно, для перенесення даних необхідно скористатися оператором INSERT, але для розуміння роботи функції SUBSTR цілком підійде розглянутий приклад.

SELECT SUBSTR ( `My_string", 4, 3) FROM DUAL поверне рядок str.

Розглянуті вище функції можна використовувати у вхідних параметрах. Так якщо нам потрібно виділити всі символи, після якогось певного, то в функцію SUBSTR можна передати номер шуканого символу з функції INSTR. Наприклад, якщо потрібно перенести всі символи з поля таблиці, які розташовані після «,» то можна використовувати таку конструкцію:

SELECT SUBSTR (My_string, INSTR (My_string, `,", 1), LENGTH (My_string) - INSTR (My_string, `,", 1) +1) FROM DUAL.

Для визначення початкового символу ми викликаємо функцію INSTR (), яка поверне номер символу першого входження підрядка «,». Далі ми визначаємо кількість символів до кінця рядка як різницю довжини рядка і номера першого входження підрядка.

Для визначення коду символу використовується функція ASCII (рядок), яка повертає код 1 символу рядка. наприклад:

SELECT ASCII (W) FROM DUAL поверне значення 87.

Зворотна функція перетворення коду символу в символ CHR (число).

SELECTCHR (87) FROM DUAL поверне символ W.

Функції маніпулювання символьними рядками

Oracle пропонує великий набір функцій для маніпулювання рядковими даними:

CHR (N) - Повертає символ ASCII коду для десяткового коду N;

ASCII (S) - Повертає десятковий ASCII код першого символу рядка;

INSTR (S2. S1.pos [, N] - Повертає позицію рядка S1 в рядку S2 велику або рівну pos. N - число входжень;

LENGHT (S) - Повертає довжину рядка;

LOWER (S) - Замінює всі символи рядка на прописні символи;

INITCAP (S) - Встановлює перший символ кожного слова в рядку на титульний, а решта символи кожного слова - на прописні;

SUBSTR (S, pos, [, len]) - Виділяє в рядку S підрядок довжиною len, починаючи з позиції pos;

UPPER (S) - Перетворює великі літери в рядку на великі літери;

LPAD (S, N [, A]) - Повертає рядок S, доповнену зліва символами A до числа символів N. Символ - наповнювач за замовчуванням - пробіл;

RPAD (S, N [, A]) - Повертає рядок S, доповнену справа символами A до числа символів N. Символ - наповнювач за замовчуванням - пробіл;

LTRIM (S,) - Повертає усічену зліва рядок S. Символи видаляються до тих пір, поки що видаляється символ входить в рядок - шаблон S1 (за замовчуванням - пробіл);

RTRIM (S,) - Повертає усічену справа рядок S. Символи видаляються до тих пір, поки що видаляється символ входить в рядок - шаблон S1 (за замовчуванням - пробіл);

TRANSLATE (S, S1, S2) - Повертає рядок S, в якій всі входження рядка S1 заміщені рядком S2. якщо S1<> S2, то символи, яким немає відповідності, виключаються з результуючого рядка;

REPLACE (S, S1, [, S2]) - Повертає рядок S, для якої все входження рядка S1 заміщені на подстроку S2. Якщо S2 не вказано, то все входження підрядка S1, видаляються з результуючого рядка;

NVL (X, Y) - Якщо Х є NULL, то повертає в Y або рядок, або число, або дату в залежності від вихідного типу Y;

SOUNDEX (S) - Повертає фонетичне уявлення рядки;

Лабораторна робота. Робота із строковими функціями

Строкові функції в запитах Oracle SQL, функції UPPER (), CONCAT (), SUBSTR ().

завдання:

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

Напишіть запит, який повертав би з таблиці hr.employees інформацію про ім'я та прізвища співробітника, а також ідентифікатор співробітника відповідно до поставлених умов. Результат виконання запиту повинен бути таким, як представлено на рис. 3.1-1.

Рішення:

Код відповідного запиту може бути таким:

SELECT first_name AS «Ім'я», last_name As «Прізвище», UPPER (CONCAT (SUBSTR (first_name, 1, 3), SUBSTR (last_name, 1,2))) AS «Ідентифікатор» FROM hr.employees.

Символьна функція отримує в якості параметра одне або кілька символьних значень і повертає символьне і числове значення. Якщо символьна функція повертає символьне значення, воно завжди має тип VARCHAR2 (змінна довжина) - крім функцій UPPER і LOWER. Ці функції перетворять задану рядок до верхнього або нижнього регістру відповідно і повертають значення фіксованої довжини типу CHAR, якщо передані в аргументах рядки мали тип CHAR.

Коротке зведення строкових функцій

Як згадувалося раніше, PL / SQL надає в розпорядження програміста широкий набір потужних, високорівневих строкових функцій для отримання інформації про рядках і модифікації їх вмісту. Наступний список дає уявлення про їхні можливості і синтаксисі. За повною інформацією про конкретні функції звертайтеся до довідника Oracle SQL Reference.

• ASCII (символ) Повертає числовий код (NUMBER) уявлення заданого символу в наборі символів бази даних.
• ASCIISTR (рядок1) Отримує рядок в будь-якому наборі символів і перетворює її в рядок ASCII-символів. Всі символи, відсутні в кодуванні ASCII, представляються у формі \\ XXXX, де XXXX - код символу в Юникоде.

За інформацією про Юникоде і кодах символів звертайтеся на сайт http://unicode.org.

• CHR (код)
  Повертає символ типу VARCHAR2 (довжина 1), що відповідає заданому коду. Функція є зворотною по відношенню до функції ASCII. У неї є різновид, зручна при роботі з даними в національних наборів символів:

CHR (код USING NCHAR_CS)

Повертає символ типу NVARCHAR2 з національного набору символів.

• COMPOSE (рядок1)
  Отримує рядок символів в форматі Юнікод і повертає її в нормалізованому вигляді. Наприклад, ненормалізоване уявлення "a \\ 0303" визначає символ "a" з тильдой cверху (тобто a). Виклик COMPOSE ( "a \\ 0303") повертає значення "\\ 00E3" - шістнадцятковий код символу a в Юникоде.

У Oracle9i Release 1 функція COMPOSE могла бути викликана тільки з SQL-команд; в програмах PL / SQL вона використовуватися не могла. Починаючи з Oracle9i Release2, функція COMPOSE також може використовуватися в виразах PL / SQL.

• CONCAT (рядок1, строка2)
  Приєднує строку2 в кінець строкі1. Аналогічного результату можна досягти за допомогою виразу рядок1 || строка2. оператор || набагато зручніше, тому функція CONCAT використовується відносно рідко.
• CONVERT (рядок1, набор_сімволов)
  Перетворює рядок з набору символів бази даних в заданий набір символів. При виклику також можна задати початковий набір символів:

CONVERT (рядок1, конечний_набор, ісходний_набор)

• DECOMPOSE (рядок1)
  Отримує рядок в Юникоде і повертає рядок, в якій усі складові символи розкладені на елементи. Функція є зворотною по відношенню до COMPOSE. Наприклад, виклик DECOMPOSE ( "a") повертає рядок "a ~" (див. Опис COMPOSE).

Існує два різновиди цієї функції:

• DECOMPOSE (рядок1 CANONICAL)
  Виконує канонічну декомпозицію; отриманий результат може бути відновлений викликом COMPOSE. Використовується за замовчуванням.
• DECOMPOSE (рядок1)
  Декомпозиція виконується в так званому режимі сумісності. Відновлення викликом COMPOSE може виявитися неможливим.

Функція DECOMPOSE, як і COMPOSE, не може безпосередньо викликатися в виразах PL / SQL в Oracle9i Release 1; її необхідно викликати з інструкцій SQL. Починаючи з Oracle9i Release 2, це обмеження було зняте.

• GREATEST (рядок1, строка2, ...)
  Отримує одну або кілька рядків і повертає рядок, яка виявилася б останньою (тобто найбільшою) при сортуванні вхідних рядків по зростанню. Також см. Опис функції LEAST, зворотної по відношенню до GREATEST.
• INITCAP (рядок1)
  Змінює регістр символів строкового аргументу, переводячи першу букву кожного слова рядка в верхній регістр, а інші літери - в нижній. Словом вважається послідовність символів, відокремлена від інших символів пропуск або знак, які не є буквено-цифровим (наприклад, # або _). Наприклад, виклик INITCAP ( "this is lower") дає результат "This Is Lower".
• INSTR (рядок1, строка2)
  Повертає позицію, з якої строка2 входить в строку1; якщо входження не виявлено, функція повертає 0.

Існує кілька різновидів цієї функції:

• INSTR (рядок1, строка2, начальная_позіція)
  Пошук строкі2 в строке1 починається з позиції, заданої останнім параметром. За замовчуванням пошук починається з позиції 1, так що виклик INSTR (string1, string2, 1) відповідає виклику INSTR (string1, string2).
• INSTR (рядок1, строка2, отріцательная_начальная_позіція)
  Зсув початкової позиції задається не від початку, а від кінця строкі1.
• INSTR (рядок1, строка2, начальная_позіція, n)
  Знаходить n-е входження строкі2, починаючи із заданою початковою позиції.
• INSTR (рядок1, строка2, отріцательная_начальная_позіція, n)
  Знаходить n-е входження строкі2, починаючи із заданою початковою позиції від кінця строкі1.

Функція INSTR розглядає рядок як послідовність символів. Її різновиди INSTRB, INSTR2 і INSTR4 розглядають рядок як послідовність байтів, кодових одиниць (code units) або кодових індексів (code points) Юникода відповідно. Різновид INSTRC розглядає рядок як послідовність повних символів Юнікоду. Наприклад, рядок "a \\ 0303", яка представляє собою розкладений еквівалент "\\ 00E3", або a, розглядається як один символ. Навпаки, функція INSTR розглядає "a \\ 0303" як послідовність з двох символів.

• LEAST (рядок1, строка2, ...)
  Отримує одну або кілька рядків і повертає рядок, яка виявилася б першої (тобто найменшою) при сортуванні вхідних рядків по зростанню. Також см. Опис функції GREATEST, зворотної по відношенню до LEAST.
• LENGTH (рядок1)
  Повертає кількість символів в рядку. Різновиди LENGTHB, LENGTH2 і LENGTH4 повертають кількість байтів, кодових одиниць (code units) або кодових індексів (code points) Юникода відповідно. Різновид LENGTHC повертає кількість повних символів Юнікоду, нормалізованих по мірі можливості (тобто з перетворенням "a \\ 0303" в "\\ 00E3").

Функція LENGTH зазвичай не повертає нуль. Згадайте, що Oracle розглядає порожній рядок ( "") як NULL, тому виклик LENGTH ( "") фактично еквівалентний спробі отримати довжину NULL; її результат теж буде дорівнює NULL. Єдиний виняток зустрічається при застосуванні LENGTH до типу CLOB. Тип CLOB може містити 0 байт і при цьому бути відмінним від NULL. У цьому єдиному випадку LENGTH повертає 0.

• LOWER (рядок1)
  Перетворює всі літери заданої рядки в нижній регістр. Функція є зворотною по відношенню до UPPER. Тип значення, що повертається відповідає типу вхідних даних (CHAR, VARCHAR2, CLOB). Також см. NLS_LOWER.
• LPAD (рядок1, ітоговая_дліна)
  Повертає значення строкі1, доповнене зліва пропусками до ітоговой_дліни. У функції існує наступний різновид:
• LPAD (рядок1, ітоговая_дліна, заповнювач)
  Приєднує достатню кількість повних або часткових входжень заповнювач, щоб загальна довжина рядка досягла заданої ітоговой_дліни. Наприклад, виклик LPAD ( "Merry Christmas!", 25, "Ho!") Поверне результат "Ho! Ho! HMerry Christmas!".

• ? LTRIM (рядок1)
  Видаляє пробіли з лівого краю строкі1. Також см. Опису функцій TRIM (стандарт ISO) і RTRIM. У функції існує наступний різновид:
• LTRIM (рядок1, удаляемий_набор)
  Видаляє будь-які символи, що входять в рядок удаляемий_набор, від лівого краю строкі1.
• NCHR (код)
  Повертає символ типу NVARCHAR2 (довжина 1), що відповідає заданому коду. Функція CHR з умовою USING NCHAR_CS реалізує ту ж функціональність, що і NCHR.
• NLS_INITCAP (рядок1)
  Повертає версію строкі1, яка повинна ставитися до типу NVARCHAR2 або NCHAR, в якій перша буква кожного слова перекладається в верхній регістр, а інші літери - в нижній. Функція повертає значення типу VARCHAR2. «Словом» вважається послідовність символів, відокремлена від інших символів пропуск або знак, які не є буквено-цифровим.

Ви можете задати порядок сортування, що впливає на визначення «першої літери»:

• NLS_INITCAP (рядок1, "NLS_SORT \u003d правіло_сортіровкі")
  У цій формі синтаксису правіло_сортіровкі є одним з допустимих назв правил сортування, перерахованих в керівництві Oracle Database Globalization Support Guide, Appendix A, розділ «Linguistic Sorts».

Наступний приклад показує, ніж функція INITCAP відрізняється від NLS_INITCAP:

BEGIN DBMS_OUTPUT.PUT_LINE (INITCAP ( "ijzer")); DBMS_OUTPUT.PUT_LINE (NLS_INITCAP ( "ijzer", "NLS_SORT \u003d XDUTCH")); END; Результат: Ijzer IJzer

У нідерландському мові послідовність символів «? »Розглядається як один символ. Функція NLS_INITCAP розпізнає цю обставину при завданні правила NLS_SORT і правильно перетворює символи слова «? Zer» ( «залізо» по-нідерландською).

• NLS_LOWER (рядок1) і NLS_LOWER (рядок1, "NLS_SORT \u003d правіло_сортіровкі") Повертає строку1, перетворену в нижній регістр за правилами заданого мови. Про те, як NLS_SORT може вплинути на результат перетворення, розказано в описі функції NLS_INITCAP.
• NLS_UPPER (рядок1) і NLS_UPPER (рядок1, "NLS_SORT \u003d правіло_сортіровкі") Повертає строку1, перетворену в верхній регістр за правилами заданого мови. Про те, як NLS_SORT може вплинути на результат перетворення, розказано в описі функції NLS_INITCAP.
• NLSSORT (рядок1) і NLSSORT (рядок1, "NLS_SORT \u003d правіло_сортіровкі") Повертає рядок байтів, яка може використовуватися для сортування строкового значення за правилами заданого мови. Рядок повертається в форматі RAW. Наприклад, порівняння двох рядків по правилам французької мови виконується так: IF NLSSORT (x, "NLS_SORT \u003d XFRENCH")\u003e NLSSORT (y, "NLS_SORT \u003d XFRENCH") THEN ... Якщо другий параметр не вказано, функція використовує порядок сортування за замовчуванням , призначений для сеансу. Повний список правил наведено в керівництві Oracle Database Globalization Support Guide, Appendix A, розділ «Linguistic Sorts».
• REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR За описами цих функцій, призначених для роботи з регулярними виразами, можна вивчити.
• REPLACE (рядок1, іскомая_строка, заміна) Повертає рядок, отриману в результаті заміни всіх входжень іскомой_строкі в строке1 рядком заміна. Функція REPLACE може використовуватися для заміни всіх входжень певної підрядка в одній інструкції.
• REPLACE (рядок1, іскомая_строка)
  Повертає рядок, отриману в результаті видалення всіх входжень іскомой_строкі з строкі1.
• RPAD (рядок1, ітоговая_дліна)
  Повертає значення строкі1, доповнене справа пробілами до ітоговой_дліни. У функції існує наступний різновид:
• RPAD (рядок1, ітоговая_дліна, заповнювач)
  Приєднує достатню кількість повних або часткових входжень заповнювач, щоб загальна довжина рядка досягла заданої ітоговой_дліни. Виклик RPAD ( "Merry Christmas!", 25, "Ho!") Поверне результат "Merry Christmas! Ho! Ho!".

Функція RPAD доповнює рядок праворуч, а парна їй функція LPAD - зліва.

• RTRIM (рядок1)
  Видаляє пробіли з правого краю строкі1. Також см. Опису функцій TRIM (стандарт ISO) і LTRIM. У функції існує наступний різновид:
• RTRIM (рядок1, удаляемий_набор)
  Видаляє будь-які символи, що входять в рядок удаляемий_набор, з правого краю строкі1.
• SOUNDEX (рядок1)
  Повертає рядок з «фонетичним поданням» аргументу.
  приклад:

SOUNDEX ( "smith") -\u003e "S530" SOUNDEX ( "SMYTHE") -\u003e "" S530 "SOUNDEX (" smith smith ") -\u003e" S532 "SOUNDEX (" smith z ") -\u003e" S532 "SOUNDEX (" feuerstein ") -\u003e" F623 "SOUNDEX (" feuerst ") -\u003e" F623 "

При використанні функції SOUNDEX слід пам'ятати кілька правил:

• Значення SOUNDEX завжди починається з першої літери вхідного рядка.
• Значення, що повертається генерується тільки по перших п'яти згодним в рядку.
• Для обчислення цифрового частини SOUNDEX використовуються тільки приголосні. Всі голосні в рядку, крім початкових, ігноруються.
• Функція SOUNDEX ігнорує регістр символів; для букв верхнього та нижнього регістру генеруються однакові значення SOUNDEX.

Функція SOUNDEX корисна для запитів, при яких точне написання значення в базі даних невідомо чи не може бути легко визначено.

Алгоритм SOUNDEX орієнтований на англійську мову; в інших мовах він може працювати погано (або не працювати взагалі).

• SUBSTR (рядок1, начальная_позіція, довжина)
  Повертає підрядок з строкі1, яка починається з начальной_позіціі і має задану довжину. Якщо кількість символів до кінця строкі1 виявиться менше довжини, повертаються всі символи від початкової позиції до кінця рядка. У функції існують такі різновиди:
• SUBSTR (рядок1, начальная_позіція)
  Повертає всі символи від начальной_позіціі до кінця строкі1.
• SUBSTR (рядок1, отріцательная_начальная_позіція, довжина)
  Початкова позиція подстроки відраховується від кінця строкі1.
• SUBSTR (рядок1, отріцательная_начальная_позіція)
  Повертає останні ABS (отріцательная_начальная_позіція) рядки.

Функція SUBSTR розглядає рядок як послідовність символів. Її різновиди SUBSTRB, SUBSTR2 і SUBSTR4 розглядають рядок як послідовність байтів, кодових одиниць (code units) або кодових індексів (code points) Юникода відповідно. Різновид SUBSTRC розглядає рядок як послідовність повних символів Юнікоду. Наприклад, рядок "a \\ 0303", яка представляє собою розкладений еквівалент "\\ 00E3", або a, розглядається як один символ. Навпаки, функція SUBSTR розглядає "a \\ 0303" як послідовність з двох символів.

• TO_CHAR (національние_сімвольние_данние)
  Перетворює дані з національного набору символів в еквівалентну уявлення в наборі символів бази даних. Також см. TO_NCHAR.

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

• TO_MULTI_BYTE (рядок1)
  Перетворює однобайтові символи в їх багатобайтові еквіваленти. У деяких мультибайтних кодуваннях, і перш за все UTF-8, може існувати кілька варіантів представлення одного символу. Скажімо, в UTF-8 уявлення букви "G" може містити від 1 до 4 байт. Для переходу від однобайтового уявлення до мультибайтних використовується функція TO_MULTI_BYTE. Ця функція є зворотною по відношенню до TO_SINGLE_BYTE.
• TO_NCHAR (сімволи_в_наборе_бази_данних)
  Перетворює дані з набору символів бази даних в еквівалентну уявлення в національному наборі символів. Також см. TO_CHAR і TRANSLATE ... USING.

Функція TO_NCHAR також може використовуватися для перетворення дати / часу і чисел в легку для читання форму.

• TO_SINGLE_BYTE (рядок1)
  Перетворює багатобайтові символи в їх однобайтові еквіваленти. Функція є зворотною по відношенню до TO_MULTI_BYTE.
• TRANSLATE (рядок1, іскомий_набор, набор_замени)
  Замінює в строке1 кожне входження символу з іскомого_набора відповідним символом набора_замени. приклад:

TRANSLATE ( "abcd", "ab", "12") -\u003e "12cd"

Якщо іскомий_набор містить більше символів, ніж набор_замени, «зайві» символи, що не мають відповідності в наборе_замени, не включаються до результат. приклад:

TRANSLATE ( "abcdefg", "abcd", "zyx") -\u003e "zyxefg"

Буква «d» видалена, тому що вона присутня в іскомом_наборе, але не має еквівалента в наборе_замени. Функція TRANSLATE замінює окремі символи, а функція REPLACE - цілі рядки.

• TRANSLATE (текст USING CHAR_CS) і TRANSLATE (текст USING NCHAR_CS)
  Перетворює символьні дані в набір символів бази даних (CHAR_CS) або в національний набір символів (NCHAR_CS). Вихідним типом даних буде VARCHAR2 або NVARCHAR2 в залежності від того, чи виконується перетворення до набору символів бази даних або національному набору символів відповідно.

Функція TRANSLATE ... USING входить в число функцій SQL за стандартом ISO. Починаючи з Oracle9i Release 1, можна просто привласнити значення VARCHAR2 змінної типу NVARCHAR2, і навпаки - система неявно виконає потрібне перетворення. Якщо ж ви хочете виконати перетворення явно, використовуйте функції TO_CHAR і TO_NCHAR для перетворення тексту в набір символів бази даних і національний набір символів відповідно. Oracle рекомендує користуватися зазначеними функціями замість TRANSLATE ... USING, тому що вони підтримують більш широкий набір вхідних типів даних.

• TRIM (FROM рядок1)
  Повертає рядок, отриману в результаті видалення з строкі1 всіх початкових і кінцевих пробілів. У функції існують такі різновиди:
• TRIM (LEADING FROM ...)
  Видалення тільки початкових пробілів.
• TRIM (TRAILING FROM ...)
  Видалення тільки кінцевих пробілів.
• TRIM (BOTH FROM ...)
  Видалення як початкових, так і кінцевих пробілів (використовується за умовчанням).
• TRIM (... удаляемий_сімвол FROM рядок1)
  Видалення входжень одного удаляемого_сімвола на вибір програміста.

Функція TRIM була включена в Oracle8i для забезпечення більш повної сумісності зі стандартом ISO SQL. Вона поєднує в собі функціональність LTRIM і RTRIM, але відрізняється від них тим, що TRIM дозволяє задати тільки один видаляється символ, тоді як при використанні LTRIM і RTRIM можна задати набір видаляються символів.

• UNISTR (рядок1)
  Повертає строку1, перетворену в Юнікод; таким чином, функція є зворотною по відношенню до ASCIISTR. Для уявлення недрукованих символів у вхідному рядку можна використовувати запис \\ XXXX, де XXXX - кодовий індекс символу в Юникоде. приклад:

BEGIN DBMS_OUTPUT.PUT_LINE (UNISTR ( "Знак євро \\ 20AC")); END; Знак євро €.

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

• UPPER (рядок1)
  Перетворює всі літери заданої рядки в верхній регістр. Тип значення, що повертається відповідає типу вхідних даних (CHAR, VARCHAR2, CLOB). Функція є зворотною по відношенню до LOWER. Також см. NLS_UPPER.

Функції, які ми обговоримо в цій частині зазвичай використовують вбудовані PL / SQL код, згрупований в пакети і поставляється Oracle. Деякі обробляють чисельні, символьні значення і значення дати, інші перетворюють дані в різні типи даних. Функції можуть використовувати вкладені виклики і деякі функції призначені для роботи зі значенням NULL. Функції умови CASE і DECODE дозволяють відображати різний результат залежно від значень даних, що надає можливість розгалуження в контексті SQL запиту

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

визначення функції

Функція - це програма, яка може приймати (але необов'язково) вхідні параметри, виконувати будь-які операції і повертати значення-літерал. Функція повертає тільки одне значення за виклик.

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

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

Виклики функцій можуть бути вкладеними, наприклад, як F1 (x, y, F2 (a, b), z), де функція F2 приймає два вхідних параметра і повертає третій з чотирьох параметрів для функції F1. Функції можуть працювати з будь-якими типами даних: найбільш часто використовувані це символьні і числові дані, а також дані типу дата. Цими параметрами функції можуть бути стовпцями або виразами.

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

типи функцій

Функції можна глобально розділити на дві категорії: обробні рядок (рядкові функції) і обробні набір рядків (функції угруповання). Це виділення дуже важливо для розуміння контексту де використовуються різні функції.

рядкові функції

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

Наступний запит вибирає два стовпці з таблиці REGIONS і вираз використовує функцію LENGTH і стовпець REGION_NAME

select region_id, region_name, length (region_name) from regions;

Довжина значення стовпця REGION_NAME розраховується для кожної з чотирьох рядків в таблиці REGIONS; функція виконується чотири рази, повертаючи кожен раз значення-літерал.

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

Крім використання функцій в розділі SELECT рядкові функції можна використовувати в розділах WHERE і ORDER BY.

Функції, які працюють з набором даних

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

Використання функцій, що змінюють регістр

Дані в таблицях можуть заповнюватися з різних джерел: програм, крипти і так далі. Не варто покладатися що символьні дані будуть вводитися в заздалегідь визначеному регістрі. Рядкові функції, які змінюють регістр призначені для двох важливих завдань. Їх можна використовувати, по-перше, для зміни регістра даних при збереженні або виведення інформації, або в умовах WHERE для більш гнучкого пошуку. Набагато легше шукати рядок використовуючи фіксований регістр, вместро перевірки всіх комбінацій верхнього і нижнього регістра. Пам'ятайте, що виклик функцій не змінює дані, які зберігаються в таблиці. Вони перетворюють дані результату запиту.

Вхідними параметрами можуть бути символьні літерали, стовпці символьного типу даних, символьні вирази або числа і дати (які неявно будуть перетворені в рядки).

функція LOWER

Функція LOWER замінює усі символи прописні регістра на еквівалентні символи сатиричного регістру. Сінтакіс функції LOWER (string). Розглянемо приклад запиту використовує ці функції

select lower (100 + 100), lower ( 'SQL'), lower (sysdate) from dual

Припустивши що поточна дата 17 грудня 2015 року. Результатом запиту будуть рядки '200', 'sql' та '17 -dec-2015 '. Чисельне вираження і дата неявно перетворюються в рядок перед викликом функції LOWER.

У наступному прикладі функція LOWER використовується для пошуку рядків де букви 'U' і 'R' в будь-якому регістрі йдуть один за одним

select first_name, last_name, lower (last_name) from employees

where lower (last_name) like '% ur%';

Можна написати аналогічний запит без використання функції LOWER. наприклад так

select first_name, last_name from employees

where last_name like '% ur%' or last_name like '% UR%'

or last_name like '% uR%' or last_name like '% Ur%'

Цей запит працює, але він занадто громіздкий, і кількість операторів OR зростає експоненціально зі збільшенням рядки.

функція UPPER

Функція UPPER логічна протилежність функції LOWER і замінює всі рядкові символи на їх прописні еквіваленти. Синтаксис функції - UPPER (string). Розглянемо приклад

select * from countries where upper (country_name) like '% U% S% A%';

Цей запит вибирає рядки з таблци COUNTRIES де COUNTRY_NAME містить букви 'U', 'S', 'A' в будь-якому регістрі в цьому порядку.

функція INITCAP

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

select initcap ( 'init cap or init_cap or init% cap') from dual

Результатом цього запиту буде рядок Init Cap Or Init_Cap Or Init% Cap

Використання функцій роботи з рядками

Функції роботи з рядками одна з найпотужніших можливостей, що надаються Oracle. Вони дуже корисні і зрозумілі практично без детальних пояснень і дуже часто використовуються різними програмістами при обробці даних. Часто використовуються вкладені виклики цих функцій. Оператор конкатенації може використовуватися замість функції CONCAT. Функції LENGTH, INSTR, SUBSTR і REPLACE можуть доповнювати один одного, так само як RPAD, LPAD і TRIM.

функція CONCAT

Функція CONCAT об'єднує два литерала, стовпці або виразу для складання одного великого вираження. У функції CONCAT два вхідних параметра. Синтаксис функції CONCAT (string1, string2) де string1 і string2 можуть бути літералом, стовпцем або виразом результат якого символьний літерал. Наступний приклад показує використання функції CONCAT

select concat ( 'Today is:', SYSDATE) from dual

Другий параметр функції це функція SYSDATE, яка повертає поточний системний час. Значення перетворюється в рядок і до неї приєднується перший параметр. Якщо поточна системна дата 17 Грудня 2015 року, то запит поверне рядок 'Today is: 17-DEC-2015'.

Розглянемо як використовувати функція для об'єднання трьох елементів. Так як функція CONCAT може приймати тільки два вхідних параметра, то можна об'єднати тільки два елементи. У такому випадку можна використовувати виклик функції як параметр другово виклику функції. Тоді запит буде виглядати так

select concat ( 'Outer1', concat ( 'Inner1', 'Inner2')) from dual;

У першій функції два параметри: перший параметр це буквальний 'Outer1', а другий параметра це вкладена функція CONCAT. Друга функція приймає два параметри: буквальний 'Inner1' і буквальний 'Inner2'. Результатом виконання цього запиту буде рядок 'Outer1 Inner1 Inner 2'. Вкладені функції розглянемо трохи пізніше.

функція LENGTH

Функція LENGTH повертає число символів які складають рядок. Прогалини, табуляція і спеціальні символи враховуються функцією LENGTH. У функції один параметра і синтаксис LENGTH (string). Розглянемо запит

select * from countries where length (country_name)\u003e 10;

Функція LENGTH використовується для вибору тих країн у яких довжина назви більше ніж десять символів.

функції RPAD і LPAD

Функції RPAD і LPAD повертають рядок фіксованої довжини і при необхідності доповнюють початкове значення певним набором символів зліва чи справа. Символами використовувані для зміни можуть бути буквальний, значення стовпця, вираз, пробіл (значення за замовчуванням), табуляція і спец символи. Функції LPAD і RPAD приймають три вхідних параметра і синтаксис LPAD ( s, n, p) І RPAD ( s, n, p) де s - значення рядка для обробки, n - кількість символів результату і p - символи для додавання. Якщо використовується LPAD, то символи p додаються зліва до досягнення довжини n. Якщо RPAD - то справа. Зверніть увагу що якщо довжина s більше ніж довжина n - то результатом буде перші n символів значення s. Розглянемо запити на малюнку 10-1

Малюнок 10-1 - Використання функцій RPAD і LPAD

Перший запит не змінює дані і результат не дуже читабельний в порівнянні з результатом другого запиту. RPAD використовується для додавання пробілів там де необхідно для first_name і last_name щоб всі значення були фіксованої довжини в 18 символів, і LPAD використовується для додавання пробілів в початок значення salary до досягнення довжини 6 символів.

функція TRIM

Функція TRIM прибирає символи і початку або закінчення рядка щоб зробити її потенцільно коротше. Функція приймає обов'язковий параметр і необов'язковий. Синтаксис функції TRIM ([ trailing| leading| both] trimstring from string). Параметр вхідний рядок (s) обов'язковий. Наступні пункти перераховують параметри

• TRIM (s) прибираються прогалини на початку в до кінці рядка
• TRIM (trailing trimstring from s) прибирає символи trimgstring в кінці рядка
• TRIM (leading trimstring from s) прибирає символи trimgstring на початку рядка
• TRIM (both trimstring from s) OR TRIM (trimstring from s) прибирають все символи trimstring на початку і в кінці рядка

select trim (both '*' from '**** Hidden ****'),

trim (leading '*' from '**** Hidden ****'),

trim (trailing '*' from '**** Hidden ****') from dual;

Поверне "Hidden", "Hidden ****", і "**** Hidden". Зверніть увагу що, вказавши всього один символ, все символи прибираються якщо вони послідовно повторюються.

функція INSTR

Функція INSTR шукає підрядок в рядку. Повертається число, що позначає позицію звідки n-ве входження починається, починаючи з позиції пошуку, щодо початку рядка. Якщо підрядок не знайдено в рядку - повертається 0.

У функції INSTR два параметра обов'язкових і два параметра необов'язкових. Синтаксис функції INSTR (source string, search string,,). Значення за замовчуванням для search start position \u003d 1 або іншими словами початок рядка source string. Значення за замовчуванням для n occurrence \u003d 1 або перше входження. Розглянемо кілька прикладів

Query 1: select instr ( '1 # 3 # 5 # 7 # 9 #', '#') from dual;

Query 2: select instr ( '1 # 3 # 5 # 7 # 9 #', '#', 5) from dual;

Query 3: select instr ( '1 # 3 # 5 # 7 # 9 #', '#', 3, 4) from dual;

Перший запит шукає перше входження хеш-тега в рядку і повертає значення 2. Другий запит шукає хеш-тег в рядку починаючи з п'ятого символу і знаходить перше входження з 6 символу. Третій запит шукає четверте входження хеш-тега починаючи з третього символу і знаходить його в позиції 10.

функція SUBSTR

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

У функції SUBSTR три параметра, перші два обов'язкові і синтаксис SUBSTR (source string, start position,). Значення за замовчуванням для characters to extract \u003d різниця між довжиною source string і start position. Розглянемо наступні приклади

Query 1: select substr ( '1 # 3 # 5 # 7 # 9 #', 5) from dual;

Query 2: select substr ( '1 # 3 # 5 # 7 # 9 #', 5, 3) from dual;

Query 3: select substr ( '1 # 3 # 5 # 7 # 9 #', -3, 2) from dual;

Запит 1 повертає підрядок починаючи з позиції 5. Так як третій параметр не вказано, кількість символів дорівнює довжині початкового рядка мінус початкова позиція і дорівнюватиме шести. Перший запит поверне подстроку '5 # 7 # 9 #'. Запит два повертає три символи починаючи з п'ятого символу і рядок результат буде '5 # 7'. Запит три починається з позиції мінус три. Негативна початкова позиції говорить Oracle про те, що початкова позиція розраховується від кінця рядка. Таким чином початкова позиція буде довжина рядка мінус три і дорівнює 8. Третій параметр дорівнює двом і повертається значення '# 9'.

функція REPLACE

Функція REPLACE замінює всі входження шуканого елемента на значення рядка для підстановки. Якщо довжина замінного елемента не дорівнює довжині елемента, на який відбувається заміна, довжина одержуваної рядки буде відмінною від початкового рядка. Якщо шукана підрядок не знайдено, рядок повертається без змін. Доступно три параметра, два перших обов'язкові і синтаксис виклику REPLACE (source string, search element,). Якщо явно не вказати параметр replace element, то з початкового рядка видаляються всі входження search element. Іншими словами, replace element одно порожній рядку. Якщо все символи початкового рядка замінюються порожнім replace element повертається NULL. Розглянемо кілька запитів

Query 1: select replace ( '1 # 3 # 5 # 7 # 9 #', '#', '-\u003e') from dual

Query 2: select replace ( '1 # 3 # 5 # 7 # 9 #', '#') from dual

Query 3: select replace ( '#', '#') from dual

Хеш в першому запит позначає символ для пошуку і рядок для заміни '-\u003e'. Хеш з'являється в рядку п'ять разів і замінюється, отримуємо підсумковий рядок '1\u003e 3\u003e 5\u003e 7-\u003e 9-\u003e'. Запит 2 не вказує явно рядок для заміни. Заводський параметр порожній рядок і результатом буде '13579'. Запит номер три поверне NULL.

Використання чисельних функцій

У Oracle є безліч вбудованих функцій для роботи з числами. Суттєвою різницею між чисельними функція і іншими є те, що ці функції беруть параметрами тільки числа і повертають тільки числа. Oracle надає чисельні функції для роботи з тригонометричними, експонентними і логарифмічними виразами і з багатьма іншими. Ми сфокусуємось на простих численних малих функціях: ROUND, TRUNC і MOD.

функція ROUND

Функція ROUND округлює число в залежності від необхідної точності. Значення, що повертається округляється або в більшу, або в меншу сторону, в залежності від значення останньої цифри в необхідному розряді. Якщо значення точності n, то цифра, яка буде округлятися буде на позиції n після коми, а значення буде залежати від цифри на позиції (n + 1). Якщо значення точності негативне, то всі цифри після розряду n зліва від коми будуть 0, а значення n буде залежати від n + 1. Якщо значення цифри від якої залежить округлення більше або дорівнює 5, то округлення відбувається в більшу сторону, інакше в меншу.

Функція ROUND приймає два вхідних параметра і синтаксис ROUND (source number, decimal precision). Source number може бути будь-яким числом. Параметр decimal precision визначає необхідну точність і необов'язковий. Якщо цей параметр не вказано, значення за замовчуванням буде 0, що означає необхідність округлення до найближчого цілого числа.

Розглянемо таблицю 10-1 для числа 1601.916. Негативні значення точності знаходяться зліва від точки (ціла частина), коли позитивні вважаються вправо від точки (дрібна частина).

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

Query 1: select round (1601.916, 1) from dual;

Query 2: select round (1601.916, 2) from dual;

Query 3: select round (1601.916, -3) from dual;

Query 4: select round (1601.916) from dual;

Перший запит використовує параметр точності рівні одиниці, що означає що число буде округлено до найближчої десятою. Так як значення сотої частини дорівнює одиниці (менше ніж 5), то відбувається округлення в меншу сторону і повертається значення 1601.9. Точність другого запиту дорівнює двійці, таким чином значення оточується до сотої. Так як значення тисячної частини дорівнює 6 (що більше 5), то значення сотої частини округляється вгору і повертається значення 1601.92. Значення параметра точності в третьому запиті одно мінус трьох. Так як значення негативне, це означає, що округлення відбуватиметься, грунтуючись на значенні третій позиції зліва від точки, в другому розряді (сотні), і значення 6. Так як 6 більше п'яти, то відбувається округлення вгору і повертається значення 2000. Запит 4 викликає функцію без параметра точності. Це означає що число округляється до найближчого цілого. Так як десята частина дорівнює 9, то значення округляється в більшу сторону і возвращется значення 1 602.

Чисельна функція TRUNC

Функия TRUNC скорочує значення числа грунтуючись на значення параметра точності. Скорочення відрізняється від округлення тим, що при скороченні зайва частина просто відрізається і не відбувається ніяких змін інших цифр числа. Якщо значення точності негативне, то вхідний значення скорочується на позиції зліва від коми. Синтаксис функції TRUNC (source number, decimal precision). Параметром source number може бути будь-яке число і цей параметр обов'язковий. Параметр decimal precision визначає позицію округлення і не обов'язковий, значенням за замовчуванням буде нуль, що означає скорочення до цілого числа.

Якщо значення decimal precision одно одному, то число скорочується до десятих, якщо два, то до сотих і так далі. Розглянемо кілька прикладів використання цієї функції

Query 1: select trunc (1601.916, 1) from dual;

Query 2: select trunc (1601.916, 2) from dual;

Query 3: select trunc (1601.916, -3) from dual;

Query 4: select trunc (1601.916) from dual;

У запиті 1 використовується точність дорівнює одиниці, що означає скорочення значення до десятих і повертається значення 1601.9. Точність у другому запиті дорівнює двом, початкове значення скорочується до сотих і повертається значення 1601.91. Зверніть увагу що отримується значення буде відрізнятися від значення, що повертається функцією ROUND з такими ж параметрами, так як при виклику ROUND відбудеться округлення в більшу сторону (6 більше 5). У запиті номер три використовується негативне число як значення параметра точності. Позиція три зліва від коми означає що скорочення буде до третього розряду (скорочуються сотні) як показано в таблиці 10-1 і повертається значення буде 1000. І нарешті в четвертому запиті явно Незазначені значення точності і скорочується дрібна частина вихідного числа. Результатом буде 1601.

функція MOD

Функція MOD повертає залишок від ділення. Два числа, ділене (число яке ділиться) і дільник (число на яке ділиться) визначаються як параметри і обчислюється операція ділення. Якщо ділене ділиться на дільник без остачі, то повертається нуль, так як немає залишку. Якщо дільник нуль, то не відбувається помилки ділення на нуль, а повертається ділене. Якщо дільник більше ніж ділене, повертається ділене.

У функції MOD два вхідні параметра і синтаксис MOD (dividend, divisor). Параметри dividend і divisor можуть бути чисельними літералами, стовпцями або виразами і можуть бути позитивними або негативними. Наступні приклади показують використання цієї функції

Query 1: select mod (6, 2) from dual

Query 2: select mod (5, 3) from dual

Query 3: select mod (7, 35) from dual

Query 4: select mod (5.2, 3) from dual

У запиті один 6 ділиться на два без остачі без залишку і повертається 0. У запиті два 5 ділиться на 3, ціла частина буде 1 і повертається залишок 2. У запиті номер три сім ділиться на 35. Так як дільник більше ніж ділене - повертається ділене, тобто ціла частина 0. Запит чотири використовує дробове число як ділене. Цілою частиною буде один і залишок буде 2.2.

Будь-яке парне число ділиться на два без залишку, будь непарне число при діленні на два поверне залишок 1. Тому функцію MOD часто використовують щоб відрізняти парні і непарні числа.

Робота з датами

Функції роботи з датами пропонують зручний спосіб вирішувати завдання, пов'язані з датами без необхідності враховувати високосні роки, скільки днів в конкретному місяці. Спочатку розглянемо, як зберігаються дані типу дата і форматування дати, а також функцію SYSDATE. Потім розглянемо функції ADD_MONTHS, MONTHS_BETWEEN, LAST_DAT, NEXT_DAY, ROUND і TRUNC.

Зберігання дати в базі даних

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

функція SYSDATE

Функція SYSDATE не використовує вхідні параметри і повертає поточний час і дату встановлену на сервері БД. За замовчуванням функція SYSDATE повертає дату в форматі DD-MON-RR і відображає дату на сервері. Якщо сервер встановлено в іншому часовому поясі ніж машина клієнта, то час і дата, які повертаються SYSDATE можуть відрізнятися від локальних значень на кліентсой машині. Можна виконати такий запит для відображення системної дати на сервері

select sysdate from dual

Арифметика над датами

Следуещее рівняння відображає важливий принцип при роботі з датами

Date1 - Date2 \u003d Num1

Дата може відніматися з іншої дати. Різниця між двуми датами розуміється як кількість днів між ними. Будь-яке число, включаючи дробові, може бути додано або відніме з дати. У цьому контексті число являє собою кількість днів. Сума або різниця між число і датою - це завжди дата. Цей принцип має на увазі що додавання, множення або ділення двох дат неможливий.

функція MONTHS_BETWEEN

Функція MONTHS_BETWEEN повертає кількість місяців між двома обов'язковими вхідними параметрами. Синтаксис функції MONTHS_BETWEEN (date1, date2). Функція розраховує різницю між date1 і date2. Якщо date1 менше ніж date2, то повертається негативне число. Значення, що повертається може складатися з цілої частини, що відбиває кількість місяців між двома датами, і дробової частини, що відбиває скільки днів і годин залишилося (грунтуючись на місяці рівному 31 дня) після вирахування цілого кількості місяців. Ціле число вовзращается якщо день порівнюваних місяців однаковий або останній день відповідного місяця.

Наступні приклади використовують функию MONTHS_BETWEEN

Query 1: select months_between (sysdate, sysdate-31) from dual;

Припустивши що поточна дата 16 Апреля 2009 Запит один поверне один як кількість місяців між 16 квітня 2009 і 16 березня 2009 року Запит два неявно конвертує літерали в дати використовуючи формат DD-MON-YYYY. Так як частина про час опущена Oracle встановить значення часу 00.00.00 для обох дат. Фукнція поверне значення приблизно рівне 1.03225806. Ціла частина результату позначає що між датами один місяць. Між 28 лютого і 28 березня рівно один місяць. Тоді дрібна частина повинна показувати рівно один день. Результат включає в себе годинник хвилини і секунди, але в нашому випадку тимчасова складова дат однакова. Множення 0.03225806 на 31 поверне 1, так як дрібна частина, яка повертається MONTHS_BETWEEN, розраховується, допускаючи що місяць дорівнює рівно 31 дня. Тому запит номер три поверне значення 32.

Популярною помилкою є припущення що повертається тип даних меню залежить від Вашого функції (функції роботи з датою повинні повертати дату, функції обробки рядків - рядок). Це вірне тільки для численних функцій. Символьні функції і функції роботи з датами можуть повертати значення будь-якого типу даних. Наприклад, INSTR явлется символьної функцією, а MONTS_BETWEEN функцією роботи з датою, але обидві вони повертають результатом число. Також часто помилково міркують що різниця між датами - це дата, коли фактично це число.

функція ADD_MONTHS

Функція ADD_MONTHS повертає дату, отриману шляхом додавання певної кількості місяців до вихідної датою. У цій функції два обов'язкових параметра і синтаксис ADD_MONTHS (start date, number of months). Значення параметра number of months може бути негативним, тоді вихідне значення буде зменшуватися на це кількість місяців і дробовим, але враховуватися буде тільки ціла частина. Наступні три запиту показують використання функції ADD_MONTHS

Результатом першого запиту буде 7 травня 2009, так як день залишається однаковим якщо це можливо і місяць збільшується на один. У другому запиті число місяців дробове, що ігнорується, тобто цей запиту дорівнює ADD_MONTHS ('31 -DEC-2008 ', 2). Додавання двох місяців має повернути 31-FEB-2009, але такої дати не існує, тому повертається останній день місяця. В останньому прикладі використовується негативне число для параметра кількість місяців і повертається дата 07-APR-2008 що на дванадцять місяців раніше, ніж початкове значення.

функція NEXT_DAY

Функція NEXT_DATE повертає наступний найближчий заданий день тижня по материнській дати. У цій функції два обов'язкових параметра і синтаксис NEXT_DAY (start date, day of the week). Функція виічсляет значення, коли заданий day of the week настане після start date. Параметр day of the week може бути заданий як числом, так і рядком. Допустимі значення визначаються параметром NLS_DATE_LANGUAGE і за замовчуванням використовуються три перші літери назви дня тижня в будь-якому регістрі (SUN, mon etc) або цілі числа де 1 одно неділі, 2 - понеділок і так далі. Також імена днів тижня можуть бути більш ніж три символи; наприклад, неділю можна вказати як sun, sund, Sunday. Розглянемо кілька запитів

1 січня 2009 року цю четвер. Наступні вівторок буде через 5 днів, 6 січня 2009 року. Другий запит поверне 7 січня 2009 року - наступної середи після 1 січня. Третій запит використовує число як параметр і якщо у вас встановлені Американські значення, то п'ятий день - це четвер. Наступного четверга після 1 січня рівно через тиждень - 8 січня 2009 року.

функція LAST_DAY

Функція LAST_DAY повертає дату останнього дня місяця вихідної дати. Ця функція вимагає один обов'язкові параметр і синтаксис LAST_DAY (start date). Функція вибирає місяць вихідної дати і потім розраховує останній день місяця. Наступний запит поверне 31 січня 2009 року

функція ROUND для роботи з датами

Функція ROUND округлює значення дати до заданої точності дати. Значення, що повертається округляється або до більшого, або r меншому значенню в залежності від значення округляється елемента. Ця функція вимагає один обов'язковий параметр і допускає один необов'язкові і синтаксис функції ROUND (source date,). Параметром source data може бути будь-який елемент типу даних дата. Параметр date precision format визначає рівень округлення і значення за замовчуванням - день. Параметром date precision format може бути вік (CC) рік YYYY квартал Q місяць M тиждень W день DD годину HH хвилина MI.

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

Припустимо, що цей запит був виконаний 17 квітня 2009 року в 00:05. Спочатку відбувається округлення поточної дати до дня (параметр точності явно невказаним). Так як час 00:05 якогось дня не округляється в більшу сторону.Так як 1 квітня 2009 року цю среда, то другий стовпець поверне середу того тижня, в яку входить вихідна дата. Перша середа тижні, в яку входить 19 квітня - це 15 квітня 2009 року. Третій стовпець оругляет місяць до наступного (так як 17 більше 16) і повертає 01 травня 2009 року поледнего стовпець округлює дату до року і повертає 1 явнваря 2009 року, так як квітня ця 4-ий місяць.

функція TRUNC при роботі з датами

Функція TRUNC скорочує дату грунтуючись на параметрі точності. У цій функції один параметр обов'язковий і один немає і синтаксис виклику TRUNC (source date,). Параметром source date може бути будь-яка валідна дата. Параметр date precision format визначає рівень скорочення дати і необов'язковий, значення за замовчуванням - скорочення до дня. Це означає що всі значення часу обнуляються - 00 годин 00 хвилин 00 секунд. Скорочення до місяця поверне дату рівну першого дня місяця вихідної дати. Скорочення до року - поверне перший день року вихідної дати. Розглянемо запит, який використовує функцію з різними параметрами

Цей запит виконуватися 17 квітня в 00:05. Перший стовпець скорочує системну дату до дня, час перетворюється з 00:05 в 00:00 (параметр точності явно невказаним, використовується значення за замовчуванням) і повертається поточний день. Другий стовпець скорочує дату до такого ж дня тижня, який був першого числа місяця (середа) і повертає середу поточного тижня - 15 квітня. Третій стовпець скорочує дату до місяця і повертає перший день місяця - 1 квітня. Четвертий стовпець скорочує дату до року і повертає перший день року.