|
|
бд полный курс. Информация, данные, информационные системы Информация как социальный ресурс
Встроенные функции SQL и их использование в запросах
Арифметические функции
SQL поддерживает полный набор арифметических операций и математических функций для построения арифметических выражений над колонками базы данных ( +, -, *, /, ABS, LN, SQRT и т.д.). Список основных встроенных математических функций дан ниже в таблице 8.2.
Таблица 8.2. Математические функции SQL
|
Математическая функция
|
Описание
|
ABS(X)
|
Возвращает абсолютное значение числа Х
|
ACOS(X)
|
Возвращает арккосинус числа Х
|
ASIN(X)
|
Возвращает арксинус числа Х
|
ATAN(X)
|
Возвращает арктангенс числа Х
|
COS(X)
|
Возвращает косинус числа Х
|
EXP(X)
|
Возвращает экспоненту числа Х
|
SIGN(X)
|
Возвращает -1, если Х<0,0, если Х=0, +1, если Х>0
|
LN(X)
|
Возвращает натуральный логарифм числа Х
|
MOD(X,Y)
|
Возвращает остаток от деления Х на Y
|
CEIL(X)
|
Возвращает наименьшее целое, большее или равное Х
|
ROUND(X,n)
|
Округляет число Х до числа с n знаками после десятичной точки
|
SIN(X)
|
Возвращает синус числа Х
|
SQRT(X)
|
Возвращает квадратный корень числа Х
|
TAN(X)
|
Возвращает тангенс числа Х
|
FLOOR(X)
|
Возвращает наибольшее целоеб меньшее или равное Х
|
LOG(a,X)
|
Возвращает логарифм числа Х по основанию А
|
SINH(X)
|
Возвращает гиперболический синус числа Х
|
COSH(X)
|
Возвращает гиперболический косинус числа Х
|
TANH(X)
|
Возвращает гиперболический тангенс числа Х
|
TRANC(X,n)
|
Усекает число Х до числа с n знаками после десятичной точки
|
|
|
POWER(A,X)
|
Возвращает значение А, возведенное в степень Х
|
Набор встроенных функций может изменяться в зависимости от версии СУБД одного производителя и также в СУБД различных производителей. Так, например, в СУБД SQLBase, Centure Inc. есть функция @ATAN2(X,Y), которая возвращает арктангенс Y/X, но отсутствует функция SIGN(X).
Арифметические выражения необходимы для получения данных, которые непосредственно не сохраняются в колонках таблиц базы данных, но значения которых необходимы пользователю. Допустим, что вам необходим список служащих, показывающий выплату, которую получил каждый служащий с учетом премий и штрафов.
SELECT ENAME, SAL, COMM, FINE, SAL + COMM - FINE
FROM EMPLOYEE
ORDER BY DEPNO;
Арифметическое выражение SAL + COMM - FINE выводится как новая колонка в результирующей таблице, которая вычисляется в результате выполнения запроса. Такие колонки называют еще производными (вычисляемыми) атрибутами или полями.
Функции обработки строк
SQL предоставляет вам широкий набор функций для манипулирования со строковыми данными (конкатенация строк, CHR, LENGTH, INSTR и другие). Список основных функций для обработки строковых данных приведен в таблице 8.3.
Таблица 8.3. Функции SQL для обработки строк
|
Функция
|
Описание
|
CHR(N)
|
Возвращает символ ASCII кода для десятичного кода N
|
ASCII(S)
|
Возвращает десятичный ASCII код первого символа строки
|
INSTR(S2.S1.pos[,N]
|
Возвращает позицию строки S1 в строке S2 большую или равную pos.N - число вхождений
|
LENGHT(S)
|
Возвращает длину строки
|
LOWER(S)
|
Заменяет все символы строки на прописные символы
|
INITCAP(S)
|
Устанавливает первый символ каждого слова в строке на заглавный, а остальные символы каждого слова - на прописные
|
SUBSTR(S,pos,[,len])
|
Выделяет в строке S подстроку длиной len, начиная с позиции pos
|
UPPER(S)
|
Преобразует прописные буквы в строке на заглавные буквы
|
LPAD(S,N[,A])
|
Возвращает строку S, дополненную слева симолами A до числа символов N. Символ - наполнитель по умолчанию - пробел
|
Rpad(S,N[,A])
|
Возвращает строку S, дополненную справа симолами A до числа символов N. Символ - наполнитель по умолчанию - пробел
|
LTRIM(S,[S1])
|
Возвращает усеченную слева строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел)
|
RTRIM(S,[S1])
|
Возвращает усеченную справа строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку - шаблон S1 (по умолчанию - пробел
|
TRANSLATE(S,S1,S2)
|
Возвращает строку S, в которой все вхождения строки S1 замещены строкой S2. Если S1 <> S2, то символы, которым нет соответствия, исключаются из результирующей строки
|
REPLACE(S,S1,[,S2])
|
Возвращает строку S, для которой все вхождения строки S1 замещены на подстроку S2. Если S2 не указано, то все вхождения подстроки S1 удаляются из результирующей строки
|
NVL(X,Y)
|
Если Х есть NULL, то возвращает в Y либо строку, либо число, либо дату в зависимости от исходного типа Y
|
Названия одних и тех же функций могут отличаться в различных СУБД. Так, например, функция СУБД Oracle SUBSTR(S, pos, [, len]) в СУБД SQLBase называется @SUBSTRING(S, pos, len). В СУБД SQLBase имеются функции, которых нет в СУБД Oracle (см. таблицу ниже, где приведен список таких функций).
Таблица 8.4. Строковые функции СУБД SQLBase, отличающиеся от строковых функций СУБД Oracle
|
Функция
|
Описание
|
@EXACT(S1,S2)
|
Возвращает результат сравнения двух строк
|
@LEFT(S,len)
|
Возвращает левую подстроку длиной len
|
LENGTH(S)
|
Возвращает длину строки
|
@MID(S, pos, len)
|
Возвращает подстроку указанной длины, начиная с позиции pos
|
@REPEAT(S,n)
|
Повторяет строку S n раз
|
@REPLACE(S1,pos,len,S2)
|
Замещает с позиции pos len символов в строке S2 символами строки S1
|
RIGHT(S,len)
|
Возвращает правую подстроку S длиной len
|
@SCAN(S,pat)
|
Возвращает позицию подстроки pat в строке S
|
@STRING(X,scale)
|
Возвращает символьное представление числа с указанным масштабом scale
|
@TRIM(S)
|
Удаляет пробелы в строке справа и слева
|
@VALUE(S)
|
Преобразует символьное представление числа в числовое значение
|
Можно использовать функцию INITCAP, чтобы при получении списка имен служащих фамилии всегда начинались с заглавной буквы, а все остальные были прописными.
SELECT INITCAP(ENAME)
FROM EMPLOYEE
ORDER BY DEPNO;
Специальные функции
SQL обеспечивает набор специальных функций для преобразований значений колонок. Список таких функций приведен в таблице 8.5.
Таблица 8.5. Специальные функции
|
Функция
|
Описание
|
DECODE(E,S1,R1,S2,R2,…,[def])
|
Если E соответствует Si, то возвращается Ri, в противном случае - def или NULL, если умолчание не задано
|
TO_NUMBER(S)
|
Возвращает результат преобразования строки S в аргумент типа NUMBER
|
TO_CHAR(X[,F])
|
Возвращает результат преобразования строки S в аргумент типа DATE согласно заданному формату даты F
|
TO_DATE(S[,F])
|
Возвращает результат преобразования значения параметра S символьного типа в тип DATE
|
В таблице EMPLOYEE для каждого служащего можно ввести признак пола - добавить колонку SEX типа CHAR(1) (0 - мужской, 1 - женский). Допустим, что вам нужен список служащих, в котором требуется разделение их по признаку пола с указанием его в числовом формате; тогда можно задать такую команду:
SELECT ENAME, LNAME, AGE, 'Пол:', TO_NUMBER(SEX)
FROM EMPLOYEE
ORDER BY 5;
В качестве примера использования функции DECODE приведем запрос, вычисляющий список служащих с указанием их руководителя. Если руководитель неизвестен, то выводится по умолчанию "не имеет".
SELECT ENAME, DECODE(DEPNO, 10, 'Дрягин', 20,'Жиляева', 30,'
Коротков', 'не имеет')
FROM EMPLOYEE
ORDER BY ENAME;
Предположим, что руководитель организации имеет неопределенное значение колонки DEPNO и, следовательно, для него будет работать умолчание, предусмотренное в DECODE.
Функции для обработки даты
В диалекте SQL СУБД Oracle имеется небольшой набор функций для манипулирования колонками с типом date. Список основных функций обработки даты и времени приведен в таблице 8.6.
Таблица 8.6. Функции обработки даты и времени
|
Функция
|
Описание
|
SYSDATE
|
Возвращает текущую дату и время
|
ROUND(D[,F])
|
Округляет значение даты D согласно заданному шаблону
|
TRANC(D[,F])
|
Усекает значение даты D согласно заданному шаблону
|
NEXT_DAY(D,S)
|
Возвращает дату дня, который является первым днем, более поздним, чем текущая дата с названием S
| |
|
|
Скачать 1.56 Mb.