Лаврентев Освоение СQЛ 2009

11

DEMO@ORCL>PRINT var1 VAR1

---------

Oracle

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

II.1.4. Переменные подстановки в среде SQL*Plus

Q1_2 (Query 2 по теме 1). Использование переменной подстановки («t_name» в данном случае) для имени таблицы и значения столбца («n_loc» в данном случае) location_id в ней при извлечении информации. Знак «&» предшествует имени переменной подстановки.

DEMO@ORCL>SELECT * FROM &t_name WHERE location_id=&n_loc;

Введите значение для t_name: location Введите значение для n_loc: 123

прежний 1: SELECT * FROM &t_name WHERE location_id=&n_loc новый 1: SELECT * FROM location WHERE location_id=123

LOCATION_ID REGIONAL_GROUP

----------- --------------------

123 CHICAGO

Q1_3. Использование переменной подстановки («1» в данном случае) для значения столбца location_id.

DEMO@ORCL>SELECT REGIONAL_GROUP FROM location WHERE location_id ='&1';

Введите значение для 1: 124

прежний 2: WHERE location_id ='&1' новый 2: WHERE location_id ='124'

REGIONAL_GROUP

-----------------

DALLAS

Сохраним последний запрос в файл Q1_3.sql DEMO@ORCL>SAVE Q1_3

Создано file Q3_1.sql

Посмотрим значение параметра SQL*Plus termout DEMO@ORCL>SHOW TERMOUT

termout ON

Посмотрим значение параметра SQL*Plus ECHO DEMO@ORCL>SHOW ECHO

echo ON

12

Выполним команды «SET ECHO OFF», «SET TERMOUT OFF».

Выполним скрипт Q1_3 с вводимым значением столбца location_id =124 DEMO@ORCL>@Q1_3 124

DEMO@ORCL>

Скрипт выполнен, но ни выполняемая команда SQL, ни результат выполнения этой команды SQL*Plus не вывел.

Задействуем параметр TERMOUT и выполним скрипт Q1_3 с вводимым значением столбца location_id =124

еще раз

DEMO@ORCL>SET TERMOUT ON

DEMO@ORCL>@Q1_3 124

прежний 2: WHERE location_id ='&1' новый 2: WHERE location_id ='124'

REGIONAL_GROUP

--------------------

DALLAS

Выведен результат выполнения SQL команды, но сама команда не выведена. Задействуем теперь параметр ECHO и выполним скрипт Q1_3 с вводимым значением столбца location_id =124 еще раз

DEMO@ORCL>SET ECHO ON DEMO@ORCL>@Q1_3 124

DEMO@ORCL>SELECT regional_group FROM location WHERE location_id ='&1'

/

прежний 2: WHERE location_id ='&1'

новый 2: WHERE location_id ='124'

REGIONAL_GROUP

--------------------

DALLAS

Выведены SQL команда и результат ее выполнения.

Отметим здесь, что при отключенном параметре TERMOUT вывод полностью отключается вне зависимости от значения параметра echo DEMO@ORCL>SET TERMOUT OFF

DEMO@ORCL>@Q1_3 124 DEMO@ORCL>

Не выводится не только результат выполнения команды, но и сама команда, не смотря на установленный в «ON» параметр echo. Вернем прежнее значение параметра, выполнив

«SET TERMOUT ON».

Q1_4. Использование переменных подстановки («loc_number», «reg_group» в данном случае) для вставки значений двух столбцов location_id, regional_group в таблицу location.

DEMO@ORCL>INSERT INTO location(location_id, regional_group) VALUES(&loc_number, &reg_group);

Введите значение для loc_number: 125 Введите значение для reg_group: 'МОСКВА'

13

прежний 1: INSERT INTO location(location_id, regional_group) VALUES(&loc_number,&reg_group)

новый 1: INSERT INTO location(location_id, regional_group) VALUES(125,'МОСКВА')

1 строка создана.

!!!Для возврата содержимого таблицы к исходному состоянию ликвидируем последнюю вставку командой «ROLLBACK»

DEMO@ORCL>ROLLBACK;

откат завершен.

Q1_5. Использование двойного знака «&&» для переменной подстановки («loc» в данном случае). Значение введенной после сдвоенного знака «&» переменной подстановки запоминается в SQL*Plus, и в следующий раз при обращении к этой переменной подстановки ее значение вводится автоматически. DEMO@ORCL>SELECT * FROM location WHERE location_id=&&loc ;

Введите значение для loc: 123

прежний 1: SELECT * FROM location WHERE location_id=&&loc новый 1: SELECT * FROM location WHERE location_id=123

LOCATION_ID REGIONAL_GROUP

----------- --------------------

123 CHICAGO

DEMO@10g>SELECT * FROM department WHERE location_id=&&loc;

прежний 1: SELECT * FROM department WHERE location_id=&&loc новый 1: SELECT * FROM department WHERE location_id=123

DEPARTMENT_ID	NAME	LOCATION_ID
-------------	--------------	-----------
30	SALES	123
34	OPERATIONS	123

Здесь уже отсутствует приглашение для ввода значения «loc».

Покажем теперь, как изменить значение для переменной подстановки «loc». Сначала выведем текущее значение этой переменной:

DEMO@ORCL>DEFINE loc DEFINE LOC = "123" (CHAR)

Теперь присвоим этой переменной подстановки «loc» новое значение:

DEMO@ORCL>DEFINE loc = 124

DEMO@ORCL>SELECT * FROM department WHERE location_id=&&loc;

прежний 1: SELECT * FROM department WHERE location_id=&&loc новый 1: SELECT * FROM department WHERE location_id=124

DEPARTMENT_ID	NAME	LOCATION_ID
-------------	--------------	-----------
20	RESEARCH	124
23	SALES	124
24	OPERATIONS	124

Отменим присвоенное значение для переменной «loc»:

14

DEMO@ORCL>UNDEFINE loc

DEMO@ORCL>SELECT * FROM department WHERE location_id=&&loc;

Введите значение для loc: 122

прежний 1: SELECT * FROM department WHERE location_id=&&loc новый 1: SELECT * FROM department WHERE location_id=122

DEPARTMENT_ID NAME		LOCATION_ID
-------------	--------------	-----------
10	ACCOUNTING	122
12	RESEARCH	122
13	SALES	122
14	OPERATIONS	122

II.1.5. Выборка столбцов таблицы

Q1_6. Выборка всех столбцов из таблицы

DEMO@ORCL>SELECT * FROM department;

Q1_7. Использование арифметических выражений. Общая стоимость отдельных товаров в покупке

DEMO@ORCL>SELECT order_id, actual_price*quantity FROM item;

ORDER_ID ACTUAL_PRICE*QUANTITY

----------		---------------------
600		42
610		58
611		45
-	-	- - - - - -
-	-	- - - - - -
510		1252,8
510		83,8

271 строк выбрано.

Q1_8. Интерпретация NULL-значений столбца. NULL значение в таблицах Oracle понимается, как не определенное, неизвестное значение, это, например, не число «0», если столбец имеет числовой тип, не строка из пробелов, если столбец имеет символьный тип.

15

Повторим сначала:

DEMO@ORCL>SELECT * FROM department;

Для department_id 55 и 56 location_id не выводятся, так как для этих отделов в столбце «location_id» содержатся NULL значения.

Явный вывод в числовом виде значений столбца «location_id» DEMO@ORCL>SELECT department_id, name, NVL(TO_CHAR(location_id),’нет’ ) AS location FROM department;

Функция TO_CHAR() преобразует числовой тип в символьный, NVL() с совместно используемой функцией TO_CHAR() возвращает символы «нет», если значение в столбце «location» NULL и значение этого столбца – в противном случае.

Q1_9. Сцепление столбцов department_id и name DEMO@ORCL>SELECT location_id||' '||regional_group FROM location;

Q1_10. Использование литералов (набора символов) для формирования столбца

DEMO@ORCL>SELECT ' Department ', name, 'settled in region', location_id FROM department;

16

Q1_11. Исключение повторяющихся строк с использованием DISTINCT DEMO@ORCL>SELECT DISTINCT name FROM department;

NAME

--------------

ACCOUNTING OPERATIONS RESEARCH SALES

Выведено 4 строки, хотя в таблице department 13 строк.

Q1_12. Использование псевдонимов столбцов

DEMO@ORCL>SELECT name AS НАЗВАНИЕ_ОТДЕЛА, location_id AS РАСПОЛОЖЕНИЕ FROM department;

Q1_13. Упорядочение строк результата запроса с использованием ORDER BY DEMO@ORCL>SELECT department_id, name FROM department ORDER BY name;

17

Или иначе:

DEMO@ORCL>SELECT department_id, name FROM department ORDER BY 2;

Q1_14. Сортировка в порядке убывания

DEMO@ORCL>SELECT department_id, name FROM department ORDER BY 2 DESC;

Q1_15. Сортировка по нескольким столбцам

DEMO@ORCL>SELECT department_id, name FROM department ORDER BY 2,1 DESC;

II.1.6. Выборка строк таблицы

Условия выборки во фразе WHERE

Q1_16. Вывести имя, город и кредитный рейтинг всех покупателей из штата «NEW YORK» («NY»)

18

DEMO@ORCL>SELECT name, city, credit_limit FROM customer WHERE state='NY'

Q1_17. Выбор отделов, не прикрепленных ни к какому региону

DEMO@ORCL>SELECT * FROM department WHERE location_id IS NULL;

Q1_18. Вывести отделы, не принадлежащие региону с номером 122

DEMO@ORCL>SELECT * FROM department WHERE NVL(location_id,0) !=122;

Q1_19. Вывести сведения о заказах, оплаченных в день заказа

DEMO@ORCL>SELECT * FROM sales_order WHERE ship_date=order_date;

Q1_20. Выбор покупателей, у которых кредитный рейтинг меньше или равен 10000 и больше или равен 8000

DEMO@ORCL>SELECT name, credit_limit FROM customer

WHERE credit_limit BETWEEN 8000 AND 10000;

19

Q1_21. Выбор отделов , номера регионов которых 123 или 124

DEMO@ORCL> SELECT * FROM department WHERE NVL(location_id,0) in (123,124);

Q1_22. Выбор имени, города и штата покупателей, у которых имя начинается на «A»

DEMO@ORCL>SELECT name, city, state FROM customer

WHERE name LIKE 'A%';

Q1_23. Выбор имени, города и штата покупателей у которых кредитный рейтинг не меньше 9000

DEMO@ORCL>SELECT name, city, state, credit_limit FROM customer WHERE NOT credit_limit < 9000;

Q1_24. Выбор из таблицы price товаров, закупленных фирмой в 1988 году, у которых определено поле end_date ( не имеет NULL значение). При отработке этого запроса используется усложненный критерий поиска с объединением AND DEMO@ORCL>SELECT * FROM price

WHERE TO_CHAR(start_date,’YY’)=’88’ AND end_date IS NOT NULL;

20

Q1_25. Выбор из таблицы price товаров, закупленных фирмой в 1988 или 1989 годах, у которых не определено поле end_date (имеет NULL значение). При отработке этого запроса используется усложненный критерий поиска с объединением AND и OR DEMO@ORCL>SELECT * FROM price

WHERE (TO_CHAR(start_date,’YY’)=’88’ OR TO_CHAR(start_date,’YY’)=’89’) AND end_date IS NULL;

Q1_26. Использование SELECT-а в SELECT-е или вложенный подзапрос в SELECT-е. Вывести номер, имя и название отдела служащего для служащих 20-го отдела

DEMO@ORCL>SELECT employee_id, last_name , (SELECT name FROM department

WHERE department.department_id=employee.department_id) AS dname FROM employee

WHERE department_id=20;

II.2. Выборка данных из нескольких таблиц II.2.1. Подзапросы

Q2_1. Найти покупателей , имеющих минимальный кредитный рейтинг

DEMO@ORCL>SELECT name, city, state, credit_limit FROM customer

WHERE credit_limit =(SELECT MIN(credit_limit) FROM customer);

В запросе использована агрегатная функция min(credit_limit), которая возвращает минимальное значение столбца «credit_limit».

Q2_2. Найти покупателей из штата «NY» имеющих тот же кредитный рейтинг, что и покупатели из штата «TX», обслуживаемые служащим по фамилии «WEST»

DEMO@ORCL>SELECT name, state, city, credit_limit FROM customer WHERE state ='NY' AND credit_limit in

(SELECT credit_limit FROM customer WHERE state='TX' AND salesperson_id= (SELECT employee_id FROM employee