Oracle view с параметрами
Существует несколько важных представлений словаря базы данных, которые можно использовать для нахождения детальной информации о любом из объектов базы данных, о которых говорилось в этой главе. Администраторы баз данных также интенсивно используют представления словаря данных, чтобы управлять различными объектами схемы. Здесь приводится краткий список важнейших представлений, часть из которых упоминалась выше. Полные данные о типах информации, которую можно получить от каждого из этих представлений, доступны по команде DESCRIBE (например, DESCRIBE DBA_CATALOG).
В этой статье блога будут описаны некоторые важные представления словаря данных, которые помогут управлять объектами, не хранящими данные (т.е. объектами, которые не относятся к таблицам и индексам). Ниже приведен список важнейших представлений словаря данных для просмотра объектов базы данных.
- DBA_SYNONYMS. Информация о синонимах базы данных.
- DBA_TRIGGERS. Информация о триггерах.
- DBA_SEQUENCES. Информация о созданных пользователем последовательностях.
- DBA_DB_LINKS. Информация о связях базы данных.
Как упоминалось ранее, представление DBA_OBJECTS предоставляет важную информацию обо всех перечисленных объектах, наряду с некоторыми другими типами объектов базы данных. Однако перечисленные представления содержат детальную информацию о каждом объекте, такую как исходный текст триггера, которую вы не получите из представления DBA_OBJECTS.
Управление такими объектами, как таблицы и представления, осуществляется ссылкой на представления словаря данных, наподобие DBA_TABLES и DBA_VIEWS. Существуют также отдельные представления для секционированных таблиц. Давайте рассмотрим ключевые представления словаря данных, относящиеся к таблицам и индексам.
DBA_TABLES
Представление DBA_TABLES содержит информацию обо всех реляционных таблицах базы данных. Представление DBA_TABLES — основной справочник для нахождения информации о хранении, количестве строк в таблице, состоянии протоколирования, информации буферного пула и многих других деталях. Ниже приведен простой пример запроса представления DBA_TABLES:
На заметку! Представление DBA_ALL_TABLES содержит информацию обо всех объектных и реляционных таблицах в базе данных, в то время как представление DBA_TABLES ограничено только реляционными таблицами.
Представление DBA_TABLES служит для нахождения таких вещей, как включено ли сжатие и отслеживание зависимостей на уровне строки, и была ли таблица уничтожена и помещена в корзину (Recycle Bin).
DBA_INDEXES
Представление словаря DBA_INDEXES служит для того, чтобы узнать все необходимое об индексах в базе данных, включая имя индекса, его тип, таблицу и табличное пространство, к которому он относится. Определенные столбцы, наподобие BLEVEL (сообщает уровень B-дерева индекса) и DISTINCT_KEYS (количество разных значений ключа индекс), наполняются, только если собрана статистика по индексу с использованием пакета DBMS_STATS.
Пример
Вот пример использования SQL DROP VIEW.
В этом SQL примере DROP VIEW удалит VIEW с именем sup_orders .
DBA_OBJECTS
Представление DBA_OBJECTS содержит информацию обо всех объектах базы данных, включая таблицы, индексы, пакеты, процедуры, функции, измерения, материализованные представления, планы ресурсов, типы, последовательности, синонимы, триггеры, представления и разделы таблиц (оно же секционирование). Как несложно догадаться, это представления удобно, когда нужно знать общую информацию относительно любого объекта базы данных. В листинге ниже показан запрос, предназначенный для нахождения времени создания и времени последней модификации объекта (LAST_DDL_TIME). Этот тип запроса поможет идентифицировать время модификации определенного объекта, что часто используется в процессе аудита.
Описание
SQL VIEW (представление), по сути, является виртуальной таблицей, которая физически не существует. Скорее, он создается оператором SQL, который объединяет одну или несколько таблиц.
DBA_VIEWS
Как известно, представления — это результаты запросов к некоторым таблицам базы данных. Представление словаря данных DBA_VIEWS позволяет увидеть SQL-запросы, лежащие в основе представлений. В листинге ниже показано, как получить текст представления OS_CUSTOMERS, принадлежащего пользователю oe.
Совет. Чтобы обеспечить полное отображение текста при использовании представления DBA_VIEWS, установите большое значение переменной long (например, SET LONG 2000). В противном случае вы увидите лишь несколько первых строк определения представления.
Часто задаваемые вопросы
Вопрос: Можно ли обновить данные в SQL VIEW.
Ответ: VIEW в SQL создается путем объединения одной или нескольких таблиц. Когда вы обновляете записи в представлении, обновляются записи в базовых таблицах, составляющих представление SQL.
Так что, да, вы можете обновить данные в SQL VIEW, если у вас есть соответствующие привилегии для базовых таблиц SQL.
Синтаксис
Синтаксис для SQL оператора DROP VIEW.
INDEX_STATS
Представление INDEX_STATS полезно для того, чтобы узнать, насколько эффективно индекс использует свое пространство. Крупные индексы имеют тенденцию со временем становиться несбалансированными, если происходит много удалений данных таблицы (а, следовательно, и индекса). Ваша цель — не упускать из виду эти крупные индексы,чтобы сохранять их сбалансированными.
Обратите внимание, что представление INDEX_STATS наполняется, только когда таблица подвергается анализу с помощью команды ANALYZE, как показано ниже:
Запрос из листинга ниже, использующий представление INDEX_STATS, помогает определить необходимость в перестройке индекса. Чтобы определить, следует ли перестраивать индекс, в запросе необходимо сосредоточиться на перечисленных ниже столбцах представления INDEX_STATS.
Дальнейшая автоматизация персональных настроек и фильтрации.
В предыдущей заметке мы обсуждали представление с функциональным параметром: в раздел where определения представления было включено сравнение значения столбца с результатом, возвращаемым функцией. Действительно, чтоб получить, например, представление, показывающее только сегодняшние события лога, можно записать
create view . as
select . from .
where . >= trunc(sysdate)
;
Никаких запретов на использование функций sysdate и trunc() в коде представления нет, все будет работать. Точно так же мы включили в код представления свою функцию, которая возвращает значение фильтрующего параметра, но перед использованием представления должны вызвать свою процедуру, которая установит необходимое значение пакетной переменной, которое вернет функция, включенная в код представления.
Oracle APEX предлагает возможность дальнейшей автоматизации этого подхода.
Предположим, у нас есть система на Oracle APEX, работающая с регионами РФ. Структура регионов достаточно сложная и в иерархическом, и во временном аспектах - иногда пользователям удобно исключать какие-то регионы из всех своих дел в системе.
Причем хотелось бы сделать это так (две схемы, естественно):
- Есть таблица регионов regions_tab в схеме-владельце и доверенное схеме-публикации экранирующее ее представление regions_vi ;
- Есть таблица исключенных пользователями регионов regions_excl_glb_tab в схеме-владельце и доверенное схеме-публикации экранирующее ее представление regions_excl_glb_vi ;
- Есть необходимые синонимы/представления в схеме-публикации;
- Хотелось бы, чтоб представление regions_vi схемы-публикации сразу же и автоматически не включало в себя регионы, исключенные данным пользователем, как нет их вообще для всей системы во время сеанса данного пользователя.
Оказывается, при создании сессии Oracle APEX записывает в стандартный контекст сеанса userenv пару параметр/значение, значение которой содержит разделенные двоеточием имя текущего пользователя и идентификатор текущей сессии.
Значение может быть извлечено из контекста функцией sys_context() с указанием имен контекста и параметра:
sys_context('userenv', 'client_identifier')
Если предположить для простоты, что имена пользователей не имеют в своем составе цифр (это, конечно, весьма сильное допущение), то имя пользователя можно извлечь из контекста просто подавлением двоеточия и цифр:
regexp_replace(sys_context('userenv', 'client_identifier'), ':|7+', '')
Тогда список исключенных данным пользователем регионов может быть получен простым запросом:
select region_id
from regions_excl_glb_vi
where usr
= regexp_replace(sys_context('userenv', 'client_identifier'), ':|3+', '')
;
А представление, "игнорирующее" в пределах сеанса для всей системы исключенные данным пользователем регионы, может быть записано следующим образом:
create view regions_vi as
select
from .regions_vi
where id not in
(
select region_id
from regions_excl_glb_vi
where usr
= regexp_replace(sys_context('userenv', 'client_identifier'), ':|4+', '')
)
;
Точно так же просто может быть решена частая и насущная задача показа текущему пользователю только его активов.
И мне это нужно каждый месяц, поэтому я хотел бы поместить его в представление, но, поскольку дата меняется каждый месяц, было бы здорово иметь параметр даты, который я могу передать представлению при его вызове. Есть ли способ, как я могу достичь этого? Я новичок в оракуле, ценю любую помощь. Спасибо.
DBA_TAB_MODIFICATIONS
Представление DBA_TAB_MODIFICATIONS показывает все изменения DML в таблице,произошедшие с момента последнего сбора статистики по этой таблице. Вот запрос к этому представлению:
База данных не обновляет представление DBA_TAB_MODIFICATIONS в реальном времени. Следовательно, вы можете и не увидеть изменений в различных таблицах, немедленно отраженных в этом представлении.
DBA_MVIEWS
Представление словаря DBA_MVIEWS сообщает все о материализованных представлениях в базе данных, в том числе информацию, включено ли для них средство переписывания запросов. В листинге ниже демонстрируется использования этого представления.
Обновить SQL VIEW
Вы можете изменить определение SQL VIEW, не удаляя его, с помощью SQL оператора CREATE OR REPLACE VIEW.
Пример
Вот пример использования SQL CREATE VIEW.
Этот SQL пример CREATE VIEW создаст виртуальную таблицу на основе результирующего набора оператора select. Теперь вы можете запросить SQL VIEW следующим образом.
Drop SQL VIEW
После создания SQL VIEW его можно удалить с помощью SQL оператора DROP VIEW.
DBA_TAB_COLUMNS
Предположим, вы нужно узнать среднюю длину каждой строки таблицы или значение по умолчанию каждого столбца (если таковое есть). Представление DBA_TAB_COLUMNS — отличный способ быстро получить всю детальную информацию о столбцах таблиц схемы, как показано в листинге ниже.
DBA_TAB_PARTITIONS
Представление DBA_TAB_PARTITIONS подобно представлению DBA_TABLES, но содержит детальную информацию о разделах таблиц. Благодаря DBA_TAB_PARTITIONS, можно узнать имя раздела, его максимальные значения, информацию о хранении раздела,статистику по разделу, а также прочую информацию, которая доступна в представлении DBA_TABLES. В листинге ниже показан простой запрос, использующий представление DBA_TAB_PARTITIONS.
4 ответа
Существуют способы «параметризации» представления, например, используя контексты Oracle, но они не часто полезны и, конечно, не для вашего случая.
Если ваш запрос на самом деле просто выбирает из одной таблицы только с датами в качестве предикатов, то представление также не добавляет особой ценности. Вы можете создать SQL скрипт (в файле, например, myquery.sql), используя переменные связывания:
Затем каждый месяц вы можете просто открыть файл и запустить его, и он предложит вам 2 даты. Или вы можете запустить скрипт как этот, и он также предложит вам:
Или если вы используете подстановочные строки '& 1.' и '& 2.' вместо:
Затем вы можете передать даты в командной строке следующим образом:
(Потому что & 1. Означает первый параметр в командной строке и т. Д.)
Начиная с 19.6 вы можете создавать параметризованные представления, используя Макросы SQL.
Другой способ сделать это - использовать функцию, которая извлекает параметры из таблицы, поэтому вам не нужно манипулировать каким-либо DDL. Идея здесь
- Используя таблицу для хранения параметров, в основном вам нужно значение параметра и описание параметра.
- Использование функции для получения значения этого параметра, когда вход является именем параметра
- Использование вызова функции внутри представления.
- Затем вы можете автоматически управлять представлением, изменяя значения таблицы параметров.
Этот запрос возвращает данные за предыдущий месяц , то есть месяц перед текущим месяцем на момент запроса (= sysdate ).
Вы используете trunc с 'MM' для получения первых месяцев и арифметику с add_months
Views have always been an efficient tool for encapsulating complex logic, creating defined access structures and so on. But there is one thing views cannot do: accept parameters. And this is unfortunately a big disadvantage in terms of flexibility. As a result, there are a number of workarounds, none of them without their drawbacks.
I will use a little bit complicated example but the one that have a real practical application in the domain of data warehousing and ETL. It’s about historizing all changes according to slowly changing dimensions type II principle. My colleague Philipp Savlisberg has posted a historized version of the famous SCOTT schema to look at joining temporal intervals. He also provides a script to create this schema and data.
To simplify the queries, two types of the views are often placed on top of the versioned tables: the one to query a version of the data at any point in time and the other to query all versions within a time interval.
Let’s look at the data for one employee (EMPNO=7788):
Listing 1: keeping all changes in a historized EMP table
Would be great, if we could get a view with the following query:
Listing 2: the desired query for the view
From my point of view, the best known workaround is that a view is defined using SYS_CONTEXT calls instead of the desired parameters. You have to create a user context and set the variables your view should use before querying the view. That alone is quite inconvenient. But another drawback is that you are always using bind variables then. Yes, I said that! Bind variables are good most of the time but not always. I’m doing a lot of ETL and I would often prefer literals over binds.
Listing 3: simplified description of SYS_CONTEXT workaround
Another one is to use a package and package variables as parameters. Define the setter and getter methods and use the getter methods instead of parameters in your view. Well, the drawbacks are the same: you have to set the variables first and you cannot switch to literals too.
Listing 4: simplified description of a workaround with package variables
Yet another popular option is not having a view at all, but rather to query a pipelined function which acts as view and can accept parameter. But again, mixing SQL and PL/SQL means you have to keep context switches in mind.
Okay, you might ask, what’s the problem here? Just query the view with the desired parameters as WHERE predicates and they will hopefully be pushed down into the view query. It may be or maybe not, depending of how complex your view is. And, first of all, you have to expose those columns from the view to be able to filter on them. What if your view has a scalar subquery and that’s where you want to introduce a parameter? How simple would it be just to return another column from a scalar subquery? Not at all! Maybe you have to rewrite the whole query because of that.
SQL Macros
Let’s again look at the query, we would like to implemet as a view:
Listing 5: the desired query for the view
Well, we know, with a plain view it is just not possible. How to solve the task with a SQL macro? First, we need a table macro (default), because we will use it in the FROM clause. Then just take the query from above and put it into a SQL macro function:
Listing 6: create and query SQL macro
That’s it! It looks like we were quering a function, just like in a workaround with pipelined functions, but the difference is huge. The function will run only at parse time and two substitutions are made then. First, the parameter reference will be substituted in the return string of a function. Second, the return string of the function (now with a literal instead of a parameter) will be substitued in the query text instead of a function call. Using DBMS_UTILITY.expand_sql_text, we can see the query actually running. No context switch at all!
But the requirement is actually generic. We need views for all versioned tables. Do we have to create a SQL macro function for every table? No! Being able to pass a table parameter into a SQL macro function, we can just create very simple interface for querieng those tables. With function overloading capabilities of PL/SQL we can even use the “same” function for both scenarios, point in time and interval. See implementation of the package TIME_MACHINE in Listing 7. I’ve borrowed this “time machnine” analogy from my colleague Dani Schnider, but his presentation is only available in german.
Listing 7: creating the time machine interface
This is how we can use the “time machine” to get both point in time as well as interval data from EMPV and DEPTV tables:
Listing 8: querying using the new generic interface
Listing 9: using bind variables with SQL macro functions
Summary
I am really impressed with what we have been able to achieve here with SQL macros! On the one hand, one can disagree that we don’t actually have a parameterized view here. Yes, we don’t have a view at all. But on the other hand, we can use the SQM-function exactly the same way. We have built one function that can handle ALL tables, instead of having a lot of views. And moreover, we have not hidden any information from query optimizer. It can see literals or do bind peeking and use histograms if available.
В этом учебном материале вы узнаете, как создавать, обновлять и удалять SQL VIEWS с синтаксисом и примерами.
DBA_IND_COLUMNS
Представления DBA_IND_COLUMNS по структуре подобно представлению DBA_CONS_COLUMNS и содержит информацию обо всех проиндексированных столбцах каждой таблицы. Эта информация важна при настройке производительности, когда вы замечаете,что запрос использует индекс, но вы не знаете точно, на каких столбцах этот индекс определен. Запрос, приведенный в листинге ниже, показывает, что таблица имеет индексы, определенные на неверных столбцах.
Совет. Взглянув на столбец INDEX_NAME, можно легко идентифицировать составные ключи. Если одно и то же вхождение INDEX_NAME появляется больше одного раза, значит, это составной ключ; и столбцы, являющиеся его частью, показаны в столбце COLUMN_NAME. Например,INVENTORY_PK — первичный ключ таблицы INVENTORIES, определенный на двух столбцах:PRODUCT_ID и WAREHOUSE_ID. Порядок столбцов в определении составного ключа можно узнать с помощью столбца COLUMN_POSITION.
Синтаксис
Синтаксис для SQL оператора CREATE OR REPLACE VIEW.
Синтаксис
Синтаксис для оператора CREATE VIEW в SQL.
view_name Имя SQL VIEW, которую вы хотите создать WHERE conditions Необязательный. Условия, которые должны быть выполнены для включения записей в представление.
DBA_PART_TABLES
Представление DBA_PART_TABLES содержит информацию о типе схемы раздела и прочих параметрах хранения разделов и подразделов. Узнать тип каждого раздела каждой секционированной таблицы можно с помощью следующего запроса:
Пример
Вот пример того, как бы вы использовали SQL оператор CREATE OR REPLACE VIEW.
В этом SQL примере CREATE OR REPLACE VIEW обновляет определение SQL VIEW, называемое sup_orders , не удаляя его. Если SQL VIEW еще не существует, SQL VIEW будет просто создан впервые.
Создать SQL VIE
DBA_EXTERNAL_TABLES
Представление DBA_EXTERNAL_TABLES показывает подробности о любой внешней таблице в базе данных, включая их тип доступа, параметры доступа и информацию о каталоге.
Читайте также: