Geum.ru

Курс лекций "Базы данных и субд" Ульянов В. С. Лекция Язык sql. Однострочные функции

Вид материалаКурс лекций

Содержание


Модель формата
Подобный материал:

Курс лекций “Базы данных и СУБД” Ульянов В.С.



Лекция 4. Язык SQL. Однострочные функции.


Функции

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

function(argument, argument, ...)

Функции могут использоваться для выполнения расчетов с данными, преобразования типов данных, изменения форматов вывода дат и т.д. Функции SQL бывают двух основных типов:
  • однострочные (или скалярные) функции
  • групповые (или агрегатные) функции

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

Однострочные функции

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

Числовые функции

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

Функция
Описание

ABS(n)
Возвращает абсолютную величину n.

MOD(m, n)
Возвращает остаток от деления m на n. Если n=0, возвращает m.

POWER(m, n)
Возвращает m в степени n. Основание m и степень n могут быть любыми числами, но если m отрицательно, то n должно быть целым.

ROUND(m[, n])
Возвращает m, округленное до n позиций после десятичной точки; если n опущено, то до целого, если n отрицательно, округляется целая часть числа. n должно быть целым.

SQRT (n)
Возвращает квадратный корень из n. n должно быть неотрицательным.

TRUNC(m[, n])
Возвращает m, усеченное до n цифр после десятичной точки. Если n опущено, усечение выполняется до целого. n может быть отрицательным, что приводит к усечению (обнулению) n цифр слева от десятичной точки.

Примеры использования числовых функций

В примерах используется фиктивная таблица СУБД Oracle DUAL. Эта таблица принадлежит пользователю SYS и доступна всем пользователям. Она содержит один столбец с именем DUMMY и одну строку со значением X. Таблица DUAL полезна в случае, если требуется получить результат действия какой-либо однострочной функции, вызванной с аргументами, не связанными с данными БД.

Пример: Найти квадратный корень из 10.

SELECT SQRT(10)

FROM s_dept;

Результат:

SQRT(10)

---------

3,1622777

3,1622777

3,1622777

3,1622777

3,1622777

3,1622777

3,1622777

…

12 rows selected.

-- Мы получили 12 значений, так как в таблице s_dept 12 строк. Но так как -- вычисляемое значение никак не связано с данными таблицами, то все эти 12 -- значений одинаковы. Чтобы избежать подобных излишеств, и предназначена -- таблица DUAL:

SELECT SQRT(10)

FROM dual;

Результат:

SQRT(10)

---------

3,1622777

Пример использования ABS: Найти модуль чисел –13, 0 и 13.

SELECT ABS(-13), ABS(0), ABS(13)
FROM dual;

Результат:

ABS(-13) ABS(0) ABS(13)

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

13 0 13

Пример использования MOD: Найти остаток от деления 18763298 на 813.

SELECT MOD(18763298, 813)
FROM dual;

Результат:

MOD(18763298,813)

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

71

Пример использования ROUND: Округление числа 268,57 с различными значениями аргумента n.

SELECT ROUND(268.57, 5), ROUND(268.57, 1), ROUND(268.57),
ROUND(268.57, -2), ROUND(268.57, -3)
FROM dual;

Результат:

ROUND(268.57,5) ROUND(268.57,1) ROUND(268.57) ROUND(268.57,-2) ROUND(268.57,-3)

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

268,57 268,6 269 300 0

Пример использования TRUNC: Усечение числа 268,57 с различными значениями аргумента n.

SELECT TRUNC(268.57, 5), TRUNC(268.57, 1), TRUNC(268.57),
TRUNC(268.57, -2), TRUNC(268.57, -3)
FROM dual;

Результат:

TRUNC(268.57,5) TRUNC(268.57,1) TRUNC(268.57) TRUNC(268.57,-2) TRUNC(268.57,-3)

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

268,57 268,5 268 200 0

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

SELECT last_name, salary

FROM s_emp

WHERE MOD(salary,100)=0;

Результат:

LAST_NAME SALARY

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

Velasquez 2500

Nagayama 1400

Urguhart 1200

Biri 1100

Catchpole 1300

Magee 1400

Maduro 1400

Nozaki 1200

Chang 800

Schwartz 1100

10 rows selected.

Пример использования однострочной функции в предложении ORDER BY: Вывести фамилии и зарплаты служащих для всех служащих. Результат отсортировать по остатку от деления зарплаты на 100.

SELECT last_name, salary

FROM s_emp

ORDER BY MOD(salary,100);

Результат:

LAST_NAME SALARY

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

Velasquez 2500

Nagayama 1400

Biri 1100

Magee 1400

Maduro 1400

Chang 800

Schwartz 1100

Nozaki 1200

Catchpole 1300

Urguhart 1200

Havel 1307

Sedeghi 1515

Nguyen 1525

Smith 940

Ngao 1450

Menchu 1250

Quick-To-See 1450

Markarian 850

Newman 750

Dumas 1450

Ropeburn 1550

…

25 rows selected.

Символьные функции

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



Функция
Описание

ASCII(char)
Возвращает ASCII-код первого символа строки char в наборе символов базы данных.

CHR(n)
Возвращает символ, имеющий двоичный код n в наборе символов базы данных.

CONCAT(char1, char2)
Возвращает строку char1, сцепленную со строкой char2. Эта функция эквивалентна оператору конкатенации (||).

INITCAP(char)
Возвращает строку char, каждое слово которой начинается с прописной буквы, а остальные буквы строчные.

INSTR(char1, char2[, n[,m]])
Просматривает строку char1, начиная с ее n-го символа, отыскивает m-е вхождение подстроки char2, и возвращает позицию первого символа в этом вхождении относительно начала строки char1. Если n отрицательно, то Oracle отсчитывает и просматривает в обратном направлении, начиная с конца строки char1. Значение m должно быть положительным. По умолчанию оба значения m и n равны 1, т.е. Oracle отыскивает первое вхождение подстроки char2, начиная поиск с первого символа char1. Возвращаемое значение всегда вычислено относительно начала строки char1, независимо от значения n, и выражено в символах. Если поиск безуспешен (т.е. если подстрока char2 не встречается m раз после n-го символа строки char1), то возвращается 0.

LENGTH(char)
Возвращает длину строки char в символах. Если char имеет тип данных CHAR, то эта длина включает все хвостовые пробелы. Если строка char пуста, то возвращается NULL.

LOWER(char)/ UPPER(char)
Возвращает строку char, все буквы которой строчные/прописные.

LPAD(char1, n[, char2])/ RPAD(char1, n[, char2])
Возвращает строку char, дополненную слева/справа до n символов цепочками символов char2. Если char2 опущено, подразумевается пробел. Если char1 длиннее, чем n, то возвращаются первые n символов строки char1.

SUBSTR(char, m[, n])
Возвращает часть строки char, начиная с символа с номером m и длиной n символов. Если m положительно, Oracle отсчитывает символы от начала строки char, если m отрицательно - от конца строки char. Значение m не может быть нулевым. Если n опущено, возвращаемая часть строки продолжается до конца char. Значение n не может быть меньше 1.



Примеры использования символьных функций

Пример использования LOWER, UPPER, INITCAP:

SELECT UPPER(last_name), LOWER(first_name), INITCAP(title)

FROM s_emp;

Результат:

UPPER(LAST_NAME) LOWER(FIRST_NAME) INITCAP(TITLE)

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

VELASQUEZ carmen President

NGAO ladoris Vp, Operations

NAGAYAMA midori Vp, Sales

QUICK-TO-SEE mark Vp, Finance

ROPEBURN audry Vp, Administration

URGUHART molly Warehouse Manager

…

25 rows selected.

Пример использования CONCAT: Для каждого служащего вывести его фамилию и имя через пробел в одном столбце с названием ‘employee’.

SELECT CONCAT(CONCAT(first_name,’ ‘), last_name) employee

FROM s_emp;

Результат:

EMPLOYEE

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

Carmen Velasquez

LaDoris Ngao

Midori Nagayama

Mark Quick-To-See

Audry Ropeburn

Molly Urguhart

Roberta Menchu

Ben Biri

…

25 rows selected.

Пример использования LENGTH: Вывести названия отделов, в которых больше 10 символов:

SELECT name

FROM s_dept

WHERE LENGTH(name)>10;

Результат:

NAME

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

Administration

Пример использования LPAD, RPAD:

SELECT LPAD('table', 15, '!?'), RPAD('table', 15, '!?'),

LPAD('table', 3, '!?'), RPAD('table', 3, '!?')

FROM dual;

Результат:

LPAD('TABLE',15 RPAD('TABLE',15 LPA RPA

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

!?!?!?!?!?table table!?!?!?!?!? tab tab

Пример использования INSTR:

SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 2) "Instring",

INSTR('CORPORATE FLOOR', 'OR', -3, 2) "Reversed Instring"

FROM dual;

Результат:

Instring Reversed Instring

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

14 2

Функции для работы с датами

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

С датами можно производить такие арифметические операции, как сложение и вычитание. При этом прибавлять и вычитать можно не только даты, но и числовые константы:

Операция
Результат
Описание

дата + число
Дата
Прибавление количества дней к дате.

дата - число
Дата
Вычитание количества дней из даты.

дата - дата
Число
Вычитание одной даты из другой, результат – количество дней между датами.

дата + число/24
Дата
Прибавление часов к дате.

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

Функция
Описание

ADD_MONTHS(дата, n)
Возвращает заданную дату, увеличенную на n месяцев. Аргумент n может быть любым целым числом. Если заданная дата попадает на последний день месяца, или если результирующий месяц имеет меньше дней, чем компонента дня заданной даты, то результирующий день будет последним днем результирующего месяца. В противном случае результирующий день остается таким же, как компонента дня заданной даты.

LAST_DAY(дата)
Возвращает дату последнего дня месяца, содержащего указанную дату.

MONTHS_BETWEEN
(дата1, дата2)
Возвращает количество месяцев между датой1 и датой2. Если дата1 - более поздняя дата, чем дата2, то результат положителен; в противном случае – отрицателен. Если дата1 и дата2 попадают на одинаковые дни своих месяцев или на последние дни своих месяцев, то результат будет целым числом; в противном случае Oracle вычисляет дробную часть результата.

NEXT_DAY(дата, символ)
Возвращает дату первого дня недели, имеющего имя символ и следующего за заданной датой. Аргумент символ может задавать порядковый номер или название дня недели.

ROUND(дата[, fmt])
Возвращает заданную дату, округленную к единицам, заданным моделью формата fmt. Если fmt опущена, то дата округляется до ближайшего дня на момент полуночи.

SYSDATE
Возвращает текущие дату и время, не требует аргументов.

TRUNC(дата[, fmt])
Возвращает заданную дату, в которой порция времени усечена к единицам, заданным моделью формата fmt. Если fmt опущена, то дата округляется до ближайшего дня на момент полуночи.

Описание моделей формата дат будет приведено далее в этой же лекции.

Примеры использования функций для работы с датами

Пример использования SYSDATE: Получить сегодняшнюю дату:

SELECT SYSDATE

FROM dual;

Результат:

SYSDATE

--------

06.10.03

В дальнейших примерах считать, что сегодняшняя дата: 06 октября 2003 года.

Пример использования арифметических операторов: Узнать, какое число было 10 дней назад и будет через 18 дней:

SELECT SYSDATE-10, SYSDATE+18

FROM dual;

Результат:

SYSDATE-10 SYSDATE+18

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

26.09.03 24.10.03

Пример использования ADD_MONTHS: Узнать, какое число будет через 9 месяцев:

SELECT ADD_MONTHS(SYSDATE, 9)

FROM dual;

Результат:

ADD_MONT

--------

06.07.04

Пример использования LAST_DAY: Какой день является последним днем текущего месяца и февраля 2005 года:

SELECT LAST_DAY(SYSDATE), LAST_DAY('01-02-2005')

FROM dual;

Результат:

LAST_DAY LAST_DAY

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

31.10.03 28.02.05

Пример использования MONTHS_BETWEEN: Сколько месяцев между текущим месяцем, 1 январем 2003 года, 6 январем 2004 года и 6 октябрем 2005 года:

SELECT MONTHS_BETWEEN(SYSDATE,'01-01-2003') one,

MONTHS_BETWEEN(SYSDATE,'06-01-2004') two,

MONTHS_BETWEEN(SYSDATE,'06-10-2005') three

FROM dual;

Результат:

ONE TWO THREE

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

9,1821658 -3 -24

Пример использования NEXT_DAY: На какой день приходится ближайший понедельник после сегодняшней даты:

SELECT NEXT_DAY(SYSDATE,1) one,

NEXT_DAY(SYSDATE,'mon') two,

NEXT_DAY(SYSDATE,'monday') three

FROM dual;

Результат:

ONE TWO THREE

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

13.10.03 13.10.03 13.10.03

-- День недели можно задать с помощью любой из моделей формата даты,
-- возвращающей день недели (см. далее таблицу моделей форматов). В данном -- примере в первом столбце день недели задается с помощью модели d, во -- втором – DY, в третьем – DAY. Обратите внимание, если Вы задаете день -- недели с помощью модели d, Вы должны знать, с какого дня производится -- нумерация дней недели Вашим сервером. Нумерация может производиться с -- понедельника (русская версия) или с воскресенья (американская версия).

Пример использования ROUND:

SELECT ROUND(to_date('27-09-2003','dd-mm-yyyy'),'year'),

ROUND(to_date('27-09-2003','dd-mm-yyyy'),'month'),

ROUND(to_date('27-09-2003','dd-mm-yyyy'),'day')

FROM dual;

Результат:

ROUND(TO ROUND(TO ROUND(TO

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

01.01.04 01.10.03 28.09.03

-- Об использовании функции to_date будет рассказано далее в этой же лекции.


Пример использования TRUNC:

SELECT TRUNC(to_date('27-09-2003','dd-mm-yyyy'),'year'),

TRUNC(to_date('27-09-2003','dd-mm-yyyy'),'month'),

TRUNC(to_date('27-09-2003','dd-mm-yyyy'),'day')

FROM dual;

Результат:

TRUNC(TO TRUNC(TO TRUNC(TO

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

01.01.03 01.09.03 22.09.03

-- Об использовании функции to_date будет рассказано далее в этой же лекции.

Функции преобразования

Функции преобразования преобразуют значения из одного типа данных в другой. В следующей таблице приведены некоторые из функций преобразования:

Функция
Описание

TO_CHAR(число|дата[, fmt])
Преобразует число или дату в символьную строку VARCHAR2 с моделью формата fmt.

TO_NUMBER(char)
Преобразует строку символов char содержащую цифры, в число.

TO_DATE(char[, fmt])
Преобразует строку символов char с датой в значение типа DATE в соответствии с заданной моделью fmt.

Модели формата

Модель формата – это символьный литерал, который описывает формат значения типа DATE или NUMBER, представленного в символьном виде и хранящегося в строке символов. Модель формата в любом случае заключается в апострофы. Вы можете использовать модель формата, чтобы указать, в каком формате Oracle должен возвратить вам значение из базы данных или в каком формате Вы предоставляете значение для помещения его в базу данных. Например, Вы можете пожелать, чтобы дата при выборке выводилась в виде: “13 October of 2003” или “2003, October, 13”. Заметьте, что модель формата не изменяет внутреннего представления значения в базе данных.

Модели формата даты и времени

Модель формата
Описание

SCC или CC
Столетие. “S” означает, что даты до н.э. получают префикс “-”.

YYYY или SYYYY
Год четырьмя цифрами. ”S” означает, что даты до н.э. получают префикс “-”.

YYY или YY или Y
Последние 3, 2 или 1 цифра года.

Y,YYY
Год с запятой в указанной позиции.

SYEAR или YEAR
Год словами. “S” означает, что годы до н.э. получают префикс “-”.

BC или AD
Индикатор “до н.э./н.э.”.

B.C. или A.D.
Индикатор “до н.э./н.э.” с точками.

Q
Квартал.

ММ
Месяц в виде двузначного числа.

MONTH
Название месяца, дополненное конечными пробелами до 9 символов.

MON
Трехбуквенное сокращение название месяца.

RM
Номер месяца римскими цифрами.

WW или W
Неделя года или месяца.

DDD или DD или D
День года, месяца или недели.

DAY

Название дня недели, дополненное конечными пробелами до 9 символов.

DY
Трехбуквенное сокращение название дня недели.

AM или PM
Индикатор “до полудня/после полудня”.

A.M. или P.M.
Индикатор “до полудня/после полудня ” с точками.

HH или HH12 или HH24
Время суток, час в 12-часовом диапазоне или час в 24-часовом диапазоне.

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

SS
Секунды (0-59).

SSSSS
Количество секунд после полуночи (0-86399.)

При выводе дат словами надо учитывать, что слова будут выводиться в том же регистре, что и модель формата. Например, если Вы укажете модель формата ‘Month’, то название месяца будет выводиться как ‘October’, если Вы укажете ‘month’, то – ‘october’, если Вы укажете ‘MONTH’, то – ‘OCTOBER’.

Модели формата числа

В примерах приведен результат запроса: SELECT TO_CHAR(-1234.567, ‘fmt’) FROM dual;

Модель формата

Описание

Пример

Результат

9
Числовая позиция (число девяток определяет ширину поля вывода)
999999
999
-1235
####

0
Вывод ведущих нулей
099999
-001235

$
Плавающий знак доллара
$999999
-$1235

L

Плавающий знак местной валюты
L999999
-F1234

.
Задает наличие в данной позиции десятичной точки
999999.99
999999.9999
-1234.57
-1234.5670

,
Ставит запятую в заданной позиции
999,999
-1,235

MI
Знак минус справа (для отрицательных значений)
999999MI
1235-

PR
Заключение отрицательных значений в скобки
999999PR
<1235>

EEEE
Научный формат (должен быть задан четырьмя символами ‘E’)
99.999EEEE
-1.235E+03

V
Умножить на 10 n раз (n = число символов “9” после V)
9999V99
-123457



Суффиксы, префиксы и другие элементы, влияющие на формат вывода

Задавая модель формата можно использовать знаки пунктуации (/ . ,), а также символьные константы (заключенные в двойные кавычки), которые будут включены в результат. Кроме того, можно использовать:

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

Суффикс TH
Для вывода порядковых числительных (например, DDTH для вывода в формате “4th”).

Суффикс SP
Для вывода чисел словами (например, DDSP для вывода слова “FOUR”)

Сочетание суффиксов SP и TH:
SPTH или THSP
Для вывода порядковых числительных словами (например, DDSPTH или DDTHSP для вывода слова “FOURTH”).



Примеры использования функций преобразования и моделей форматов

Примеры использования TO_CHAR для преобразования дат: Чаще всего функция TO_CHAR используется для преобразования даты при выводе. Дело в том, что Oracle выводит даты при выборке в формате, установленном по умолчанию. Это может быть, например, формат ‘dd-mm-yy’, ‘dd-mon-yy’ или ‘dd.mm.yy’. Как правило, формат, используемый по умолчанию, не содержит информации о времени, квартале месяца, столетии и пр. На практике часто возникает необходимость вывести более полную информацию, задав необходимый формат.

SELECT TO_CHAR(SYSDATE,'dd - mm - yyyy - hh24 - mi - ss') data

FROM dual;

Результат:

DATA

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

06 - 10 - 2003 - 17 - 05 - 49




SELECT TO_CHAR(SYSDATE,'dd "of" Month "year" yyyy') data

FROM dual;

Результат:

DATA

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

06 of October year 2003

-- В данном примере число состоит из одной цифры, поэтому перед ним
-- выводится ведущий 0, так как в модели формата указано, что число надо -- выводить двумя цифрами. Кроме того, модель ‘Month’ предполагает, что -- название месяца дополняется конечными пробелами до 9 символов. Чтобы -- убрать ведущие нули и конечные пробелы, используйте префикс FM.




SELECT TO_CHAR(SYSDATE,'fmdd "of" Month "year" yyyy') data

FROM dual;

Результат:

DATA

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

6 of October year 2003




SELECT TO_CHAR(SYSDATE,'fmDAY : MONTH : YEAR') data

FROM dual;

Результат:

DATA

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

MONDAY : OCTOBER : TWO THOUSAND THREE

Пример использования TO_CHAR для преобразования чисел:

SELECT salary, TO_CHAR(salary, '099999'), TO_CHAR(salary, '999'),

TO_CHAR(salary, '$99999'), TO_CHAR(salary, '99999V999')

FROM s_emp;

Результат:

SALARY TO_CHAR TO_C TO_CHAR TO_CHAR(S

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

2500 002500 #### $2500 2500000

1450 001450 #### $1450 1450000

1400 001400 #### $1400 1400000

1450 001450 #### $1450 1450000

…

25 rows selected

-- Символы # выводятся в случае, если количество цифр в числе превышает
-- количество цифр, предусмотренное моделью формата.

Пример использования TO_CHAR в предложении WHERE: Вывести фамилии служащих, которые были приняты на работу в понедельник.

SELECT last_name

FROM s_emp

WHERE TO_CHAR(start_date, 'dy')='mon';

Результат:

LAST_NAME

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

Nagayama

Menchu

Magee

Sedeghi

Примеры использования TO_DATE: Если Вы хотите привести в запросе конкретную дату, необходимо заключить ее в апострофы. Но сервер Oracle расценит ее как дату только в случае, если заданная дата приводится в соответствующем контексте и если ее формат совпадает с одним из форматов, используемых сервером по умолчанию. В противном случае, заданная дата может быть расценена как символьное значение, что может привести к ошибкам. Чтобы их избежать, рекомендуется всегда при задании дат в запросах пользоваться функцией TO_DATE, чтобы явно указать серверу, что вводимое Вами значение является датой.

SELECT last_name

FROM s_emp

WHERE start_date='05/09/1991';

Результат:

no rows selected

-- Данным запросом пользователь хотел найти служащих, принятых на работу 9 -- мая 1991 года. Сервер воспринимает введенное значение, как 5 сентября -- 1991года, поэтому не вернул ни одной строки. Тот же пример, но с
-- указанием модели формата:

SELECT last_name

FROM s_emp

WHERE start_date=to_date('05/09/1991','mm/dd/yyyy');

Результат:

LAST_NAME

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

Schwartz




SELECT ROUND('27-09-2003', 'year')

FROM dual;

Результат:

ERROR at line 1:

ORA-01722: invalid number

-- Функция ROUND может применяться как для округления чисел, так и для
-- округления дат. По умолчанию Oracle пытается произвести округление числа, -- но так как округляемое значение заключено в апострофы, его нельзя
-- расценить как число, что вызывает ошибку Oracle. Чтобы явно указать, что -- необходимо произвести округление даты, необходимо воспользоваться
-- функцией TO_DATE:

SELECT ROUND(TO_DATE('27-09-2003','dd-mm-yyyy'), 'year')

FROM dual;

Результат:

ROUND(TO

--------

01.01.04


Вложенные однострочные функции

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

Пример: Для каждого служащего заглавными буквами вывести первую букву его имени с точкой и фамилию в одном столбце. Столбец назвать employees.

SELECT

UPPER(CONCAT(CONCAT(SUBSTR(first_name,1,1),’.’), last_name)) employees

FROM s_emp;

Результат:

EMPLOYEES

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

C.VELASQUEZ

L.NGAO

M.NAGAYAMA

M.QUICK-TO-SEE

A.ROPEBURN

M.URGUHART

R.MENCHU

B.BIRI

A.CATCHPOLE

M.HAVEL

C.MAGEE

H.GILJUM

…

25 rows selected.

Прочие однострочные функции

DECODE

Синтаксис:

DECODE (выражение, значение1, результат1[, значение2, результат2, …] [значение_по_умолчанию])

Чтобы вычислить значение этой функции, Oracle сравнивает выражение с каждым значением. Если выражение совпадает с некоторым значением, Oracle возвращает соответствующий результат. Если ни одного совпадения не найдено, Oracle возвращает значение_по_умолчанию, или NULL, если значение_по_умолчанию опущено. Максимальное количество аргументов DECODE, включая выражение, значение_по_умолчанию и все значения и результаты, равно 255.

Пример: Вывести текущий размер суммарной зарплаты всех служащих, а также размер суммарной зарплаты в случае, если всем торговым представителям (Sales Representative) повысить зарплату до 2000, а всем клеркам (Stock Clerk) – до 1500.
В решении используется групповая функция SUM, описанная в Лекции 8. В первом выходном столбце суммируются текущие зарплаты служащих, во втором столбце суммирование производится в зависимости от значения столбца title. Если значение title – ‘Sales Representative’ прибавляется 2000, если – ‘Stock Clerk’, прибавляется 1500, в противном случае – прибавляется текущее значение зарплаты salary.

SELECT SUM(salary) old,

SUM(DECODE(title, 'Sales Representative', 2000,

'Stock Clerk', 1500, salary)) new

FROM s_emp;

Результат:

OLD NEW

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

31377 39507

GREATEST

Синтаксис:

GREATEST(выражение[, выражение] ...)

Возвращает наибольшее значение из списка выражений.

Пример:

SELECT GREATEST('Harry', 'Harriot', 'Harold')

FROM dual;

Результат:

GREAT

-----

Harry

LEAST

Синтаксис:

LEAST(выражение[, выражение] ...)

Возвращает наименьшее значение из списка выражений.

Пример:

SELECT LEAST('Harry', 'Harriot', 'Harold')

FROM dual;

Результат:

LEAST(

------

Harold

NVL

Синтаксис:

NVL (выражение1, выражение2)

Если выражение1 равно NULL, функция возвращает выражение2, в противном случае – выражение1.

Пример: Для каждой фирмы-клиента вывести ее номер и комментарии. В случае, если комментарии отсутствуют вывести фразу ‘no comments’.

SELECT id, NVL(comments, 'no comments') comments

FROM s_customer;

Результат:

ID COMMENTS

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

201 Customer usually orders large amounts and has a high or

der total. This is okay as long as the credit rating r

emains excellent.


202 Customer should always pay by cash until his credit rat

ing improves.


203 Customer specializes in baseball equipment and is the l

argest retailer in India.


204 no comments

205 no comments

…

15 rows selected.



Л екция 4. Язык SQL. Однострочные функции.