Для упрощения работы со строками имеется ряд встроенных функций, что значительно облегчает такие операции как преобразование строк к данным других типов, поиск подстроки в строке, определение длины строки и т. д. В данной статье мы рассмотрим самые распространенные функции для работы со строками.

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.

Теперь давайте рассмотрим особенности работы с функцией TO_CHAR() применительно к преобразованию, чисел. При выполнении этой операции функция имеет следующий синтаксис:

TO_CHAR(число, [, формат[, nls_параметр]])

При этом функция TO_CHAR() преобразует тип NUMBER в тип VARCHAR2 . В таблице указано, какие есть форматы и как они используются при преобразовании:

Элемент формата	Пример строки данного формата	Описание
9	99	Каждая цифра 9 представляет значащую цифру результата. Число значащих цифр возвращаемого значения равно числу цифр 9, отрицательное значение предваряется знаком минуса. Все начальные нули заменяются пробелами.
0	0999	Возвращается число с начальными нулями, а не пробелами.
0	9990	Возвращается число с конечными нулями, а не пробелами.
$	$999	Возвращаемое значение предваряется знаком доллара не зависимо от используемого символа денежной единицы можно применить совместно с начальными или конечными нулями.
B	B999	Вместо нулевой целой части десятичного число возвращаются пробелы.
MI	999MI	Возвращает отрицательное число, у которого знак минуса указан не в начале, а в конце. В положительном значении на этом месте будет пробел.
S	S9999	Возвращаемое число предваряется знаком: + для положительных, чисел - для отрицательных.
S	9999S	Возвращаемое число заканчивается знаком: + для положительных, чисел - для отрицательных.
PR	99PR	Возвращается отрицательное число в угловых скобках "<", ">". У положительных чисел, на этом месте пробелы.
D	99D9	Возвращает число с десятичной точкой в указанной позиции. Число 9 с обеих сторон указывает максимальное число цифр.
G	9G999	Возвращает число с разделителем групп в указанной позиции. G
C	C99	Возвращает число с символом денежной единицы ISO в указанной позиции. C может появляться в указанной строке формата неоднократно.
L	L999	Возвращает число с символом денежной единицы национального языка в указанной позиции.
,	999,999	Возвращает число с запятой в указанной позиции, не зависимо от выбранного разделителя групп.
.	99.99	Возвращает число с десятичной точкой в указанной позиции, не зависимо от выбранного десятичного разделителя.
V	99V999	Возвращает число, умноженное на 10n , где n - это число цифр 9 после V. При необходимости значение округляется.
EEEE	9.99EEEE	Возвращает число в экспоненциальном представлении.
RM	RM	Возвращает число при помощи римских цифр верхнего регистра.

Для примера, приведем вот такой блок:

SET SERVEROUTPUT ON BEGIN -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(534523)); -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(34387, "99999")); -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(5000, "$9999")); -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(-9, "9S")); -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(-34, "S99")); -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(3 - 5, "999MI")); -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(7 - 3, "S9")); -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(4 - 5, "99PR")); -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(8900, "L9999")); -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(10000000, "9.9EEEE")); -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(10, "RM")); -- Function TO_CHAR() -- DBMS_OUTPUT.put_line(TO_CHAR(105, "RM")); END; /

После запуска получаем:

SQL> BEGIN 2 3 -- Function TO_CHAR() -- 4 DBMS_OUTPUT.put_line(TO_CHAR(534523)); 5 6 -- Function TO_CHAR() -- 7 DBMS_OUTPUT.put_line(TO_CHAR(34387, "99999")); 8 9 -- Function TO_CHAR() -- 10 DBMS_OUTPUT.put_line(TO_CHAR(5000, "$9999")); 11 12 -- Function TO_CHAR() -- 13 DBMS_OUTPUT.put_line(TO_CHAR(-9, "9S")); 14 15 -- Function TO_CHAR() -- 16 DBMS_OUTPUT.put_line(TO_CHAR(-34, "S99")); 17 18 -- Function TO_CHAR() -- 19 DBMS_OUTPUT.put_line(TO_CHAR(3 - 5, "999MI")); 20 21 -- Function TO_CHAR() -- 22 DBMS_OUTPUT.put_line(TO_CHAR(7 - 3, "S9")); 23 24 -- Function TO_CHAR() -- 25 DBMS_OUTPUT.put_line(TO_CHAR(4 - 5, "99PR")); 26 27 -- Function TO_CHAR() -- 28 DBMS_OUTPUT.put_line(TO_CHAR(8900, "L9999")); 29 30 -- Function TO_CHAR() -- 31 DBMS_OUTPUT.put_line(TO_CHAR(10000000, "9.9EEEE")); 32 33 -- Function TO_CHAR() -- 34 DBMS_OUTPUT.put_line(TO_CHAR(10, "RM")); 35 36 -- Function TO_CHAR() -- 37 DBMS_OUTPUT.put_line(TO_CHAR(105, "RM")); 38 39 END; 40 / 534523 34387 $5000 9- -34 2- +4 р.8900 1.0E+07 X CV Процедура PL/SQL успешно завершена.

И для полноты картины, давайте рассмотрим функцию TO_NUMBER() , так как с TO_CHAR() они практически близнецы, но с двух противоположных сторон. Синтаксис у TO_NUMBER следующий:

TO_NUMBER(строка_символов, [, формат [, nls_параметр]])

В данном случае "строка_символов" - это тип CHAR или VARCHAR2 , которая применительно к "формат", преобразуется в типу NUMBER . "формат" в данной функции, тот же что и для TO_CHAR() . Вот так достаточно просто. А, если в предыдущем примере поменять, например, третью снизу строку вот так:

DBMS_OUTPUT.put_line(TO_CHAR(TO_NUMBER("10000000", "9.9EEEE"), "9.9EEEE"));

S. Moiseenko

To within two decimal digits, define the average amount of guns for the battleship classes.

I supposed there is only one problem in this task, and that is rounding. But recently I received the following solution:

SELECT SUM(sum_g)/SUM(count_g) FROM
(SELECT SUM(numGuns) AS sum_g , COUNT(*) AS count_g
FROM Classes INNER JOIN Ships ON Classes.class = Ships.class
WHERE type="bb"
UNION
SELECT SUM(numGuns) AS sum_g , COUNT(*) AS count_g
FROM Classes INNER JOIN Outcomes ON Classes.class = Outcomes.ship
WHERE type="bb") AS a

It"s very rich for mistakes analysis J . Let"s start from the rounding. The number of guns is an integer (according to the column type, not to the logic!). That"s why the sum itself will be a whole number. When dividing integers in SQL Server we always get an integer. And the result is achieved not by rounding but by DISCARDING the fractional part. For example, execute the following query:

The result will be 0, and that confirms what we"ve said. So, to make cosmetic improvements of this query, at least one operand should be converted to real type. As I"ve written in the Help at www.sql-ex.ru site, you can use implicit type conversion:

SELECT SUM(sum_g)*1.0/SUM(count_g)

that is, when multiplying by a real unity the numerator becomes a real number itself.

Now, as we need to count the average by classes, at first, we shouldn"t take ships into account and, second, we don"t need to remember ships from the Outcomes table.

But for analyzing the mistakes let"s consider the solution in its author"s interpretation, that is, we"ll define the average value by all linear ships from the database, and this turns out to be task 54. I"ve discussed that task but with another mistake.

So, the number of guns and amount are counted separately by ships from the Ships table and by the head ships from the Outcomes table. Then, in the main query we summarize the number of guns and the amount of ships by every table and divide the first by second to get the average value.

Let"s discuss an example. Let there be 2 ships with 11 and 8 guns in the Ships table, and 1 ship with 11 guns in the Outcomes table. So we get 3 ships with 30 guns. The average value is 30/3=10. Is this correct? No, that is, it"s correct only for certain cases, while we need to write a query that will be correct for any data. I see several examples to prove this.

First. What if there is no head ship that corresponds to the terms of the task in the Outcomes table? Then the second query will return: 0 ships, the number of guns is NULL. As a result of calculating the average we"ll get

(19 + NULL)/(2+0) = NULL

Second. Let the head ship of ‘bb" class be both in the Ships and the Outcomes table, that means, it"s the same ship. Then we should get 19/2 but not 30/3 as the solution presents.

Third. But what if in the previous situation on the ships the head vessel took part in battles twice? Then we"ll get (19 + 22) /(2 + 2) instead of our 19/2.

Fourth… Think of it yourself. That"s how the checking base on the site is formed J .

» Given examples here can be done directly on the website by selecting the check box “Without checking” on the page with SELECT exercises .

В предыдущей статье мы рассмотрели встроенные функции для работы со строками. В данной статье речь пойдет о функциях работы с датой/временем и функциями предобразования типов для даты. Для хранения даты и времени в Oracle предусмотрен специальный тип DATE. С физической точки зрения это дробное число, целая часть которого хранит количество дней с некоторой базовой даты, а дробная - время. Это позволяет совершать над датами арифметические операции - сложение и вычитание.

Функция SYSDATE

Это одна из самых часто употребляемых функций, она возвращает текущую дату и время по часам сервера.

Функция LAST_DAY(d)

Возвращает последнее число месяца, указанного в дате d.

SELECT SYSDATE d,

LAST_DAY(SYSDATE) d1

Функция MONTHS_BETWEEN(d1, d2)

Функция MONTH_BETWEEN возвращает количество месяцев между двумя датами d1 и d2 с учетом знака как d1-d2, возвращаемое число является дробным.

SELECT MONTHS_BETWEEN("2.09.2006", "2.05.2006") d1,

MONTHS_BETWEEN("12.09.2006", "2.05.2006") d2,

MONTHS_BETWEEN("2.05.2006", "12.09.2006") d3

Рассмотрим типовые примеры - усечение даты до часов, дней, месяца и года. Форматная маска по умолчанию равна «DD»

SELECT SYSDATE d1,

TRUNC(SYSDATE, "HH24") d2,

TRUNC(SYSDATE, "DD") d3,

TRUNC(SYSDATE, "MM") d4,

TRUNC(SYSDATE, "YYYY") d5

Форматные маски, допустимые для функций TRUNC и ROUND

Рассмотрим подробнее форматные маски и особенности их применения.

Маска	Назначение
	Первый день столетия
YEAR, или YYYY, или YY, или Y	Первый день года
	Первый день квартала
MONTH, или MON, или MM	Первый день месяца
	Тот же день недели, что и первый день текущего года
	Тот же день недели, что и первый день текущего месяца
DAY, или DY, или D	Первый день недели
HH, или HH12, или HH24

Функция TO_DATE(str[,mask [,nls_lang]])

Функция TO_DATE преобразует строку str в дату. Преобразование ведется по маске mask, если она указана. Если маска не указана, то берется маска по умолчанию. В случае указания маски можно указать еще один параметр - язык, используемый при форматировании названий месяцев и дней. В случае ошибки анализа строки str в соответствии с заданной маской возникает исключительная ситуация. Наиболее распространенная ошибка «ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода». Кроме того, нередко встречается ошибка «ORA-01821: формат даты не распознан» - она возникает при указании недопустимой форматной маски.

SELECT TO_DATE("12.09.2006") d

Форматные маски, допустимые для функций TO_CHAR в случае форматирования даты

Столетие, причем перед датами до нашей эры ставится знак «минус».

SELECT SYSDATE d1,

TO_CHAR(SYSDATE-1000000, "SCC") d2

26.09.2006 17:14:21

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "YYYY") d2

Год, записанный прописью c учетом текущего национального языка.

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "YEAR") d2

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "MM") d2

Название месяца прописью.

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "MONTH") d2

Неделя года.

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "WW") d2

День года.

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "DDD") d2

День недели прописью, при необходимости дополняется до девяти символов пробелами.

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "DAY") d2

Дата юлианского календаря. Является числом дней от 1.01.4712 до нашей эры.

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "J") d2

Час дня по 12-часовой шкале (1-12).

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "HH") d2

Минуты (0-59).

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "MI") d2

Данная форматная маска полезна для измерения временных интервалов в секундах.

Знаки пунктуации. Они выводятся в соответствующие места отформатированной даты.

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "DD.MM.YYYY HH24.MI") d2,

TO_CHAR(SYSDATE, "DD/MM/YYYY HH12.MI PM") d3

Элементы форматной маски, предназначенные для вывода текстовой информации, чувствительны к регистру - регистр управляет регистром форматируемого текста. Рассмотрим управление регистром на примере форматной маски DAY:

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, "DAY") d2,

TO_CHAR(SYSDATE, "Day") d3,

TO_CHAR(SYSDATE, "day") d4

26.09.2006 17:47:45

Несложно заметить, что если маска записана в верхнем регистре, то и форматируемое текстовое значение возвращается в верхнем регистре. Если в нижнем - форматируемое значение тоже будет в нижнем. И наконец, запись маски с прописной буквы приводит к форматированию формируемого текста таким образом, чтобы он начинался с прописной буквы.

Заключение

В данной статье мы рассмотрели все основные встроенные функции Oracle, предназначенные для работы с датами и выполнения операций преобразования типов с типом «дата». В следующей статье мы рассмотрим функции преобразования и форматирования чисел и перейдем к рассмотрению практических запросов, использующих рассмотренные ранее функции.