УП СУБД ч1

CUSTOMER_NAME	ROWID	ROWNUM
Mr.Pundleberry	AAADy8AAEAAAADUAAA	1
Mr.O’Raily	AAADy8AAEAAAADUAAB	2
Ms.Magpie	AAADy8AAEAAAADUAAC	3
Ms.Magpie	AAADy8AAEAAAADUAAD	4
Mr.Pundleberry	AAADy8AAEAAAADYAAA	5
Ms.Magpie	AAADy8AAEAAAADYAAB	6
Mr.O’Raily	AAADy8AAEAAAADYAAC	7

Рис. 6.11. Значение запроса 6.11

Поле ROWNUM содержит номер записи в результирующем множестве запроса (см. следующий раздел). Следует иметь в виду, что это значение формируется при вычислении запроса и никак не связано с хранением записи в базе данных. Это поле можно использовать, например, для того чтобы ограничить количество выдаваемых на экран строк, как в запросе 6.12:

SELECT customer_name,item FROM acceptedorders

WHERE rownum < 5		(6.12)
	CUSTOMER_NAME	ITEM
	Mr.Pundleberry	Book
	Mr.O’Raily	DVD Disk
	Ms.Magpie	Pizza
	Ms.Magpie	PS3

Рис. 6.12. Значение запроса 6.12

6.4. Значение SQL-запроса

Теперь более точно объясним, что такое значение SQLзапроса. Значение запроса можно определить с двух точек зрения – с формальной и с неформальной. Формальным значением запроса всегда является таблица (возможно пустая), структура и содержание которой определяется в соответствии с семантикой языка SQL. Неформальное значение описывает смысл запроса с точки зрения предметной области. Сформулируем правило вычисления формального значения SQL-запросов при извлечении данных из одной таблицы.

61

1.Выполнение проверки каждой строки исходной таблицы на соответствие логическому условию конструкции WHERE, если оно указано.

2.Вычисление всех выражений, перечисленных после слова SELECT для каждой строки таблицы, оставшейся после проверки логического условия, и формирование результирующего множества, в котором будет столько же строк, сколько осталось после проверки логических условий, и столько колонок, сколько выражений перечислено после SELECT.

3.Удаление дубликатов из результирующего множества, если используется DISTINCT.

4.Упорядочивание строк в результирующем множестве

всоответствии с выражениями конструкции ORDER BY. Рассмотрим два примера.

1.SELECT ‘Покупатель‘, customer_name, ‘ из ‘,

customer_address, ‘ заказал ‘, item					(6.13)
FROM acceptedorders;
‘ПОКУПАТЕЛЬ’	CUSTOMER_NAME	‘ИЗ’	CUSTOMER_ADDRESS	‘ЗАКАЗАЛ’	ITEM
Покупатель	Mr.Pundleberry	из	Green str. 2	заказал	Watch
Покупатель	Ms.Magpie	из	Pall Mall, 3	заказал	Book
Покупатель	Ms.Magpie	из	Pall Mall, 3	заказал	Pizza
Покупатель	Ms.Magpie	из	Pall Mall, 3	заказал	PS3
Покупатель	Mr.O’Raily	из	Docks	заказал	Pizza
Покупатель	Mr.Pundleberry	из	Green str. 2	заказал	Book
Покупатель	Mr.O’Raily	из	Docks	заказал	DVD Disk

Рис. 6.13. Значение запроса 6.13

2. SELECT ‘Покупатель‘ || customer_name || ‘ из ‘ ||

customer_address ||	‘ заказал ‘ || item
FROM acceptedorders;	(6.14)

‘ПОКУПАТЕЛЬ’||CUSTOMER_NAME||’ИЗ’||CUSTOMER_ADDRESS||’ЗАКАЗАЛ’||ITEM

Покупатель Mr.Pundleberry из Green str. 2 заказал Watch

Покупатель Ms.Magpie из Pall Mall, 3 заказал Book

Покупатель Ms.Magpie из Pall Mall, 3 заказал Pizza

Покупатель Ms.Magpie из Pall Mall, 3 заказал PS3

Покупатель Mr.O’Raily из Docks заказал Pizza

Покупатель Mr.Pundleberry из Green str. 2 заказал Book

Покупатель Mr.O’Raily из Docks заказал DVD Disk

Рис. 6.14. Значение запроса 6.14

62

Несмотря на внешнюю схожесть, результирующее множество запроса 6.13 состоит из шести колонок, тогда как запрос 6.14 – из одной.

Выводы

•текст на языке SQL состоит из команд языка и комментариев;

•команда SQL включает ключевые слова языка, имена таблиц, полей и других объектов базы данных, литеральные константы и выражения (логические, арифметические, строковые);

•SQLтаблица – множество записей или строк; все строки в таблице имеют одинаковую структуру, которая определяется при создании таблицы; для создания таблицы используется команда CREATE TABLE;

•наиболее распространенные типы полей таблиц – числа, строки и даты;

•для просмотра структуры таблицы используется команда DESCR;

•структура простейшего запроса – SELECT <список выражений > FROM <имя таблицы>

•значением SELECT-запроса является выборка (результирующее множество), имеющая структуру таблицы;

•функция COUNT(*) используется для определения количества записей в таблице;

•конструкция WHERE используется для селекции данных при помощи логического условия;

•для изменения имени столбца в результирующем множестве используется конструкция AS;

•для упорядочивания результирующего множества используется конструкция ORDER BY;

•для удаления дубликатов из результирующего множества используется конструкция DISTINCT.

Вопросы для контроля

1. Каким образом записываются комментарии в языке SQL?

63

2.Перечислите имена известных типов данных в языке

SQL.

3.Как определить количество записей и имена полей в SQL-таблицы?

4.Сформулируйте правила вычисления значения SELECT-запроса.

5.Объясните, сколько колонок будет в результирующем множестве некоторого SELECT-выражения.

6.Что такое «формальные» и «неформальные» значения некоторого SELECT-запроса?

64

Раздел 7. Язык Oracle SQL. Встроенные функции

ипреобразования типов

Вданном разделе описаны наиболее распространенные встроенные функции языка SQL Oracle и способы их практического использования, рассмотрены вопросы, связанные

спреобразованием типов, а также с использованием оператора CASE.

Ввыражениях в SQL-запросах можно использовать различные встроенные функции: математические, строковые, системные, функции преобразования и агрегатные функции. Применение встроенных функций существенно расширяет возможности обработки данных при помощи языка SQL и на практике чрезвычайно полезно.

К математическим функциям относятся алгебраические (SQRT, POWER), тригонометрические (SIN, COS и т.д.), логарифмические функции (LOG, LN), а также функции округления (FLOOR, CEIL, ROUND, TRUNC). Подробный список существующих в системе ORACLE функций приводится в документации. Запрос 3.1. иллюстрирует использование математических функций в SQL-запросе:

SELECT TRUNC( LN( 10 ), 12), FLOOR( 23.45 ),

CEIL( 23.45 ) FROM dual;		(7.1)
TRUNC(LN(10),12)	FLOOR(23.45)	CEIL(23.45)
2.302585092994	23	24

Рис. 7.1. Значение запроса 7.1

В запросе 7.1. имя DUAL – это специальное имя, которое используется в соответствии с синтаксисом языка SQL в системе ORACLE вместо имени реальной таблицы для вызова функций. Функция TRUNC используется для отбрасывания от десятичной дроби значения разрядов, в данном случае – начиная с двенадцатого, CEIL – округление до ближайшего большего целого.

Строковые функции предназначены главным образом для преобразования строковых значений. Изучение и ис-

65

пользование строковых функций имеет большое практическое значение, в первую очередь для формирования удобных презентабельных текстовых отчетов. Среди многочисленных строковых функций необходимо выделить следующие:

	Таблица 3
	Строковые функции
LPAD	Добавление к строке нескольких символов слева
RPAD	Добавление к строке нескольких символов справа
LTRIM	Удаление у строки незначащих символов слева
RTRIM	Удаление у строки незначащих символов справа
UPPER	Преобразование символов строки в верхний регистр
LOWER	Преобразование символов строки в нижний регистр
INITCAP	Преобразование первых символов слов строки
	в верхний регистр
REPLACE	Замена в строке одного набора символов (подстроки)
	на другую строку
SUBSTR	Выделение из строки подстроки
INSTR	Поиск подстроки в строке
LENGTH	Вычисление длины строки (в количестве символов)

Строковые функции корректно работают со строковыми значениями национальных алфавитов.

Ниже приведены запросы, иллюстрирующие использование строковых функций. Запрос 7.2. иллюстрирует добавление символов «*» к строке слева или справа, чтобы общая длина получившейся строки стала 25 символов; эта функция удобна для выравнивания колонок текста в текс­ товых отчетах.

SELECT customer_name,

LPAD( customer_name, 25, ‘*‘ ) «LPAD», RPAD( customer_name, 25, ‘*‘ ) «RPAD»

FROM acceptedorders;		(7.2)
CUSTOMER_NAME	LPAD	RPAD
Mr.Pundleberry	*****Mr.Pundleberry	Mr.Pundleberry *****
Ms.Magpie	*****Ms.Magpie	Ms.Magpie *****

Рис. 7.2. Значение запроса 7.2

66

В запросе 7.3 используется обратное действие – отсечение незначащих символов от строки слева или справа (сначала к исходным значениям полей искусственно добавляется строка ‹!!!!!!› ).

SELECT customer_name,

LTRIM( ‘!!!!!!’ || customer_name, ‘!’ ) “LTRIM”,

RTRIM( customer_name || ‘!!!!’, ‘!’ ) “RTRIM”

FROM acceptedorders;		(7.3)
CUSTOMER_NAME	LTRIM	RTRIM
Mr.Pundleberry	Mr.Pundleberry	Mr.Pundleberry
Ms.Magpie	Ms.Magpie	Ms.Magpie
Ms.Magpie	Ms.Magpie	Ms.Magpie
Ms.Magpie	Ms.Magpie	Ms.Magpie
Mr.O’Raily	Mr.O’Raily	Mr.O’Raily
Mr.Pundleberry	Mr.Pundleberry	Mr.Pundleberry
Mr.O’Raily	Mr.O’Raily	Mr.O’Raily

Рис. 7.3. Значение запроса 7.3

В запросе 7.4. производится преобразование строковых значений в верхний и нижний регистры и замена первых букв слов на заглавные.

SELECT customer_name, UPPER( customer_name ) «UPPER», LOWER( customer_name ) «LOWER»,

INITCAP( LOWER( customer_name )) «INITCAP»

FROM acceptedorders;			(7.4)
CUSTOMER_NAME	UPPER	LOWER	INITCAP
Mr.Pundleberry	MR.PUNDLEBERRY	mr.pundleberry	Mr.Pundleberry
Mr.Pundleberry	MR.PUNDLEBERRY	mr.pundleberry	Mr.Pundleberry
Mr.O’Raily	MR.O’RAILY	mr.o’raily	Mr.O’Raily
Ms.Magpie	MS.MAGPIE	ms.magpie	Ms.Magpie
Ms.Magpie	MS.MAGPIE	ms.magpie	Ms.Magpie
Ms.Magpie	MS.MAGPIE	ms.magpie	Ms.Magpie
Mr.O’Raily	MR.O’RAILY	mr.o’raily	Mr.O’Raily

Рис. 7.4. Значение запроса 7.4

67

В запросе 7.5. выполняется замена подстроки ‘Ms’ на строку ‘Miss’, выделение подстроки из строки, начиная с четвертого символа от начала, а также вычисление длин строк.

SELECT customer_name,

REPLACE( customer_name, ‘Ms’, ‘Miss’) «REPLACE», SUBSTR( customer_name, 4 ) «SUBSTR»», LENGTH( RTRIM( customer_name )) «LENGTH»

FROM acceptedorders;			(7.5)
CUSTOMER_NAME	REPLACE	SUBSTR	LENGTH
Mr.PundleBerry	Mr.PundleBerry	PundleBerry	14
Mr.PundleBerry	Mr.PundleBerry	PundleBerry	14
Mr.O’Raily	Mr.O’Raily	O’Raily	10
Ms.Magpie	Miss.Magpie	Magpie	9
Ms.Magpie	Miss.Magpie	Magpie	9
Ms.Magpie	Miss.Magpie	Magpie	9
Mr.O’Raily	Mr.O’Raily	O’Raily	10

Рис. 7.5. Значение запроса 7.5

Функция INSTR используется для поиска подстроки в строке, значение функции – позиция, с которой начинается подстрока, или 0, если подстрока не найдена. Следующий запрос выводит значение самого поля customer_name и позицию, с которой начинается указанная подстрока. По результату видно, в каких строках в данном поле подстрока встречается, а где отсутствует.

SELECT customer_name, INSTR( customer_name, ‘Raily‘)

FROM acceptedorders		(7.6)
CUSTOMER_NAME		INSTR(CUSTOMER_NAME,’RAILY’)
Mr.Pundleberry		0
Mr.O’Raily		6
Ms.Magpie		0
Ms.Magpie		0
Mr.Pundleberry		0
Ms.Magpie		0
Mr.O’Raily		6

Рис. 7.6. Значение запроса 7.6

68

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

Большое значение имеет работа со значениями-датами и с функциями работы с датами. Значения-даты записываются в литеральном представлении, как и строковые значения, при помощи ограничивающих одиночных кавычек. С этими значениями можно выполнять некоторые дополнительные действия, в частности добавлять и вычитать числа, что интерпретируется как переход к следующей или предыдущей дате. Поскольку значения-даты могут записываться в разных форматах, обычно функции, получающие такие значения, как параметр, используют специальные строки, определяющие использованный формат записи даты. Для полного описания всех функций работы с датами следует обратиться к документации. В данном издании приводятся примеры использования наиболее типичных способов использования значений-дат.

Системная функция SYSDATE, употребляемая без параметров и без скобок, возвращает текущую дату, т.е. дату вызова этой функции. Следущий запрос иллюстрирует инкрементирование значения-даты; значение инкремента интерпретируется как количество дней.

SELECT SYSDATE, SYSDATE + 1, SYSDATE + 0.1, SYSDATE - 1, SYSDATE + 30, SYSDATE + 300

FROM DUAL;					(7.7)
SYSDATE	SYSDATE+1	SYSDATE+0.1	SYSDATE-1	SYSDATE+30	SYSDATE+300
29-JUL-08	30-JUL-08	29-JUL-08	28-JUL-08	28-AUG-08	25-MAY-09

Рис. 7.7. Значение запроса 7.7

Функции TRUNC и ROUND выполняют округление арифметических значений; применительно к значениям-датам эти функции используются для получения даты первого или последнего дня месяца или года. В запросе 3.7 определяются первые дни текущего и будущего года, первые дни текущего

69

и следующего месяца и округление текущей даты с арифметической точки зрения в нижнюю и верхнюю сторону, т.е. к текущей и следующей дате с точностью до дня. Второй параметр функций – это строка-формат преобразования даты.

SELECT SYSDATE “TODAY”,

TRUNC( SYSDATE, ‘YEAR’ ) «YEAR»,

ROUND( SYSDATE, ‘YEAR’ ) «YEAR», TRUNC( SYSDATE, ‘MONTH’ ) «MONTH», ROUND( SYSDATE, ‘MONTH’ ) «MONTH»,

	TRUNC( SYSDATE, ‘DDD’ ) «DDD»,				(7.8)
	ROUND( SYSDATE, ‘DDD’ ) «DDD» FROM DUAL;
TODAY	YEAR	YEAR	MONTH MONTH	DDD	DDD

29-JUL-08 01-JAN-08 01-JAN-09 01-JUL-08 01-AUG-08 29-JUL-08 30-JUL-08

Рис. 7.8. Значение запроса 7.8

В таблице 4 приводятся наиболее распространенные форматы.

Таблица 4

	Форматы дат
YYYY	Номер года из четырех знаков
YY	Номер года (последние две цифры)
Year	Запись номера года словами (по-английски)
MM	Номер месяца
Month	Название месяца (использует национальные настройки)
Mon	Первые три буквы названия месяца
Q	Номер квартала
DDD	Номер дня от начала года
DD	Номер дня от начала месяца
D	Номер дня недели
Day	Название дня недели (использует национальные настройки)
HH	Номер часа в 12-часовом формате
HH24	Номер часа в 24-часовом формате
MI	Минуты (от начала часа)
SS	Секунды (от начала минуты)

Функция LAST_DAY удобна для определения последнего дня месяца:

70