Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
 Скачать 11.62 Mb.
|
|
4.5. Типы данных SQL Типы данных позволяют установить основные правила для данных, содер- жащихся в конкретном столбце таблицы, в том числе размер выделяемой для них памяти. В языке SQL имеется несколько категорий типов данных, основные из кото- рых приведены в табл. 4.7. Таблица 4.7. Категории и типы данных SQL:2003 Категория Пример Описание Символьные CHAR , VARCHAR Любые комбинации символов из до- пустимого набора. Вариабельные ти- пы позволяют хранить строки пере- менной длины, тогда как остальные — только строки фиксированной. В ва- риабельных типах данных автомати- чески удаляются замыкающие пробе- лы, а в других типах пробелы остаются NATIONAL CHARACTER Типы данных, связанные с нацио- нальными символами Двоичные BLOB , CLOB Двоичные (символьные) большие объекты Числовые NUMERIC(p, s) Числовые значения с регулируемой точностью ( p ) и масштабом ( s ) Дата/время DATE Данные, связанные со временем Связь с данными DATALINK Определяет ссылку на файл или дру- гой внешний источник, не являющийся частью среды SQL Интервальные INTERVAL Определяет набор временных значе- ний или промежуток времени XML XML Данные формата XML СУБД Oracle поддерживает множество типов данных, в том числе и боль- шинство стандартных типов SQL:2003. Часть II. Язык SQL. Извлечение данных 78 4.5.1. Символьные Символьные данные могут иметь фиксированную и переменную длину (табл. 4.8). Эти данные обозначаются в SQL с помощью одинарных кавычек, о чем подробно рассказывалось в разд. 4.4.3. Данные с символами в формате UNICODE (стандарт кодирования символов, позволяющий представить знаки практически всех письменных языков), как правило, содержат большее число байт, чем данные в стандарте ASCII (American Standard Code for Information Interchange), и это надо учитывать, устанавливая длину ( n ) в байтах. Таблица 4.8. Символьные данные в Oracle Oracle SQL:2003 Описание CHAR(n) [BYTE | CHAR] , CHARACTER(n) [BYTE | CHAR] CHARACTER(n) Хранит символьные данные фиксированной длины до 2000 байт. При указании ат- рибута BYTE длина массива измеряется в байтах. При указании атрибута CHAR дли- на измеряется в символах NCHAR(n) , NATIONAL CHARACTER(n) , NATIONAL CHAR{n} NATIONAL CHARACTER Хранит данные в формате символов UNICODE дли- ной от 1 до 2000 байт. По умолчанию — 1 байт VARCHAR(n) , CHARACTER VARYING(n) , CHAR VARYING(n) CHARACTER VARYING(n) Хранит символьные данные размером от 1 до 4000 байт. Oracle рекомендует исполь- зовать вместо этого типа тип VARCHAR2 VARCHAR2(n [BYTE | CHAR]) CHARACTER VARYING(n) Хранит символьные дан- ные переменной длины до 4000 байт (определяется параметром n ). Атрибут BYTE показывает, что раз- мер измеряется в байтах. Если вы используете ат- рибут CHAR , база Oracle должна провести внутрен- нее преобразование в оп- ределенное количество байт, которое должно со- ответствовать ограниче - нию в 4000 байт Глава 4. Основы SQL 79 Таблица 4. 8 (окончание) Oracle SQL:2003 Описание NVARCHAR2(n) Отсутствует Представляет собой реко- мендуемый Oracle тип для хранения символов UNICODE переменной длины от 1 до 4000 байт 4.5.2. Двоичные LOB-типы используются для хранения больших объектов (Large Object). Двоичные объекты BLOB используются для хранения очень больших объек- тов данных с неопределенным или переменным размером, таких как графика, векторная графика, звуковые файлы, фотографии, видеосегменты и другие виды мультимедийной информации. Символьные объекты CLOB использу- ются для хранения глав книг, больших документов и т. п. В табл. 4.9 приве- дено описание двоичный типов данных. Таблица 4.9. Двоичные данные в Oracle Oracle SQL:2003 Описание BLOB BLOB Хранит большой двоичный объект ( Binary Large Object , BLOB) размером от 8 до 128 терабайт в зависимости от размера блока в базе данных CLOB CLOB Хранит большой символьный объект (Large Character Object , CLOB) размером от 8 до 128 терабайт в зависимости от размера блока в базе данных NCLOB NCLOB Представляет собой CLOB с поддержкой мно- гобайтовых символов (например, UNICODE) размером от 8 до 128 терабайт в зависимости от размера блока базы данных В Oracle большие двоичные объекты (BLOB, CLOB и NCLOB) имеют сле- дующие ограничения: их нельзя выбирать с удаленной машины; их нельзя сохранять в кластерах; они не могут быть компонентом предложений ORDER BY и GROUP BY в за- просе; Часть II. Язык SQL. Извлечение данных 80 их нельзя использовать в агрегатных функциях запроса; на них нельзя ссылаться в запросах при помощи инструкций DISTINCT и UNIQUE или в соединениях; они не могут быть частью первичного ключа или ключа индекса; их нельзя использовать в предложении UPDATE ОF триггера UPDATE 4.5.3. Числовые Используются для хранения нулевых, положительных и отрицательных чи- сел с фиксированной и плавающей запятой (точкой). Из всех, перечислен- ных в табл. 4.10, типов на практике можно использовать только один — NUMBER(p, s) . Остальные типы числовых данных существуют в стандарте и сохранены для поддержания ранее созданных программ. Таблица 4.10. Числовые данные в Oracle Oracle SQL:2003 Описание BINARY_FLOAT FLOAT Хранит 32 - битное число с пла- вающей точкой BINARY_DOUBLE FLOAT Хранит 64 - битное число с пла- вающей точкой DECIMAL(p, s) DECIMAL(p, s) Синоним типа NUMBER , прини- мающий в качестве аргументов точность и масштаб DOUBLE PRECISION DOUBLE PRECISION Хранит значения с плавающей точкой двойной точности. То же, что FLOAT(126) FLOAT(n) FLOAT(n) Хранит числовые данные с пла- вающей точкой с двоичной точ- ностью до 126 INTEGER(n) INTEGER Хранит целые числа со знаком и без знака с точностью до 38. Тип INTEGER считается синони- мом NUMBER NUMBER(p, s) , NUMERIC(p, s) NUMERIC(p, s) Хранит числа с точностью в пределах от 1 до 38 и масшта- бом от –84 до 127 Глава 4. Основы SQL 81 Таблица 4.10 (окончание) Oracle SQL:2003 Описание REAL REAL Хранит значения с плавающей точкой с одинарной точностью. То же, что FLOAT(63) SMALLINT SMALLINT То же, что INTEGER 4.5.4. Дата/время В Oracle тип DATE хранит дату и время, т. е. заменяет стандартные типы DATE и TIMESTAMP , хотя и сохранил форматы стандарта (табл. 4.11). Для получения всех необходимых видов дата/время в Oracle используются функции TO_CHAR и (или) TO_DATE (см. разд. 4.6) и соответствующий формат даты (см. разд. 4.5). Таблица 4.11. Дата/время в Oracle Oracle SQL:2003 Описание DATE DATE Хранит дату и время от 00:00:00 01-01- 4712 до н. э. до 23:59:59 31-12-9999 TIMESTAMP(n){[WITH TIME ZONE] | [WITH LOCAL TIME ZONE]} TIMESTAMP[WITH TIME ZONE] Значение полной даты и време- ни, где n — количество цифр для долей секунды в поле секунд (допустимые значения 0—9, по умолчанию — 6). При указании атрибута WITH TIME ZONE сохраняется пере- данный в качестве параметра часовой пояс (по умолчанию — часовой пояс текущего сеанса) и значение времени выдается с учетом этого часового пояса. При указании атрибута WITH LOCAL TIME ZONE данные хра- нятся с учетом часового пояса текущего сеанса и возвращают- ся также с учетом часового поя- са текущего сеанса Часть II. Язык SQL. Извлечение данных 82 4.5.5. Связь с данными Для связи с данными в Oracle используется тип BFILE (в SQL:2003 DATALINK ), который содержит указатель на объект типа BLOB ,хранимый вне пределов базы данных, но находящийся на локальном сервере и имеющий размер до 4 Гбайт. База данных осуществляет потоковый доступ по чтению (но не по записи) к этому внешнему объекту. Если выудалите строку, содержащую значение типа BFILE ,будет удален только указатель (исходная структура файлов не затрагивается). 4.5.6. Интервальные Используются для описания промежутков времени между двумя временными отсчетами, задаваемыми типом "Дата/время" (табл. 4.12). Таблица 4.12. Интервальные данные в Oracle Oracle SQL:2003 Описание INTERVAL DAY(n) ТО SECOND (х) INTERVAL Хранит промежуток времени, измеряе- мый в днях, часах, минутах и секундах, n — число цифр в поле "день" (допусти- мые значения 0—9, по умолчанию — 2), х — число цифр для долей секунды в поле секунд (допустимые значения 0— 9, по умолчанию — 6) INTERVAL YEAR (n) ТО MONTH (х) INTERVAL Хранит промежуток времени, измеряе- мый в годах и месяцах, где n — число цифр в поле года. Значение n может быть от 0 до 9, по умолчанию — 2 4.5.7. XML Для хранения в базе Oracle данных формата XML используется XMLTYPE (в SQL:2003 XML ). Доступ к данным XML осуществляется с помощью выра- жений XPath, а также нескольких встроенных XPath-функций, функций SQL и пакетов PL/SQL. Тип XMLTYPE определяется системой, поэтому его можно применять в качестве аргумента функций, а также типа данных для столбца в таблице или представлении. При использовании этого типа в таблице дан- ные можно сохранить в форме CLOB или связанного объекта. Глава 4. Основы SQL 83 4.5.8. Данные, специфичные для СУБД Oracle В Oracle есть специфичные данные, отсутствующие в стандарте SQL:2003, описание которых приведено в табл. 4.13. Таблица 4.13. Данные в Oracle Oracle SQL:2003 Описание ROWID Нет Представляет собой уникальный иден- тификатор типа base- 64 для каждой строки таблицы. Часто используется с псевдостолбцом ROWID UROWID [(п)] Нет Хранит значение типа base- 64, показы- вающее логический адрес строки в таб- лице. По умолчанию размер составляет 4000 байт. При желании можно указать размер в пределах до 4000 байт 4.6. Функции SQL Функции могут быть использованы везде, где используются переменные, столбцы или выражения (соответствующего типа). Их обычно подразделяют на числовые, символьные, агрегатные, функции работы с датами (дата и вре- мя) и т. п. (см. табл. 4.14—4.22). В описаниях функций используются следующие параметры: char , char1 , char2 ,... — константы в апострофах или выражения типа CHAR ; d , d1 , d2 ,... — константы в апострофах или выражения типа DATE ; expr , expr1 , expr2 ,... — любые выражения; fmt — формат данных; k , m , n — числовые константы или выражения типа NUMBER ; nls — выражение вида 'NLS_SORT = name' ; raw — исходные данные; rowid — внутренний уникальный идентификатор строки; set , set1 , set2 — наборы символов; z1 , z2 — часовые пояса (см. табл. 4.16). Часть II. Язык SQL. Извлечение данных 84 4.6.1. Числовые функции Таблица 4.14. Числовые функции Функции Возвращаемое значение ABS(n) Абсолютное значение n , большее или равное n ACOS(n) Арккосинус n ( n от –1 до 1), результат от 0 до в радианах ATAN(n) Арктангенс n ( n не ограничено), результат от – /2 до /2 ATAN2(n/m) Арктангенс n и m ( n и m не ограничены), результат от – до CEIL(n) Наименьшее целое, не меньшее n COS(n) Косинус n , заданного в радианах COSH(n) Гиперболический косинус n в радианах EXP(n) Возведение e (exp) в степень n (где е = 2.7182818) FLOOR(n) Наибольшее целое, меньшее или равное n LN(n) Натуральный логарифм n , где n > 0 LOG(m,n) Основание m логарифма n MOD(m,n) Остаток от деления m на n POWER(m,n) m в степени n . Если n не целое, то оно усекается до целого ROUND(n[,m]) n , округленное до m - го десятичного знака; если m опущено, то оно принимается равным 0. m может быть отрицательным для округления цифр левее десятичной точки SIGN(n) Если n < 0, то –1; если n = 0, то 0; если n > 0, то 1 SIN(n) Синус n , заданного в радианах SINH(n) Гиперболический синус n в радианах SQRT(n) Квадратный корень из n ; если n < 0, то NULL TAN(n) Тангенс n , заданного в радианах TANH(n) Гиперболический тангенс n в радианах TRUNC(n[,m]) n , усеченное до m десятичных знаков; если m опущено, то оно принимается равным 0. m может быть отрицательным для усе- чения (обнуления) цифр слева от десятичной точки Глава 4. Основы SQL 85 4.6.2. Символьные функции Таблица 4.15. Символьные функции Функции Возвращаемое значение ASCII(char) Код ASCII первого символа символьной переменной char CHR(n) Символ, код ASCII которого равен n CONCAT(char1, char2 Соединяет (конкатенирует) строку char1 со строкой char2 (Эквивалентна выражению: char1 || char2 ) INITCAP(char) Символьная переменная с первыми буквами слов, начи- нающихся с заглавной буквы INSTR(char1, char2[,n[,m]]) Позиция m - го включения char2 в char1 при начале поиска с позиции n . Если m опущено, по умолчанию оно равно 1; аналогично для n . Позиции даются относительно первого знака char1 , даже если n > 1 INSTRB(char1, char2[,n[,m]]) Эквивалентна INSTR , но n и результат возвращаются в байтах, а не в позициях символов. Эту функцию полезно использовать при работе с многобайтовыми символьными строками LENGTH(char) Длина в знаках символьной переменной char LENGTHB(char) Длина в байтах символьной переменной char LOWER(char) char , где все буквы преобразованы в строчные (ма - ленькие) LPAD(char1,n [,char2]) Строка char1 , дополненная слева до длины n последова- тельностью символов из строки char2 с повторением этой последовательности столько раз, сколько необходимо. Если char2 опущено, то для заполнения используются пробелы LTRIM(char [,set]) Удаляет из char начальные знаки до тех пор, пока не поя- вится знак, отсутствующий среди знаков set . При отсутст- вии set из char удаляются все левые пробелы NLS_INITCAP(char [,nls]) Аналог INITCAP , но необязательный аргумент nls позво- ляет задать используемый в функции национальный язык NLS_LOWER(char [,nls]) Аналог LOWER , но необязательный аргумент nls позволяет задать используемый в функции национальный язык Часть II. Язык SQL. Извлечение данных 86 Таблица 4.15 (окончание) Функции Возвращаемое значение NLSSORT(char [,nls]) Байтовая строка, использованная для сортировки char на базе языка, заданного аргументом nls . Эту функцию по- лезно применять для сравнения строк в различных языках NLS_UPPER(char [,nls]) Аналог UPPER , но необязательный аргумент nls позволяет задать используемый в функции национальный язык REPLACE(char1, char2[,char3]) Строка, полученная из char1 , в которой все вхождения char2 заменены на char3 . Если char3 отсутствует, то все вхождения char2 в char1 удаляются RPAD(char1,n [,char2]) Строка char1 , дополненная справа символами char2 , с повторением, если необходимо. Если char2 опущена, то char1 дополняется пробелами RTRIM(char [,set]) Удаляет из char конечные знаки до тех пор, пока не поя- вится знак, отсутствующий среди знаков set . При отсутст- вии set из char удаляются все правые пробелы SOUNDEX(char) Фонетическое представление char (четырехсимвольное представление, показывающее, как звучит начало char ) SUBSTR(char,m [,n]) Подстрока, получаемая из char , начиная с символа m Если задано n , то подстрока ограничивается n символами. При отрицательном m символы отсчитываются с конца char SUBSTRB(char,m [,n]) Эквивалентно SUBSTR , но аргументы m и n выражаются не в символах, а в байтах. Эту функцию полезно исполь- зовать при работе с многобайтовыми символьными строками TRANSLATE(char, set1, set2) Строка, полученная трансляцией char в наборе set1 в набор set2 TRIM([{{LEADING | TRAILING BOTH} char1} char1} FROM] char2) Если задано LEADING , из char2 удаляются начальные char1 . Если задано TRAILING , из char2 удаляются конеч- ные char1 . Если задано BOTH или ничего, из char2 удаля- ются начальные и конечные char1 . По умолчанию char1 — пробел; если определено только char2 , из char2 удаляются начальные и конечные пробелы; если char1 или char2 равно NULL , то возвращается NULL UPPER(char) Строка, полученная из char заменой ее строчных букв на заглавные Глава 4. Основы SQL 87 4.6.3. Даты и время Таблица 4.16. Функции работы с датами и временем Функции Возвращаемое значение ADD_MONTHS(d,n) Дата d плюс n месяцев LAST_DAY(d) Дата последнего дня месяца, заданного датой d MONTHS_BETWEEN (d1,d2) Количество месяцев между датами d1 и d2 . Eсли d1 > d2 , то результат положителен, иначе отрицателен NEW_TIME(d,z1,z2) Преобразует дату и время, заданное d в часовом поясе z1 , в дату и время в часовом поясе z2 . Символьные значения z1 и z2 выбираются из следующего списка: AST , ADT — Атлантическое стандартное и дневное время; BST , BDT — Берингово стандартное и дневное время; CST , CDT — Центральное стандартное и дневное время; EST , EDT — Восточное стандартное и дневное время; GMT — Среднее время по Гринвичу; HST , HDT — Аляски - Гаваев стандартное и дневное время; MST , MDT — Монтаны стандартное и дневное время; NST — Нью - Фаунленда стандартное время; PST , PDT — Тихоокеанское стандартное и дневное время; YST , YDT — Юкона стандартное и дневное время NEXT_DAY(d,char) Дата первого из дней недели, обозначенной char , кото- рая больше или равна d ROUND(d[,fmt]) Значение d , округленное до ближайшего числа в фор- мате, заданном fmt (например, год или месяц). По умолчанию DD SYSDATE Текущая дата и время TRUNC(d[,fmt]) Значение d , усеченное до ближайшего числа в форма- те, заданном fmt (например, год или месяц). По умол- чанию DD Часть II. Язык SQL. Извлечение данных 88 Таблица 4.17. Форматы функций ROUND и TRUNCATE Используемый формат Значение CC | SCC Дата первого дня века YYYY | SYYYY | YEAR | SYEAR | YYY | YY | Y | IYYY | IY | I Дата первого дня года (при округлении: до или после 1 июля) (для года по ISO) Q Дата первого дня квартала (при округлении: до или после 16 числа второго месяца квартала) MONTH | MON | MM | RM Дата первого дня месяца (при округлении: до или после 16 числа месяца) WW | IW (для года по I SO) Дата первого дня недели, начинающейся не с воскресения, а с дня недели, определенного по первому дню года (при округлении: до или после 4 дня недели) W Дата первого дня недели, начинающейся не с воскресения, а с дня недели, определенного по первому дню месяца (при округлении: до или по- сле 4 дня недели) DDD | DD | J Номер дня DAY | DY | D Дата первого дня недели (воскресенья) HH | HH12 | HH24 Час MI Минута Таблица 4.18. Форматы дат и времени Формат Описание SCC или СС Век; 'S' — префикс перед датой (до н. э.) с ' - ' YYYY | SYYYY Год; 'S' — префикс перед датой (до н. э.) с ' - ' YYY | YY | Y Последние 3, 2 или 1 цифра(ы) года IYYY 4 цифры года в стандарте ISO IYY | IY | I Последние 3, 2 или 1 цифра(ы) года в стандарте ISO Y,YYY Год с запятой в данной позиции Глава 4. Основы SQL 89 Таблица 4.18 (окончание) Формат Описание SYEAR | YEAR Год прописью. 'S' — префикс перед датой (до н. э.) с' - ' BC | AD BC / AD — до н э . / н э B.C. | A.D. Аналогично BC / AD Q Квартал (четверть) года MM Номер месяца RM Номер месяца римскими цифрами ( I..XII ; JAN=I ) MONTH Имя месяца, дополненное пробелами до девяти сим- волов MON Трехбуквенная аббревиатура имени месяца ( JAN , FEB ,...) WW | W Неделя года (1—52) или месяца (1— 5) IW Неделя года в стандарте ISO (1—21 или 1— 53) DDD DD | D Номер дня года (1—366), месяца (1—31) или недели (1 — 7) DAY Наименование дня, дополненное пробелами до 9 символов DY Трехбуквенная аббревиатура наименования дня J День по Юлианскому календарю; количество дней от 31 декабря 4713 г. до н. э. AM | PM Указатель часового пояса A.M. | P.M. Указатель часового пояса с периодами HH | HH12 Время суток, интервал 1— 12 HH24 Время суток, интервал 0— 23 MI Минуты (0— 59) SS | SSSSS Секунды (0—59) или после полуночи (0— 86399) -/.,;: Пунктуация, которая воспроизводится в результате "...текст..." Строка в кавычках, которая воспроизводится в ре- зультате Часть II. Язык SQL. Извлечение данных 90 4.6.4. Преобразование данных Таблица 4.19. Функции преобразования Функция Возвращаемое значение CHARTOROWID(char) Идентификатор строки (тип данных ROWID ) из стро- ки char CONVERT(char,set1 [,set2]) Преобразованное char (по набору символов set1 ). Необязательный аргумент set2 задает исходный набор символов HEXTORAW(char) Строка char , преобразованная из шестнадцатерич- ного представления в двоичное — удобное для включения в RAW - столбец (столбец с исходными данными) RAWTOHEX(raw) Строка шестнадцатеричных значений, получаемая из raw (исходные данные) ROWIDTOCHAR(rowid) Символьная строка длиной 18 символов, получен- ная rowid (идентификатор строки) TO_CHAR(expr[,fmt [,nls]]) expr преобразуется из числового значения или да- ты в символьную строку по формату, заданному в fmt . Необязательный аргумент nls позволяет за- дать используемый в функции национальный язык. Если fmt опущено, то числовое expr преобразует- ся в строку такой длины, которая вмещает только значащие цифры; дата же преобразуется по фор- мату даты согласно умолчанию: 'DD-MON-YY' TO_DATE(char[,fmt [,nls]]) Преобразование даты из символьного вида в зна- чение даты по формату, заданному в fmt . Необяза- тельный аргумент nls позволяет задать исполь- зуемый в функции национальный язык. Если fmt опущена, char должна иметь формат даты по умолчанию: 'DD-MON-YY' TO_LOB(long_column) При выполнении операции INSERT...SELECT по- зволяет преобразовать значения из столбца с ти- пом данных LONG или LONG RAW в столбец с данны- ми типа CLOB или BLOB TO_MULTI_BYTE(char) Преобразование char с однобайтовыми символами в многобайтовые символы Глава 4. Основы SQL 91 Таблица 4.19 (окончание) Функция Возвращаемое значение TO_NUMBER(char[,fmt [,nls]]) Преобразование char в число по формату fmt Необязательный аргумент nls позволяет задать используемый в функции национальный символ валюты TO_SINGLE_BYTE(char) Преобразование char с многобайтовыми символа- ми в однобайтовые TRANSLATE(char USING {CHAR_CS|NCHAR_CS}) Преобразует char в набор символов национально- го языка ( NCHAR_CS ) или наоборот ( CHAR_CS ); функ- ция аналогична CONVERT и используется с типом данных NCHAR и NVARCHAR2 Таблица 4.20. Числовые форматы Элемент Пример Описание 9 9999 Количество цифр, определяющих ширину вывода 0 0999 Вывод ведущих нулей $ $9999 Вывод перед значением знака доллара B B9999 Вывод пробелов вместо ведущих нулей FM FM90.9 Вывод без начальных и конечных пробелов MI 9999MI Вывод знака "–" после отрицательных чисел S S9999 Вывод "+" для положительных чисел и "–" для отри- цательных PR 9999PR Вывод отрицательных чисел в <угловых скобках> D 99D99 Вывод десятичного разделителя G 9G999 Вывод разделителя групп (например, триад в де- нежных данных) C C999 Вывод символа интернациональной денежной еди- ницы L L999 Вывод конкретного денежного символа , 9,999 Вывод запятой в указанной позиции Часть II. Язык SQL. Извлечение данных 92 Таблица 4.20 (окончание) Элемент Пример Описание 99.99 Вывод точки в указанной позиции V 999V99 Умножение на 10 в N - ой степени, где N задается в виде количества девяток после 'V' EEEE 9&999EEEE Вывод в экспоненциальной форме RN rn RN rn Возвращает значения прописными или строчными римскими цифрами (преобразуемое значение между 1 и 3999) 4.6.5. Различные функции для работы с одиночной строкой Таблица 4.21. Различные функции Функция Возвращаемое значение BFILENAME ('directory', 'filename') Возвращает BFILE (указатель доступа к файлам ОС, где хранятся данные типа LOB) DUMP(expr[,k[,m[,n]]] ) Строка символов, содержащая код типа данных, длину в байтах и внутреннее представление expr . Необязательный аргумент k позволяет за- дать представление возвращаемого значения: 8 — восьмеричное, 10 — десятичное, 16 — шест- надцатеричное, 17 — одиночные символы. Не- обязательный аргумент m задает начальную по- зицию в expr , а необязательный аргумент n — длину возвращаемого значения, начиная с m GREATEST(expr1, expr2,...) Наибольшее значение из перечня. Перед сравне- нием все выражения пpеобpазуются к типу перво- го выражения LEAST(expr1, expr2,...) Наименьшее значение из перечня. Перед сравне- нием все выражения преобразуются к типу перво- го выражения NLS_CHARSET_DECL _LEN (width,char) Возвращает ширину ( width ) объявления NCHAR столбца с именем ( char ) NLS_CHARSET_ID (char) Возвращает идентификатор NLS набора симво- лов, соответствующий имени набора ( char ) Глава 4. Основы SQL 93 Таблица 4.21 (окончание) Функция Возвращаемое значение NLS_CHARSET_NAME (n) Возвращает имя NLS набора символов, соответ- ствующего номеру идентификатора набора ( n ) NVL(expr1,expr2) Если expr1 равно NULL , возвращает expr2 , иначе возвращает expr1 Expr1 и expr2 могут быть лю- бого типа. Тип возвращаемой величины такой же, как для expr1 NVL2 (expr1, expr2, expr3) Если expr1 равно NULL , возвращает expr3 , иначе возвращает expr2 Expr1 имеет любой тип данных, expr2 и expr3 не могут быть типа LONG . Тип возвра- щаемой величины такой же, как для expr2 или expr3 SYS_CONTEXT ('namespace', 'attribute' [,n]) Возвращает значение атрибута, связанного с кон- текстом namespace . Значение n позволяет изме- нять заданную по умолчанию максимальную длину возвращаемого значения (256), доводя ее до 4000 SYS_GUID() Возвращает глобально уникальный идентификатор UID Уникальный целочисленный идентификатор те- кущего пользователя USER Имя текущего пользователя USERNV(char) Информация о среде текущего сеанса. Если char равен: 'ISDBA' — возвращает 'TRUE' если вы имеете ISDBA - роль; 'LANGUAGE' — возвращает используемый язык; 'TERMINAL' — возвращается идентификатор терминала пользователя (в терминах операцион- ной системы); 'SESSIONID' — возвращается идентификатор сеанса пользователя; 'ENTRYID' — возвращается доступный иденти- фикатор элемента, за которым идет слежение; 'LANG' — возвращает ISO - аббревиатуру текуще- го языка; 'INSTANCE' — возвращает номер текущего эк- земпляра ( INSTANCE ); 'CLIENT_INFO' — возвращает до 64 байтов ин- формации сеанса; она может быть сохранена прикладной программой, использующей пакет DBMS_APPLICATION_INFO VSIZE(expr) Число байтов во внутреннем представлении expr Часть II. Язык SQL. Извлечение данных 94 4.6.6. Агрегатные функции Агрегатные функции возвращают результат обработки группы строк (табл. 4.22). Они имеют смысл в конструкциях SELECT , ORDER BY , HAVING и в аналитических функциях. Использование DISTINCT позволяет учитывать только различающиеся значе- ния аргумента expr . При указании ALL (или по умолчанию) учитываются все значения expr . Например, DISTINCT при нахождении среднего значения из 1,1,1 и 3 дает результат 2 , тогда как ALL при этой же операции дает резуль- тат 1.5 Таблица 4.22. Агрегатные функции Функция Возвращаемое значение AVG([DISTINCT | ALL] expr) Среднее значение expr , с игнорировани- ем пустых ( NULL ) значений CORR(expr1,expr2) Возвращает коэффициент корреляции набора пар значений expr1 , expr2 ; для его определения используется формула: COVAR_POP(expr1,expr2)/ (STDDEV_POP(expr1)* STDDEV_POP(expr2)) COUNT({[DISTINCT | ALL] expr |*}) Количество строк, в которых expr не яв- ляется пустым ( NULL ) значением. Уста- новка * позволяет подсчитать все вы- бранные строки, включая строки с NULL - значениями COVAR_POP(expr1, expr2) Значение (СУММА(expr1*expr2) - СУММА(expr2)* СУММА(expr1)/n)/n , где n — номер пары (expr1,expr2) , в кото- рой ни expr1 , ни expr2 не содержат NULL COVAR_SAMP(expr1, expr2) Значение (СУММА(expr1*expr2) - СУММА(expr1)*СУММА(expr2)/n)/(n-1) , где n — номер пары (expr1,expr2) , в которой ни expr1 , ни expr2 не содер - жат NULL MAX([DISTINCT | ALL] expr) Максимальное значение expr MIN([DISTINCT | ALL] expr) Минимальное значение expr Глава 4. Основы SQL 95 Таблица 4.22 (окончание) Функция Возвращаемое значение STDDEV([DISTINCT | ALL] expr) Среднеквадратичное (стандартное) от- клонение от expr с игнорированием пус- тых ( NULL ) значений SUM([DISTINCT | ALL] expr) Сумма значений expr VARIANCE([DISTINCT | ALL] expr) Дисперсия expr , с игнорированием пус- тых значений 4.6. 7. Функции CASE, CAST и DECODE Функция CASE позволяет использовать выражения IF-THEN-ELSE в инструк- циях SELECT и UPDATE .Эта функция проверяет список условий и возвращает одно из нескольких возможных значений. Инструкция CASE имеет два способа применения: простой и поисковый. -- Простая операция сравнения CASE выражение WHEN искомое_значение THEN результат [...] [ELSE альтернативный_результат] END В простой функции CASE входное значение ( выражение ) проверяется на соот- ветствие каждому предложению WHEN .Результирующее значение ( результат ) возвращается при первой истинности: выражение = искомое_значение Если ни одно из условий when не оказывается истинным, то возвращается альтернативный_результат . Если это альтернативное значение не указано, то возвращается NULL Рассмотрим пример преобразования оценок, хранимых в таблице Ведомости базы данных "UCHEB", в официальные текстовые значения. В примере ис- пользованы синтаксические конструкции, которые подробно рассматривают- ся в главе 5. SELECT оценка, CASE оценка WHEN '5' THEN 'отлично' Часть II. Язык SQL. Извлечение данных 96 WHEN '4' THEN 'хорошо' WHEN '3' THEN 'удовлетворительно' WHEN '2' THEN 'неудовлетворительно' WHEN 'зачет' THEN 'зачет' WHEN 'незач' THEN 'незачет' WHEN 'осв' THEN 'освобождение' WHEN 'неявка' THEN 'неявка' ELSE 'отсутствие данных' END официальное_значение, дата, состояние FROM н_ведомости WHERE члвк_ид = 116483; Часть оценок студента с номером 116483 имеет следующий вид: ОЦЕНКА ОФИЦИАЛЬНОЕ_ЗНАЧЕНИЕ ДАТА СОСТОЯНИЕ -------- -------------------- ----------- ------------ 5 отлично 24.12.1998 актуальна 4 хорошо 29.12.1998 актуальна зачет зачет 05.01.1999 актуальна 3 удовлетворительно 16.06.1999 актуальна . отсутствие данных 05.01.2001 актуальна неявка неявка 28.12.2001 неактуальна зачет зачет 05.06.2007 актуальна неявка неявка 19.06.2007 актуальна 2 неудовлетворительно 19.06.2007 актуальна Поисковые выражения CASE позволяют проанализировать несколько логиче- ских условий, и как только одно из них оказывается истинным, выражение возвращает соответствующий этому условию результат. -- Булева поисковая операция CASE WHEN логическое_условие THEN результат [...] [ELSE альтернативный_результат] END Структура более сложной логической поисковой операции, аналогична структуре простой операции сравнения за исключением того, что для каж- дого предложения WHEN существует своя логическая операция сравнения (не обязательно " = "). Глава 4. Основы SQL 97 Так, предыдущий пример можно записать и в таком виде: SELECT оценка, CASE WHEN оценка = '5' THEN 'отлично' WHEN оценка = '4' THEN 'хорошо' WHEN оценка = '3' THEN 'удовлетворительно' WHEN оценка = '2' THEN 'неудовлетворительно' WHEN оценка = 'зачет' THEN 'зачет' WHEN оценка = 'незач' THEN 'незачет' WHEN оценка = 'осв' THEN 'освобождение' WHEN оценка = 'неявка' THEN 'неявка' ELSE 'отсутствие данных' END официальное_значение, дата, состояние FROM н_ведомости WHERE члвк_ид = 116483: Приведем более сложный пример с подсчетом количества различных оценок, полученных студентом за время обучения в университете. SELECT CASE WHEN оценка = '5' THEN 'отлично' WHEN оценка = '4' THEN 'хорошо' WHEN оценка = '3' THEN 'удовлетворительно' WHEN оценка = '2' THEN 'неудовлетворительно' WHEN оценка = 'зачет' THEN 'зачет' WHEN оценка = 'незач' THEN 'незачет' WHEN оценка = 'осв' THEN 'освобождение' WHEN оценка = 'неявка' THEN 'неявка' ELSE 'отсутствие данных' END официальное_значение, оценка, COUNT(*) количество FROM н_ведомости WHERE члвк_ид = 116483 GROUP BY оценка; Результат выполнения запроса: ОФИЦИАЛЬНОЕ_ЗНАЧЕНИЕ ОЦЕНКА КОЛИЧЕСТВО -------------------- -------- ---------- отсутствие данных . 6 Часть II. Язык SQL. Извлечение данных 98 неудовлетворительно 2 4 удовлетворительно 3 66 хорошо 4 26 отлично 5 2 зачет зачет 91 неявка неявка 2 Функция CAST неявно преобразует выражение с одним типом данных в дру- гой тип и имеет синтаксис: CAST (выражение AS тип_данных [(длина)]) Функция CAST преобразует любое выражение, например значение в столбце или переменную, в другой, указанный тип данных. Для тех типов, которые поддерживают указание длины (например, CHAR или VARCHAR ), можно указать длину. Отметим, что некоторые преобразования, например такие, как преобразова- ние значений DECIMAL к INTEGER , приводят к операциям округления. Кроме того, некоторые преобразования могут завершиться ошибкой, если у нового типа данных не достаточно места для отображения преобразованного значения. Функция DECODE имеет синтаксис DЕСОDЕ(выражение, искомое_значение1, результат1 [,искомое_значение2, ре- зультат2 [,…]], [, по_умолчанию]) Функция сравнивает выражение с искомым _ значением1 . Если выражение равно ему, то возвращается результат1 . В противном случае выражение сравнива- ется с искомым_значением2 и т. д. Если совпадение не обнаруживается, функ- ция возвращает значение по_умолчанию или NULL , если это значение опущено. Реализуем с помощью функции DЕСОDЕ рассмотренный ранее пример преоб- разования оценок: SELECT оценка, (DECODE(оценка,'5','отлично', '4','хорошо', '3','удовлетворительно', '2','неудовлетворительно', 'зачет', 'зачет', 'незач', 'незачет', 'осв', 'освобождение', 'неявка', 'неявка', 'отсутствие данных')) эквивалент, дата, состояние FROM н_ведомости WHERE члвк_ид = 116483; |