|
|
ГЛАВА 3 ОПЕРАЦИИ И ФУНКЦИИ
В этой главе описываются методы обработки отдельных элементов данных.
Например, описываются такие стандартные арифметические операции как сложение
и вычитание, а также более редкие функции, как абсолютное значение или длина
символьной строки. Глава содержит следующие разделы:
* операции
* функции
* модели форматов
* выражения
* условия
Операции
Операции и функции выполняют сходные задачи; они используют ноль или более
аргументов и возвращают один или более результатов.
Операции отличаются от функций тем, что представляются специальными
символами, в то время как функции используют следующий формат:
function_name(argument1, argument2, ...)
Большинство операций используют максимально 2 аргумента, структура же
функций позволяет использовать три и более.
В каждой приводимой ниже таблице операторы приводятся в порядке убывания стар-
шинства. В нашем обсуждении старшинство - это порядок, в котором операции
выполняются. Например, в выражении 1+2*3 сперва 2 умножается на три
(результат 6), а затем результат прибавляется к 1 (получая 7), так как
умножение выполняется перед сложением. В зависимости от порядка выполнения
(т.е. старшинства) ответ может быть 8 или 9. Операторы с одинаковым
старшинством группируются вместе.
Арифметические операции
Арифметические операции используются в выражениях для отрицания, сложения,
вычитания, умножения и деления числовых величин. Результат арифметической
операции имеет также числовое значение. Некоторые из этих операций
используются и для работы с датами. В следующей таблице представлены
арифметические операции в порядке убывания старшинства:
Операция Выполняемая функция Пример
------------------------------------------------------------
() Меняет нормальный порядок SELECT (X+Y)/(X_Y)...
выполнения операций. Опе-
рации внутри скобок выпо-
лняются в первую очередь.
+ - Означает положительное или ...WHERE QTYSOLD=-1
отрицательное выражение. ...WHERE -SAL < 0
* / Умножить, разделить. SELECT 2*X+1
...WHERE X>Y/2
-- 46 --
+ - Сложить, вычесть SELECT 2*X+1
...WHERE X>Y-Z
Символьные операции
Символьные операции используются в выражениях, работающих с символьными
строками. В следующей таблице приводится единственная символьная операция:
Операция Выполняемая функция Пример
------------------------------------------------------------
|| Конкатенация (сцепление) SELECT 'NAME IS'||ENAME
символьных значений
В результате конкатенации также получается символьное значение. Символьные
строки могут быть длиной максимально 255 символов; это же ограничение
относится и к результату конкатенации символьных строк.
Операции сравнения
Операции сравнения применяются в условиях, сравнивающих одно выражение с
другим. Как и для всех условий, результат сравнения одного выражения с другим
может быть либо TRUE, либо FALSE (См. ниже раздел "Условия"). В следующей
таблице приводятся операции сравнения SQL:
Операция Выполняемая функция Пример
------------------------------------------------------------
() Меняет нормальные пра- ...NOT(A=1 OR B=1)
вила старшинства операций.
= Проверяет на равенство ...WHERE SAL=10000
!=,^=,<> Проверяет на неравенство ...WHERE SAL!=10000
> "Больше чем" и ...WHERE SAL>10000
< "меньше чем"
>= "Больше или равно" и ...WHERE SAL>=10000
<= "меньше или равно"
IN "Равен любому члену из ...WHERE JOB IN
списка" ('CLERK','ANALIST')
Эквивалентен "=ANY" ...WHERE SAL IN
(SELECT SAL FROM EMP
WHERE DEPTNO=30)
NOT IN Эквивалентен "!=ALL" ...WHERE SAL NOT IN
Результат FALSE любой (SELECT SAL FROM EMP
элемент из набора NULL WHERE DEPTNO=30)
ANY Сравнивает значение с каж- ...WHERE SAL=ANY
дым значением из списка (SELECT SAL FROM EMP
или запроса. Должен предва- WHERE DEPTNO=30)
ряться: =,!=,>,<,<=,>=.
ALL Сравнивает значение со ...WHERE (SAL,COMM)>=ALL
всеми значениями из списка ((1400,300),(3000,0))
или запроса. Должен предва-
ряться: =,!=,>,<,<=,>=.
[NOT] [Не] больше или равно WHERE A BETWEEN 1
BETWEEN x x и меньше или равно y. AND 9
AND y
-- 47 --
[NOT] TRUE, если запрос воз- WHERE EXISTS
EXISTS вращает [не возвращает] (SELECT SAL FROM EMP
как минимум одну строку WHERE DEPTNO=30)
[NOT] "не сопоставляется/сопо-
LIKE ставляется со следующим
шаблоном". Символ "%" ис-
пользуется для сопостав-
ления с любой строкой из
нуля или более символов,
кроме NULL - строки, а "_"
сопоставляется с любым
одиночным символом. При-
меры приводятся ниже в
разделе "Операция LIKE".
IS [NOT] "проверка на [не] null. WHERE JOB IS NULL
NULL IS должен использоваться
для проверки на NULL.
NULL обсуждается в Главе 3
"Элементы языка SQL".
Операция NOT IN
Все строки будут иметь значение false (и ни одной строки не выберется), если
какой-нибудь элемент в списке операции NOT IN имеет значение null. Например:
SELECT 'TRUE'
FROM EMP
WHERE DEPTNO NOT IN (5,15,null)
- не возвращает строк, в то время как:
SELECT 'TRUE'
FROM EMP
WHERE DEPTNO NOT IN (5,15)
- вернет значение эTRUE'. Первый пример не возвращает строк, потому что:
DEPTNO NOT IN (5,15,null)
вычисляется как:
DEPTNO !=5 AND DEPTNO !=15 AND DEPTNO != null
Так как все условия, которые сравнивают с null, дают в результате null,
результатом всего выражения будет null. Такое специфичное поведение операции
можно легко проглядеть, особенно если она ссылается на подзапрос.
Логические операции
Логические операции используются для объединения результатов нескольких
условий. Оператор AND используется, когда строка должна удовлетворять обоим
условиям, OR - одному из условий. Например, в следующей фразе WHERE оператора
SELECT операция AND гарантирует, что должны выдаваться только строки с датой
приема на работу ранее 1984 года и заработком больше 1000 долларов в месяц:
-- 48 --
SELECT * FROM EMP
WHERE (HIREDATE < '01-JAN-84) AND (SAL > 1000)
Следующая таблица представляет логические операции в порядке убывания
старшинства:
Операция Выполняемая функция Пример
------------------------------------------------------------
() Меняет нормальные пра- SELECT...WHERE x=y
вила старшинства опе- and (a-b or p=q)
раций.
NOT Инвертирует результат ...WHERE NOT(JOB IS NULL)
логического выражения ...WHERE NOT(CODE LIKE 'T%')
...WHERE NOT(SAL BETWEEN
500 AND 999)
...WHERE NOT(A=1)
AND Устанавливает в TRUE ...WHERE A=1 AND B=1
логическое выражение,
если оба условия TRUE.
OR Устанавливает в TRUE ...WHERE A=1 OR B=1
логическое выражение,
если одно из условий TRUE.
Рисунок 4-1 иллюстрирует результаты операции AND с двумя условиями.
Ъ----------ДТ------------ДВ------------ДВ------------Дї
і AND є true і false і null і
ЖНННННННННННОНННННННННННННШНННННННННННННШНННННННННННННµ
і true є true і false і null і
Г----------ДЧ------------ДЕ------------ДЕ------------Дґ
і false є false і false і false і
Г----------ДЧ------------ДЕ------------ДЕ------------Дґ
і null є null і false і null і
А----------ДР------------ДБ------------ДБ------------ДЩ
pисунок 4-1 Таблица истинности: AND
Рисунок 4-2 иллюстрирует результаты операции OR с двумя условиями.
Ъ----------ДТ------------ДВ------------ДВ------------Дї
і OR є true і false і null і
ЖНННННННННННОНННННННННННННШНННННННННННННШНННННННННННННµ
і true є true і true і true і
Г----------ДЧ------------ДЕ------------ДЕ------------Дґ
і false є true і false і null і
Г----------ДЧ------------ДЕ------------ДЕ------------Дґ
і null є true і null і null і
А----------ДР------------ДБ------------ДБ------------ДЩ
pисунок 4-2 Таблица истинности: OR
Операции с множествами
Операции с множествами объединяют в один результаты нескольких запросов. В
следующей таблице в порядке убывания старшинства приводятся SQL - операции над
множествами.
-- 49 --
Операция Выполняемая функция Пример
------------------------------------------------------------
UNION Объединяет запросы для ...SELECT...
выдачи всех различающих- UNION SELECT...;
ся строк каждого отдель-
ного запроса
INTERSECT Объединяет запросы для ...SELECT
выдачи всех различающих- INTERSECT SELECT...;
ся строк обоих запросов
MINUS Объединяет запросы для ...SELECT...
выдачи всех различающих- MINUS SELECT...;
ся строк, выданных пер-
вым запросом, но не вторым.
Замечание: Если в двух таблицах встречаются дублирующиеся записи, то при
использовании множественных операций UNION, MINUS, INTERSECT выдается
только одна.
Рисунок 4-3 графически иллюстрирует результаты операций с множествами.
Затемненные части показывают результат, возвращаемый ORACLE.
ОПЕРАЦИИ С МНОЖЕСТВАМИ
A B
SELECT * Ъ----------------Дї
FROM A і±±±±±±±±±±±±±±±±±і
UNION Ъ----------ДБ----Дї±±±±±±±±±±±і
SELECT * і±±±±±±±±±±±±±±±±±і±±±±±±±±±±±і
FROM B і±±±±±±±±±±±±±±±±±і±±±±±±±±±±±і
А----------ДВ----ДЩ±±±±±±±±±±±і
і±±±±±±±±±±±±±±±±±і
А----------------ДЩ
A B A B
Ъ----------------ї Ъ----------------Дї
і±±±±±±±±±±±±±±±±і і і
і±±±±±±±±±±Ъ----ДБ------ї Ъ----------ДЕ----Дї і
і±±±±±±±±±±і і і і±±±±±і і
і±±±±±±±±±±і і і і±±±±±і і
і±±±±±±±±±±А----ДВ------Щ А----------ДЕ----ДЩ і
і±±±±±±±±±±±±±±±±і і і
А----------------Щ А----------------ДЩ
SELECT * FROM A SELECT * FROM A
MINUS SELECT * FROM B INTERSECT SELECT * FROM B
Рисунок 4-3 Операции с множествами.
Другие операции
Ниже описываются другие операции языка SQL:
Операция Выполняемая функция Пример
------------------------------------------------------------
-- 50 --
(+) Указывает, что предыду- WHERE DEPT.DEPTNO=
щий столбец - столбец EMP.DEPTNO(+)
внешнего объединения
[table.]* Выбирает в запросе все SELECT * FROM EMP
столбцы из таблиц. Когда
предваряется именем таб-
лицы с точкой, запрос вы- SELECT EMP.* FROM EMP
бирает все столбцы этой
таблицы.
COUNT(expr) Возвращает число строк, SELECT COUNT(COMM)
в которых expr не null. FROM EMP
COUNT(*) Возвращает число строк SELECT COUNT(*)
в таблице, включая содер- FROM EMP
жащие null.
ALL Возвращает дублирующиеся SELECT ALL * ...
значения в запросах и аг- ...COUNT(ALL DEPTNO)
регатах. (ALL - умалчива-
емое в отличие от DISTINCT)
DISTINCT Удаляет дублирующиеся стро- DELECT DISTINCT *...
ки из запроса или удаляет
дублирующиеся значения из ...COUNT(DISTINCT
агрегатного выражения. DEPTNO)...
PRIOR Определяет отношение CONNECT BY
"отец-сын" в древовидном PRIOR EMPNO=MGR
запросе. Выражение в левой
части условия (EMPNO) пре-
дставляет родительскую
строку; выражение справа
(MGR) представляет строку
ребенка. Может использова-
ться в любой фразе древо-
видного запроса, а не толь-
ко в CONNECT BY.
-- 51 --
Операция LIKE
Назначение Используется при сравнении символьных строк с помощью шаблонов.
Предпосылки Нет.
Синтаксис SELECT ....
WHERE char LIKE pattern
Ключевые слова char выражение с типом данных CHAR.
и параметры Строчные литералы должны заключаться в одиночные
кавычки. Обратитесь к описанию expr ранее в этой
главе и text в Главе 3 данного руководства.
pattern выражение с типом данных CHAR, в котором
используются специальные символы сопоставления
с шаблоном (% и _).
Замечания по использованию
Когда Вы применяете операцию "=" для точного сравнения одной символьной строки
с другой, на самом деле Вы используете операцию LIKE для сравнения части
одной строки с другой.
В фразе LIKE вы должны сравнить столбец с шаблоном, а не с константой. Столбец
специфицируется перед ключевым словом LIKE.
Например, Вы должны ввести такой запрос:
SELECT SAL
FROM EMP
WHERE ENAME LIKE 'BL%'
вместо любого из следующих запросов:
SELECT SAL
FROM EMP
WHERE ENAME = 'BL%'
SELECT SAL
FROM EMP
WHERE 'BL%' LIKE ENAME
Символьный шаблон может включать в себя два специальных символа, с которыми
можно сопоставлять различные символы в значении данных. Символ подчеркивания
"_" используется для сравнения ровно одного символа, а знак процента "%"
- для сравнения нуля и более символов. Заметьте однако, что строка '%' не
может сопоставляться с NULL.
Во всех условиях, сравнивающих символьные выражения, включая операции LIKE
и =, регистр имеет значение. Для выполнения регистронезависимого сравнения
можно воспользоваться функцией UPPER(), например:
UPPER(ENAME) LIKE UPPER('sm%')
LIKE можно использовать в любом логическом выражении, работающем с данными
типа CHAR. Если LIKE используется для поиска индексированного столбца по
шаблону, преимущества индекса в скорости теряются, если первый символ в
шаблоне "%" или "_". Если же это не так, то преимущества индекса почти
полностью сохраняются, так как ORACLE может ограничить сравниваемые строки
теми, которые начинаются с известного символа.
-- 52 --
Сами символы "%" и "_" можно отыскивать с помощью функции TRANSLATE.
Например, для поиска всех служащих, содержащих в имени символ подчеркивания,
надо ввести:
SELECT ENAME
FROM EMP
WHERE TRANSLATE(ENAME,'_','#') LIKE '%#%'
Предыдущий пример использует функцию TRANSLATE для преобразования "_" в "#".
Он вернет всех служащих, содержащих в имени #.
Примеры
Условие ENAME LIKE 'BL%' означает "Вернуть TRUE, если значение ENAME
начинается с BL, а далее следует ноль или более символов". Все
перечисленные ниже значения приведут к выдаче TRUE:
BLAKE, BL, BLACK, BLUE
Регистр здесь играет важную роль, так что значения Bl и bl возвратят FALSE.
Для условия ENAME LIKE 'SMITH_' верны следующие значения: SMITHE, SMITHY,
SMITHS, но не верно значение SMITH, потому что шаблон "_" означает
сопоставление в точности с одним символом.
Связанные Раздел "Операторы сравнения", синтаксическое описание expr,
разделы оператор SELECT - в Главе 5 "Справочник по SQL - операторам";
Фраза WHERE в Главе 6 этого руководства (в разделе "Фразы
оператора SELECT").
-- 53 --
Функции
Функции ведут себя аналогично операциям. И те и другие работают с нулем или
более аргументами и возвращают один или более результатов. И те и другие
работают с отдельными элементами данных.
Операции отличаются от функций тем, что используют формат "argument1 операция
argument2", в то время как функции - function_name(argument1,argument2,...).
Argument - это задаваемая пользователем переменная или константа.
Большинство операций используют максимально два аргумента;
структура функции позволяет ей принимать три и более аргументов.
Однострочные функции имеют следующие признаки:
* однострочные функции возвращают один результат на строку
* однострочные функции ожидают одного или более аргументов
* однострочные функции могут быть вложенными
* однострочные функции могут использоваться везде, где можно использовать
пользовательские перемен- ные, столбцы или выражения соответствующего типа
* однострочные функции работают с каждой строкой, возвращаемой запросом, не
ожидая выборки следующих строк
В данном документе приняты следующие сокращения для аргументов:
Сокращение Объяснение аргумента
аргумента
----------------------------------------------------------Д
char Символьная константа в одиночных
кавычках или выражение типа CHAR.
d или e Константа или выражение типа DATE.
m или n Любая числовая константа или выра-
жение типа NUMBER.
raw Выражение типа RAW.
rowid Выражение типа ROWID.
* Тип данных зависит от контекста
функции.
expr Выражение любого типа.
Однострочные числовые функции
Числовые функции принимают на входе и возвращают на выходе
числовые значения. В следующей таблице описываются числовые
функции SQL:
ABS Синтаксис ABS(n)
Назначение Возвращает абсолютную величину n.
Пример SELECT ABS(-15) "Absilute" FROM DUAL
-- 54 --
Absolute
--------
15
CEIL Синтаксис CEIL(n)
Назначение Возвращает наименьшее целое, большее
или равное n.
Пример SELECT CEIL(15.7) "Celling" FROM DUAL
Celling
-------
16
FLOOR Синтаксис FLOOR(n)
Назначение Возвращает наибольшее целое, меньшее или
равное n.
Пример SELECT FLOOR(15.7) "Floor" FROM DUAL
Floor
-----
15
MOD Синтаксис MOD(m,n)
Назначение Возвращает остаток от деления m на n.
Пример SELECT MOD(7,5) "Modula" FROM DUAL
Modula
------
2
POWER Синтаксис POWER(m,n)
Назначение Возвращает m в степени n. n должен быть
целым, иначе возникает ошибка.
Пример SELECT POWER(3,2) "Raised" FROM DUAL
Raised
------
9
ROUND Синтаксис ROUND(n[,m])
Назначение Возвращает n, округленное до m знаков
после десятичной точки; если m опущено,
округляется до 0 знаков. Для округления
слева от десятичной точки m должно быть
отрицательным. m должно быть целым.
Пример SELECT ROUND(15.193,1) "Round" FROM DUAL
Round
-----
15.2
SELECT ROUND(15.193,-1) "Round" FROM DUAL
Round
-----
20
-- 55 --
SIGN Синтаксис SIGN(n)
Назначение Если n<0, функция возвращает -1; если
n=0, функция возвращает 0 и при n>0
возвращает 1.
Пример SELECT SIGN(-15) "Sign" FROM DUAL
Sign
----
-1
SQRT Синтаксис SQRT(n)
Назначение Возвращает квадратный корень из n; если
n<0, возвращает NULL. Эта функция возв-
ращает действительный (real) результат.
Пример SELECT SQRT(25) "Square root" FROM DUAL
Square root
-----------
5
TRUNC Синтаксис TRUNC(n[,m])
Назначение Возвращает n с отсеченными m младшими
разрядами. Если m отсутствует, отсека-
ется до 0 разряда (до целых). Если m -
отрицательное, отсечение разрядов (об-
нуление) выполняется влево от десятич-
ной точки.
Пример SELECT TRUNC(15.79,1) "Truncate" FROM DUAL
Truncate
--------
15.7
SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL
Truncate
--------
10
Однострочные символьные функции
Однострочные символьные функции требуют символьных исходных
данных, а возвращать могут как символьные, так и числовые
значения.
Однострочные символьные функции, возвращающие символьные
значения
CHR Синтаксис CHR(n)
Назначение Возвращает символ, имеющий соответству-
ющее значение n в коде ASCII или EBCDIC.
Пример SELECT CHR(75) "Character" FROM DUAL
-- 56 --
Character
---------
K
INITCAP Синтаксис INITCAP(char)
Назначение Возвращает char, каждое слово в котором
начинается с прописной буквы, а осталь-
ные - строчные.
Пример SELECT INITCAI('MR. SAMUEL HILLHOUSE')
"Capitalized"
FROM DUAL
Capitalized
--------------------
Mr. Samuel Hillhouse
LOWER Синтаксис LOWER(char)
Назначение Возвращает char, все символы которого
переведены в строчные
Пример SELECT INITCAI('MR. SAMUEL HILLHOUSE')
"Lowercase"
FROM DUAL
Lowercase
--------------------
mr. samuel hillhouse
LPAD Синтаксис LPAD(char1,n [,char2])
Назначение Возвращается char, дополненная слева до
n символов заполнителем char2. Подразу-
меваемое значение для char2 - пробел.
Пример SELECT LPAD('Page 1',14,'*.')
"LPAD example"
FROM DUAL
LPAD example
---------------
.*.*.*.*.Page 1
LTRIM Синтаксис LTRIM(char,[set])
Назначение Удаляет левые символы из char, пока
первый символ в char входит в set. Умол-
чание для set ''.
Пример SELECT LTRIM('xxxXxxLAST WORD','x')
"Left trim example"
FROM DUAL
Left trim example
-----------------
XxxLAST WORD
REPLACE Синтаксис REPLACE(char,search_string
[,replacement_string])
Назначение Заменяет в char все вхождения
-- 57 --
search_string на replacement_string.
Если replacement_string не указана, все
вхождения search_string удаляются. Если
не указаны ни replacement_string, ни
search_string, функция возвращает NULL.
Функция REPLACE расширяет возможности
функции TRANSLATE, которая обеспечивает
замену одиночных символов. REPLACE
обеспечивает замену строки на строку, а
также удаление символьных подстрок.
Пример SELECT REPLACE('JACR & JUE','J','BL')
"Changes"
FROM DUAL
Changes
------------
BLACK & BLUE
RPAD Синтаксис RPAD(char1,n [,char2])
Назначение Возвращает char1, дополненную справа до
n символов заполнителем char2, повторя-
емым столько раз, сколько необходимо.
Если char2 опущено, производится допол-
нение пробелами.
Пример SELECT RPAD('ENAME',10,'x')
"RPAD example"
FROM EMP
WHERE ENAME = 'TURNER'
RPAD example
------------
TURNERxxxx
RTRIM Синтаксис RTRIM(char [,set])
Назначение Возвращает char с завершающими символа-
ми, усеченными справа после последнего
символа, не входящего в set. Если set
опущен, предполагается по умолчанию ''.
Пример SELECT RTRIM('TURNERxxXxx','x')
"Right trim example"
FROM DUAL
Right trim example
------------------
TURNERxxX
SOUNDEX Синтаксис SOUNDEX(char)
Назначение Возвращает символьную строку, представ-
ляющую из себя символьное представление
слова (слов), заданных в char и имеющих
такое написание. Эта функция позволяет
сравнивать слова с различным написани-
ем, но звучащих одинаково.
-- 58 --
Пример SELECT ENAME
FROM EMP
WHERE SOUNDEX(ENAME) =
SOUNDEX('SMYTHE')
ENAME
-----------
SMITH
SUBSTR Синтаксис SUBSTR(char, m[,n])
Назначение Возвращает часть char, начиная с симво-
ла с номером m и длиной n символов. Ес-
ли n опущено - до конца char. Первая
позиция char 1.
Пример SELECT SUBSTR('ABCDEFG',2,3)
"Substring"
FROM DUAL
Substring
---------
BCD
TRANSLATE Синтаксис TRANSLATE(char,from,to)
Назначение Возвращает char со всеми замененными
символами from на to. Не сопоставленные
символы не меняются. TRANSLATE не уда-
ляет символы.
Пример SELECT TRANSLATE('HELLO! THREE!','!','-')
"Translate example"
FROM DUAL
Translate example
-----------------
HELLO- THREE-
UPPER Синтаксис UPPER(char)
Назначение Возвращает char со всеми символами, пе-
реведенными в верхний регистр.
Пример SELECT UPPER('Mr. Rogers')
FROM DUAL
UPPER('Mr. Rogers')
-------------------
MR. ROGERS
Однострочные символьные функции, возвращающие числовые значения
ASCII Синтаксис ASCII(char)
Назначение Выдает значение из последовательности
слияния для первого символа в char. Со-
ответствующей функции EBCDIC не сущест-
вует, но на машинах, работающих в коде
-- 59 --
EBCDIC эта функция выдает соответствую-
щее значение из последовательности сли-
яния EBCDIC.
Пример SELECT ASCII('Q') FROM DUAL
ASCII('Q')
----------
81
INSTR Синтаксис INSTR(char1,char2[,n[,m]])
Назначение Выдает позицию m - го вхождения char2 в
char1; поиск начинается с n - го симво-
ла в char1. Если "m" опущено, предпола-
гается 1, то же относится и к "n". По-
зиция выдается относительно первого
символа в char1, даже если n > 1.
Пример SELECT INSTR('MISSISSIPPI','S',5,2)
"In string example"
FROM DUAL
In string example
-----------------
7
LENGTH Синтаксис LENGTH(char)
Назначение Длина char.
Пример SELECT LENGTH('ELEPHANT')
"Length"
FROM DUAL
Length
------
8
NLSSORT Синтаксис NLSSORT(char)
Назначение Возвращает значение последовательности
слияния для char, основанной на местном
Национальном Языке. За информацией о
"Поддержке национальных языков" обрати-
тесь к Приложению F "ORACLE RDBMS Руко-
водства администратора базы данных".
Пример SELECT NLSSORT('Q') "Sequence #"
FROM DUAL
Sequence #
----------
5100
Групповые функции
-- 60 --
Групповые функции возвращают результаты, основанные на груп-
пе строк (в отличие от однострочных функций, возвращающих
результат для каждой строки). Для разделения результата на
меньшие группы используется фраза GROUP BY оператора SELECT.
Фраза DISTINCT заставляет групповые функции рассматривать
только отличающиеся значения выражений; ALL включает в расс-
мотрение все значения, включая дублирующиеся. Например,
среднее значение 1,1,1,3 для DISTINCT равно 1.5, а для ALL
оно будет равно 2. (Так в оригинале, но правильно должно
быть наоборот: ALL=1.5; DISTINCT=2. - Прим. перев.) Если в
операторе SELECT опущены обе этих фразы, предполагается ALL.
Все эти функции (кроме COUNT(*)) пропускают значения NULL.
Если в указывается "expr", тип данных аргумента может быть
CHAR, NUMBER или DATE.
AVG Синтаксис AVG([DISTINCT | ALL] n)
--Д
Назначение Возвращает среднее значение n, игнорируя
значения null.
Пример SELECT AVG(SAL) "Average" FROM EMP
Average
----------
2073.21429
COUNT(expr) Синтаксис COUNT([DISTINCT | ALL] expr)
--Д
Назначение Возвращает количество строк, где expr
не NULL.
Пример SELECT COUNT(JOBS) "Count"
FROM EMP
Count
-----
4
SELECT COUNT(DISTINCT JOB) "Jobs"
FROM EMP
Jobs
----
5
COUNT(*) Синтаксис COUNT(*)
Назначение Возвращает число строк в таблице, вклю-
чая дублирующиеся и содержащие NULL.
Пример SELECT COUNT(*) "Total" FROM EMP
-- 61 --
Total
-----
14
MAX Синтаксис MAX([DISTINCT | ALL] expr)
--Д
Назначение Возвращает максимальное значение expr.
Пример SELECT MAX(SAL) "Maximum" FROM EMP
Maximum
-------
5000
MIN Синтаксис MIN([DISTINCT | ALL] expr)
--Д
Назначение Возвращает минимальное значение expr.
Пример SELECT MIN(HIREDATE) "Minimum Date"
FROM EMP
Minimum Date
------------
17-DEC-80
STDDEV Синтаксис STDDEV([DISTINCT | ALL] n)
--Д
Назначение Возвращает среднеквадратичное отклонение
n, игнорируя значения null.
Пример SELECT STDDEV(SAL) "Deviation" FROM EMP
Deviation
----------
1182.50322
SUM Синтаксис SUM([DISTINCT | ALL] n)
--Д
Назначение Возвращает сумму значений n.
Пример SELECT SUM(SAL) "Total" FROM EMP
Total
-----
29025
VARIANCE Синтаксис VARIANCE([DISTINCT | ALL] n)
--Д
Назначение Возвращает дисперсию n, игнорируя значе-
ния null.
Пример SELECT VARIANCE(SAL) "Variance"
FROM EMP
-- 62 --
Variance
----------
1389313.87
-- 63 --
Функции преобразования
Функции преобразования переводят значение из одного типа
данных в другой. В общем случае такая функция преобразования
называется datatypeTOdatatype. Первый datatype - это входная
переменная, второй - выходная. Если в имени функции указан
только выходной тип, значит входным может быть любой дейс-
твительный тип данных системы ORACLE.
В этом разделе описываются функции преобразования ORACLE:
CHARTOROWID Синтаксис CHARTOROWID(char)
Назначение Преобразование значения из типа CHAR
в ROWID.
Пример SELECT ENAME
FROM EMP
WHERE ROWID =
CHARTOROWID('0000000F.0003.0002')
ENAME
-----
SMITH
CONVERT Синтаксис CONVERT(char [,dest_char_set
[,source_char_set] ] )
Назначение Преобразовывает между двумя различными
реализациями одного символьного набора.
Например, из символов DEC 8 bit в сим-
волы HP 8 bit.
dest_char_set - имя символьного набора,
в который будет выполняться преобразо-
вание. Это может быть имя столбца базы,
содержащего название символьного набора
или символьным литералом. Умалчиваемое
значение 'US7ASCII'.
source_char_set - имя символьного набо-
ра, в котором данные хранятся в базе.
Это может быть имя столбца, содержащего
имя набора или символьный литерал.
Умалчиваемое - текущее значение аргу-
мента (параметр LANGUAGE в
файле INIT.ORA).
Более подробную информацию о средстве
"Поддержки национальных языков" можно
подчерпнуть в Приложении F "ORACLE
RDBMS Руководства администратора базы
данных".
Существуют следующие стандартные символьные наборы:
US7ASCII (умалчиваемый) символьный набор US 7-bit
ASCII
WE8DEC западноевропейский 8-bit ASCII фирмы DEC
WE8HP западноевропейский 8-bit ASCII фирмы HP
F7DEC французский 7-bit ASCII фирмы DEC
WEIBMPC западноевропейский 8-bit ASCII, исполь-
зуемый в IBM PC.
-- 64 --
Пример SELECT ('NEW WORD','US7ASCII','WE8HP')
"Conversion"
FROM DUAL
Conversion
----------
NEW WORD
HEXTORAW Синтаксис HEXTORAW(char)
Назначение Преобразует символьное значение, состо-
ящее из шестнадцатеричных цифр в двоич-
ный вид (пригодный для занесения в
столбец типа RAW).
Пример INSERT INTO GRAPHICS (RAW_COLUMN)
SELECT HEXTORAW('7D')
FROM DUAL
RAWTOHEX Синтаксис RAWTOHEX(raw)
Назначение Преобразование raw в символьное значе-
ние, состоящее из шестнадцатеричных
цифр.
Пример SELECT RAWTOHEX(RAW_COLUMN) "Graphics"
FROM GRAPHICS
Graphics
--------
7D
ROWIDTOCHAR Синтаксис ROWIDTOCHAR(rowid)
Назначение Преобразование значений ROWID в тип
данных CHAR. В результате преобра-
зования всегда получается строка
длиной 18 символов.
Пример SELECT ROWID
FROM GRAPHICS
WHERE ROWIDTOCHAR(ROWID) LIKE '%F38%'
ROWID
------------------
00000F38.0001.0001
TO_CHAR, Синтаксис TO_CHAR(n [,fmt])
преобразо- Назначение Преобразование значения типа NUMBER
вание чисел в тип CHAR, используя необязатель-
ную строку формата. fmt должен быть
числовым форматом. Подробно форматы
описаны в разделе "Модели форма-
тов".Если fmt опущен, n преобразо-
вывается в символьное значение дли-
ной, достаточной для хранения всех
значащих цифр.
-- 65 --
Пример SELECT TO_CHAR(17145,'$099,999')
"Char"
FROM DUAL
Char
---------
$017,145
TO_CHAR, Синтаксис TO_CHAR(d [,fmt])
преобразова- Назначение Преобразование значения типа DATE в
ние дат тип CHAR, используя необязательную
строку формата. fmt должен быть
форматом даты. Форматы описаны ниже
в разделе "Модели форматов". Если
fmt опущен, d преобразуется в стан-
дартный формат системы ORACLE, "DD-
MON-YY".
Формат и число символов результата,
возвращаемого функцией TO_CHAR за-
висят от указанного значения fmt.
Пример SELECT TO_CHAR(HIREDATE,'Month DD,YYYY')
"New data format"
FROM EMP WHERE ENAME = 'SMITH'
New date format
---------------------------------
December 17, 1980
-- 66 --
TO_DATE Синтаксис TO_DATE(char [,fmt])
Назначение Преобразует char в значение типа
дата. Текущие значения месяца, года
и т.д. применяются в случае, если
они не точно определяются из аргу-
мента. fmt - это символьное значе-
ние в формате даты, которое специ-
фицирует формат char. Информация о
форматах предоставляется ниже, в
разделе "Модели форматов". Если fmt
опущен, значение даты в символьном
формате должно быть в виде "DD-MON-
YY". Если формат "J" - Юлианский,
то char должен быть числом.
Пример INSERT INTO BONUS (BONUS_DATE)
SELECT TO_DATE('January 15, 1989',
'Month dd, YYYY')
FROM DUAL
TO_NUMBER Синтаксис TO_NUMBER(char)
Назначение Преобразовывает char, являющийся
символьным представлением числа в
значение типа NUMBER.
Пример UPDATE EMP
SET SAL = SAL +
TO_NUMBER(SUBSTR('$100 raise',2,3))
WHERE ENAME = 'BLAKE'
Функции для работы с датами
Эти функции работают с датами системы ORACLE. Все функции
для работы с датами возвращают значение типа DATE (за исклю-
чением MONTHS_BETWEEN, которая возвращает числовое значение).
ADD_MONTHS Синтаксис ADD_MONTHS(d,n)
Назначение Возвращает дату d, увеличенную на n
месяцев. n должно быть целым и мо-
жет быть отрицательным.
Пример SELECT ADD_MONTHS(HIREDATE,12)
"Next year"
FROM EMP
WHERE ENAME = 'SMITH'
Next year
---------
17-DEC-81
LAST_DAY Синтаксис LAST_DAY(d)
Назначение Возвращает дату последнего дня ме-
сяца, содержащего d. Полезна для
определения количества дней в дан-
ном месяце.
-- 67 --
Пример SELECT LAST_DAY(SYSDATE) "Last"
FROM DUAL
Last
-----------------
31-AUG-89
MONTHS_BETWEEN Синтаксис MONTHS_BETWEEN(d,e)
Назначение Возвращает количество месяцев между
датами d и e. Если d более поздняя
дата, чем e, результат положитель-
ный, если более ранняя - отрица-
тельный. Дробная часть результата
означает часть 31 - дневного месяца.
Пример SELECT MONTHS_BETWEEN('02-feb-86',
'01-jan-86')
"Months"
FROM DUAL
Months
----------
1.03225806
NEW_TIME Синтаксис NEW_TIME(d,a,b)
Назначение Возвращает дату и время во времен-
ной зоне b, когда дата и время во
временной зоне a равны d. a и b -
это символьные выражения, принимаю-
щие следующие значения:
AST,ADT Атлантическое Стандартное
или Атлантическое Дневное Время
BST,BDT Берингово Стандартное или
Дневное Время
CST,CDT Центральное Стандартное или
Дневное Время
EST,EDT Восточное стандартное или
Дневное Время
GMT Среднее Гринвическое время
HST,HDT Стандартное или Дневное
время Аляски-Гаваев
MST,MDT Стандартное или Дневное
время Монтаны
NST Стандартное время Ньюфаунленда
PST,PDT Атлантическое Стандартное
или Дневное время
YST,YDT Стандартное или Дневное
Время Юкона
Пример SELECT
TO_CHAR(
NEW_TIME(TO_DATE(17:47','hh24:mi'),
'PST','GMT'),
'hh24:mi') "GREENWICH TIME"
FROM DUAL
-- 68 --
GREENWICH TIME
--------------
01:47
NEXT_DAY Синтаксис NEXT_DAY(d,char)
Назначение Возвращает дату дня, специфициро-
ванного char, первой недели, следу-
ющей за датой, определяемой d.
Пример В следующем примере возвращается да-
та следующего вторника после 17
марта 1989 г.
SELECT NEXT_DAY('17-MAR-89','TUESDAY')
"Next Day"
FROM DUAL
Next Day
---------
29-MAR-89
ROUND Синтаксис ROUND(d [,fmt])
Назначение Возвращает d, округленную на осно-
вании fmt. fmt подразумевает бли-
жайший день.
За деталями функций ROUND и TRUNC
обратитесь ниже к разделу "ROUND и
TRUNC".
Пример SELECT ROUND(TO_DATE('27-OCT-88'),'YEAR')
"FIRST OF THE YEAR"
FROM DUAL
FIRST OF THE YEAR
-----------------
01-JAN-89
-- 69 --
SYSDATE Синтаксис SYSDATE
Назначение Возвращает текущую дату и время. Аргу-
ментов не требует.
Пример SELECT SYSDATE FROM DUAL
SYSDATE
---------
19-MAR-89
TRUNC Синтаксис TRUNC(d [,fmt])
Назначение Возвращает дату и время, приведенные к
указанному в fmt значению (округляет в
меньшую сторону). По умолчанию удаляет
временную компоненту даты. См. также
следующий раздел "ROUND и TRUNC".
Пример SELECT TRUNC(TO_DATE('28-OCT-88','YEAR')
"First of the Year"
FROM DUAL
First of the Year
-----------------
01-JAN-88
ROUND и TRUNC
Следующая таблица показывает элементы формата и соответству-
ющий временной период для функций ROUND и TRUNC. По умолча-
нию предполагается "dd", что означает приведение временной
части даты к полуночи.
Модель формата Элемент (период)
----------------------------------------------------------Д
CC,SCC век
SYYY,YYYY,YEAR, год (округляется к 1 июля)
SYEAR,YYY,YY,Y
Q квартал (округляется на 16 число
старого месяца)
MONTH,MON,MM месяц (округляется на 16 число)
WW начальная неделя года
W начальная неделя месяца
DDD,DD,J день
DAY,DY,D ближайшее воскресенье
HH,HH12,HH24 часы
MI минуты
-- 70 --
Другие функции
DECODE Синтаксис DECODE(expr, search1, result1,
[search2, result2,] ...
[default] )
Назначение expr сравнивается с каждым значением
search и возвращает result, если expr
равно search. Если не сравнилось, функ-
ция DECODE возвращает значение default.
Если default отсутствует, предполагает-
ся null.
expr может быть любого типа, search
должна быть того же типа, что и expr.
Возвращаемое значение приводится к тому
же типу, что и первый result.
Пример SELECT ENAME,DECODE(MGR, 7934, 'SMITH',
7251, 'JONES', 7839, 'KING', 'NONE') Mgr
FROM EMP
ENAME MGR
----------- ----------
JONES KING
CLARK KING
OAKLEY NONE
FORD JONES
DUMP Синтаксис DUMP (expr [,display_format
[,start_position [, length]]])
Назначение Выдает значение выражения во внутреннем
формате. Действительными значениями для
display_format являются: 8 - для вось-
меричной системы, 10 - для десятичной,
16 - для шестнадцатеричной, 17 - для
символьного. start_position и length
определяют, какую часть результата надо
выдавать. По умолчанию выдается весь
результат.
Примеры SELECT DUMP(ENAME) "ASCII"
FROM EMP
WHERE ENAME = 'SCOTT'
ASCII
----------------------------
Type=1 Len=5: 83,67,79,84,84
SELECT DUMP(ENAME) "OCTAL"
FROM EMP
WHERE ENAME = 'SCOTT'
OCTAL
---------------------------------
Type=1 Len=5: 123,103,117,124,124
-- 71 --
SELECT DUMP(ENAME) "CHAR"
FROM EMP
WHERE ENAME = 'SCOTT'
CHAR
-----------------------
Type=1 Len=5: S,C,O,T,T
GREATEST Синтаксис GREATEST(expr [,expr] ...)
Назначение Возвращает наибольшее значение из спис-
ка. Перед выполнением сравнения все вы-
ражения (expr) преобразовываются к типу
данных первого.
Пример SELECT GREATEST('HARRY','HARRYOT','HAROLD')
"GREATEST"
FROM DUAL
GREATEST
--------
HARRIOT
LEAST Синтаксис LEAST(expr [,expr] ...)
Назначение Возвращает наименьшее значение из спис-
ка. Перед выполнением сравнения все вы-
ражения (expr) преобразовываются к типу
данных первого.
Пример SELECT LEAST('HARRY','HARRYOT','HAROLD')
"LEAST"
FROM DUAL
LEAST
--------
HAROLD
NVL Синтаксис NVL(expr1, expr2)
Назначение Если expr1 равно NULL, возвращает
expr2; если expr1 не равно NULL, возв-
ращает expr1. Тип данных возвращаемого
значения должен быть тем же, что и
expr1.
Пример SELECT ENAME,
NVL(TO_CHAR(COMM),'NOT APPLICATABLE')
"COMMISSION"
FROM EMP
WHERE DEPTNO = 30
ENAME COMMISSION
------------- -----------
ALLEN 300
WARD 500
MARTIN 1400
-- 72 --
BLAKE NOT APPLICATABLE
TURNER 0
JAMES NOT APPLICATABLE
UID Синтаксис UID
Назначение Возвращает уникальное число, назначен-
ное каждому имени пользователя (сущест-
вует единственное UID на пользователя).
Аргументов не требует.
Пример SELECT USER, UID FROM DUAL
USER UID
------------- -----
OPS$KING 9
USER Синтаксис USER
Назначение Возвращает имя текущего пользователя
ORACLE. В аргументах не нуждается.
Пример SELECT USER, UID FROM DUAL
USER UID
------------- -----
OPS$KING 9
USERENV Синтаксис USERENV(option)
Назначение Возвращает информацию типа CHAR о поль-
зователе/сеансе, используемую для спе-
цифичную для приложения таблицу журнала
регистрации доступа. Следующая таблица
показывает значение, возвращаемое для
каждой действительной опции option:
option Возвращаемое значение
--------------------------------Д
'ENTRYID' Доступный идентификатор
элемента регистрации
'SESSIONID' Идентификатор регистрации
сеанса пользователя
'TERMINAL' Идентификатор терминала
пользователя в операционной
системе
'LANGUAGE' Используемый язык (как оп-
ределено в параметре LANGUAGE
файла INIT.ORA
Пример SELECT USERENV('LANGUAGE') "Language"
FROM DUAL
Language
-------------------------
AMERICAN_AMERICA.US7ASCII
-- 73 --
VSIZE Синтаксис VSIZE(expr)
Назначение Возвращает количество байтов, использу-
емых ORACLE для хранения во внутреннем
виде expr.
Пример SELECT ENAME, VSIZE(ENAME) "BYTES"
FROM EMP
WHERE DEPTNO = 10
ENAME BYTES
------------ -------
CLARK 5
KING 4
MILLER 6
Модели форматов
Модели форматов используются с двумя целями:
* изменить формат выдачи столбца
* предоставить ORACLE значение формата, отличное от подразумеваемого.
Модели формата используются в функциях TO_CHAR и TO_DATE.
Внутри единственной функции TO_CHAR или TO_DATE могут быть применено
несколько моделей форматов.
Изменение формата выдачи
Модели формата могут применяться для изменения формы выдачи столбцов,
возвращаемых оператором SELECT. Модели формата никак не влияют на реальное
внутреннее представление данных столбца.
Например, чтобы выдать комиссионные всех служащих отдела 30 из таблицы EMP,
используя лидирующие символы доллара, запятые между каждыми тремя цифрами и
две десятичных позиции, введите:
SELECT ENAME EMPLOYEE, TO_CHAR(COMM,'$0,999.99') COMMISSION
FROM EMP
WHERE DEPTNO = 30
EMPLOYEE COMMISSION
----------- ----------
ALLEN $300.00
WARD $500.00
MARTIN $1,400.00
BLAKE
TURNER $0.00
JAMES
Обратите внимание, что функция TO_CHAR возвращает NULL для всех служащих, с
комиссионными NULL. Для подстановки 0 вместо NULL обратитесь к описанию
функции NVL выше по тексту. Заметьте также, что используются различные
модели формата ("$","9","0" и ".").
-- 74 --
Для выдачи даты приема на работу всех сотрудников отдела 20 с месяцами,
выводимыми словами, а также с включением века в год, необходимо ввести:
SELECT ENAME, TO_CHAR(HIREDATE,'fmMonth DD, YYYY') HIREDATE
FROM EMP
WHERE DEPTNO = 20
ENAME HIREDATE
------------ -----------------------
SMITH December 17, 1980
JONES April 2, 1981
SCOTT February 15, 1988
ADAMS March 20, 1988
FORD December 3, 1981
Назначение правильного формата
Модели формата могут быть также использованы для изменения значения в
формат, который ожидает ORACLE. При добавлении или изменении таблицы ORACLE
ожидает получить значения с типами данных, соответствующими типам данных
столбцов. Например, если Вы вставляете дату, ORACLE ожидает получить дату
или символьную строку в стандартном формате даты DD-MON-YY. Если Вы не
используете стандартный формат даты, необходимо воспользоваться функцией
TO_DATE с соответствующими моделями формата.
Для изменения даты найма служащего JONES на 20-May-88 не используя стандартный
формат, необходимо ввести:
UPDATE EMP
SET HIREDATE = TO_DATE('88 08 20','YY MM DD')
WHERE ENAME = 'JONES'
Функция TO_NUMBER
Функция TO_NUMBER не принимает модели формата. Все символьные выражения,
используемые в функции TO_NUMBER, должны приводить к правильному числу (См.
Главу 5 "Справочник по SQL - операторам").
Числовые форматы
Числовые форматы используются совокупно с функцией TO_CHAR для перевода
значений из типа данных NUMBER в CHARACTER. Их можно использовать только с
функцией TO_CHAR. Преобразование из NUMBER в CHARACTER может выполняться как
при выдаче данных, так и при передаче их системе ORACLE.
Все числовые форматы приводят к округлению числа до указанного количества
значащих цифр.
Если значение имеет больше значащих цифр в целой части, чем специфицировано
в формате, оно заменяется звездочками.
В следующей таблице показаны допустимые числовые форматы:
-- 75 --
Элемент Пример Описание
------------------------------------------------------------
9 9999 Число "9" определяет число выдаваемых позиций.
0 0999 Выдавать лидирующие нули.
$ $9999 Предварять значение знаком доллара.
B B9999 Выдавать нулевое значение как пробел, а не как "0".
MI 9999MI Выдавать "-" после отрицательного значения.
PR 9999PR Выдавать отрицательные значения в <угловых скобках>.
запятая 9,999 Выдать в данной позиции запятую.
точка 99.99 Выдать в данной позиции десятичную точку.
V 999V99 Умножить значение на 10*n, где n -
количество девяток после "V".
E 9.999EEEE Выдать результат в научной нотации
(формат должен содержать точно 4 "Е")
DATE DATE Для дат, хранящихся в числовом формате (ORACLE
Version 2). Выдается как дата в формате 'MM/DD/YY'.
Форматы даты
Форматы даты используются в функции TO_CHAR для выдачи даты в различных фор-
матах. Кроме того, они используются функцией TO_DATE для передачи ORACLE
значений, заданных в нестандартном формате. (Стандартный формат даты:DD-MON-YY
В следующей таблице приведены элементы форматов даты:
Элемент Значение
------------------------------------------------------------
SCC или CC Век; "S" означает предварение даты до
нашей эры символом "-".
YYYY или SYYYY Год; "S" означает предварение даты до нашей эры
символом "-".
YYY или YY или Y Последние 3, 2 или одна цифра года.
Y,YYY Год с запятой в указанной позиции
SYEAR или YEAR Год словами; "S" означает указание "-", если год до н.э.
BC или AD BC - до нашей эры, AD - наша эра.
B.C. или A.D. То же, но с точками в указанных позициях
Q Номер квартала (1,2,3,4; январь-март=1)
MM Месяц (01-12; январь=01)
MONTH Название месяца, дополненное пробелами до 9 символов.
MON Название месяца, трехбуквенное сокращение
WW Неделя года (1-52). Первая неделя начи-
нается первого числа года и продолжается до седьмого.
W Неделя месяца (1-5). Первая неделя начи-
нается с первого дня месяца и продолжается до седьмого.
DDD День года (1-366).
DD День месяца (1-31).
D День недели (1-7).
DAY Название дня недели, дополненное до 9символов пробелами.
DY Название дня, трехбуквенная аббревиатура
J Юлианская дата; число дней, прошедших с
1 января 4712 г. до н.э.
AM или PM До полудня или после полудня.
A.M или P.M То же, но с точками.
HH или HH12 Час дня (1-12).
HH24 Час дня (0-24).
MI Минуты (0-59).
SS Секунды (0-59).
-- 76 --
SSSSS Количество секунд, прошедших с полуночи.
/.,. Пунктуация, переносящаяся в результат.
"..." Многоточие, переносящееся в результат.
К элементам формата функции TO_CHAR может быть добавлен префикс FM (Fill Mode
- режим заполнения). Этот префикс подавляет пробелы, вставляемые во все
последующие элементы (например - в MONTH), а также подавляет для всех
последующих элементов лидирующие нули (например - в MI). Функция TO_CHAR
возвращает результат переменной длины.
FM - это параметр типа ключа. Если в модели формата он появляется дважды, то
после первого появления пробелы начинают подавляться, а после второго - вновь
вставляются в результат.
К первичным кодам формата функции TO_CHAR могут быть добавлены следующие
суффиксы:
TH Порядковый номер (т.е. "DDTH" для "4TH").
SP Номер выдается словом (т.е. "DDSP" для "FOUR").
SPTH или Порядковый номер выдается словом (т.е. "DDSPTH"
THSP для "FOURTH").
Выдача номеров (словами) прописными или строчными буквами зависит от
регистра, в котором набирается соответствующий элемент формата. Например,
"DAY" приведет к выдаче соответствующего слова прописными буквами - "MONDAY";
задав "Day", Вы получите "Monday", а задав "day" - "monday".
Кроме того, в модели формата можно вставлять различные знаки пунктуации,
например - дефисы, слеши, запятые, символьные константы, заключенные в
кавычки (не в апострофы !). Такие знаки пунктуации и символьные константы
переносятся в результат без изменений. Например, модель формата:
'DDTH "of" fmMonth, YYYY'
приводит к выдаче следующего результата:
15TH of March, 1989
Обратите внимание на наличие префикса "fm". В случае его отсутствия месяц
дополнялся бы пробелами до 9 символов:
15TH of March , 1989
Для включения в элемент формата апострофа, наберите его дважды. Например
следующий запрос:
SELECT TO_CHAR(sysdate, 'fmDay"''s Special"')
выдаст такой результат:
Tuesday's Special
-- 77 --
Выражения
Выражение - это комбинация одного или нескольких значений, операций и
функций, для которого вычисляется значение. В общем случае для компонентов
выражения предполагается определенный тип данных. Формальное определение
выражения ORACLE приводится в следующем разделе.
В следующем простом примере выражение имеет значение 4 и тип данных NUMBER
(один и тот же для обоих компонентов):
2*2
В следующем более комплексном примере используются как операции, так и функции.
В данном выражении к текущей дате добавляется семь дней, из суммы удаляется
временная компонента и результат преобразовывается к типу данных CHAR.
TO_CHAR(TRUNC(SYSDATE+7))
Выражения можно использовать практически везде, где можно использовать
постоянное значение. Например, можно вставить выражение вместо литерала
'smith' во фразе SET оператора UPDATE:
SET ENAME = 'smith'
В приведенном ниже примере вместо строки 'smith' вводится выражение
LOWER(ENAME):
SET ENAME = LOWER(ENAME)
-- 78 --
Expr
Назначение Текстовая строка, число, дата или выражение.
Предпосылки Нет.
Синтаксис Выражение может быть:
Форма I Столбцом, константой или специальным значением
[table.] {column | ROWID }
text
number
sequence.CURRVAL
sequence.NEXTVAL
NULL
ROWNUM
LEVEL
SYSDATE
UID
USER
Примеры EMP.ENAME
'this is a text string'
10
SYSDATE
Форма II Связанная переменная с необязательной индикаторной переменной
: { n | variable } [:ind_variable ]
Примеры :employee_name:employee_name_indicator_var
:department_location
Форма III Ссылка на функцию (доступные функции описываются в этой главе).
function_name ( [DISTINCT | ALL] expr [,expr]...)
Примеры LENGTH('BLAKE')
ROUND(1234.567*43)
Форма IV Комбинация других выражений (перечисленных выше).
(expr)
+expr, -expr, PRIOR expr
expr * expr, expr / expr
expr + expr, expr - expr, expr || expr
Примеры ('CLARK' || 'SMITH')
LENGTH('MOOSE') * 57
SQRT(114) + 72
Форма V Список выражений, заключенный в скобки:
(expr [,expr]...)
Примеры (10, 20, 40)
('SCOTT', 'BLAKE', 'TAYLOR')
(LENGTH('MOOSE') * 57, -SQRT(144) + 72, 69)
-- 79 --
Замечания по Выражения используются в:
использованию * списке выборки оператора SELECT
* условиях во фразах WHERE и HAVING
* фразах CONNECT BY, START WITH, ORDER BY
* фразе VALUE оператора INSERT
* фразе SET оператора UPDATE
Связанные Раздел "Функции" в этой главе, а также разде-
разделы лы "Text", "Integer" и "Number" в Главе 3 этого руководства.
Условия
Условие - это комбинация одного или более выражений и логических операций,
результатом которой являются значения FALSE либо TRUE. Условие можно было
бы назвать "Логическим" типом данных, хотя формально ORACLE такого типа не
поддерживает. Описание синтаксиса условия приводится в следующем разделе.
Покажем пример простого условия, результатом которого всегда будет TRUE:
1 = 1
В следующем примере приводится более сложное условие, которое выбирает ROWID
для каждой строки, преобразует в тип данных CHAR, сцепляет со строкой
'THE ROWID' и результат сравнивает на больше или равно с символьной константой
'smith':
ROWIDTOCHAR(ROWID) || 'THE ROWID' >= 'smith'
С помощью логических операций можно объединять несколько условий в одно.
Например, с этой целью можно использовать операцию AND:
(1 = 1) AND (5 < 7)
Для более подробной информации о вычислении условий с логическими операциями
обратитесь к разделу этой главы "Логические операции".
Условия наиболее часто используются во фразах WHERE операторов DELETE, SELECT
и UPDATE. Кроме того, они применяются во фразах START WITH, CONNECT BY и
HAVING оператора SELECT.
Condition
Назначение Элемент с результатом TRUE или FALSE.
Предпосылки Нет.
Синтаксис Условие может быть:
Форма I Сравнение с выражением или результатом запроса.
Форма II Сравнение с любым или всеми элементами списка запроса
-- 80 --
{ANY|ALL }([,]...)
{ANY|ALL }
{ANY | ALL}
( [,]...)
{ANY | ALL}
Форма III Тест на наличие в списке запроса
[NOT] IN ( [,]...)
[NOT] IN
[NOT] IN ( [,]...)
[NOT] IN
Форма IV Тест на вхождение в диапазон
[NOT] BETWEEN AND
Форма V Тест на значение NULL
IS [NOT] NULL
Форма VI Тест на существование в запросе строк
EXISTS
Форма VII Комбинация других условий (ранее перечисленных)
( )
NOT
AND
OR
Замечания по Используется во фразах WHERE, START WITH,
использованию CONNECT BY и HAVING.
Примеры ENAME = 'SMITH'
EMP.DEPTNO = DEPT.DEPTNO
HIREDATE > '01-JAN-88'
JOB IN ('PRESIDENT', 'CLERK', 'ANALYST')
SAL BETWEEN 500 AND 1000
COMM IS NULL AND SAL = 2000
Связанные Операторы SELECT, INSERT, UPDATE и DELETE в
разделы Главе 5 "Справочник по SQL - операторам";
фразы HAVING и WHERE в Главе 6 "Фразы опера-
тора SELECT".
-- 81 --
ГЛАВА 5
СПРАВОЧНИК ПО SQL - ОПЕРАТОРАМ
В этой главе содержится детальное описание всех SQL - операторов. За
небольшими исключениями эти операторы могут использоваться во всех
продуктах ORACLE. Операторы описываются в алфавитном порядке; описание
каждого включает следующие разделы:
Назначение описывает область использования оператора
Предпосылки перечисляются привилегии, необходимые
для выполнения этого оператора.
Синтаксис показывается, как надо вводить этот оператор. Используемая
нотация описана в предисловии к данному документу.
Ключевые слова описывается функция каждого ключевого
и параметры слова и параметра. Ключевое слово - это строка, которую
надо вводить именно так, как показано (она приводится в
символах верхнего регистра). Параметр - строка, вместо
которой пользователь помещает необходимое ему значение
(приводится в символах нижнего регистра).
Замечания по обсуждается, каким образом и где ис-
использованию пользуется этот оператор.
Примеры приводятся примеры каждого оператора.
Связанные разделы перечисляются разделы с дополнительной информацией.
Сводка по SQL - операторам
В Приложении В "Синтаксис SQL" дана полная сводка по синтаксису SQL - операто-
ров. В приводимой ниже таблице сведены все SQL - операторы с их функциональным
назначением. В таблице описываются следующие типы SQL - операторов:
DDL Data Defenition Language (Язык Определения Дан-
ных): эти операторы действуют на структуру объек-
тов базы данных и неявно используют словарь дан-
ных. До и после выполнения каждого DDL - оператора
ORACLE неявно выполняет операцию COMMIT.
DML Data Manipulation Language (Язык манипуляции дан-
ными): эти операторы выполняют различные манипуля-
ции с данными базы.
EMB Embedded SQL (Встроенный SQL): операторы, используемые для помещения
DDL и DML - операторов внутрь программы на процедурном языке.
Встроенный SQL поддерживается с помощью следующих прекомпиляторов
ORACLE: Pro*Ada, Pro*C, Pro*COBOL, Pro*FORTRAN, Pro*Pascal и
Pro*PL/I.
Оператор Тип Новый? Описание
------------------------------------------------------------
/* ... */ Помещает комментарий внутри
или между SQL - операторами.
ALTER CLUSTER DDL Y Переопределяет параметры
памяти кластера.
ALTER DATABASE DDL Y Открывает (закрывает, монтирует), pазмонтирует
-- 82 --
или переименовывает файлы базы данных;
переключает режимы архивации;
добавляют/отменяют файлы повторного выполнения
ALTER INDEX DDL Y Переопределяет будущее
распределение памяти индекса
ALTER ROLLBACK DDL Y Переопределяет будущее
SEGMENT распределение памяти сегмента отката
ALTER SEQUENCE DDL Y Переопределяет генерацию
последовательных номеров.
ALTER SESSION DDL Y Изменяет текущий сеанс пользователя.
ALTER TABLE DDL Добавляет столбцы к табли-
це, переопределяет их в су-
ществующих таблицах, а так-
же - будущее распределение памяти.
ALTER TABLESPACE DDL Y Переводит табличное прост-
ранство в off/online, уста-
навливает начало/окончание
backup, добавляет/переиме-
новывает файлы базы данных,
связанные с табличным прост-
ранством, меняет параметры
распределения памяти.
ALTER USER DDL Y Меняет пароль пользователя,
умалчиваемое табличное
пространство и умалчиваемое
временное табличное прост-
ранство.
AUDIT DDL Включает регистрацию досту-
па к указанным ресурсам ба-
зы данных.
CLOSE CURSOR EMB Освобождает ресурсы,
задержанные курсором.
COMMENT DDL Вносит в словарь данных
комментарии о таблице.
COMMIT DML Y Делает изменения, проведен-
ные с начала транзакции,
постоянными.
CONNECT EMB Присоединяет (подключает)
пользователя к базе данных.
CREATE CLUSTER DDL Создает кластер, который
может содержать одну или
более таблиц.
CREATE DATABASE DDL Y Создает базу данных и под-
готавливает ее к использо-
ванию.
CREATE DATABASE DDL Создает связь с определенным
LINK пользователем в удаленной ба-
зе данных.
CREATE INDEX DDL Создает индекс для таблицы.
CREATE ROLLBACK DDL Y Создает в табличном простра-
SEGMENT нстве сегмент отката.
CREATE SEQUENCE DDL Y Создает последовательность
пригодную для генерирования
основных ключей.
CREATE SYNONYM DDL Создает синоним для таблицы
или обзора.
CREATE TABLE DDL Создает таблицу и определя-
-- 83 --
ет ее столбцы и распределе-
ние памяти.
CREATE TABLESPACE DDL Y Создает в базе данных об-
ласть для хранения таблиц,
индексов, временных сегмен-
тов и сегментов отката и
именует входящие в него
файлы.
CREATE VIEW DDL Определяет обзор на одной
или нескольких таблицах или
других обзорах.
DECLARE CURSOR EMB Определяет курсор.
DECLARE DATABASE EMB Определяет имя удаленной
базы данных для ссылок в по-
следующих SQL - операторах.
DECLARE STATEMENT EMB Определяет имя SQL - пере-
менной для SQL - оператора.
DESCRIBE EMB Инициализирует дескриптор
для хранения описаний host
- переменной.
DELETE DML Удаляет все или выбранные
строки из таблицы.
DROP object DDL Удаляет кластер, межбазовую
связь, индекс, сегмент от-
ката, последовательность,
синоним, таблицу или обзор
из базы данных.
EXECUTE IMMEDIATE EMB Подготавливает и выполняет
SQL - оператор, не содержа-
щий host - переменных.
EXPLAIN PLAN Y Показывает план выполнения
SQL - оператора.
FETCH EMB Выбирает данные из базы.
GRANT DDL Создает новых пользователей
базы данных, назначает па-
роли и предоставляет поль-
зователям привилегии систе-
мы ORACLE. Предоставляет
пользователям привилегии на
таблицы, а также привилегию
использования ресурсов таб-
личного пространства.
INSERT DML Добавляет к таблице или об-
зору новые строки.
LOCK TABLE DML Блокирует таблицу, временно
ограничивая к ней доступ
другим пользователям.
NOAUDIT DDL Отменяет регистрацию досту-
па полностью или частично,
влияя на предыдущий опера-
тор AUDIT или на
опции регистрации.
OPEN cursor EMB Распределяет ранее описан-
ный курсор.
PREPARE EMB Выполняет разбор SQL -
оператора.
-- 84 --
RENAME DDL Изменяет имена таблиц, об-
зоров или синонимов.
REVOKE DDL Отменяет привилегии по базе
данных или пользовательские
привилегии по доступу к
таблицам.
ROLLBACK DML Y Откатывает все проведенные
изменения к точке сохране-
ния или к началу транзакции.
SAVEPOINT DML Y Устанавливает контрольную
точку, к которой впоследс-
твии можно будет выполнить
откат.
SELECT DML Выполняет запрос; выбирает
строки и столбцы из одной
или нескольких таблиц.
SET TRANSACTION DDL Y Начинает согласованную по
чтению транзакцию.
UPDATE DML Изменяет данные в таблице.
VALIDATE INDEX DDL Проверяет целостность индек-
са.
WHENEWER EMB Специфицирует, как будут
обрабатываться ошибки и
предупреждения.
/* ...*/ (SQL - комментарий)
Назначение Комментарии вставляются для лучшего понимания
SQL - оператора, не оказывая никакого влияния
на его работу.
Предпосылки Нет; комментарии можно помещать в любой SQL -
оператор.
Синтаксис /* text */
Ключевые слова text строка, содержащая печатаемые
и параметры символы из символьного набора
операционной системы, т.е.
ASCII или EBCDIC.
Замечания по Комментарии могут находиться внутри любого
использованию SQL - оператора ("внутри" означает - между
слов).
/* начинает комментарий, а */ его завершает.
Комментарий может распространяться на любое
число строк и может начинаться или заканчи-
ваться посредине строки. Между слешем и звез-
дочкой не должно быть никаких символов.
Пример Следующий SQL - оператор включает в себя нес-
колько комментариев:
SELECT ENAME, SAL+COMM COMP, JOB, LOC
/* Select all employees whose compensation is
greater then that Jones. - выбрать всех слу-
жащих, чья зарплата превышает зарплату
-- 85 --
служащего Jones */
FROM EMP, DEPT
/* DEPT is used to get depar-
tment name - DEPT использует-
ся для получения названия от-
дела */
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND SAL + COMM > /* Подзапрос: */
(SELECT SAL+COMM /* COMP=SAL+COMM */
FROM EMP /* зарплата */
WHERE ENAME='JONES') /* Джонса */
Связанные Оператор COMMENT.
разделы
-- 86 --
ALTER CLUSTER
Назначение Переопределение будущих характеристик памяти
для кластера.
Предпосылки Вы должны быть либо владельцем кластера, либо
иметь привилегию DBA для изменения характе-
ристик памяти чужого кластера.
Синтаксис ALTER CLUSTER [user.]cluster
[PCTUSED integer] [PCTFREE integer]
[SIZE integer]
[INITRANS integer] [MAXTRANS integer]
[STORAGE storage]
Ключевые слова user имя пользователя - владельца
и параметры кластера.
cluster имя кластера.
PCTUSED integer (Обратитесь к ниже к параметру
PCTUSED в описании оператора
CREATE TABLE).
PCTFREE integer (Обратитесь к ниже к параметру
PCTFREE в описании оператора
CREATE TABLE).
SIZE integer определяет, сколько ключей кла-
стера будет запоминаться в од-
ном блоке ORACLE, распределен-
ном кластеру. Полное описание
этого параметра дается ниже в
операторе CREATE CLUSTER.
INITRANS integer (Обратитесь к ниже к параметру
INITRANS в описании оператора
CREATE TABLE).
MAXTRANS integer (Обратитесь к ниже к параметру
MAXTRANS в описании оператора
CREATE TABLE).
STORAGE storage (См. ниже описание фразы
STORAGE).
Замечания по Используя команду ALTER CLASTER, Вы можете:
использованию
* изменить PCTUSED и PCTFREE для будущих бло-
ков кластера
* изменить SIZE для будущих блоков кластера
* изменить для будущих блоков значения
INITRANS и MAXTRANS
* изменить будущие распределения памяти, за-
даваемые с помощью параметров PCTINCREASE,
MAXEXTENTS, NEXT.
Используя команду ALTER CLUSTER, Вы не можете:
* изменять количество столбцов кластера и их
имена
* изменять параметр MINEXTENTS
* изменять любой параметр относительно бло-
ков, уже распределенных кластеру
-- 87 --
* удалять таблицы из кластера (См. команды
DROP CLUSTER и DROP TABLESPACE)
* менять табличное пространство, которому
принадлежит кластер
Пример Чтобы изменить кластер CUSTOMER пользователя
SCOTT так, чтобы в будущем он содержал по 4
ключа кластера на один блок ORACLE (предпола-
гается размер блока ORACLE 2 килобайта
2К/(SIZE 512)=4) и установить MAXEXTENTS в
25, введите:
ALTER CLUSTER SCOTT.CUSTOMER
SIZE 512
STORAGE (MAXEXTENTS 25)
Связанные Операторы CREATE CLUSTER и CREATE TABLE, фраза
разделы STORAGE.
-- 88 --
ALTER DATABASE
Назначение Изменение существующей базы данных в следую-
щих направлениях:
* монтировать базу данных (в разделяемом или
исключительном режимах)
* открыть или закрыть базу данных
* переименовать или отменить файл журнала
повторного выполнения
* специфицировать, что файл журнала будет ар-
хивироваться и, следовательно, будет полезен
при восстановлении носителя или не будет ар-
хивироваться и, значит, поможет только при
восстановлении экземпляра.
Предпосылки Требуется привилегия DBA.
Синтаксис ALTER DATABASE [ database ]
{ADD LOGFILE filespec [,filespec ...]
|DROP LOGFILE 'filename' [,'filename'] ...]
|RENAME FILE 'filename' [,'filename'] ...
TO filename [,filename] ... ]
|ARCHIVELOG
|NOARCHIVELOG
|MOUNT [SHARED | EXCLUSIVE]
--------Д
| DISMOUNT
|OPEN
| CLOSE [NORMAL | IMMEDIATE]}
------
Ключевые слова database идентификатор базы данных,
и параметры который не может содержать
более 8 символов. Если от-
сутствует, имя базы будет
браться из значения параметра
файла INIT.ORA - DB_NAME.
Этот идентификатор не связан
со спецификацией базы данных
в SQL*Net.
ADD LOGFILE спецификация базы данных в
форме:
'filename' [SIZE integer [K|M]] integer [REUSE]
указывает SIZE в байтах. Если
integer опущено, предполага-
ется размер файла журнала в
500К. Если далее идет "К",
integer умножается на 1024,
если "М" - на 1048576.
DROP LOGFILE имя текущего файла журнала,
filename который должен быть отменен.
RENAME FILE специфицирует старое и новое
filename TO имена для файлов базы данных.
filename Новые имена файлов должны со-
ответствовать соглашениям Ва-
шей операционной системы.
|