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

Наша взаимовыгодная связь https://banwar.org/

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

функції конвертації

Oracle дозволяє визначати стовпці з типами даних ANSI, DB2 і SQL / DS. Ці типи перетвориться до типам даних Oracle. У кожного стовпця визначається тип даних який обмежує природу даних які можуть зберігатися в цьому стовпці. Стовпець NUMBER не може зберігати символьну інформацію. Стовпець DATE не може зберігати випадкові символи або числа. VARCHAR2 може зберігати символьні еквіваленти чисел і дат.

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

Неявна конвертація типів

Значення, які не відповідають типам даних параметрів функції неявно конвертується перед виконанням якщо це можливо. Обидва типи даних VARCHAR2 і CHAR використовуються як символьні типи даних. Символьні типи даних досить гнучкі для зберігання практично будь-якої інформації. Таким чином, ДАТА і ЧИСЛО можна легко перетворити в їх символьний еквівалент. Така конвертація відома як перетворення число в рядок і дата в рядок. Розглянемо наступні запити

Query 1: select length (1234567890) from dual

Query 2: select length (SYSDATE) from dual

Обидва запиту використовують функцію LENGTH у якій вхідний параметр визначений як рядок. Число 1234567890 в запиті один неявно конвертується в рядок '1234567890' перед обчислення функції LENGTH і результат функції буде 10. Запит номер два обчислює функцію SYSDATE припустимо 7 квітня 2008 року. Результат перетворюється в рядок '07 -APR-08 'і результат виконання функції LENGTH буде число 9.

Зазвичай не прийнято допускати неявну конвертацію рядків у числа, так як єдина ситуація, коли це можливо, це якщо рядок являє собою валідності число. Рядок '11' буде неявно перетворена в число 11, але рядок '11 .123.345 'не буде, як показано в наступних прикладах

Query 3: select mod ('11 ', 2) from dual

Query 4: select mod ('11 .123 ', 2) from dual

Query 5: select mod ('11 .123.456 ', 2) from dual

Query 6: select mod ( '$ 11', 2) from dual

Запити 3 і 4 неявно перетворили рядки '11' і '11 .123 'в числа 11 і 11.123 відповідно, перед викликом функції MOD яка в свою чергу повернула результат 1 і 1.123. Запит 5 повернув помилку 'ORA-тисячі сімсот двадцять дві: invalid number', коли Oracle спробував неявно перетворити рядок в число, так як '11 .123.456 'не є коректним числом. Запит 6 також повернув помилку так як символ долара не може бять неявно перетворений в число.

Неявна конвертація рядки в дату можлива, коли рядок задовольняє наступним шаблонах: [D | DD] separator1 [MON | MONTH] separator2] [R | RR | YY | YYYY], де D і DD це день MON перші три букви місяця, MONTH - повна назва місяця. R і RR YY і YYYY відображають одну, дві і чотири цифри року відповідно. Параметром separator1 і separator2 може бути практично будь-який спец символ, включаючи сюди пробіл, табуляцію, знаки пунктуації і т.д. Таблиця 10-2 показує неявну конвертацію рядки в дату, включаючи виклик функцій роботи з датами і результати. Ці результати припускають, що система використовує американську локаль.

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

Явна конвертація типів даних

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

Число і дату можна явно конвертувати в рядок використовуючи функцію TO_CHAR. Рядок можна явно конвертувати в число використовуючи функцію TO_NUMBER. Функція TO_DATE використовується для конвертації рядок в DATE. Маски форматування Oracle дозволяють гнучко контролювати процес конвертації рядки в число або дату.

Exam tip

Розуміння широко використовуваних масок форматування перевіряється досить просто питаннями виду: який результат поверне виклик функцій TO_CHAR (TO_DATE ('01 -JAN-00 ',' DD-MON-RR '),' Day ').

Використання функцій TO_CHAR, TO_NUMBER і TO_DATE

При сертифікації дуже часто використовуються питання про функції TO_NUMBER, TO_DATE і TO_CHAR з прикладами для аналізу. Обговорення функції TO_CHAR розділимо на дві частини: конвертація дати в рядок і конвертація числа в рядок. Такий поділ грунтується на тому що для різних типів даних доступні різні маски форматування. Крім цих функцій існує багато інших, але ці використовується найбільш часто.

Використання функцій конвертації

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

Таблиця 10-3 показує синтаксис малих функцій конвертації даних

Необов'язковий параметр підтримки національного формату (nls_parameters) корисний для вказівки мови і форматування, в якому назви днів, місяців і роздільники розрядів, цілої і дробової частини заздалегідь визначені. На малюнку 10-2 відображено уявлення NLS_SESSION_PARAMETERS яке містить значення парамент NLS для поточної сесії. За замовчуванням значення NLS_CURRENCY - знак долара, але це можна змінити на рівні сесії. Наприклад, для зміни символу валюти на рядок 'GBP' маєте змогу надсилати запити

ALTER SESSION SET NLS_CURRENCY = 'GBP'

Малюнок 10-2 - Подання NLS_SESSION_PARAMETERS

Конвертація числа в рядок використовуючи функцію TO _CHAR

Функція TO_CHAR повертає значення типу VARCHAR2. Коли вхідних параметром є число то доступні деякі параметри форматування. Синтаксис команди TO_CHAR (num, [format], [nls_parameter]). Параметр num обов'язковий і повинен бути числом. Необов'язковий параметр format можна використовувати для вказівки інформації про форматування, такий як довжина, символ валюти, позиція роздільник дробової і цілої частини і роздільник розрядів (три розряди) і повинен бути укладений в одинарні лапки. Доступні різні опції форматування і частина з них представлена ​​в таблиці 10-4. Розглянемо два запити

Query 1: select to_char (00001) || ' is a special number 'from dual;

Query 2: select to_char (00001, '0999999') || ' is a special number 'from dual;

У запиті номер один у числа 00001 прибираються провідні нулі, значення перетвориться в рядок '1' і повертається результат '1 is a special number'. Зпрос номер два використовує маску форматування '0999999' для числа 00001 перетворюючи число в рядок '0000001' і повертаючи результат '0000001 is a special number'. 0 і 6 дев'яток в масці форматування вказують функції TO_CHAR що необхідно використовувати провідні нулі, і довжина рядка повинна бути 7. Таким чином рядок, яка повертається функцією TO_CHAR містить сім символів.

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

Конвертація дати в рядок використовуючи функцію TO _CHAR

Ви можете використовувати переваги моделі масок форматування при конвертації ДАТИ в практично будь-який варіант відображення дати як символьного значення використовуючи функцію TO_CHAR. Синтаксис функції TO_CHAR (date1, [format], [nls_parameter]).

Тільки параметр date1 обов'язковий; тоді він повинен бути значенням, яке може неявно перетворитися в рядок. Необов'язковий параметр format чутливі до регістру і повинен бути обрамлений одинарними лапками. Маска форматування вказує які Лементи дати повинен бути обрані і як відображати назви елементів дати: повні назви або абревіатури. Назви днів і місяців автоматично розділяються пропуском. Таку поведінку можна змінити, використовуючи параметр маски fill mode (fm). Вказавши на початку маски параметр fm, ви вкажете Oracle про необхідність прибрати всі прогалини. Доступно багато опцій для маски форматування, частина з яких відображена в таблиці 10-5. Розглянемо три запиту

Query 1: select to_char (sysdate) || ' is today »s date 'from dual;

Query 2: select to_char (sysdate, 'Month') || 'is a special time' from dual;

Query 3: select to_char (sysdate, 'fmMonth') || 'is a special time' from dual;

Якщо поточна системна дата 3 січня 2009 року і за замовчуванням формат відображення DD / MON / RR тоді запит один поверне рядок '03 / JAN / 09 is todays date '. У другому запиті зверніть увагу на дві деталі: по-перше, тільки місяць вибирається з дати, і по-друге так як маска форматування регістрозавсімая і в запиті використовується 'Month', то запит поверне 'January is a special time'. Немає потреби додавати пробіл на початку литерала, так як функція TO_CHAR автоматично додасть пробіл до назви місяця. Якби маска в другому запиті була 'MONTH' то запит повернув би 'JANUARY is a special time'. Параметр fm в третьому запиті перешкоджає додаванню прогалин і результатом буде 'Januaryis a special time'. У таблиці 10-5 передбачається що обробляється дата 2 червня 1975 року та поточний рік 2009.

Параметри форматування, пов'язані з тижнем, кварталом, століттям і іншими більш рідко використовуються елементами дати показані на малюнку 10-7. Стовпець результату передбачає, що функція використовувалася для роботи з датою 24 вересня 1000 року, із маскою форматування зазначеної в стовпець format element.

Компонент час в типі даних дата вибирається, використовуючи моделі форматування в таблиці 10-7. Результат розраховується функцією TO_CHAR використовуючи дату 27 червня 2010 час 21:35:13 з маскою форматування зазначеної в стовпці format element.

Деякі різні елементи, які можна використовувати в форматуванні дати і часу перераховані в таблиці 10-8. Знаки пунктуації використовуються для поділу елементів форматування. Три типу суфіксів існують для форматування елементів. Більш того, символьні літерали можуть бути включені в модель форматування якщо вони укладені в подвійні лапки. Результати в таблиці 10-8 отримані використовуючи функцію TO_CHAR для дати 12 вересня 2008 року 14:31 з маскою форматування зазначеної у відповідному стовпці.

Таблиця JOB_HISTORY зберігає інформацію про посади співробітника, які він займав в компанії. Запит на малюнку 10-3 вибирає інформацію з цієї таблиці про кінець терміну дії посади співробітника для кожного сотрудінка на підставі стовпців END_DATE, EMPLOYEE_ID і JOB_ID і форматує результат в красиву пропозицію. Символьне вираз об'єднується з функцією TO_CHAR в якій використовується маска 'fmDay «the« ddth «of» Month YYYY'. Параметр fm використовується щоб прибрати пробіли в імені дня тижня і місяця. Два Литерат обмеженою подвійними лапками використовуються для додавання читабельності результату. Літерал "th" застосовується до елементу дати щоб зробити літературно правильним відображення дати як 17th або 31th. 'Month' в масці означає використовувати повне ім'я місяці і нарешті YYYY форматує рік як всі чотири символи року.

Малюнок 10-3 - Запит в таблицю JOB_HISTORY

select 'Employee' || EMPLOYEE_ID || ' quit as '|| JOB_ID ||' on '|| TO_CHAR (END_DATE,' fmDay «the« ddth «of» Month YYYY ') «Quitting Date»

FROM JOB_HISTORY

ORDER BY END_DATE;

Незважаючи на те що компонент «вік» не відображається за замовчуванням, він зберігається в базі даних і доступний для запиту. Маска форматування DD-MON-RR використовується за умовчанням для введення значень і відображення. Коли значення додається або змінюється якщо явно невказаним століття, то використовується століття з функції SYSDATE. Формат RR відрізняється від формату YY і так як RR також використовує значення сторіччя. Вплив значення сторіччя на формат RR легше зрозуміти якщо розглянути наступні принципи

• Якщо дві останні цифри поточного року між 0 і 49, а в зазначеному значенні дати дві останні цифри року між 50 і 99 то використовується попереднє століття. Припустимо, що поточна дата 2 червня 2007 року. Значення століття для дати 24-JUNE-94 року буде 20
• Якщо дві цифри поточного року між 50-99 і зазначеної дати також між 55 і 99, то повертається поточний століття. Припустивши що поточна дата 2 червня 1975 року. Тоді значення століття для 24-JUL-94 буде 20.
• Якщо дві цифри поточної дати між 50 і 99, а в укащанной датою рік між 0 і 49 - то вважається наступне століття. Припустимо, що поточна дата 2 червня 1975 року народження, тоді для значення 24-JUL-07 значення століття буде 21.

Конвертація рядки в дату використовуючи функцію TO _DATE

Функція TO_DATE повертає значення типу даних DATE. Рядок, конвертована в дату може містити всі або частину компонентів, складових тип DATE. Коли рядок містить тільки частину компонентів дати перетворюється в дату, Oracle використовує значення за замовчуванням для складання валидного значення типу DATE. Частини рядки зіставляються з елементами дати використовуючи маску (або модель) форматування. Синтаксис функції TO_DATE (string1, [format], [nls_parameter]).

Тільки параметра string1 обов'язковий, і eсли маска форматування не вказана, string1 повинна бути в формат неявно конвертованій в дату. Необов'язковий параметр format використовується практично завжди і повинен бути укладений в одинарні лапки. Маска форматування ідентичний перерахованим в таблицях 10-5, 10-6, і 10-7. У функції TO_DATE є модифікатор fx, які використовується подібно параметру fm функції TO_CHAR. Параметр fx вимагає обов'язкового збігу рядка і маски форматування. Якщо рядок не збігається з маскою - повертається помилка. Розглянемо кілька прикладів

Query 1: select to_date ('25 -DEC-2010 ') from dual;

Query 2: select to_date ('25 -DEC ') from dual;

Query 3: select to_date ('25 -DEC ',' DD-MON ') from dual;

Query 4: select to_date ('25 -DEC-2010 18:03:45 ',' DD-MON-YYYY HH24: MI: SS ') from dual;

Query 5: select to_date ('25 -DEC-10 ',' fxDD-MON-YYYY ') from dual;

Запит 1 перетворить рядок '25 -DEC-2010 'і рядок може бути перетворена в дату використовуючи маску DD-MON-YYYY. Дефіс можна замінити на інший роздільник. Так як не було вказано жодної інформації про час то використовується значення за замовчуванням 00:00:00. Запит 2 не може неявно перетворити рядок в дату, так як недостатньо інформації і повертається помилка ORA-01840: input value is not long enough for date format. Вказавши маску DD-MON для рядка '25 -DEC 'в запиті номер три, Oracle може конвертувати в дату, але так як рік і час не вказані, в значенні року буде використовуватися значення року функції SYSDATE, а час встановлено опівночі. Якщо поточний рік очікується 2009, то запит 3 поверне дату 25 / DEC / 09 00:00:00. Запит 4 перетворює рядок з явно вказаними всіма компнонентамі дати і часу. Запит 5 використовує параметра fx. Так як рік в рядку вказано двома символами, а необхідний формат передбачає чотири цифри року, то запит повертає помилку ORA-01862: the numeric value does not match the length of the format item.

Конвертація рядки в число використовуючи функцію TO _NUMBER

Функція TO_NUMBER повертає значення типу даних NUMBER. Вихідна рядок повинна бути складена таким чином, щоб всі несумісні символи були відсутні або були вказані у відповідній масці форматування. Синтаксис функції TO_NUMBER (string1, [format], [nls_parameter]). Тільки string1 є обов'язковим параметром, і якщо не вказано параметр format то значення має бути таким, щоб була можливість неявно конвертувати його в число. Маски форматування ідентичні перерахованим в таблиці 10-4. Розглянемо запити

Query 1: select to_number ( '$ 1,000.55') from dual;

Query 2: select to_number ( '$ 1,000.55', '$ 999,999.99') from dual;

Запит один не може неявно перетворити рядок, так як вона містить знак валюти і роздільники, які явно Незазначені в масці, тому повертається помилка ORA-1722: invalid number. Запит два знаходить символ валюти, коми і крапки в масці форматування і незважаючи на те що довжина маски більше ніж ніж початкове значення в рядку, повертається число 1000.55

Exam tip

Функція TO_NUMBER конвертує значення рядка в число. Якщо ви використовуєте число довше ніж маска, повертається помилка. Якщо ви конвертуєте число використовуючи більш довгу маску - повертається значення вихідної довжини. Не плутайте TO_NUMBER з TO_CHAR. Наприклад TO_NUMBER ( '123.45', '999.9') поверне помилку, коли TO_CHAR (123.45, '999.9') поверне 123.6