УП СУБД ч1

SELECT LAST_DAY( SYSDATE ),

LAST_DAY( SYSDATE ) - SYSDATE «осталось»

FROM DUAL;		(7.9)
	LAST_DAY(SYSDATE)	осталось
	31-JUL-08	2

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

Кроме того, существуют функции ADD_MONTH, MONTH_BETWEEN, NEXT_DAY и др.

Теперь рассмотрим вопрос преобразования типов при вычислении выражений. В качестве примера приведем три запроса:

SELECT item + price FROM acceptedorders;	(7.10)
SELECT item || price FROM acceptedorders;	(7.11)
SELECT item || TO_CHAR( price )	(7.12)
FROM acceptedorders;

Из них первый запрос 7.10 является некорректным и не будет вычислен, поскольку тип поля item – строка, тип поля price – число, а оператор «+» применяется только к числовым значениям. Второй запрос, однако, будет выполнен, несмотря на то, что оператор «||» применяется только к строковым значениям, и сформирует следующую выборку:

ITEM||PRICE

Watch 199.99

Book 12.5

Pizza 5.5

PS3 400

Pizza 5.5

Book 12.5

DVD Disk 6.35

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

Разница состоит в том, что перед вычислением выражений производится попытка преобразования операндов к типу, который требуется при вычислении данной операции.

71

В первом случае, поскольку оператор «+» – арифметический, произойдет попытка преобразовать значения поля item в значения целого типа, причем преобразованы должны быть значения этого поля во всех строках, которые используются в запросе. Однако конкретные значения ‘Watch’, ‘Book’ не могут быть преобразованы в числа, поэтому запрос 7.9 является ошибочным с точки зрения исполнения, хотя синтаксически он правильный. Например, следующий запрос будет выполнен, поскольку значение ‘134’ допускает преобразование в число:

SELECT ‘123’ + price FROM acceptedorders

(7.13)

Во втором случае, при вычислении запроса 7.11, оператор «||» строковый, а числовые значения (поле price) всегда преобразуются в строки.

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

К функциям преобразования типов относятся функции

TO_CHAR(), TO_NUMBER(), TO_DATE(); вторым параметром этих функций обычно является специальная строка, определяющая способ форматирования значения. Ниже приведены примеры основных способов использования этих функций.

Запрос 7.14 преобразует числовое значение поля price в формат для денежного представления и в значение в научном формате.

SELECT price, TO_CHAR( price, ‘U999,000.99’ ),
TO_CHAR( price, ‘9.9EEEE’ )	(7.14)
FROM acceptedorders;

72

PRICE	TO_CHAR(PRICE,’U999,000.99’)	TO_CHAR(PRICE,’9.9EEEE’)
199.99	$199.99	2.0E+02
12.5	$012.50	1.3E+01
5.5	$005.50	5.5E+00
400	$400.00	4.0E+02
5.5	$005.50	5.5E+00
12.5	$012.50	1.3E+01
6.35	$006.35	6.4E+00

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

Запрос 7.15 иллюстрирует вывод даты в различных форматах.

SELECT SYSDATE,

TO_CHAR( SYSDATE, ‘DD, Day Month YYYY’ )

«DD, Day Month YYYY»,
TO_CHAR( SYSDATE, ‘DD MM YYYY’ )			(7.15)
«DD MM YYYY» FROM DUAL
SYSDATE	DD, Day Month YYYY		DD MM YYYY
22-AUG-08	22, Friday August 2008		22 08 2008

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

При форматировании значений-дат можно использовать значения форматеров, приведенных в таблице 4.

Помимо многочисленных функций преобразования вида TO_XXXX(), можно использовать функцию CAST, имеющую следующий синтаксис:

CAST( выражение AS тип )

Эта функция выполняет преобразование значения выражения к указанному типу, если это возможно. Ниже приведены примеры преобразования типов при помощи этой функции.

SELECT name,
CAST( name AS VARCHAR(3) ) «VARCHAR(3)»,
CAST( name AS CHAR(3) ) «CHAR(3)»	(7.16)
FROM student

73

NAME	VARCHAR(3)	CHAR(3)
Jane	Jan	Jan
Bill	Bil	Bil
Polie	Pol	Pol
Gill	Gil	Gil

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

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

Оператор CASE предназначен для замены одних значений на другие, в зависимости от выполнения логических условий, и напоминает операторы IF и SWITCH в процедурных языках программирования. Оператор CASE имеет два варианта. Синтаксис и использование первого варианта иллюстрирует следующий запрос 7.17:

SELECT DISTINCT student,

CASE student WHEN ‘Bill’ THEN student || ‘-Boy’ WHEN ‘Polie’ THEN ‘My Lovely ‘ || student WHEN ‘Jane’ THEN ‘Naughty ‘ || student

ELSE student || ‘...Who is that? ‘

END «An Opinion»		(7.17)
FROM CourseResult
	STUDENT	An Opinion
	Bill	Bill -Boy
	Jane	Naughty Jane
	Polie	My Lovely Polie

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

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

74

используется значение после слова ELSE. Конструкцию CASE завершает ключевое слово END.

Во втором варианте оператора CASE после слова WHEN используется логическое выражение, а не литеральная конс­ танта, как в первом варианте:

SELECT student || ‘ gets ‘ || CASE WHEN Result = 5 THEN ‘A’ WHEN Result = 4 THEN ‘B’

WHEN Result = 3 THEN ‘C’

WHEN Result IS NULL THEN ‘nothing’

ELSE ‘..What is that? ‘
END «Result»	(7.18)
FROM CourseResult

Примечание: Пустые значения NULL подробнее рассмотрены в разделе 10.

Result

Bill gets A

Polie gets nothing

Jane gets A

Jane gets B

Bill gets nothing

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

Существует также функция DECODE, аналогичная оператору CASE; эта функция использовалась в более ранних версиях Oracle. Ниже приведен запрос 7.19, аналогичный запросу 7.18, реализованный с использованием функции

DECODE:

SELECT student || ‘ gets ‘ ||

DECODE( Result, 5, ‘A’,

	4, ‘B’,
	3, ‘C’,
	NULL, ‘nothing’,
‘	...What is that? ‘ ) «Result»	(7.19)
FROM CourseResult

75

Выводы

•использование встроенных функций существенно расширяет возможности обработки данных при помощи языка SQL;

•вычислить значение некоторой функции можно при помощи следующего запроса:

SELECT <имя функции> FROM DUAL;

•к математическим функциям относятся алгебраические (SQRT, POWER), тригонометрические (SIN, COS и т.д.), а также функции округления (FLOOR, CEIL, ROUND, TRUNC);

•строковые функции (LPAD, LTRIM, UPPER, REPLACE, SUBSTR, LENGTH и др.) предназначены главным образом для преобразования строковых значений;

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

•к функциям для работы со значениями-датами относятся функции SYSDATE и LAST_DAY, а также функции TRUNC и ROUND, выполняющие округление значений-дат;

•при использовании в выражении значений различных типов необходимо выполнять преобразование типов, которое бывает явное и неявное.

•для выполнения явного преобразования типов используются функции TO_CHAR(), TO_NUMBER(), TO_DATE(), а также CAST();

•оператор CASE предназначен для замены одних значений на другие, в зависимости от выполнения логических условий, и напоминает операторы IF и SWITCH в процедурных языках программирования.

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

1.Какие преимущества обеспечивает использование встроенных функций в выражениях языка SQL?

2.Перечислите основные строковые функции языка SQL Oracle.

76

3.Объясните различия между явным и неявным способами преобразования типов.

4.Перечислите функции округления и объясните различия между ними.

5.Приведите примеры использования функции TO_ CHAR() для преобразования значений дат в строки с различными форматерами.

6.Объясните различия между двумя вариантами оператора CASE.

77

Раздел 8. Язык Oracle SQL. Использование селекции данных

Данный раздел посвящен описанию средств языка SQL Oracle для построения запросов с селекций.

Для спецификации условий селекции записей исходной таблицы используется логическое выражение, указываемое после слова WHERE, которое может включать, помимо арифметических и строковых операций и функций, операции сравнения ( <, >, =, <> или !=, >=, <= ), логические операции AND, OR и NOT и специальные предикаты.

Ниже приведены простые примеры использования логических выражений. Запрос 8.1 позволяет выбрать заказы, относящиеся к заказчику ‘Ms.Magpie’.

SELECT item, price, quantity, customer_address
FROM acceptedorders				(8.1)
WHERE customer_name = ‘Ms.Magpie’ ;
ITEM	PRICE	QUANTITY	CUSTOMER_ADDRESS
Book	12.5	1	Pall Mall, 3
Pizza	5.5	3	Pall Mall, 3
PS3	400	1	Pall Mall, 3

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

Запрос 8.2. выбирает заказы с общей стоимостью более 100:

SELECT item, price, quantity, customer_address

FROM acceptedorders
WHERE price * quantity > 100;			(8.2)
ITEM	PRICE	QUANTITY	CUSTOMER_ADDRESS
Watch	199.99	1	Green str. 2
PS3	400	1	Pall Mall, 3

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

Запросы 8.3 и 8.4 иллюстрируют использование логических операций AND и OR.

78

SELECT item, price, quantity, customer_address

	FROM acceptedorders
	WHERE customer_name = ‘Ms.Magpie’
		AND	item = ‘Book’;	(8.3)
ITEM		PRICE	QUANTITY	CUSTOMER_ADDRESS
Book		12.5	1	Pall Mall, 3

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

SELECT item, price, quantity, customer_address

	FROM acceptedorders
	WHERE customer_name = ‘Ms.Magpie’
		OR item = ‘Book’;		(8.4)
ITEM		PRICE	QUANTITY	CUSTOMER_ADDRESS
Book		12.5	1	Pall Mall, 3
Pizza		5.5	3	Pall Mall, 3
PS3		400	1	Pall Mall, 3
Book		12.5	2	Green str. 2

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

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

CREATE TABLE Адрес

(Улица CHAR(100),

Дом		CHAR(10),
Фамилия		CHAR(100) )
	УЛИЦА		ДОМ	ФАМИЛИЯ
	Садовая		10	Ахмедов
	Лесная		2	Кузнецов
	Садовая		10	Ким
	Садовая		3	Кирпичный
	Школьная		10	Архипов
	Новая		1	Столярова
	Школьная		1	Школьников
	Лесная		2	Петровский

Рис. 8.5. Таблица «Адрес»

79

SELECT * FROM Адрес	(8.5)
WHERE Улица = ‘Садовая’ AND Дом = ‘10’;
SELECT * FROM Адрес	(8.6)
WHERE Улица = ‘Садовая’ OR Дом = ‘10’

Обратите внимание на использование в именах объектов русских букв; использовать двойные кавычки здесь не обязательно.

Действительно, запрос с использованием OR обладает двумя недостатками:

1.Условие типа «Найти жителей, которые живут на улице Ленина или в доме номер 10» выглядит с логической точки зрения странным.

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

с высокой селективностью.

Использование запросов с малой селективностью, скорее всего, свидетельствует о неправильном понимании решаемой задачи или неправильной формулировке запроса.

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

SELECT Фамилия FROM Адрес
WHERE Улица = ‘Садовая’	(8.7)
AND Улица = ‘Школьная’;

Значением этого запроса, конечно же, всегда будет пустое множество, поскольку одно и то же поле не может быть одновременно равным двум различным значениям. В данном случае связку «и» из словесной формулировки следует интерпретировать как «или на Садовой, или на Школьной». Запрос приобретет правильный вид:

SELECT Фамилия FROM Адрес WHERE Улица = ‘Садовая’

OR Улица

= ‘Школьная’;

(8.8)

80