ФУНКЦИИ ORACLE

4.1. Определение текущей даты и времени, системная переменная SYSDATE

Пример:

SELECT SYSDATE FROM DUAL;

SYSDATE можно использовать в условиях WHERE, в операторах INSERT, DELETE и др.

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

1) TO_CHAR(number[,format]) – преобразование числа в эквивалентное строковое представление;

Пример:

SELECT TO_CHAR(17145,'$099,999') ‘CHAR’ FROM DUAL;

Результат

Char

$017,145

2) TO_CHAR(date[,format]) – преобразование типа дата/время в символьный формат;

Пример:

SELECT TO_CHAR(HIREDATE,'Month DD,YYYY') "New data format"

FROM EMP WHERE ENAME = 'SMITH';

New date format

December 17, 1980

3) TO_DATE(string[,format]) – преобразует символьную строку в дату Oracle.

Пример:

INSERT INTO BONUS (BONUS_DATE)

SELECT TO_DATE('January 15, 1989', 'Month dd, YYYY') FROM DUAL

4) TO_NUMBER(char) - Преобразовывает char, являющийся символьным представлением числа в значение типа NUMBER.

Пример:

UPDATE EMP SET SAL = SAL + TO_NUMBER(SUBSTR('$100 raise',2,3))

WHERE ENAME = 'BLAKE'

5) MONTHS_BETWEEN(date1,date2) – определяет число месяцев между двумя датами;

4.3. Календарные функции

1) ADD_MONTHS(date,number) – возвращает дату, отстоящую от даты, переданной в параметре date на указанное количество месяцев;

Пример:

SELECT ADD_MONTHS(HIREDATE,12) "Next year" FROM EMP

WHERE ENAME = 'SMITH'

Next year

17-DEC-81

2) ROUND(date[,format]) – округляет переданную дату до ближайшего заданного в параметре format элемента формата;

Пример:

SELECT ROUND(TO_DATE('27-OCT-88'),'YEAR') ‘FIRST OF THE YEAR’

FROM DUAL

FIRST OF THE YEAR

01-JAN-89

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

1) ABS(number) – абсолютное значение;

2) COS(number), COSH(),SIN(), SINH(), TAN(), TANH() – тригонометрические функции;

3) LN(number), LOG(base,number) _ логарифмические функции;

4) MOD(m,n) – остаток от деления m на n;

5) POWER(x,y) - x^y;

6) ROUND(number[,decimal_digits]) – округляет number до decinal_digits, десятичных знаков.

7) SIGN(number) - =1 если number>0, =-1 если number<0, =0 если number=0;

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

1) ASCII(character) – возвращает значение десятичного кода символа, в кодировке базы данных;

2) CHR(number) – возвращает символ, соответствующий значению ASCII–кода;

3) INITCAP(string) – преобразует первый символ каждого слова в верхний регистр;

4) INSTR(input_string,sub_string[,n[,m]]) – находит –е вхождение искомой строки, находящееся во входной строке , начиная с позиции n. Если строка не найдена, то функция возвращает 0, в противном случае возвращает позицию первого символа искомой строки во входной;

5) LENGTH(string) – длина строки;

6) LOWER(string)

UPPER(string)

Приводит строку к нижнему и верхнему регистру соответственно.

6) LPAD(string, n[, pad_chars])

RPAD(string, n[, pad_chars])

Если не указан параметр pad_chars – дополняет входную строку string пробелами до длины n слева и справа соответственно. В противном случае для дополнения строки до длины n используются символы заполнения pad_chars. Если строка string длиннее n символов, она обрезается слева и справа до длины n.

7) LTRIM (string[, set_of_chars])

RTRIM (string[, set_of_chars])

Убирает начальные и завершающие символы, входящие в набор set_of_chars. Если этот набор символов не указан, то удаляются пробелы.

8) SUBSTR(string,start[,length]) – выделяет подстроку длиной length из входной строки string, начиная с позиции start.

9) TRANSLATE(string,search_set,replace_set) – заменяет в строке string каждый символ из набора search_set на соответствующий символ из набора символов replace_set.

SELECT TRANSLATE(to_char(sysdate,’dd month year’), ‘0123456789abcd…xyz’,’@@@@...@##...##’) Result from dual;

4.6. Универсальные функции

1) NVL(expression,replace_value) – возвращает значение выражения, если оно не является пустым. Если же оно NULL, то результатом будет значение replace_value.

2) DECODE(expression,val1,trans1,val2,trans2,…,valn,transn,default) -

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

Ее можно использовать для преобразования входного значения в более информативный вид:

SELECT empno, ename, DECODE(job, 'CLERK', 'Клерк', 'SALESMAN', 'Продавец', 'MANAGER¹, 'Менеджер 'PRESIDENT, 'Президент', job) job FROM emp;

EMPNO ENAME JOB

Как видно, аналитик не подвергся обработке и попал в результи­рующие данные в том виде, в каком был извлечен из таблицы ЕМР.

Еще одно применение функции DECODE - определение диапа­зонов значений. Для этого необходимо также использовать функ­цию SIGN (для сравнения чисел). Предположим, что в организа­ции надбавка к зарплате зависит от стажа работы (см. табл. 5.6).

Таблица 5.6 Зависимость надбавки к зарплате от стажа работы

В таком случае надбавка может быть получена следующим за­просом:

SELECT ename, hiredate,

DECODE(SIGN(MONTHS_BETWEEN (SYSDATE, hiredate)-12), -1, 0, DECODE(SIGN(MONTHS_BETWEEN (SYSDATE, hiredate)-60), -1, 10, DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, hiredate)-180), -1, 20, 30))) надбавка FROM EMP;

Часто функцию DECODE применяют в предложении ORDER BY, если простая сортировка по столбцу не может обеспечил требуемого упорядочивания данных. Например, нужно выбрав всех сотрудников из таблицы ЕМР и отсортировать данные по должностям в следующем порядке:

PRESIDENT, MANAGER. CLERK, ANALYST, SALESMAN.

Выполнить эту задачу может сле­дующий запрос:

SELECT EMPNO, ENAME, JOB FROM emp

ORDER BY DECODE(job, 'PRESIDENT, 1, 'MANAGER’, 2, 'CLERK', 3, 'ANALYST’, 4, 'SALESMAN', 5, 99), ename;