Введение в Oracle 10g. Часть 3
Введение
предыдущей части статьи мы рассмотрели основные вопросы, связанные с резервным копированием, и перешли к простейшим запросам. А сейчас мы продолжим изучение Oracle SQL — речь пойдет о псевдонимах и псевдостолбцах.
 |
|
Псевдонимы таблиц и столбцов
севдонимы (от англ. aliases, часто можно встретить жаргонный термин «алиас») позволяют задать в рамках запроса альтернативные имена для столбцов и таблиц. С помощью псевдонимов можно решить следующие задачи:
- назначение для столбца или таблицы такого имени, которое более понятно для программиста (что соответственно упрощает сопровождение и отладку запросов) или является коротким именем (что сокращает размер текста и повышает его читаемость);
- устранение неоднозначности при извлечении данных из нескольких таблиц, имеющих столбцы с одинаковыми именами;
- присвоение имен для результатов вычисления.
Рассмотрим подробнее некоторые типовые аспекты применения псевдонимов. Во-первых, использование псевдонимов не является обязательным (исключение — неоднозначно определенный столбец, когда без псевдонима построить запрос невозможно). Во-вторых, в случае задания псевдонима таблицы программист не обязан его применять, например:
SELECT EMPNO, ENAME, JOB
FROM SCOTT.EMP emp
В данном случае для таблицы SCOTT.EMP в запросе задан псевдоним «emp», но он нигде не используется. Кроме того, допустим запрос вида:
SELECT emp.EMPNO, ENAME, emp.JOB
FROM SCOTT.EMP emp
В данном случае псевдоним применяется, но не для всех извлекаемых столбцов. Такой запрос будет выполняться, так как отсутствует неоднозначность. Кроме того, вместо псевдонима таблицы допустимо указание ее полного имени:
SELECT emp.EMPNO, SCOTT.EMP.ENAME, emp.JOB
FROM SCOTT.EMP emp
Как легко заметить, запись с использованием псевдонимов гораздо компактнее и лучше читается. Это очень важно при построении сложных запросов, особенно если их разработкой занимается несколько программистов.
Другой способ применения псевдонимов таблицы — устранение неоднозначности в условии WHERE. Рассмотрим в качестве примера запрос:
SELECT EMPNO, ENAME
FROM SCOTT.EMP e, SCOTT.DEPT d
WHERE e.DEPTNO = d.DEPTNO
В данном запросе производится соединение двух таблиц, причем обе содержат столбец с именем DEPTNO. Использование псевдонима позволяет однозначно указать, столбец какой из таблиц применяется в условии. Если немного модифицировать данный запрос (добавив извлечение столбца DEPTNO), то мы получим ту самую неоднозначность, о которой шла речь выше:
SELECT EMPNO, ENAME, DEPTNO
FROM SCOTT.EMP e, SCOTT.DEPT d
WHERE e.DEPTNO = d.DEPTNO
Попытка выполнения запроса завершится ошибкой «ORA-00918: column ambiguously defined (столбец определен неоднозначно)». Для устранения ошибки необходимо конкретизировать принадлежность столбца DEPTNO при помощи псевдонимов:
SELECT e.EMPNO, e.ENAME, d.DEPTNO
FROM SCOTT.EMP e, SCOTT.DEPT d
WHERE e.DEPTNO = d.DEPTNO
Еще один способ применения псевдонимов — создание псевдонима для извлекаемых запросом столбцов. Это можно сделать двумя путями, показанными в данном запросе:
SELECT e.EMPNO TABNOM, e.ENAME AS NAIM
FROM SCOTT.EMP e
В первом случае задание псевдонима аналогично заданию псевдонима для таблицы, во втором — применяется ключевое слов AS. Принципиальной разницы в этих объявлениях нет, но чаще применяется первая форма записи, поскольку она короче. В качестве псевдонима столбца можно указать любой текст, в том числе с пробелами и национальными символами. Единственное условие состоит в том, что псевдоним в этом случае необходимо заключить в кавычки, например:
SELECT e.EMPNO "THIS IS TABNOM", e.ENAME AS "Это имя сотрудника"
FROM SCOTT.EMP e
Несмотря на то что создание псевдонимов для столбцов не является обязательным, следует отметить ряд случаев, в которых настоятельно рекомендуется их применять. Во-первых, при использовании вычислений. Например, рассмотрим запрос, который выводит зарплату сотрудников, повышенную на 5%:
SELECT e.EMPNO, e.ENAME, e.SAL*1.05
FROM SCOTT.EMP e
Данный запрос будет отлично работать, но если выяснить имена возвращаемых запросом столбцов, то мы получим следующие данные:
EMPNO NUMBER (4)
ENAME VARCHAR (10)
E.SAL*1.05 NUMBER
Можно заметить, что имя третьего столбца — «E.SAL*1.05» — сгенерировано Oracle автоматически. Обращаться к столбцу по такому имени неудобно, а редактирование формулы приведет к изменению имени. Поэтому правильным решением будет применение псевдонима:
SELECT e.EMPNO, e.ENAME, e.SAL*1.05 CALC_SAL
FROM SCOTT.EMP e
Во-вторых, при запросе, в котором извлекаются два столбца с одинаковыми именами, например:
SELECT e.EMPNO, e.ENAME, e.DEPTNO, d.DEPTNO
FROM SCOTT.EMP e, SCOTT.DEPT d
WHERE e.DEPTNO = d.DEPTNO
К сожалению, в такой ситуации сервер Oracle не считает запрос ошибочным и, обнаруживая совпадение имен, автоматически генерирует уникальные имена. В нашем случае имена столбцов будут иметь вид:
EMPNO NUMBER (4)
ENAME VARCHAR (10)
DEPTNO NUMBER (2)
DEPTNO_1 NUMBER (2)
Как видно, сервер просто добавляет к имени номер через «_». Использовать такие имена в программе не ремомендуется, так как в ходе модернизации запроса они могут изменяться непредсказуемым образом. Особенно чреваты подобными ошибками запросы вида:
SELECT e.*, d.*
FROM SCOTT.DEPT d, SCOTT.EMP e
WHERE e.DEPTNO = d.DEPTNO
В данном случае извлекаются все столбцы из двух таблиц, и при пересечении имен столбцов сервер производит автоматическую генерацию уникальных имен.
Можно легко придумать ситуацию, в которой использующее подобный запрос приложение перестанет корректно работать. Например, в таблицу DEPT (подразделения) вводится столбец с именем EMPNO (табельный номер сотрудника), предназначенный для хранения табельного номера начальника подразделения. После такой модификации базы запрос сохранит работоспособность, но вместо табельного номера сотрудника под именем EMPNO будет извлекаться табельный номер начальника подразделения, а табельный номер сотрудника будет извлекаться под именем EMPNO_1. Обнаружить подобную ошибку очень сложно, особенно в случае коллективной разработки приложений — один из программистов может добавить один-два новых столбца в какую-нибудь таблицу, а это, в свою очередь, приведет к некорректной работе запросов в задаче, разработанной другим программистом.
Исходя из вышесказанного можно сформулировать несколько практических правил использования псевдонимов:
- если в запросе применяется более одной таблицы, то необходимо снабдить их псевдонимами и указывать имена столбцов только с псевдонимами. Это повысит читаемость запроса и защитит его от возможных сбоев и ошибок в случае модификации базы;
- все вычисляемые поля запроса необходимо снабжать псевдонимами. Желательно выработать некий стандарт именования, подчеркивающий тот факт, что это результат вычислений, а не значение одного из столбцов таблицы;
- в случае извлечения двух столбцов с одинаковыми именами как минимум для одного из них должен быть задан псевдоним.
|
|
Псевдостолбец ROWID
севдостолбцами в Oracle принять называть столбцы, которые отсутствуют в таблицах в явном виде, но могут быть использованы в запросах. Наиболее употребимым и важным из них является ROWID — псевдостолбец, являющийся уникальным идентификатором строки. Он не просто гарантированно уникален в рамках таблицы — более того: он уникален в рамках базы данных. С физической точки зрения ROWID является своеобразной координатой записи в базе.
Необходимо отметить, что существование ROWID противоречит как минимум двум из двенадцати известных правил Кодда, описывающих требования к реляционной СУБД. Во-первых, ROWID нарушает правило номер 2, которое гласит: «К каждому элементу данных должен быть обеспечен доступ при помощи комбинации имени таблицы, первичного ключа строки и имени столбца». В данном случае ROWID не является первичным ключом, хотя ввиду его уникальности для каждой строки он может выступать в роли первичного ключа.
Во-вторых, нарушается правило Кодда номер 8: «Прикладные программы не должны зависеть от используемых способов хранения данных на носителях и методов обращения к ним». Нарушение этого правила происходит из-за того, что ROWID по своей сути является физической координатой записи, поэтому он будет изменяться в случае пересоздания таблицы, перезагрузки данных, перемещения таблицы из одного табличного пространства в другое и т.п. Однако ROWID уникален и неизменен в течение сеанса пользователя, поэтому приложение может считать его неизменным.
ROWID существенно упрощает работу с базой данных, поскольку позволяет однозначно идентифицировать любую строку таблицы, что, в частности, позволяет удалять и редактировать строки таблиц без первичного ключа. Кроме того, поиск строки по ее ROWID является самым быстрым из возможных, что положительно сказывается на быстродействии приложений, активно модифицирующих данные. Однако ROWID является специфической особенностью Oracle, а следовательно, его нельзя применять при разработке приложений, рассчитанных на работу с базами других типов.
Рассмотрим простейший пример запроса, извлекающего ROWID строк:
SELECT e.ROWID, e.ENAME
FROM SCOTT.EMP e
|
|
Псевдостолбец RONUM
Как и ROWID, псевдостолбец ROWNUM является специфичным для Oracle. ROWNUM содержит порядковый номер строки запроса, например:
SELECT ROWNUM, e.ENAME
FROM SCOTT.EMP e
В данном запросе производится нумерация извлекаемых строк. Однако чаще всего ROWNUM применяется не для нумерации, а для ограничения количества обрабатываемых строк. Так, данный запрос извлекает первые пять строк данных:
SELECT ROWNUM, e.ENAME
FROM SCOTT.EMP e
WHERE ROWNUM <= 5
При использовании ROWNUM следует обратить внимание на один очень важный момент: в ходе выполнения запроса сначала производятся отбор и нумерация строк, а затем — сортировка. В качестве примера рассмотрим запрос вида:
SELECT ROWNUM, e.ENAME
FROM SCOTT.EMP e
WHERE ROWNUM <= 5
order by e.ENAME
Сначала может показаться, что список сотрудников будет отсортирован по их именам в алфавитном порядке, а затем будут выбраны первые пять записей этого списка. Но на самом деле будет выбрано пять первых попавшихся в базе сотрудников (из-за того, что ROWNUM присваивается до сортировки), а уже затем полученный список будет отсортирован по имени сотрудника:
2 ALLEN
4 JONES
5 MARTIN
1 SMITH
3 WARD
Обойти данную особенность ROWNUM довольно легко — с помощью вложенных запросов (вложенные запросы и неявные представления будут рассмотрены подробнее в одной из следующих статей):
SELECT ROWNUM, ENAME
FROM (SELECT e.ENAME
FROM SCOTT.EMP e
ORDER BY e.ENAME)
WHERE ROWNUM <= 5
В данном случае сначала производится выборка данных из таблицы SCOTT.EMP с сортировкой, а уже затем строкам присваивается ROWNUM и осуществляется отбор первых пяти строк. Результат работы данного запроса, естественно, будет отличаться от предыдущего:
1 ALLEN
2 BLAKE
3 CLARK
4 FORD
5 JAMES
