«Технологии баз данных (СУБД Oracle)» (стр. 5 )

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

ERRORS - максимально допустимое количество ошибок, выявленных утилитой SQLLDR, прежде чем загрузка будет прервана. Это могут быть самые разные ошибки, например ошибка преобразования типов данных (скажем, попытка загрузить строку ABC в числовое поле), дублирование записей по ключу уникального индекса и т. д. Стандартно допускается не более 50 ошибок, после чего загрузка прекращается. Чтобы можно было в одном сеансе загрузить все допустимые записи (при этом отвергнутые попадают в BAD-файл), укажите в качестве значения большое число, например .

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

LOAD - максимальное количество загружаемых записей. Обычно используется для загрузки небольшого образца данных из большого файла или совместно с параметром SKIP для загрузки из входного файла лишь записей определенного диапазона.

LOG - задает имя журнального (LOG) файла. По умолчанию, утилита SQLLDR будет создавать журнальный файл с именем, созданным автоматически на основе имени управляющего файла, аналогично BAD-файлу.

PARALLEL - допускаются значения TRUE или FALSE. Если указано значение TRUE, то выполняется параллельная непосредственная загрузка. Этот параметр необязателен при обычной загрузке, ее можно выполнять параллельно и без его установки.

PARFILE - может использоваться для задания имени файла, содержащего все описываемые параметры в виде пар

КЛЮЧЕВОЕ_СЛОВО=ЗНАЧЕНИЕ.

Это позволяет не задавать параметры в командной строке.

READSIZE - задает размер буфера, используемого при чтении данных.

ROWS - количество строк, которое утилита SQLLDR должна вставить, прежде чем фиксировать изменения при обычной загрузке. При непосредственной загрузке задает количество строк, которые необходимо загрузить, прежде чем сохранять данные (это аналог фиксации). При обычной загрузке стандартное значение – 64 строки. При непосредственной загрузке по умолчанию данные не сохраняются, пока загрузка не завершена.

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

SKIP - заставляет утилиту SQLLDR пропустить указанное в качестве значения этого параметра количество строк во входном файле. Чаще всего используется для продолжения прерванной загрузки (для пропуска уже загруженных записей) или для загрузки только части входного файла.

USERID - строка подключения к базе данных в формате

ИМЯ_ПОЛЬЗОВАТЕЛЯ/ПАРОЛЬ@БАЗА_ДАННЫХ.

Используется для аутентификации в базе данных.

SKIP_INDEX_MAINTENANCE - нe используется при обычной загрузке, поскольку в этом режиме поддерживаются все индексы. Если этот параметр установлен при непосредственной загрузке, СУБД Oracle не поддерживает индексы: они помечаются как недоступные для использования. После загрузки данных такие индексы необходимо пересоздать.

SKIP_UNUSABLE_INDEXES - требует от утилиты SQLLDR разрешить загрузку строк в таблицу, по которой есть недоступные для использования индексы, если эти индексы – не уникальные.

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

LOAD DATA - эта команда указывает утилите SQLLDR, что необходимо сделать (в данном случае - загрузить данные). А еще можно указывать действие CONTINUE_LOAD для возобновления загрузки. Эта опция используется только для продолжения непосредственной загрузки нескольких таблиц.

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

Учтите, что опции командной строки имеют преимущество над установками, заданными в управляющем файле.

INTO TABLE dept - эта конструкция указывает, в какую таблицу загружаются данные; в нашем случае это таблица dept.

FIELDS TERMINATED BY ',' -эта конструкция указывает, что данные будут представлены в виде списка значений через запятую. Есть десятки способов описать загружаемые данные в SQLLDR, это - лишь один из наиболее часто используемых.

(DEPTNO, DNAME, LOC) - эта конструкция указывает, какие столбцы загружаются, их порядок следования в загружаемых данных и типы. При этом указываются типы данных во входном потоке, а не типы соответствующих столбцов в базе данных. В нашем случае используется стандартный формат CHAR(255), что вполне подходит.

BEGINDATA - эта конструкция указывает утилите SQLLDR, что описание загружаемых данных закончено и что со следующей строки идут данные, которые необходимо загрузить в таблицу dept:

10,Sales, Virginia

20.Accounting, Virginia

30,Consulting, Virginia

40,Finance, Virginia

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

LOAD DATA

INFILE *

INTO TABLE dept

FIELDS TERMINATED BY ','

(DEPTNO, DNAME, LOC)

BEGINDATA

10,Sales, Virginia

20.Accounting, Virginia

30,Consulting, Virginia

40,Finance, Virginia

Чтобы применить этот управляющий файл, достаточно создать пустую таблицу dept:

CRATE TABLE dept

(deptno NUMBER(2) CONSTRAINT emp_pk PRIMARY KEY,

dname VARCHAR2(14),

loc VARCHAR (13)

)

и выполнить следующую команду:

SQLLDR USERID=scott/*****@***CONTROL=demol. ctl

Если таблица не пуста, будет выдано сообщение об ошибке:

SQLLDR-601: For INSERT option, table must be empty. Error on table DEPT

Так происходит потому, что в управляющем файле используются почти исключительно стандартные установки, а стандартно при загрузке выполняется операция INSERT (еще возможны APPEND, TRUNCATE или REPLACE). При выполнении операции INSERT предполагается, что таблица пуста. Если необходимо добавить записи в таблицу DEPT, можно указать операцию APPEND, а для замены данных в таблице DEPT - операцию REPLACE или TRUNCATE.

При каждой попытке загрузки генерируется журнальный файл. Журнальный файл для нашего примера будет иметь следующий вид:

Control File: demol. ctl

Data File: demol. ctl

Bad File: demol. bad

Discard File: none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array: 64 rows, maximum of 65536 bytes

Continuation: none specified

Path used: Conventional

Table DEPT, loaded from every logical record.

Insert option in effect for this table: INSERT

457

Column Name Position Len Term Encl Datatype

DEPTNO FIRST *, CHARACTER

DNAME NEXT *, CHARACTER

LOC NEXT *, CHARACTER

Table DEFT:

4 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 49536 bytes(64 rows)

Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0

Total logical records read: 4

Total logical records rejected: 0

Total logical records discarded: 0

Run began on Sat Apr 14 10:58:02 2010

Run ended on Sat Apr 14 10:58:02 2010

Elapsed time was: 00:00:00.11

CPU time was: 00:00:00.04

В журнальном файле представлены различные характеристики выполненной загрузки. Можно увидеть использованные опции (стандартные или явно указанные). Можно узнать, сколько записей прочитано, сколько из них было загружено и т. д. Указаны имена файлов BAD и DISCARD. Указана даже продолжительность загрузки. Журнальные файлы позволяют проверить, успешно ли прошла загрузка, и не было ли сообщений об ошибках. Если при загрузке данных были ошибки SQL (загружаемые данные - "плохие", и соответствующие записи помещены в BAD-файл), эти ошибки записываются в журнальный файл.

Описание метаданных

В Oracle для получения информации о метаданных используется словарь базы данных. Основой словаря данных является набор базовых таблиц. Базовые таблицы словарей – это объекты, создаваемые первыми в базе данных. Они создаются автоматически командным файлом sql. bsq во время выполнения команды CREATE DATABASE. Сервер Oracle записывает и считывает информацию из этих таблиц. Пользователи базы данных очень редко обращаются к ним непосредственно, так как они содержат информацию в виде, удобном для программы, a не для человека. Никогда не изменяйте напрямую содержимое таблиц с помощью команд DML, за исключением тех, работа с которыми описана в документации (например, таблица аудита AUD$). Это может привести к непредсказуемым последствиям: многие таблицы всегда обновляются сервером Oracle одновременно. В качестве примера базовой таблицы можно привести таблицу IND$, которая содержит информацию об индексах.

Представления словаря данных создаются при выполнении командного файла catalog. sql. Эти представления представляют информацию базовых таблиц словаря данных в удобном виде, соединяя базовые таблицы между собой и вводя предложение WHERE для выборки нужных данных. Например, в представлениях словаря данных используются имена объектов вместо номеров объектов, хранимых в базовых таблицах. Эти представления защищают программистов от ошибок, допуская только запросы SELECT.

Словарь данных содержит:

- Определения всех объектов схем базы данных (таблицах, представлениях, индексах, синонимах, последовательностях, процедурах, функциях, пакетах, триггерах, и т. д.);

- Информацию о пространстве, выделенном для объектов схемы и его использовании;

- Значения столбцов по умолчанию;

- Ограничения целостности;

- Имена пользователей Oracle;

- Привилегии и роли, выделенные пользователю;

- Результаты протоколирования ( кто обращался к объектам схемы и изменял их).

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

Пользователи могут ссылаться на словари данных и использовать их имена в командах SQL. Некоторые словари доступны всем пользователям Oracle, другие только администраторам базы данных.

Представления с префиксом DBA выводят информацию обо всех объектах базы данных. Доступ к этим представлениям имеет администратор базы данных или любой пользователь, которому предоставлена системная привилегия SELECT ANY TABLE. Следующий оператор SQL позволяет вывести список всех объектов базы данных:

SELECT owner, object_name, object_type FROM dba_objects;

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

SELECT owner, object_name, object_type FROM all_objects;

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

SELECT object_name, object_type FROM user_objects;

Полный список представлений словаря данных и их описание можно получить запросив представление DICTIONAКY или его синоним DICT:

SELECT * FROM dictionary;

Смысл столбцов словарей можно получить из представления DICT_COLUMNS. Таким образом, чтобы успешно получить информацию о метаданных, нет необходимости запоминать названия всех представлений. Достаточно запомнить названия DICT и DICT_COLUMNS.

Сервер Oracle представляет оперативную информацию о работе базы данных в виде набора “виртуальных” отображений, называемых динамическими представлениями производительности. Они являются интерфейсом для администратора к внутренним структурам памяти экземпляра и существуют только, когда экземпляр запущен. Информация для них поступает из внутренних структур памяти экземпляра и управляющего файла. Динамические представления производительности позволяют получить ответы на такие вопросы, как:

·  Объект в оперативном состоянии и доступен?

·  Объект открыт?

·  Какие блокировки установлены?

·  Сеанс активен?

Чтобы ознакомиться с динамическими представлениями производительности, можно выполнить запрос к представлению DICTIONARY или использовать представление V$FIXED_TABLE, которое содержит только динамические представления производительности.

SQL-запросы и подзапросы

СУБД Oracle является реляционной, для хранения информации используются двумерные таблицы, а доступ к базе данных осуществляется путем выполнения команд SQL (язык структурированных запросов), которые делятся на:

-  Команды манипулирования данными (DML) INSERT, UPDATE, DELETE, MERGE;

-  Команды определения данных (DDL) CREATE, ALTER, DROP, RENAME, TRUNCATE;

-  Команды управления данными (DCL) предоставляют или изымают права доступа, как к базе данных, так и к структурам в ней;

-  Команда SELECT производит выборку данных из базы данных

-  Команды COMMIT, ROLLBACK, SAVEPOINT – управляют изменениями, производимыми с помощью команд DML. Изменения можно группировать в логические транзакции.

С помощью команды SELECT можно производить следующие действия:

-  Проекция – позволяет задать столбцы, возвращаемые запросом.

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

-  Соединение - позволяет объединять данные из различных таблиц на базе соединения между ними.

Базовый оператор SELECT

Синтаксис базового оператора SELECT:

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table

[WHERE condition(s)]

[ORDER BY {column, expr, alias} [ASC|DESC]];

Далее будут приведены примеры основных конструкций оператора SELECT:

-  Выборка значений всех столбцов:

SELECT * FROM departments;

-  Выборка значений нескольких столбцов:

SELECT department_id, location_id FROM departments;

-  Использование в выборке арифметического выражения:

SELECT last_name, salary, salary + 300 FROM employees;

SELECT last_name, salary, 12*salary+100 FROM employees;

SELECT last_name, salary, 12*(salary+100) FROM employees;

-  Использования алиасов для столбцов:

SELECT last_name AS name, commission_pct comm. FROM employees;

SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees;

-  Объединение двух символьных столбцов:

SELECT last_name||job_id AS "Employees" FROM employees;

-  Объединение символьных столбцов с символьной строкой:

SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;

-  Исключение дублирующих записей:

SELECT DISTINCT department_id FROM employees;

-  Ограничение выборки записей:

SELECT employee_id, last_name, job_id, department_id

FROM employees WHERE department_id = 90 ; - для числового поля

SELECT last_name, job_id, department_id

FROM employees WHERE last_name = 'Whalen' ; - для символьного поля

SELECT last_name

FROM employees WHERE hire_date = '17-FEB-96’; - для поля с датой

-  Использование для ограничения выборки записей BETWEEN (между):

SELECT last_name, salary

FROM employees WHERE salary BETWEEN 2500 AND 3500; - верхняя и нижняя границы входят в выборку

-  Вхождения значений из списка:

SELECT employee_id, last_name, salary, manager_id

FROM employees WHERE manager_id IN (100, 101, 201) ;

-  Использование условия LIKE (поиск по шаблону):

SELECT first_name FROM employees WHERE first_name LIKE '_o%';

_ - обозначает один символ,

% - обозначает ноль или несколько символов.

-  Использования логических условий:

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary >= 10000 AND job_id LIKE '%MAN%' ;

SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary >= 10000 OR job_id LIKE '%MAN%' ;

SELECT last_name, job_id FROM employees

WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;

-  Использование скобок для определения порядка операций:

SELECT last_name, job_id, salary FROM employees

WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES') AND salary > 15000;

-  Использования сортировки выбранных записей:

SELECT last_name, job_id, department_id, hire_date

FROM employees ORDER BY hire_date;

-  Использования сортировки по убыванию:

SELECT last_name, job_id, department_id, hire_date

FROM employees ORDER BY hire_date DESC;

-  Сортировка по алиасу столбца :

SELECT employee_id, last_name, salary*12 annsal

FROM employees ORDER BY annsal;

-  Сортировка по позиции столбца:

SELECT last_name, job_id, department_id, hire_date

FROM employees ORDER BY 3;

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

SELECT last_name, department_id, salary

FROM employees ORDER BY department_id, salary DESC;

Работа с NULL

Если в строке отсутствует значение какого-либо столбца, считается, что столбец содержит неопределенное значение (NULL). Неопределенное значение – это значение, которое недоступно, не присвоено, неизвестно или неприменимо. Это не ноль и не пробел. Неопределенные значения допускаются в столбцах с данными любого типа за исключением случаев, когда столбец создан с ограничением NOT NULL или PRIMARY KEY. Столбец commission_pct таблицы employees показывает, что получать комиссионные могут только продавцы. Другие служащие права на комиссионные не имеют. Это показано неопределенным значением в столбце.

SELECT last_name, job_id, salary, commission_pct FROM employees;

Если какой-либо столбец в арифметическом выражении содержит неопределенное значение, результат вычислений также будет неопределенным (NULL). Например, попытка деления на ноль заканчивается ошибкой. Но, если попытаться разделить число на неопределенное значение, результатом будет неопределенное значение. В следующем примере все служащие, не являющиеся продавцами, не получат комиссионных.

SELECT last_name, 12*salary*commission_pct FROM employees;

Т. к. столбец commission_pct в арифметическом выражении содержит неопределенное значение, результатом также будет неопределенное значение.

С помощью оператора

IS NULL (IS NOT NULL )

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

SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;

Логические операции с NULL:

NULL AND FALSE = FALSE

NULL OR TRUE = TRUE

Все остальные операции с NULL дают в результате NULL.

Соединение таблиц

Иногда требуются данные из более, чем одной таблицы. Пример выборки данных из двух разных таблиц:

SELECT employee_id, location_id FROM employees, departments

WHERE employees. department_id = departments. department_id;

employee_id существует в таблице employees,

department_id существует как в таблице employees, так и в таблице departments,

location_id существует в таблице departments.

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

Пример получения декартова произведение при отсутствии условия соединения таблиц:

SELECT employee_id, location_id FROM employees, departments;

Для соединения таблиц СУБД Oracle позволяет использовать синтаксис SQL99. До версии Oracle9i синтаксис соединений отличался от стандарта ANSI и поддерживается в последующих версиях. Рассмотрим конструкции соединения таблиц в обоих стандартах.

В стандарте Oracle для выборки данных из нескольких таблиц требуется условие соединения. Строки одной таблицы соединяются со строками другой с помощью общих значений в соответствующих столбцах – обычно в столбцах первичных и внешних ключей. Для вывода данных из двух или более взаимосвязанных таблиц можно задать условие соединения в предложении WHERE. Для соединения n таблиц требуется не менее (n-1) условий соединения.

-  Эквисоединения (простое или внутреннее соединение) – для выборки данных из нескольких таблиц в качестве условия соединения используются одинаковые значения в соответствующих столбцах. Например:

SELECT employee_id, location_id FROM employees, departments

WHERE employees. department_id = departments. department_id;

-  Не-эквисоединения – при соединении таблиц в качестве условия соединения используется оператор, отличный от оператора равенства. Например:

SELECT e. last_name, e. salary, j. grafe_level FROM employee e, job_grades о

WHERE e. salary BETWEEN j. lovest_sal AND j. highest_sal;

Этот запрос создан для оценки категории заработной платы служащего. Она должна быть между любой парой нижней и верхней границ диапазона.

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

SELECT employee_id, location_id FROM employees e, departments d

WHERE e. department_id (+) = d. department_id;

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

Оператор внешнего соединения может использоваться только на одной стороне выражения – на стороне, где недостаточно информации. Условия, предполагающие внешнее соединение, не могут использовать оператор IN или быть связанными с другими условиями с помощью оператора OR.

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

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

SELECT w. last_name||’ работает на ‘|| m. last_name FROM employees w, employees m

WHERE w. manager_id = m. employee_id;

Используя синтаксис стандарта SQL99 для соединения таблиц можно получить такие же результаты, что уже были рассмотрены:

SELECT table1.column, table2.column FROM table1

[NATURAL JOIN table2] |

[JOIN table2 USING (column_name)] |

[JOIN table2

ON (table1.column_name = table2.column_name)]|

[LEFT|RIGHT|FULL OUTER JOIN table2

ON (table1.column_name = table2.column_name)]|

[CROSS JOIN table2];

Где:

NATURAL JOIN - соединяет две таблицы на основе одинаковых столбцов,

JOIN table USING (column_name) – выполняет эквисоединение на основе имени столбца,

JOIN table ON (table1.column_name = table2.column_name) – выполняет эквисоединение на основе условия в предложении ON,

LEFT|RIGHT|FULL OUTER – левое (правое), полное внешнее соединение таблиц,

CROSS JOIN – возвращает декартово произведение двух таблиц.

Рассмотрим эти конструкции на примерах:

-  Перекрестные соединения – аналог декартова произведения.

SELECT last_name, department_name

FROM employees CROSS JOIN departments;

-  Натуральные соединения - основываются на всех столбцах таблиц, имеющих одинаковые имена. У столбцов с одинаковыми именами должны быть и одинаковые типы данных.

SELECT department_id, department_name, location_id, city

FROM departments NATURAL JOIN locations;

-  Опция USING – можно указать только те столбцы, которые следует использовать в соединении (в отличие от натурального соединения, где используются все столбцы с согласованными именами и типами данных).

SELECT employee_id, last_name, location_id, department_id

FROM employees JOIN departments USING (department_id) ;

-  Условие ON – используется для определения условия соединения. Этот подход позволит вам отделить условия соединения от других условий поиска или отбора, которые задаются в предложении WHERE.

SELECT e. employee_id, e. last_name, e. department_id, d. department_id, d. location_id

FROM employees e JOIN departments d ON (e. department_id = d. department_id);

Для соединения трех таблиц:

SELECT employee_id, city, department_name FROM employees e

JOIN departments d ON d. department_id = e. department_id

JOIN locations l ON d. location_id = l. location_id;

-  Не-эквисоединения

SELECT e. last_name, e. salary, j. grade_level

FROM employees e JOIN job_grades j

ON e. salary BETWEEN j. lowest_sal AND j. highest_sal;

-  Левое внешнее соединение:

SELECT e. last_name, e. department_id, d. department_name

FROM employees e

LEFT OUTER JOIN departments d ON (e. department_id = d. department_id);

В этом запросе возвращаются все строки таблицы employees, даже если они не соответствуют строкам таблицы departments, так как таблица employees является левой таблицей во внешнем соединении.

-  Правое внешнее соединение:

SELECT e. last_name, d. department_id, d. department_name

FROM employees e

RIGHT OUTER JOIN departments d ON (e. department_id = d. department_id) ;

-  Полное внешнее соединение возвращает результаты внутреннего соединения, а также левого и правого внешнего соединений. Полное внешнее соединение не возможно в стандарте Oracle.

SELECT e. last_name, d. department_id, d. department_name

FROM employees e

FULL OUTER JOIN departments d ON (e. department_id = d. department_id);

-  Произвольные условия соединения – дополнительные условия можно указать с помощью оператора AND или WHERE.

SELECT e. employee_id, e. last_name, e. department_id, d. department_id, d. location_id

FROM employees e JOIN departments d ON (e. department_id = d. department_id)

AND e. manager_id = 149;

или

SELECT e. employee_id, e. last_name, e. department_id, d. department_id, d. location_id

FROM employees e JOIN departments d ON (e. department_id = d. department_id)

WHERE e. manager_id = 149;

Подзапросы

Подзапрос – это более сложное использование команды SELECT, создание одного запроса внутри другого. Значение, возвращаемое внутренним запросом или подзапросом, используется внешним или главным запросом. Использовать подзапрос – то же самое, что последовательно выполнить два запроса, используя результаты первого в качестве критерия поиска во втором.

Синтаксис подзапроса:

SELECT select_list FROM table

WHERE expr operator

(SELECT select_list FROM table);

Рассмотрим конструкции следующих подзапросов:

-  Однострочный подзапрос – запросы, в которых внутренняя команда SELECT возвращает только одну строку.

SELECT last_name, job_id, salary FROM employees

WHERE job_id =

(SELECT job_id FROM employees

WHERE last_name = 'Taylor')

AND salary >

(SELECT salary FROM employees

WHERE last_name = 'Taylor');

-  Многострочный подзапрос - запросы, в которых внутренняя команда SELECT возвращает более одной строки. В таких подзапросах используются операторы IN, ANY, ALL, EXISTS.

SELECT emp. last_name FROM employees emp

WHERE emp. employee_id IN

(SELECT mgr. manager_id FROM employees mgr);

Подзапрос возвращает список значений, основной запрос обрабатывает список (IN).

Оператор ANY сравнивает значение с любым значением, возвращаемым подзапросом:

SELECT employee_id, last_name, job_id, salary FROM employees

WHERE salary < ANY

(SELECT salary FROM employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';

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

Оператор ALL сравнивает значение с каждым значением, возвращаемым подзапросом.

SELECT employee_id, last_name, job_id, salary FROM employees

WHERE salary < ALL

(SELECT salary FROM employees

WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROG';

Этот запрос возвращает список служащих, которые не являются программистами и оклады которых меньше, чем у всех программистов.

SELECT * FROM departments

WHERE NOT EXISTS

(SELECT * FROM employees

WHERE employees. department_id=departments. department_id);

Оператор EXISTS обеспечивает, что поиск в подзапросе не будет продолжаться, как только будет найдена хотя бы одна строка, удовлетворяющая условию WHERE в подзапросе:

SELECT emp. last_name FROM employees emp

WHERE EXISTS

(SELECT 1 FROM employees mgr

WHERE emp. employee_id = emp. employee_id );

Внутренний подзапрос не должен возвращать конкретное значение. Поэтому может быть выбрана константа (а не столбец), что с точки зрения производительности быстрее. Конструкция EXISTS может быть использована в качестве альтернативы для оператора IN. Как и NOT EXISTS для NOT IN.

-  Связанные подзапросы – подзапрос связывается с столбцом таблицы, на который ссылается основной запрос.. Связанный подзапрос выполнится один раз для каждой строки, обрабатываемой основным запросом.

SELECT last_name, salary, department_id FROM employees outer_table

WHERE salary >
(SELECT AVG(salary) FROM employees inner_table

WHERE inner_table. department_id = outer_table. department_id);

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

-  Многостолбцовые (или парные) подзапросы - запросы, в которых внутренняя команда SELECT возвращает более одного столбца.

SELECT employee_id, manager_id, department_id

FROM employees

WHERE (manager_id, department_id) IN

(SELECT manager_id, department_id

FROM employees

WHERE employee_id IN (178,174));

-  Подзапросы в операторе FROM:

SELECT a. last_name, a. salary,

a. department_id, b. salary

FROM employees a, (SELECT department_id,

AVG (salary) salary

FROM employees

GROUP by department_id) b

WHERE a. department_id = b. department_id

AND a. salary = b. salary;

-  Подзапросы в операторе ORDER BY:

SELECT employee_id, last_name

FROM employees e

ORDER BY (SELECT department_name

FROM departments d

WHERE e. department_id=d. department_id):

-  Подзапросы в операторе CASE, DECODE

SELECT employee_id, last_name,

(CASE

WHEN department_id=

(SELECT department_id FROM departments

WHERE lacation_id=1800)

THEN ‘Canada’ ELSE ‘USA’ END) location

FROM employees;

-  Подзапросы в предложении WITH:

WITH above_average AS (

SELECT * FROM. employees

WHERE salary > (SELECT AVG (salary) FROM hr. employees)

)

SELECT last_name, first_name FROM above_average;

Использование предложения WITH позволяет повысить производительность выполнения запроса. Преимуществом предложения WITH является то, что оно обрабатывается только один раз, даже если оно появляется несколько раз в запросе. Внутреннее предложение WITH реализуется либо как встроенное представление, либо как временная таблица. Оптимизатор выбирает подходящее решение в зависимости от стоимости или принимая во внимание преимущества временного хранения результатов выполнения предложения WITH. К тому же упрощается чтение запроса. Предложение WITH может содержать более одного запроса, которые в этом случае отделяются запятой:

WITH
dept_costs AS (

SELECT d. department_name, SUM (e. salary) AS dept_total

FROM employees e JOIN departments d ON e. department_id = d. department_id

GROUP BY d. department_name),

avg_cost AS (

SELECT SUM(dept_total)/COUNT(*) AS dept_avg

FROM dept_costs)

SELECT * FROM dept_costs WHERE dept_total >

(SELECT dept_avg FROM avg_cost) ORDER BY department_name;

-  Подзапрос в команде CREATE:

CREATE TABLE dept_ninety AS

SELECT employee_id, last_name, first_name

FROM employees WHERE department_id = 90;

или с переименованием столбцов

CREATE TABLE dept_ninety (id, first, last) AS

SELECT employee_id, last_name, first_name

FROM employees WHERE department_id = 90;

Составные запросы (операторы SET)

Операторы SET объединяют в один запрос результаты одного или нескольких запросов.

-  UNION – результатом выполнения являются все отличные строки, выбранные обоими запросами:

SELECT employee_id, job_id

FROM employees

UNION

SELECT employee_id, job_id

FROM job_history;

-  UNION ALL - результатом выполнения являются все строки (включая повторяющиеся) выбранные обоими запросами:

SELECT employee_id, job_id, department_id

FROM employees

UNION ALL

SELECT employee_id, job_id, department_id

FROM job_history

ORDER BY employee_id;

-  INTERSECT – результатом выполнения являются все отличные строки, входящие в пересечение строк обоих запросов:

SELECT employee_id, job_id

FROM employees