Pl SQL рядкові функції. Зберігання дати у базі даних. Чисельна функція TRUNC

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

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

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

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

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

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

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

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

Типи функцій

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

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

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

Наступний запит вибирає два стовпці з таблиці 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) > 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=1 або іншими словами початок рядка source string. Значення за промовчанням для n occurrence=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 = різниця між довжиною 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#','#','->') from dual

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

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

Хеш в першому запиту позначає символ для пошуку і рядок для заміни '->'. Хеш з'являється у рядку п'ять разів і замінюється, отримуємо підсумковий рядок '1->3->5->7->9->'. Запит 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, то значення округляється у велику сторону і повертається значення 1602.

Чисельна функція 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 = 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 квітня. Четвертий стовпець скорочує дату до року та повертає перший день року.

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

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

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

CONCAT("Test", NULL) x2,

CONCAT(NULL, "Test") x3,

CONCAT(NULL, NULL) x4

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

SELECT CONCAT("У попа", "був собака") x1,

"У попа" || "був собака" x2

На замітку

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

SELECT "5" + "3" x1

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

Функція LOWER(str)

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

SELECT LOWER("TeXt DATA") X

Функція INITCAP(str)

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

SELECT INITCAP("Іванов Петро сіДорович") X1

Функції LPAD(str,n, [,char]) та RPAD(str,n, [,char])

Функція LPAD повертає рядок str, доповнений ліворуч символом char, до досягнення рядком довжини nсимволів. За промовчанням символ-заповнювач дорівнює пробілу і може не вказуватися. Якщо довжина переданої функції рядка більша n, то функція повертає рядок без змін. Функція RPAD аналогічна LPAD, але доповнює рядок праворуч. Ці функції дуже зручні для форматування текстової інформації під час підготовки звітів.

SELECT LPAD("Test", 20) x1,

RPAD("Test", 20) x2,

LPAD("Test", 20, "_") x3

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

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

SELECT REPLACE("У попа був собака", "собака", "кішка") x1,

REPLACE("У попа був злий собака", "злий") x2,

REPLACE("У попа був собака", "Собака", "Кішка") x3

Функція 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", "e2", "E!") x1,

TRANSLATE("Test 12345", "e234", "E") x2

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

IF TRANSLATE(PassWd, "0123456789", "*") = PassWd THEN

ADD_ERROR("Помилка - Пароль повинен містити хоча б одну цифру!");

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

SELECT TRANSLATE("123 455,23", ".,", "..") x1,

TRANSLATE("-123 455.23", "., ", "..") x2

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

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

SELECT SUBSTR("У попа був собака", 13) x1,

SUBSTR("У попа був собака", -6) x2,

SUBSTR("Це тестовий текст", 5, 8) x3,

SUBSTR("У попа був собака", 150) x4


		Текстовий

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

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

SELECT INSTR("У попа був собака", "собака") x1,

INSTR("У попа був собака", "кішка") x2,

INSTR("Це текст для демонстрації пошуку тексту", "текст", 1, 2) x3,

INSTR("11111000000001", "1", 7) x4

На замітку

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

IF INSTR(TXT_VAR, "*") = 0 THEN

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

IF NVL(INSTR(TXT_VAR, "*"), 0) = 0 THEN

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

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

SELECT LENGTH("У попа був собака") x1,

LENGTH(NULL) x3,

NVL(LENGTH(""), 0) x4

Функція CHR(n)

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

SELECT CHR(64) x1

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

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 поверне рядок My@string.

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) > 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.