Приложение 2. Функции Oracle

1. Системная переменная SYSDATE.

Для определения текущей даты и времени используется системная переменная SYSDATE.

Пример:

·     SELECT SYSDATE FROM DUAL.

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

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) – определяет число месяцев между двумя датами.

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. Числовые функции.

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.

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) – приводит строку к нижнему и верхнему регистру соответственно;

7) LPAD(string, n[, pad_chars]), RPAD(string, n[, pad_chars]) – если не указан параметр pad_chars – дополняет входную строку string пробелами до длины n слева и справа соответственно. В противном случае для дополнения строки до длины n используются символы заполнения pad_chars. Если строка string длиннее n символов, она обрезается слева и справа до длины n;

8) LTRIM (string[, set_of_chars]), RTRIM (string[, set_of_chars]) – убирает начальные и завершающие символы, входящие в набор set_of_chars. Если этот набор символов не указан, то удаляются пробелы;

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

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

Пример:

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

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', 'Продавец', 'MANAGER1, 'Менеджер 'PRESIDENT, 'Президент', job) job FROM emp;

EMPNO ENAME     JOB

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

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

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

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