Создание других объектов схемы

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

Просмотры

Представление базы данных — это логическая или виртуальная таблица, основанная на запросе. Представления запрашиваются точно так же, как таблицы. Это означает, что с вашей точки зрения разработчика или с точки зрения пользователя системы баз данных представление выглядит как таблица. Определение представления как объект хранится в словаре данных базы данных; однако представление не хранит сами данные. База данных также хранит план выполнения для создания представления — это означает, что данные могут быть быстро получены с использованием представления, даже если фактические данные, представленные запросом SELECT представления, не сохраняются. как часть представления. Скорее, данные «собираются вместе» каждый раз, когда представление запрашивается из таблиц базы данных, для которых определено представление, — они называются базовыми таблицами.

Общий синтаксис приведен ниже.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [ViewName]
[(Column Alias Name...)] 
AS [Query]
[WITH [CHECK OPTION] [READ ONLY] [CONSTRAINT]];

Из синтаксиса

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

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

Опция WITH READ ONLY позволяет создавать представление только для чтения. Вы не можете использовать команды DELETE, INSERT или UPDATE, чтобы изменить данные для представления только для чтения.

Предложение WITH CHECK OPTION позволяет обновлять строки, которые можно выбрать с помощью представления. Оно также позволяет указывать ограничения на значения. Предложение CONSTRAINT работает в сочетании с предложением WITH CHECK OPTION, позволяя администратору базы данных назначать уникальное имя. в опцию CHECK. Если администратор базы данных пропускает предложение CONSTRAINT, Oracle автоматически назначит ограничению сгенерированное системой имя, которое не будет иметь большого значения.

Типы просмотров

Простое представление создается только над одной таблицей. Это простой запрос SELECT без функций или групповых предложений, а просто выбор столбцов из таблицы без каких-либо преобразований. Если в представлении выполняется DML, это отражается сразу. в базовой таблице.

Сложное представление создается для нескольких таблиц с использованием объединений. Оно может содержать функции SQL, сгруппированные по функциям. Но так как представление состоит из нескольких данных и выбор столбцов также не прост, он не допускает операции DML над ним.

иллюстрация

Простой вид: В следующем простом виде выберите имя сотрудника, идентификатор отдела и зарплату для сотрудников с идентификатором JOB в качестве DEV.

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary
FROM employees
WHERE job_id = 'DEV';

Комплексное представление: в приведенном ниже примере показано название отдела, средняя заработная плата, полученная в отделе, и количество сотрудников, работающих в нем.

CREATE OR REPLACE VIEW EMP_VU
AS
SELECT department_name, AVG (salary) avg_sal, COUNT (first_name) count
FROM employees E, departments D
WHERE E.department_id = D.department_id
GROUP BY department_name;

DESCRIBE [имя представления] описывает структуру представления. Столбцы перечислены в той же последовательности, что и в определении представления.

Операции DML в представлении

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

Когда вы выполняете оператор DML UPDATE, DELETE или INSERT в представлении, вы фактически манипулируете строками данных для базовой таблицы или таблиц, для которых определено представление. Существуют ограничения на использование операторов UPDATE, DELETE и INSERT. with views.First, чтобы использовать оператор UPDATE, DELETE или INSERT с представлением, представление должно быть обновляемым. Представление может обновляться, если предложение SELECT не указывает никакой агрегирующей функции в листинге SELECT. Кроме того, представление не может были созданы с использованием предложения или предложений GROUP BY, DISTINCT или UNION. Допускается использование агрегатных функций в подзапросе SELECT в предложении FROM. Кроме того, представление не может иметь никаких производных столбцов в списке SELECT. Далее, если представление создается в результате операции JOIN (представление соединения), операторы UPDATE и INSERT могут изменять или вставлять строки только в одну из базовых таблиц одновременно. Вы не можете изменять строки из двух или более таблиц с помощью одного оператора языка манипулирования данными (DML). Наконец, оператор DELETE может выполняться только для представления, если на таблицу ссылаются в предложении FROM. Это просто означает, что вы не можете удалить строки из таблицы, которая не была указана.

С опцией CHECK OPTION

WITH CHECK OPTION — это необязательное условие, которое определяет уровень проверки, выполняемой при вставке или обновлении данных через представление. Если представление создается с использованием предложения WITH CHECK OPTION, каждая строка, которая вставляется или обновляется в базовой таблице через представление должен соответствовать определению представления. Обратите внимание, что этот параметр нельзя указать, если представление создано только для чтения.

Например, представление V_EMP_DEV создается для сотрудников, которые являются разработчиками (JOB_ID = DEV).

CREATE OR REPLACE VIEW v_emp_dev
AS
SELECT first_name, department_id, salary, 
FROM employees
WHERE job_id = 'DEV'
WITH CHECK OPTION empvu_dev;

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

UPDATE v_emp_dev
SET salary = salary+500
WHERE JOB_ID = 'HR';
ORA-01402: view WITH CHECK OPTION where-clause violation

Если бы это было простое представление, инструкция UPDATE не вызвала бы никакого исключения.

Отбрасывание зрения

Администратор базы данных (DBA) или владелец представления может отбросить представление с помощью оператора DROP VIEW. Если представление имеет определенные ограничения, то при отбрасывании представления необходимо указать предложение CASCADE CONSTRAINTS; в противном случае инструкция DROP VIEW не может быть обработана. Если другое представление или другой объект базы данных, такой как синоним или материализованное представление (оба эти объекта обсуждаются далее в этой главе), ссылается на отброшенное представление, Oracle не удаляет эти объекты базы данных; скорее Oracle помечает их как недействительные. Вы можете отбросить эти недопустимые объекты или переопределить их, чтобы сделать их снова действительными.

Приведенная ниже команда DROP VIEW удаляет представление EMP_VU из базы данных.

DROP VIEW EMP_VU;

Последовательности

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

Синтаксис

CREATE SEQUENCE <sequence name>
[INCREMENT BY < number >]
[START WITH < start value number>]
[MAXVALUE < MAXIMUM VLAUE NUMBER>]
[NOMAXVALUE]
[MINVALUE < minimum value number>]
[CYCLE | NOCYCLE]
[CACHE < number of sequence value to cache> | NOCACHE]
[ORDER | NOORDER];

Из синтаксиса

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

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

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

Предложение MAXVALUE указывает максимальное значение, до которого последовательность может быть увеличена. При отсутствии значения MAXVALUE максимально допустимое значение, которое может быть сгенерировано для последовательности, достаточно велико, от 10 до 27 степени — 1. Значение по умолчанию — NOMAXVALUE.

Предложение MINVALUE указывает минимальное значение последовательности для убывающей последовательности (то, которое генерирует числа в порядке убывания). Значением по умолчанию является NOMINVALUE.

Предложение CYCLE указывает, что значения последовательности могут использоваться повторно, если последовательность достигает указанного значения MAXVALUE. Если последовательность циклов, числа генерируются, начиная снова со значения START WITH.

Предложение CACHE может повысить производительность системы, позволяя Oracle генерировать указанный пакет последовательных чисел для хранения в кэш-памяти.

Если вы указываете CACHE без указания номера, размер кэша по умолчанию составляет 20 порядковых номеров. Опционально вы можете указать NOCACHE, чтобы предотвратить кэширование порядковых номеров.

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

НЕКВТАЛЛ и КУРРВАЛ

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

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

Синтаксис:

Sequence.NEXTVAL
Sequence.CURRVAL

Точки, которые следует отметить —

• CURRVAL и NEXTVAL могут использоваться только в Outer SQL оператора select.

• CURRVAL и NEXTVAL могут использоваться в операторе INSERT для замены первичного ключа столбца. Его можно использовать как в качестве предложения подзапроса, так и в предложении VALUES.

• CURRVAL и NEXTVAL могут использоваться для обновления значений в таблицах.

• CURRVAL и NEXTVAL не могут быть в списке выбора VIEW с ключевым словом DISTINCT, с предложениями GROUP BY, HAVING или ORDER BY и выражением DEFAULT в операторе CREATE TABLE или ALTER TABLE.

CURRVAL и NEXTVAL могут использоваться только в Outer SQL оператора select.

CURRVAL и NEXTVAL могут использоваться в операторе INSERT для замены первичного ключа столбца. Его можно использовать как в качестве предложения подзапроса, так и в предложении VALUES.

CURRVAL и NEXTVAL могут использоваться для обновления значений в таблицах.

CURRVAL и NEXTVAL не могут быть в списке выбора VIEW с ключевым словом DISTINCT, с предложениями GROUP BY, HAVING или ORDER BY и выражением DEFAULT в операторе CREATE TABLE или ALTER TABLE.

Изменение последовательности

Владелец последовательности может изменить последовательность, чтобы изменить только такие атрибуты, как INCREMENT BY value, MINVALUE, MAXVALUE, CYCLE или CACHE. Обратите внимание, что сделанные изменения будут отражены в следующих номерах.

Синтаксис:

ALTER SEQUENCE [sequence name]
INCREMENT BY n
MAXVALUE n
NOCACHE
NOCYCLE

Отбрасывание последовательности

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

DROP SEQUENCE [sequence name]

Индексы

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

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

Кроме того, пользователь может создавать индексы вручную для оптимизации производительности запроса. Созданные вручную индексы могут быть уникальными или неуникальными. Неуникальными могут быть индексы на основе B-Tree, Bitmap или Function. По умолчанию Oracle создает индексы B-Tree для столбцов . Вот синтаксис

Синтаксис

CREATE [UNIQUE][BITMAP]INDEX index
ON table (column [, column]...);

Обратите внимание, что UNIQUE и BITMAP должны быть указаны только для уникальных и растровых индексов. По умолчанию Oracle создает индексы B-Tree для обычных индексов.

Составной индекс (также называемый составным индексом) — это индекс, созданный для нескольких столбцов таблицы. Столбцы в составном индексе могут появляться в любом порядке и не обязательно должны быть смежными столбцами в таблице. Составные индексы повышают скорость извлечения строк для запросов, в которых предложение WHERE ссылается на все или ведущую часть столбцов в составном индексе. Индекс может содержать не более 32 столбцов.

Например, пользователь создает индекс IDX_EMP для столбца HIRE_DATE таблицы EMPLOYEES. Использование индекса сократит объем дискового ввода-вывода, пройдя сканирование по индексируемому пути и найдя данные, отфильтрованные по столбцу HIRE_DATE.

CREATE INDEX IDX_EMP ON employees(hire_date);

Отказ от индекса

Индексы не могут быть изменены, но могут быть изменены для анализа, перестроения или вычисления статистики. Если необходимо изменить определение индекса, его необходимо удалить и создать заново. Синтаксис команды DROP INDEX прост.

DROP INDEX index_name;

Синонимы

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

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

Синтаксис

CREATE [PUBLIC] SYNONYM [synonym name] 
FOR OBJECT;

Системный пользователь может предоставить привилегию на использование личных синонимов, которыми они владеют, для других системных пользователей. Для создания синонимов вам необходимо иметь привилегию CREATE SYNONYM. Кроме того, вы должны иметь привилегию CREATE PUBLIC SYNONYM для создания общедоступных синонимов. . Если синоним объявлен как общедоступный, имя синонима уже нельзя использовать в качестве общедоступного синонима. Попытка создать общедоступный синоним, который уже существует, приведет к сбою команды CREATE PUBLIC SYNONYM, и Oracle вернет ORA-00955: имя уже используется существующим сообщением об ошибке объекта.

иллюстрация

Рассмотрим двух пользователей U1 и U2.U1 имеет доступ к таблице EMPLOYEES. Таким образом, чтобы разрешить доступ к таблице EMPLOYEES к U2, в схеме U2 может быть создан синоним. Доступ должен быть предоставлен U1 к U2.

CONN U2/U2
SQL> CREATE SYNONYM EMP_SYN	FOR U1.employees;

CONN U1/U1
SQL> GRANT ALL ON EMP_SYN TO U2;

CONN U2/U2
SQL> SELECT * FROM EMP_SYN;

Удаление синонима

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