Orasql.ru

Appearance

Псевдостолбцы в Oracle ​

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

Некоторые псевдостолбцы доступны только в определенном контексте, например, лишь при использовании рекурсивных запросов.

Мы рассмотрим не все псевдостолбцы, доступны в Oracle, а лишь самые основные и часто используемые. Полный их список и описание можно почитать в докумениации.

Мы будем использовать таблицу dishes, которая создается в части про операторы сравнения.

ROWNUM ​

Данный псевдостолбец возвращает порядковый номер, под которым Oracle выбирает строку из таблицы. Для первой строки значение ROWNUM будет равно 1, для второй - 2, и т.д.

Один из классических примеров использования ROWNUM - ограничение количества получаемых строк из таблицы:

sql
select d.*
from dishes d
where rownum < 3
select d.*
from dishes d
where rownum < 3

Если в запросе используется сортировка, то она может изменить порядок строк. Т.е. строка из таблицы могла получаться первой, и ей мог быть присвоен rownum = 1, но после того, как все строки были получены, они были отсортированы в другом порядке:

sql
select d.*, rownum
from dishes d
where rownum < 6
order by price asc
select d.*, rownum
from dishes d
where rownum < 6
order by price asc

Что же делать, если мы хотим пронумеровать наши строки начиная от 1 таким образом, чтобы у самого дешевого блюда был номер 1, у более дорогого - 2 и т.п.?

Для этого можно использовать подзапросы:

sql
select dishes_ordered.*, rownum
from (
  select d.*
  from dishes d
  order by price asc
) dishes_ordered
select dishes_ordered.*, rownum
from (
  select d.*
  from dishes d
  order by price asc
) dishes_ordered

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

Следует отметить, что использование оператора ">" с ROWNUM не имеет смысла. Рассмотрим это на примере:

sql
select d.*
from dishes d
where rownum > 3
select d.*
from dishes d
where rownum > 3

Этот запрос ничего не выведет, несмотря на то, что строк в таблице больше трех. Все потому, что rownum хранит в себе номер строки, под которым Oracle получает ее из таблицы или соединения. В примере выше у первой строки(какой бы она не была, она все равно будет первой) значение rownum будет равно 1. Это значит, что условие rownum > 3 будет ложным, и строка не будет добавлена в выборку. Следующая строка опять будет иметь rownum = 1, что опять приведет значение условия в False, и так будет для всех строк из таблицы dishes.

Top-N query ​

Получим топ-3 блюда по рейтингу с помощью rownum:

sql
select rt.name,
       rt.price,
       rt.rating,
       ROWNUM
from (
  select d.*
  from dishes d
  order by d.rating desc nulls last
) rt
where ROWNUM <= 3
select rt.name,
       rt.price,
       rt.rating,
       ROWNUM
from (
  select d.*
  from dishes d
  order by d.rating desc nulls last
) rt
where ROWNUM <= 3

Подобного рода запросы относятся к так называемым "top-N queries", т.е. они получают часть данных, основываясь на каком-либо критерии сортировки ( в данном случае это рейтинг блюд).

ROWID ​

ROWID содержит в себе адрес строки в таблице. На практике он используется не часто, но иногда его значение может понадобиться сторонним библиотекам.

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

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

Для примера просто получим все строки из таблицы dishes с их rowid:

sql
select rowid, d.name
from dishes d
select rowid, d.name
from dishes d

LEVEL ​

Данный псевдостолбец доступен только в рекурсивных запросах. Подробнее про него можно почитать в части про рекурсивные запросы.