oracle with as select с параметрами
Русские Блоги
ORACLE с использованием AS
Пример: теперь нам нужно получить 11-14 из 1-19. Общий SQL выглядит следующим образом:
Использовать With as SQL это:
With Оператор запроса не select Начинается с » WITH «Начинается с ключевым словом
WITH Clause Преимущества метода
Улучшенная разборчивость SQL. Если создано несколько подзапросов, структура будет более понятной, что более важно: «один анализ, многократное использование», поэтому обеспечивается производительность и достигается «меньшее чтение». цель.
На самом деле, это поместить много повторяющихся операторов SQL в with as Внутри возьмите псевдоним, который можно использовать в следующих запросах, чтобы он играл роль оптимизации для большого числа операторов SQL, и это понятно.
Вставить данные в таблицу with as Использование:
with as Эквивалент виртуального представления.
with as Фразы, также называемые факторингом подзапроса, позволяют много чего делать, определяют фрагмент SQL, который будет использоваться всем оператором SQL.
Иногда, чтобы сделать оператор SQL более читабельным, он также может быть union all Различные части данных как часть предоставления данных.
Специально для union all Более полезно.
потому что union all Каждая часть может быть одинаковой, но если каждая часть выполняется один раз, стоимость слишком высока, поэтому вы можете использовать with as Фраза, просто выполните это один раз.
если with as Если имя таблицы, определяемое этой фразой, вызывается более двух раз, оптимизатор автоматически with as Поместите данные, полученные с помощью фразы в temp В таблице, если он вызывается только один раз, он не будет.
Oracle с использованием AS
грамматика
пример
Вопросы, требующие внимания
Интеллектуальная рекомендация
Разница между ArrayList и LinkedList
ArrayList: Нижний слой представляет собой массив, хорошо подходящий для поиска данных (доступа) LinkedList: Базовый связанный список, удобный для изменения данных (включая добавление и удаление данных.
nginx скомпилируйте и установите
nginx скомпилируйте и установите 1. Установите среду компиляции 2. Установите программный пакет pcre (сделайте так, чтобы nginx поддерживал модуль перезаписи http) 3. Установите openssl-devel (сделайт.
Используйте Matlab, чтобы нарисовать трехмерный график разложения Фурье
цель: Изучите анализ преобразования Фурье и другие методы анализа. Понять взаимосвязь между частотной областью преобразования Фурье и временной областью. Используйте MATLAB, чтобы нарисовать трехмерну.
Поговорим о статусе IPv6 и переходе
1. Текущее состояние3 февраля 2011 года адреса IPv4 были выделены, и основные операторы ждут, чтобы исчерпать свои сбережения. Люди все больше полагаются на проводные и беспроводные маршрутизаторы, та.
The note introduces basic Python syntax and strings. Python notes of open courses @Codecademy. Brief Introduction Python is a high level scripting language with object oriented features. Python progra.
Oracle with as select с параметрами
MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
13.2.15В WITH (Common Table Expressions)
A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times. The following discussion describes how to write statements that use CTEs.
Additional Resources
These articles contain additional information about using CTEs in MySQL, including many examples:
Common Table Expressions
To specify common table expressions, use a WITH clause that has one or more comma-separated subclauses. Each subclause provides a subquery that produces a result set, and associates a name with the subquery. The following example defines CTEs named cte1 and cte2 in the WITH clause, and refers to them in the top-level SELECT that follows the WITH clause:
In the statement containing the WITH clause, each CTE name can be referenced to access the corresponding CTE result set.
A CTE name can be referenced in other CTEs, enabling CTEs to be defined based on other CTEs.
A CTE can refer to itself to define a recursive CTE. Common applications of recursive CTEs include series generation and traversal of hierarchical or tree-structured data.
Common table expressions are an optional part of the syntax for DML statements. They are defined using a WITH clause:
cte_name names a single common table expression and can be used as a table reference in the statement containing the WITH clause.
The subquery part of AS ( subquery ) is called the “ subquery of the CTE ” and is what produces the CTE result set. The parentheses following AS are required.
A common table expression is recursive if its subquery refers to its own name. The RECURSIVE keyword must be included if any CTE in the WITH clause is recursive. For more information, see Recursive Common Table Expressions.
Determination of column names for a given CTE occurs as follows:
If a parenthesized list of names follows the CTE name, those names are the column names:
The number of names in the list must be the same as the number of columns in the result set.
Otherwise, the column names come from the select list of the first SELECT within the AS ( subquery ) part:
A WITH clause is permitted in these contexts:
At the beginning of subqueries (including derived table subqueries):
Immediately preceding SELECT for statements that include a SELECT statement:
Only one WITH clause is permitted at the same level. WITH followed by WITH at the same level is not permitted, so this is illegal:
To make the statement legal, use a single WITH clause that separates the subclauses by a comma:
However, a statement can contain multiple WITH clauses if they occur at different levels:
A WITH clause can define one or more common table expressions, but each CTE name must be unique to the clause. This is illegal:
To make the statement legal, define the CTEs with unique names:
A CTE can refer to itself or to other CTEs:
A self-referencing CTE is recursive.
A CTE can refer to CTEs defined earlier in the same WITH clause, but not those defined later.
A CTE in a given query block can refer to CTEs defined in query blocks at a more outer level, but not CTEs defined in query blocks at a more inner level.
For resolving references to objects with the same names, derived tables hide CTEs; and CTEs hide base tables, TEMPORARY tables, and views. Name resolution occurs by searching for objects in the same query block, then proceeding to outer blocks in turn while no object with the name is found.
Like derived tables, a CTE cannot contain outer references prior to MySQL 8.0.14. This is a MySQL restriction that is lifted in MySQL 8.0.14, not a restriction of the SQL standard. For additional syntax considerations specific to recursive CTEs, see Recursive Common Table Expressions.
Recursive Common Table Expressions
A recursive common table expression is one having a subquery that refers to its own name. For example:
When executed, the statement produces this result, a single column containing a simple linear sequence:
A recursive CTE has this structure:
The WITH clause must begin with WITH RECURSIVE if any CTE in the WITH clause refers to itself. (If no CTE refers to itself, RECURSIVE is permitted but not required.)
If you forget RECURSIVE for a recursive CTE, this error is a likely result:
The recursive CTE subquery has two parts, separated by UNION [ALL] or UNION DISTINCT :
The first SELECT produces the initial row or rows for the CTE and does not refer to the CTE name. The second SELECT produces additional rows and recurses by referring to the CTE name in its FROM clause. Recursion ends when this part produces no new rows. Thus, a recursive CTE consists of a nonrecursive SELECT part followed by a recursive SELECT part.
Each SELECT part can itself be a union of multiple SELECT statements.
The types of the CTE result columns are inferred from the column types of the nonrecursive SELECT part only, and the columns are all nullable. For type determination, the recursive SELECT part is ignored.
Each iteration of the recursive part operates only on the rows produced by the previous iteration. If the recursive part has multiple query blocks, iterations of each query block are scheduled in unspecified order, and each query block operates on rows that have been produced either by its previous iteration or by other query blocks since that previous iteration’s end.
The recursive CTE subquery shown earlier has this nonrecursive part that retrieves a single row to produce the initial row set:
The CTE subquery also has this recursive part:
At each iteration, that SELECT produces a row with a new value one greater than the value of n from the previous row set. The first iteration operates on the initial row set ( 1 ) and produces 1+1=2 ; the second iteration operates on the first iteration’s row set ( 2 ) and produces 2+1=3 ; and so forth. This continues until recursion ends, which occurs when n is no longer less than 5.
If the recursive part of a CTE produces wider values for a column than the nonrecursive part, it may be necessary to widen the column in the nonrecursive part to avoid data truncation. Consider this statement:
In nonstrict SQL mode, the statement produces this output:
The str column values are all ‘abc’ because the nonrecursive SELECT determines the column widths. Consequently, the wider str values produced by the recursive SELECT are truncated.
In strict SQL mode, the statement produces an error:
To address this issue, so that the statement does not produce truncation or errors, use CAST() in the nonrecursive SELECT to make the str column wider:
Now the statement produces this result, without truncation:
Columns are accessed by name, not position, which means that columns in the recursive part can access columns in the nonrecursive part that have a different position, as this CTE illustrates:
Because p in one row is derived from q in the previous row, and vice versa, the positive and negative values swap positions in each successive row of the output:
Some syntax constraints apply within recursive CTE subqueries:
The recursive SELECT part must not contain these constructs:
Aggregate functions such as SUM()
These constraints do not apply to the nonrecursive SELECT part of a recursive CTE. The prohibition on DISTINCT applies only to UNION members; UNION DISTINCT is permitted.
For recursive CTEs, EXPLAIN output rows for recursive SELECT parts display Recursive in the Extra column.
Cost estimates displayed by EXPLAIN represent cost per iteration, which might differ considerably from total cost. The optimizer cannot predict the number of iterations because it cannot predict at what point the WHERE clause becomes false.
CTE actual cost may also be affected by result set size. A CTE that produces many rows may require an internal temporary table large enough to be converted from in-memory to on-disk format and may suffer a performance penalty. If so, increasing the permitted in-memory temporary table size may improve performance; see Section 8.4.4, “Internal Temporary Table Use in MySQL”.
Limiting Common Table Expression Recursion
It is important for recursive CTEs that the recursive SELECT part include a condition to terminate recursion. As a development technique to guard against a runaway recursive CTE, you can force termination by placing a limit on execution time:
The cte_max_recursion_depth system variable enforces a limit on the number of recursion levels for CTEs. The server terminates execution of any CTE that recurses more levels than the value of this variable.
The max_execution_time system variable enforces an execution timeout for SELECT statements executed within the current session.
The MAX_EXECUTION_TIME optimizer hint enforces a per-query execution timeout for the SELECT statement in which it appears.
Suppose that a recursive CTE is mistakenly written with no recursion execution termination condition:
By default, cte_max_recursion_depth has a value of 1000, causing the CTE to terminate when it recurses past 1000 levels. Applications can change the session value to adjust for their requirements:
You can also set the global cte_max_recursion_depth value to affect all sessions that begin subsequently.
For queries that execute and thus recurse slowly or in contexts for which there is reason to set the cte_max_recursion_depth value very high, another way to guard against deep recursion is to set a per-session timeout. To do so, execute a statement like this prior to executing the CTE statement:
Alternatively, include an optimizer hint within the CTE statement itself:
You can do this in addition to or instead of setting a time limit. Thus, the following CTE terminates after returning ten thousand rows or running for one second (1000 milliseconds), whichever occurs first:
Recursive Common Table Expression Examples
As mentioned previously, recursive common table expressions (CTEs) are frequently used for series generation and traversing hierarchical or tree-structured data. This section shows some simple examples of these techniques.
Fibonacci Series Generation
A Fibonacci series begins with the two numbers 0 and 1 (or 1 and 1) and each number after that is the sum of the previous two numbers. A recursive common table expression can generate a Fibonacci series if each row produced by the recursive SELECT has access to the two previous numbers from the series. The following CTE generates a 10-number series using 0 and 1 as the first two numbers:
The CTE produces this result:
Recursion ends when n reaches 10. This is an arbitrary choice, to limit the output to a small set of rows.
Date Series Generation
A common table expression can generate a series of successive dates, which is useful for generating summaries that include a row for all dates in the series, including dates not represented in the summarized data.
Suppose that a table of sales numbers contains these rows:
This query summarizes the sales per day:
However, that result contains “ holes ” for dates not represented in the range of dates spanned by the table. A result that represents all dates in the range can be produced using a recursive CTE to generate that set of dates, joined with a LEFT JOIN to the sales data.
Here is the CTE to generate the date range series:
The CTE produces this result:
The nonrecursive SELECT produces the lowest date in the date range spanned by the sales table.
Each row produced by the recursive SELECT adds one day to the date produced by the previous row.
Recursion ends after the dates reach the highest date in the date range spanned by the sales table.
Joining the CTE with a LEFT JOIN against the sales table produces the sales summary with a row for each date in the range:
The output looks like this:
Some points to note:
The use of COALESCE() avoids displaying NULL in the sum_price column on days for which no sales data occur in the sales table.
Hierarchical Data Traversal
Recursive common table expressions are useful for traversing data that forms a hierarchy. Consider these statements that create a small data set that shows, for each employee in a company, the employee name and ID number, and the ID of the employee’s manager. The top-level employee (the CEO), has a manager ID of NULL (no manager).
The resulting data set looks like this:
To produce the organizational chart with the management chain for each employee (that is, the path from CEO to employee), use a recursive CTE:
The CTE produces this output:
The nonrecursive SELECT produces the row for the CEO (the row with a NULL manager ID).
Each row produced by the recursive SELECT finds all employees who report directly to an employee produced by a previous row. For each such employee, the row includes the employee ID and name, and the employee management chain. The chain is the manager’s chain, with the employee ID added to the end.
Recursion ends when employees have no others who report to them.
Common Table Expressions Compared to Similar Constructs
Common table expressions (CTEs) are similar to derived tables in some ways:
Both constructs are named.
Both constructs exist for the scope of a single statement.
Because of these similarities, CTEs and derived tables often can be used interchangeably. As a trivial example, these statements are equivalent:
However, CTEs have some advantages over derived tables:
A derived table can be referenced only a single time within a query. A CTE can be referenced multiple times. To use multiple instances of a derived table result, you must derive the result multiple times.
A CTE can be self-referencing (recursive).
One CTE can refer to another.
A CTE may be easier to read when its definition appears at the beginning of the statement rather than embedded within it.
CTEs are similar to tables created with CREATE [TEMPORARY] TABLE but need not be defined or dropped explicitly. For a CTE, you need no privileges to create tables.
Конструкция WITH в T-SQL или обобщенное табличное выражение (ОТВ)
Всем привет! Тема сегодняшнего материала будет посвящена обобщенным табличным выражениям языка T-SQL, мы с Вами узнаем, что это такое, а также рассмотрим примеры написания запросов с использованием этих самых обобщённых табличных выражений.
Для начала, конечно же, давайте поговорим о том, что вообще из себя представляют обобщенные табличные выражения, какие они бывают, рассмотрим синтаксис, для чего их можно использовать и в заключение разберем несколько примеров.
Что такое обобщенное табличное выражение?
Common Table Expression (CTE) или обобщенное табличное выражение (OTB) – это временные результирующие наборы (т.е. результаты выполнения SQL запроса), которые не сохраняются в базе данных в виде объектов, но к ним можно обращаться.
Главной особенностью обобщенных табличных выражений является то, что с помощью них можно писать рекурсивные запросы, но об этом чуть ниже, а сейчас давайте поговорим о том, в каких случаях нам могут пригодиться OTB, в общем, для чего они предназначены:
Обобщенное табличное выражение определяется с помощью конструкции WITH, и определить его можно как в обычных запросах, так и в функциях, хранимых процедурах, триггерах и представлениях.
Синтаксис:
После обобщенного табличного выражения, т.е. сразу за ним должен идти одиночный запрос SELECT, INSERT, UPDATE, MERGE или DELETE.
Какие бывают обобщенные табличные выражения?
Они бывают простые и рекурсивные.
Простые не включают ссылки на самого себя, а рекурсивные соответственно включают.
Рекурсивные ОТВ используются для возвращения иерархических данных, например, классика жанра это отображение сотрудников в структуре организации (чуть ниже мы это рассмотрим).
Примечание! Все примеры ниже будут рассмотрены в MS SQL Server 2008 R2.
В качестве тестовых данных давайте использовать таблицу TestTable, которая будет содержать идентификатор сотрудника, его должность и идентификатор его начальника.
Как видите, у директора отсутствует ManagerID, так как у него нет начальника. А теперь переходим к примерам.
Пример простого обобщенного табличного выражения
Для примера давайте просто выведем все содержимое таблицы TestTable с использованием обобщенного табличного выражения
Где TestCTE это и есть псевдоним результирующего набора, к которому мы и обращаемся.
В данном случае мы могли и не перечислять имена столбцов, так как они у нас уникальны. Можно было просто написать вот так:
Пример рекурсивного обобщенного табличного выражения
Теперь допустим, что нам необходимо вывести иерархический список сотрудников, т.е. мы хотим видеть, на каком уровне работает тот или иной сотрудник. Для этого пишем рекурсивный запрос:
В итоге, если мы захотим, мы можем легко получить список сотрудников определенного уровня, например, нам нужны только начальники отделов, для этого мы просто в указанный выше запрос добавим условие WHERE LevelUser = 1
При написании рекурсивного ОТВ нужно быть внимательным, так как неправильное его составление может привести к бесконечному циклу. Поэтому для этих целей есть опция MAXRECURSION, которая может ограничивать количество уровней рекурсии. Давайте представим, что мы не уверены, что написали рекурсивное обобщенное выражение правильно и для отладки напишем инструкцию OPTION (MAXRECURSION 5), т.е. отобразим только 5 уровня рекурсии, и если уровней будет больше, SQL инструкция будет прервана.
Запрос у нас отработал, что говорит о том, что мы написали его правильно и соответственно OPTION (MAXRECURSION 5) можно смело убрать.
Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.
Иерархические (рекурсивные) запросы
Чтобы понять рекурсию, сначала надо понять рекурсию. Возможно, поэтому рекурсивные запросы применяют так редко. Наверняка вы представляете что такое SQL-запрос, я расскажу, чем рекурсивные запросы отличаются от обычных. Тема получилась объемная, приготовьтесь к долгому чтению. В основном речь пойдет об Oracle, но упоминаются и другие СУБД.
Суть проблемы
Большинство современных СУБД (Система Управления Базами Данных) — реляционные, т.е. представляют данные в виде двумерной таблицы, в которой есть строки (записи) и столбцы (поля записей). Но на практике мы часто сталкиваемся с иной организацией данных, а именно иерархической.
Взгляните на список файлов на вашем компьютере: все они организованы в виде дерева. Аналогично можно представить книги в библиотеке: Библиотека->Зал->Шкаф->Полка->Книга. То же самое и статьи на сайте: Сайт->Раздел->Подраздел->Статья. Примеры можно приводить долго. Впрочем, тут еще можно разделить все на отдельные таблицы: таблица для хранения списка библиотек, другая таблица для списка залов, третья для шкафов и т.д. Но если заранее не известна глубина вложенности или эта вложенность может меняться, тут уж от иерархии никак не отмашешься.
Проблема в том, что данные, имеющие иерархическую структуру, очень плохо представляются в реляционной модели. В стандарте SQL-92 нет средств для их обработки.
Зато такие средства появились в стандарте SQL-1999. Правда к тому времени в Oracle уже был собственный оператор CONNECT BY. Несмотря на это, в SQL-1999 синтаксис рекурсивных запросов совершенно не похож на синтаксис CONNECT BY в Oracle и использует ключевое слово WITH. Реализация же рекурсивных запросов в других СУБД несколько запоздала, так в MS SQL Server она появилась лишь в версии 2005.
Так же как и в синтаксисе, есть отличия и в терминологии. В Oracle обычно обсуждаемые запросы называются “иерархические”, у всех остальных “рекурсивные”. Суть от этого не меняется, я буду использовать и то и другое.
От слов к делу!
Для демонстрации будем использовать структуру каталогов, нам потребуется тестовая таблица, состоящая из 3-х полей:
id – идентификатор,
pid – идентификатор родителя (ссылается на id другой записи в той же таблице),
title – название каталога (вместо него может быть что угодно, даже несколько полей или ссылок к другим таблицам).
Здесь я использовал синтаксис mySQL, в других СУБД он несколько отличается. Так, в Oracle используются другие типы данных: вместо int — number, а вместо varchar — varchar2.
Заполнить табличку тестовыми данными не составит труда. Предлагаю записать список серверов, расположенных на территории различных предприятий в разных городах. Таким образом, в единой таблице оказываются и страны, и города, и фирмы, и сервера.
Думаю, остальные строки заполнить не составит сложностей. Получается почти как на картинке, только не в том порядке. Я специально заполнял не по-порядку, чтобы простым SELECT * FROM test_table получалось не иерархическая структура:
Тестовые данные готовы. Приступим к выборкам.
mySQL
Вынужден огорчить пользователей mySQL – в этой СУБД придется рекурсию организовывать внешними по отношению к СУБД средствами, например на php (код приводить не буду, его публиковали не раз, например здесь, достаточно поискать по запросу “mySQL tree” и т.п.).
Впрочем, есть иной подход, заключающийся в создании левой и правой границы для каждого узла, предложенный Джо Селко. Тогда можно будет обойтись обычными, не рекурсивными запросами.
Как-то я выводил дерево объектов в действующем проекте на php. База данных была на mySQL. Поплевавшись на отсутствие удобных операторов, я решил тогда не отображать все дерево целиком, а показать пользователю только первый уровень (схлопнутое дерево). При клике по плюсику в узле дерева отображались дочерние узлы для выбранного объекта, при этом они подгружались через AJAX. Выборка дочерних узлов по известному pid происходит быстро, поэтому интерфейс получился вполне шустрым. Возможно, это не лучшее решение, но оно имеет право на жизнь.
SQL-1999
В отличие от предыдущего стандарта SQL-92, в названии следующего решили отобразить номер тысячелетия, чтобы он не страдал от проблемы двухтысячного года. Помимо этого :-), появились новые типы данных (LOB), новые предикаты SIMILAR и DISTINCT, точки сохранения транзакций, объекты и их методы, и многое другое. Среди нововведений появились также рекурсивные запросы, о которых мы сейчас и поговорим.
Для получения иерархических данных используется временное представление, которое описывается оператором WITH. После этого из нее выбираются данные простым селектом. В общем виде синтаксис примерно такой:
В MS SQL нет ключевого слова RECURSIVE, его следует опустить. Но в остальном все то же самое. Такой синтаксис поддерживается в DB2, Sybase iAnywhere, MS SQL, начиная с версии 2005, и во всех базах данных, которые поддерживают стандарт SQL 1999.
Тогда для получения нашего дерева запрос получится такой:
WITH RECURSIVE
Rec (id, pid, title)
AS (
SELECT id, pid, title FROM test_table
UNION ALL
SELECT Rec.id, Rec.pid, Rec.title
FROM Rec, test_table
WHERE Rec.id = test_table.pid
)
SELECT * FROM Rec
WHERE pid is null ;
Здесь используется рекурсивная таблица Rec, которую мы сами придумали, построив ее по исходной таблице test_table. В описании Rec указано правило, каким образом соединять: WHERE Rec.id = test_table.pid. А в главном запросе отметили, что начинать надо с записи, у которой pid является пустым, т.е. с корневой записи.
Честно говоря, я никогда не работал в MS SQL Server 2005 или другой СУБД, использующей такой синтаксис. Поэтому написал этот запрос чисто из теоретических соображений. Для общности картины, чтобы было с чем сравнить.
В MS SQL 2008 можно применить более новое средство hierarchyid. Спасибо XaocCPS за его описание.
Oracle
Наконец-то мы добрались до Oracle! Об этой СУБД я хотел бы рассказать поподробнее. В Oracle иерархические запросы появились в 8-ой версии, задолго до появления стандарта. Поэтому до сих пор используется совсем другой синтаксис. Лично мне он кажется более понятным и напоминающим обычные функциональные языки. Хотя, скорее всего, это дело привычки.
Описание синтаксиса в документации напоминает бусы: на единую нить запроса нанизываются нужные операторы. Никому не приходило в голову сделать украшение для гички?
Тут видно, что единственно важное условие для построения иерархического запроса – это оператор CONNECT BY, остальное “нанизывается” по мере надобности.
Необязательный оператор START WITH говорит Ораклу с чего начинать цикл, т.е. какая строка (или строки) будет корневой. Условие может быть практически любым, можно даже использовать функции или внутренние запросы: pid is null, или или даже substr(title, 1, 1) = ‘Р’.
Неплохо. Все дочерние строки оказываются под своими родителями. Сортировку бы еще добавить, чтобы записи одного уровня выводились не абы-как, а по алфавиту. Ну чтож, сортировка это просто: добавим в конец запроса конструкцию ORDER BY title.
О, нет! Вся иерархия поломалась. Что же получилось? Оракл честно выбрал нужные строки в порядке иерархии (об этом говорит правильная расстановка level), а затем пересортировал их согласно правилу ORDER BY. Чтобы указать Ораклу, что сортировать надо только в пределах одного уровня иерархии, нам поможет маленькая добавка в виде оператора SIBLINGS. Достаточно изменить условие сортировки на ORDER SIBLINGS BY title – и все встанет на свои места.
Кстати, возможно все еще не понятно, почему этот порядок строк является деревом. Можно убрать все “лишние” поля и добавить отступы, станет более наглядно:
Ну вот, теперь все в точности, как на картинке в самом начале статьи.
Помните, файловые менеджеры обычно пишут путь к каталогу, в котором вы находитесь: /home/maovrn/documents/ и т.п.? Неплохо было бы и нам сделать так же. А сделать это можно абсолютно не напрягаясь: специалисты из Oracle все уже сделали за нас. Просто берем и используем функцию SYS_CONNECT_BY_PATH(). Она принимает два параметра через запятую: название колонки и строку с символом-разделителем. Будем не оригинальны, напишем так: SYS_CONNECT_BY_PATH(title, ‘/’).
Заодно ограничим вывод, выбрав только одну строку. Для этого, как всегда, нужно добавить условие WHERE. Даже в иерархическом запросе ограничивающее условие применяется ко всем строкам. Вставить его надо до иерархической конструкции, сразу после FROM. Для примера определим путь до “Сервер 1”, который у нас записан с >
SELECT SYS_CONNECT_BY_PATH(title, ‘/’ ) as Path
FROM test_table
WHERE > START WITH pid is null
CONNECT BY PRIOR >
Еще может быть полезен псевдостолбец CONNECT_BY_ISLEAF. Его можно использовать так же, как LEVEL. В этом псевдостолбце напротив каждой строки проставляется 0 или 1. Если есть потомки – проставится 0. Если потомков нет, такой узел в дереве называется “листом”, тогда и значение в поле CONNECT_BY_ISLEAF будет равно 1.
Устали? Осталось немного, самое страшное уже позади. Раньше мы использовали оператор PRIOR, который ссылался к родительской записи. Помимо него есть другой унарный оператор CONNECT_BY_ROOT, который ссылается (ни за что не догадаетесь!) на корневую запись, т.е. на самую первую в выборке.
Стоит отметить, что если в результате выполнения запроса обнаружится петля, Oracle выдаст ошибку. К счастью, ее можно обойти, хотя если в данных содержатся петли – это явно ошибка, в деревьях не бывает петель. На картинке с “бусами” запроса был нарисован оператор NOCYCLE после CONNECT BY – его мы и будем применять. Теперь запрос не будет вылетать. А чтобы определить “больной” участок, воспользуемся псевдостолбцом CONNECT_BY_ISCYCLE – в нем во всех хороших строках будет записано 0, а в тех, которые приводят к петлям, волшебным образом окажется 1.
Чтобы проиллюстрировать это, придется немного подпортить данные. ЛискиПресс ссылается у нас на город Лиски; изменим запись Лиски, чтобы она ссылалась на ЛискиПресс (не забудьте про commit – я вечно забываю):
update test_table set pid=10 where >
Если мы запустим какой-нибудь из предыдущих запросов, увидим, что и Лиски, и ЛискиПресс выпали из выборки, будто их нет совсем. Бегая в цикле, Оракл просто перестал на них натыкаться, т.к. нет пути от записи Россия к городу Лиски. Изменим условия START WITH, чтобы начинать с города Лиски – появится ошибка. Умный Оракл видит что запись уже выбиралась ранее и отказывается бегать в бесконечном цикле. Исправляем ошибку:
SELECT CONNECT_BY_ISCYCLE as cycl, id, pid, title
FROM test_table
START WITH > CONNECT BY NOCYCLE PRIOR >
Практические примеры
Иерархические запросы можно применять не только там, где есть явная иерархия.
Например, рассмотрим задачу получения списка пропущенных номеров из последовательности. Это бывает нужно, когда в некоей таблице id генерируется автоматически путем увеличения на 1, но часть записей были удалены. Нужно получить список удаленных номеров. По хорошей традиции, это следует сделать одним селектом.
Подготовим тестовые данные. Удалим из нашей таблицы пару записей:
DELETE FROM test_table WHERE id IN (3, 5);
С чего начнем? Во-первых, неплохо было бы получить список номеров подряд от 1 до максимального значения в нашей таблице, чтобы было с чем сравнивать. Выяснить максимальное значение id из таблицы, думаю, не составит никаких трудностей:
А вот чтобы сгенерировать последовательность от 1 до max как раз и понадобится рекурсивный запрос. Ведь как здорово просто взять и получить нужное количество строк! Достаточно будет их пронумеровать – и вот список готов.
SELECT rownum as rn FROM dual
CONNECT BY level SELECT max (id) FROM test_table);
Конструкция “SELECT … FROM dual” используется, когда надо вычислить значение функции, не производя при этом выборки данных. Dual – это системная таблица, состоящая из одного столбца и одной строки. Запрос из нее всегда возвращает одну строку со значением ‘X’. Благодаря такой умопомрачительной стабильности, эту таблицу удобно использовать в качестве источника строк.
Обычно таблицу, которую нагло используют для получения нужного количества строк, не выбирая сами данные, называют pivot. В качестве такой таблицы может выступать любая большая таблица, в том числе системная. Но использование dual в Oracle является более разумным решением.
Теперь, когда список номеров подряд уже есть, достаточно пройтись по нему и сравнить, есть ли такой номер в проверяемой таблице:
SELECT sq.rn
FROM ( SELECT rownum as rn FROM dual
CONNECT BY level SELECT max (id) FROM test_table)) sq
WHERE sq.rn not in ( SELECT id FROM test_table)
ORDER BY rn;
Всё. Ведро начищено до блеска, лошадь вооружена биноклем для остроты зрения. Да не коснется вас ROLLBACK. COMMIT!