Связать таблицы oracle

Обновлено: 26.04.2024

В этих темах описывается создание наборов данных с помощью запросов SQL.

Ввод запросов SQL

Ниже описана процедура ввода запросов SQL.

Нажмите Новый набор данных , а затем SQL-запрос .
В диалоговом окне для создания нового набора данных введите имя набора данных.
Источник данных по умолчанию — источник данных по умолчанию, выбранный на странице "Свойства". Если для этого набора данных не используется источник данных по умолчанию, выберите в списке Источник данных .

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

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

Если запрос содержит переменную привязки, запрашивается создание параметра привязки. Нажмите ОК , чтобы редактор моделей данных создал параметр привязки.

Использование построителя запросов SQL

Используйте построитель запросов для создания SQL-запросов без кодирования. Построитель запросов позволяет искать и фильтровать объекты баз данных, выбирать объекты и столбцы, создавать взаимосвязи между объектами и просматривать форматированные результаты запросов с минимальными знаниями SQL.

В этом разделе описывается использование построителя запросов и рассматриваются следующие темы:

Обзор построителя запросов

Панель "Выбор объекта" содержит список объектов, на основе которых можно создавать запросы. Отображаются только объекты из текущей схемы.

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

Модель отображает выбранные объекты на панели "Выбор объекта".

Условия позволяет применять условия к выбранным столбцам.

SQL отображает запрос.

Результаты отображает результаты запроса.

Построение запроса с помощью построителя запросов

Запрос можно создать с помощью построителя запросов.

В списке "Схема" отображаются все схемы, доступные в источнике данных. Возможно, доступ будет предоставляться не ко всем схемам в этом списке.

На панели "Выбор объекта" перечислены таблицы, представления и материализованные представления из выбранной схемы. Для базы данных Oracle на панели также отображаются синонимы. Если выбрать объект в списке, он отображается на панели проектирования. С помощью панели проектирования укажите способ использования выбранных объектов в запросе.

Может потребоваться ввести строку поиска в поле "Поиск". Если источник данных содержит больше 100 таблиц, используйте функции поиска для поиска и выбора объектов.

Поддерживаемые типы столбцов

На панели проектирования столбцы всех типов отображаются как объекты. Для каждого запроса можно выбрать не более 60 столбцов.

Поддерживаемый тип столбца	Ограничения
VARCHAR2, CHAR	Н/Д
NUMBER	Н/Д
DATE, TIMESTAMP	Данные типа TIMESTAMP WITH LOCAL TIMEZONE не поддерживаются.
Большой двоичный объект (BLOB)	BLOB может быть изображением, текстом или данными XML. При выполнении запроса в построителе запросов BLOB-объект не отображается на панели "Результаты", однако при сохранении в редакторе моделей данных запрос создается правильно. Данные BLOB не поддерживаются для источника данных Oracle BI EE из-за ограничений драйвера BIJDBC.

RTF-шаблон применяется, если требуется использовать столбец данных BLOB с типом данных "Изображение".

Добавление объектов на панель проектирования

Выберите каждый объект, который необходимо добавить на панель проектирования.

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

При выборе столбца он отображается на вкладке Условия . Флажок Показать на вкладке Условия определяет, будет ли столбец включен в результаты запроса. По умолчанию этот флажок установлен.

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

Запрос можно также выполнить, нажав клавиши CTRL+ENTER .

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

Удаление или скрытие объектов на панели проектирования

Объекты на панели проектирования можно удалить или скрыть.

Чтобы удалить объект, выполните указанные ниже действия.

Чтобы временно скрыть столбцы внутри объекта, выполните указанные ниже действия.

Условия запроса

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

При выборе столбцов внутри объекта можно на вкладке Условия указать условия. Можно изменить псевдоним столбца, применить условия столбцов, отсортировать столбцы или применить функции.

Условие изменяет фразу WHERE запроса. При указании условия столбца необходимо задать соответствующие оператор и операнд. Поддерживаются все стандартные условия SQL. Пример:

IN (SELECT dept_no FROM dept)

BETWEEN SYSDATE AND SYSDATE + 15

Указывает функции. Доступные функции аргументов:

Числовые столбцы — COUNT, COUNT DISTINCT, AVG, MAXIMUM, MINIMUM, SUM

Столбцы типа VARCHAR2, CHAR — COUNT, COUNT DISTINCT, INITCAP, LENGTH, LOWER, LTRIM, RTRIM, TRIM, UPPER

Столбцы типа DATE, TIMESTAMP — COUNT, COUNT DISTINCT

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

При выборе столбцов и определении условий построитель запросов записывает SQL-код для вас. Лежащий в основе SQL-код можно просмотреть на вкладке SQL .

Создание отношений между объектами

Отношения между объектами можно создать путем создания соединения. Соединение определяет связь между двумя или более таблицами, представлениями или материализованными представлениями.

Об условиях соединения

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

Условие соединения определяет, как строки из одного объекта объединяются с строками из другого объекта.

Построитель запросов поддерживает внутренние, внешние, левые и правые соединения.

Внутреннее соединение (также называется простым) возвращает строки, удовлетворяющие условию соединения.

Внешнее соединение расширяет результат простого соединения.

Внешнее объединение возвращает все строки, которые удовлетворяют условию соединения, и возвращает некоторые или все строки одной таблицы, для которой ни одна из строк другой таблицы не удовлетворяет условию соединения.

Соединение объектов вручную

Создайте соединение вручную, выбрав на панели проектирования столбец "Соединение".

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

На панели "Выбор объекта" выберите объекты, которые требуется соединить.
Определите столбцы, которые требуется соединить.

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

Выберите для выбора столбца для объединения

Если он выделен, столбец Соединение затемнен. Чтобы отменить выделение столбца Соединение , просто выберите его еще раз или нажмите клавишу ESC .

При соединении линия соединяет два столбца. Пример показан ниже.

Сохранение запроса

Сохраняйте запросы после их создания.

После создания запроса нажмите кнопку Сохранить , чтобы вернуться в редактор моделей данных. Запрос появится в поле "Запрос SQL". Нажмите ОК , чтобы сохранить этот набор данных.

Изменение сохраненного запроса

При сохранении запроса из построителя запросов в редакторе моделей данных последний можно также использовать для редактирования запроса.

С помощью построителя запросов нельзя редактировать пользовательский или расширенный запрос.

Чтобы изменить сохраненный запрос, выполните указанные ниже действия.

Выберите набор данных SQL.
На панели инструментов нажмите Изменить выбранный набор данных , чтобы открыть диалоговое окно Редактировать набор данных .
Нажмите Построитель запросов , чтобы загрузить запрос в построитель запросов.
Измените запрос и нажмите ОК .

Добавление переменной привязки в запрос

После создания запроса пользователи могут передать ему параметр, чтобы ограничить результаты.

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

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

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

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

На этом изображении отображаются столбцы таблицы отделов.

Добавление переменной привязки с помощью текстового редактора

Редактор моделей данных используется для обновления запросов SQL.

and "COLUMN_NAME" in (:PARAMETER_NAME)

and "DEPARTMENT_NAME" in (:P_DEPTNAME)

где P_DEPTNAME — имя, которое вы выбираете для параметра, как показано ниже.

Добавление лексических ссылок в запросы SQL

Ссылки на лексические ссылки можно использовать для замены фраз, следующих после инструкций SELECT, FROM, WHERE, GROUP BY, ORDER BY или HAVING.

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

Создайте лексическую ссылку в запросе SQL, используя следующий синтаксис:

Перед созданием запроса определите параметр в пакете PL/SQL по умолчанию для каждой лексической ссылки в запросе. Механизм обработки данных использует эти значения для замены лексических параметров.
В редакторе моделей данных на странице "Свойства" укажите Oracle DB Default Package .
В редакторе моделей данных создайте триггер события До данных для вызова пакета PL/SQL.
Создайте запрос SQL с лексическими ссылками.
Если нажать ОК , чтобы закрыть запрос SQL, запрашивается ввод параметра.

Например, создайте пакет employee . В пакете employee задайте параметр where_clause :

Укажите лексический параметр в запросе SQL, где параметр необходимо заменить кодом, определенным в пакете, например:

Если нажать ОК в диалоговом окне "Создать набор данных SQL", в диалоговом окне лексической ссылки предлагает ввести значение для лексических ссылок, введенных в запрос SQL, как показано на изображении ниже. Введите значение лексической ссылки, как оно определено в пакете PL/SQL.

Во время выполнения механизм обработки данных заменяет &where_clause контентом where_clause , определенным в пакете.

Об определении запросов SQL для Oracle BI Server

В этом разделе описаны аспекты, которые необходимо помнить при определении запросов SQL для Oracle BI Server.

При создании набора данных на сервере Oracle BI Server следует учитывать следующие факторы:

При создании запроса SQL для Oracle BI Server с помощью редактора данных SQL или построителя запросов создается логический SQL, а не физический SQL, как в других источниках баз данных.

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

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

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

В построителе запросов функции Порядок сортировки и Группировать по , представленные на вкладке Условия , не поддерживаются для запросов к Oracle BI Server. Если указать порядок сортировки или установить флажок "Группировать по" построитель запросов создает SQL-код и записывает его в текстовое поле "Запрос SQL" в Publisher, но при попытке закрыть диалоговое окно "Набор данных" запрос не проходит проверку.

Чтобы применить группирование к данным, извлеченным с помощью запроса SQL, можно использовать функцию Группировать по редактора моделей данных. См. раздел "Создание подгрупп".

Если вы передаете параметры серверу Oracle BI Server и выбираете "Передано значение Null" для варианта "Возможность выбрать все", убедитесь, что в запросе обрабатывается значение null.

Определение запросов SQL для Oracle BI Server

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

В редакторе моделей данных нажмите Новый набор данных , а затем нажмите Запрос SQL .
Введите имя для этого набора данных.
В списке "Источник данных" выберите соединение с Oracle BI Server, обычно отображаемое как Oracle BI EE.
Нажмите Построитель запросов , чтобы открыть страницу "Построитель запросов".

Синтаксис SQL также можно ввести вручную в текстовом поле "Запрос SQL", однако необходимо использовать синтаксис логического SQL, используемый сервером Oracle BI Server.

Надеюсь из прошлых, почти полностью теоретических, изложений стало немного яснее, что же все-таки есть реляционные данные и все, что с ними связано. Давайте сейчас попробуем просмотреть все это практически. Наша с вами учебная БД в схеме miller содержит, пять таблиц. Все они в принципе отвечают требованиям 3НФ. Но, когда я их создавал, я не связал столбцы этих таблиц между собой с помощью стандартных средств. А, вот сейчас давайте мы с вами это сделаем. Итак для примера организуем связь, которая чаше всего рекомендована к применению, типа один-ко-многим. Ярким примером для построения такой связи служит две из наших пяти таблиц это CUSTOMERS и SALESREPS. Оператор CREATE TABLE их DDL определений записан следующим образом:

Таблица CUSTOMERS:

Таблица SALESREPS:

При просмотре данных, этих таблиц почти сразу видно, что столбец таблицы SALESREPS - EMPL_NUM есть отношение один-ко-многим столбца CUST_REP для таблицы CUSTOMERS. Для определения связи между таблицами воспользуемся оператором ALTER TABLE и запишем вот такую конструкцию:

Все, связь между столбцами таблиц установлена! Все достаточно просто. Теперь действует ограничение ссылочной целостности и нарушить его нам с вами не позволят! Можно убедиться в этом. Столбец таблицы SALESREPS - EMPL_NUM содержит следующее множество значений 101 .. 110 и отдельно 120. Попробуйте что-нибудь вроде:

После ввода, получаем:

Естественно ошибка ORA-02291! А все потому, что множество 101 .. 110 и отдельно 120 не содержит числа 150! И по этому в данном случае не допустимо! Вот и получилось жесткое отношение один-ко-многим! Так же, можно и удалить связь, между столбцами таблиц применив оператор DROP. Но, нужно узнать имя ссылочной целостности в системе. Сейчас мы его знаем благодаря ошибке. А что если, в процессе работы нужно удалить ссылочную целостность, а потом снова восстановить ее! Для этого обратимся к представлению в вашей схеме USER_CONSTRAINTS. Оно содержит все имена ваших ограничений. Дадим такой запрос:

Там где поле CONSTRAINT_TYPE содержит значение R и есть наше ограничение (по моему от REFERENCES, точно не помню!) Получаем имя ограничения - SYS_C003548 (тоже номер был и в ошибке помните?). Вот теперь давайте от него избавимся:

Вот теперь ограничение снято. Повторим наш предыдущий запрос и посмотрим, что содержит USER_CONSTRAINTS сейчас:

Хорошо видно, что осталось только ограничение первичного ключа таблицы CUSTOMERS имеющее имя SYS_C003506. Кроме того, таблица может содержать ограничение на саму себя например все с той же таблицей SALESREPS можно проделать следующее:

Теперь таблица, как бы это лучше сказать - "самоограничилась", хотя это не всегда оправдано, но вполне применимо и может использоваться! Можете сами с этим всем поработать и определить приоритеты, при проектировании БД, оптимизации и определении ссылочных целостностей таблиц! Но, слишком не увлекайтесь, границы сознания не бесконечны и не стоит выходить за границы понимания, а уж во всяком случае выпускать за них свою БД. :)

Работать с одной таблицей в БД приходится редко. Как правило, данные распределены по нескольким таблицам, которые связаны между собой.

Подготовка данных

Для демонстрации соединений понадобится несколько таблиц.

Информация о пользователях хранится в нескольких таблицах. Для того, чтобы получить данные “вместе”, придется использовать соединения.

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

Получим следующий результат:

Приведенный запрос можно читать по порядку:

Берем все записи из таблицы user_roles
Теперь “приклеиваем” справа к нашему набору данных строки из таблицы app_roles , у которых в колонке role_id содержатся такие же значения, как и в колонке role_id таблицы user_roles . При этом строки, у которых эти значения не совпадают, убираются из результирующего набора
К получившемуся на шаге 2 набору данных “приклеиваем” справа строки из таблицы app_users, у которых значение в колонке login совпадает со значением колонки login в таблице user_roles . Опять же, строки, у которых эти значение не совпадают, удаляются из результирующего набора данных.
Из получившегося набора данных, выбираем только колонки login , email , role_name . После “склейки” данных наш набор содержит все колонки, которые содержатся в используемых таблицах, так что мы могли показать значения вообще любых колонок из любой из этих трех таблиц(либо вообще все).

Рассмотрим соединение строк для пользователя с ником johndoe : Сначала соединяются таблицы app_users и user_roles . В результат соединения попадают строки, у которых совпадает логин пользователя.

В результате соединения мы получим следующий набор данных(колонку с датой регистрации не показываем):

Здесь следует обратить внимание на то, что значения строк из таблицы app_users повторяются для каждой из строк в таблице user_roles . Затем мы соединяем получившийся набор данных с таблицей APP_ROLES , и к выборке приклеиваются ещё три строки, имеющие совпадение, на этот раз по значению в колонке ROLE_ID . При этом строки, которые не имеют совпадения, в выборку не добавляются:

Left join

Предыдущий запрос выводил только тех пользователей, у которых действительно были назначены некие роли в приложении. Теперь покажем всех пользователей и их роли. Для этого будет использоваться LEFT JOIN . Он отличается от обычного JOIN тем, что он не убирает строки из уже имеющегося набора данных когда “приклеивает” справа новые данные.

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

Схематично процесс “приклеивания” показан на рисунке:

Исходная таблица и первый JOIN (или LEFT JOIN ) дают некий набор данных, который обозначен цифрой “1”. Все, далее стоит этот набор данных рассматривать как одну таблицу, к которой еще раз “приклеиваются” данные с помощью еще одного соединения.

Еще одна схема соединений:

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

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

Пример из части, где описывалось соединение join , может быть записан и без использования этого самого join .

Эти два запроса идентичны.

Вообще, Oracle позволяет записать и left/right join - соединения подобным образом, указывая правила соединения в части where запроса. Данный синтаксис использовался до версии БД = 9i и здесь рассматриваться не будет.

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

Эквисоединение

При эквисоединении (equi-join) две или более таблиц соединяются на основании условия равенства между столбцами. Другими словами, один и тот же столбец имеет одинаковое значение во всех соединяемых таблицах. Ниже приведен пример применения эквисоединения:

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

При желании соединить несколько столбцов, можно перечислить их имена в виде разделенного запятыми списка, например: USING ( dept_id , emp_name ).

Естественное соединение

Естественным соединением (natural join) называется эквисоединение, при котором столбцы, которые должны сопоставляться для выполнения соединения, специально не указываются. Oracle автоматически определяет подлежащие соединению столбцы на основании совпадающих столбцов в двух таблицах. Ниже приведен пример применения естественного соединения:

В этом примере условием для выполнения соединения служит наличие идентичных значений в столбце last_name в таблицах emp и dept .

Рефлексивное соединение

Под рефлексивным соединением (self join) подразумевается соединение таблицы с самой собой за счет использования псевдонимов. В следующем примере осуществляется соединение таблицы employees с самой собой при помощи псевдонима с удалением всех дублированных строк.

Внутреннее соединение

Внутреннее соединение (inner join), также называемое простым соединением (simple join), предусматривает возврат всех строк, которые удовлетворяют указанному условию соединения. Раньше в синтаксисе внутреннего соединения для указания того, каким образом должны соединяться таблицы, нужно было использовать конструкцию WHERE , например, так:

Теперь Oracle позволяет задавать критерии соединения в синтаксисе внутреннего (или простого) соединения за счет применения новой конструкции ON или USING , например:

Внешнее соединение

Внешнее соединение (outer join) применяется для возврата всех строк, которые удовлетворяют указанному условию соединения, плюс некоторых или всех строк из таблицы, в которой нет подходящих строк, удовлетворяющих указанному условию соединения. Существуют три вида внешнего соединения: левое внешнее соединение (left outer join), правое внешнее соединение (right outer join) и полное внешнее соединение (full outer join). В операторе полного внешнего соединения слово OUTER обычно опускается.

Oracle позволяет использовать операцию внешнего соединения, подразумевающую применение знака плюс (+) для обозначения недостающих значений в одной таблице, но рекомендует лучше использовать вместо нее более новый синтаксис соединения ISO/ANSI. Ниже приведен пример типичного запроса с оператором полного внешнего соединения:

Соединение в модели обозначает связь между одной таблицей фактов и одной таблицей измерений.

О соединениях

Соединение в модели обозначает связь между одной таблицей фактов и одной таблицей измерений. При моделировании данных с помощью мастера "Добавить в модель" мастер автоматически создает соединения между таблицей фактов и каждой соответствующей ей таблицей измерений.

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

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

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

Присоединение таблиц фактов и измерений

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

Заблокируйте модель для редактирования в построителе моделей данных.
В области "Таблицы измерений" перетащите таблицу измерений в область "Таблицы фактов". В области "Соединение" нажмите Создать соединение .

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

Чтобы удалить изменения, щелкните значок X. Если начать создавать новое соединение и нажать X, новая строка для данного соединения удаляется из таблицы "Соединения".

Чтобы просмотреть иерархии и уровни по умолчанию после создания соединений, следует перейти на вкладку "Иерархии" для заданной таблицы измерений.

Читайте также: