Skip to content

MD&SUBD Questions

Jump to bottom
Sᴛѧʀʟɪɴɢ edited this page Jan 3, 2019 · 4 revisions

8.

Индексы – это объекты базы данных, созданные для ускорения поиска данных в определенной таблице.

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

Создание индекса:

CREATE [UNIQUE] INDEX имя_индекса ON имя_таблицы

13.

Представление – это сохраненный запрос, который выдает пользователю БД требуемый набор данных из одной или нескольких таблиц.

Представления скрывают (основная функция представлений):

  • поля
  • строки
  • сложные операции с БД

Создание представления:

CREATE [OR REPLACE] VIEW имя_представления AS
SQL-запрос;

14. Типы данных. Ссылочные типы. Создание пользовательских типов. Команды присваивания, ветвления, циклов. Использование команд SQL в программах на PL/SQL. Особенности использования команды SELECT.

Переменные в языке PL/SQL:

  • объявляются в разделе объявлений явным и неявным образами
  • используются в исполняемом разделе
  • могут быть переданы в качестве параметров PL/SQL подпрограмме
  • могут использоваться для хранения выходных данных PL/SQL подпрограммы

Явное объявление переменной:

DECLARE имя_переменной [CONSTANT] тип [NOT NULL] [{:= | DEFAULT} значение];

Каждая переменная объявляется отдельно.
При использовании ключевого слова CONSTANT переменной должно быть присвоено значение, которое не может быть изменено. Переменная инициализируется значением своего типа либо с помощью оператора присваивания, либо с помощью ключевого слова DEFAULT. Если указано ключевое слово NOT NULL, то переменную необходимо проинициализировать, и она не может принимать значение NULL.

При объявлении переменной должен быть указан тип этой переменной.

Основные типы данных языка PL/SQL:

  • скалярные (символьные, числовые, даты, логические)
  • составные типы данных (TABLE, RECORD, NESTED TABLE, VARRAY)

Символьные типы данных:

  • CHAR [(максимальная_длина)] - основной тип для хранения последовательности символов фиксированной длины не более 32 767 байт. Если максимальная длина не задана, то по умолчанию она равна 1.
  • VARCHAR2 (максимальная_длина) - основной тип для хранения последовательности символов переменной длины не более 32 767 байт. Для переменных и констант типа VARCHAR2 размера по умолчанию не существует.
  • LONG используется для хранения последовательности символов переменной длины (разновидность типа данных VARCHAR2 с большим ограничением значения длины).

Числовые типы данных:

  • NUMBER [(точность, масштаб)] используется для хранения чисел с фиксированной и плавающей точкой в диапазоне от 1Е-130 до 10Е125. По умолчанию параметр длина равен 1.
  • BINARY_INTEGER используется для хранения целых знаковых чисел в двоичном виде. Диапазон этого типа от -2147483647 до 2147483647.
  • PLS_INTEGER используется для хранения целых знаковых чисел в двоичном виде. Диапазон этого типа от -2147483647 до 2147483647. Значение PLS_INTEGER требуют меньше памяти. Арифметические операции со значениями типа PLS_INTEGER быстрее, чем над данными NUMBER.

Даты:

  • DATE - основной тип для дат и времени. Значение DATE включают время в секундах с полуночи. Диапазон дат: 4712 г. до.н.э – 9999 н.э.
  • TIMESTAMP [(точность)] расширяет тип данных DATE и хранит год, месяц, день, час, минуту, секунду и доли секунды. Для задания точности необходимо указывать целое литеральное значение в диапазоне от 0 до 9. Это число цифр в дробной части поля секунд. По умолчанию – 6.
  • TIMESTAMP [(точность)] WITH TIME ZONE расширяет тип данных TIMESTAMP и содержит смещение временной зоны. Смещение временной зоны представляет собой разницу в часах и минутах между местным временем и универсальным глобальным временем (UTC). Для задания точности необходимо указывать целое литеральное значение в диапазоне от 0 до 9. Это число цифр в дробной части поля секунд. По умолчанию – 6.
  • TIMESTAMP [(точность)] WITH LOCAL TIME ZONE расширяет тип данных TIMESTAMP и содержит смещение временной зоны. Смещение временной зоны представляет собой разницу в часах и минута между местным временем и универсальным глобальным временем UTC. В отличие от TIMESTAMP WITH TIME ZONE вставляемые в столбцы БД значения данного типа нормализуются в соответствии с временной зоной базы. Кроме того, смещение временной зоны не хранится в столбце. При извлечении значения из базы данных Oracle преобразует его к временной зоне сеанса.
  • INTERVAL YEAR [(точность)] TO MONTH используется для хранения и манипулирования интервалами, содержащими годы и месяцы. Точность задает число цифр в поле лет. Нельзя использовать символические константы или переменные для задания точности; Необходимо указать целое литеральное значение в диапазоне от 0 до 4. По умолчанию – 2.
  • INTERVAL DAY [(точность1)] TO SECOND [(точность2)] используется для хранения и манипулирования интервалами, содержащими дни, часы, минуты и секунды. Точность 1 и Точность 2 определяют соответственно число цифр в поле дней и точность в поле секунд. В обоих случаях нельзя использовать символические константы или переменные для задания точности; Необходимо указать целое литеральное значение в диапазоне от 0 до 9. Значения по умолчанию соответственно -2 и 6.

Логический тип данных:

  • BOOLEAN - основной тип для хранения значений, используемых в логических вычислениях: True (истинно), False (ложно) или NULL (не определено).

Неявное объявление переменной скалярного типа осуществляется с помощью специального атрибута %TYPE, который позволяет объявить переменную, тип которой соответствует либо типу другой переменной, либо типу столбца таблицы БД.
Синтаксис неявного объявления переменной:

DECLARE 
  имя_переменной_1 имя_таблицы.имя_столбца%TYPE; 
  имя_переменной_2 имя_ранее_объявленной_переменной%TYPE;

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

DECLARE TYPE тип_записи IS RECORD 
(поле1 тип1 [NOT NULL] [{:= ⎪ DEFAULT } значение1], 
поле2 тип2 [NOT NULL] [{:= ⎪ DEFAULT } значение2], ...); 

DECLARE TYPE NEW_TYPE IS RECORD (NAME VARCHAR2(30));

Допускается** неявное определение переменных типа записи**, выполняемое с помощью атрибута %ROWTYPE, что позволяет определять переменные-записи, структура которых идентична структуре записи указанной таблицы или структуре ранее определенной переменной-записи.

DECLARE NEW_RECORD OLD_RECORD%ROWTYPE;

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

Для того, чтобы сделать внутреннюю переменную видимой, необходимо указать метку <<outher>> для внешнего блока.

15. Курсоры, команды для работы с курсорами. Атрибуты курсоров. Неявные курсоры и их атрибуты.

Для выполнения оператора SELECT система выделяет специальную область, куда помещает выбранные строки и некоторую служебную информацию. Курсор - специальная конструкция в PL/SQL, которая позволяет задать имя этой области и осуществить доступ к хранящейся там информации.

Типы курсоров:

  • Явные курсоры объявляются программистом для запросов SELECT, которые возвращают более одной строки.
  • Неявные курсоры определяются автоматически для SQL DML-операторов (INSERT, UPDATE, DELETE, MERGE) и для оператора SELECT, который возвращает только одну строку.

Объявление курсора:

DECLARE CURSOR имя_курсора IS SELECT ...;

Управление явным курсором:

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

Явное открытие курсора:

OPEN имя_курсора [(список_параметров)];

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

Явный выбор строк курсора:

FETCH имя_курсора INTO {имя_записи ⎪ список_столбцов};

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

Явное закрытие курсора:

CLOSE имя_курсора;

После закрытия курсора все попытки считывания информации приведут к ошибке.

Курсорная переменная-запись дает возможность объявить одну переменную для всех выбираемых курсором строк. Такое объявление является неявным и выполняется с помощью атрибута %ROWTYPE:

DECLARE 
  var1 NUMBER(8); 
  var2 имя_курсора%ROWTYPE;

Курсорные атрибуты - функции, возвращающие определенное значение в зависимости от выполненных действий:

  • %ISOPEN - возвращает значение TRUE, если курсор открыт, и FALSE, если курсор закрыт
  • %NOTFOUND - возвращает значение TRUE, если строка не найдена, и FALSE, если строка найдена
  • %FOUND - возвращает значение TRUE, если строка найдена, и FALSE, если строка не найдена
  • %ROWCOUNT - возвращает числовое значение, равное количеству выбранных строк в курсоре

Использование курсорного атрибута:

IF NOT имя_курсора%ISOPEN THEN OPEN имя_курсора;

Для автоматической обработки курсора используются циклы FOR с курсором. В этом случае открытие, выборка и закрытие происходят автоматически:

-- имя_записи - имя НЕЯВНО объявленной курсорной переменной-записи
FOR имя_записи IN имя_курсора 
LOOP 
 . . . 
END LOOP;

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

DECLARE CURSOR имя_курсора (список_параметров) IS SELECT…;

Параметры используются в определяющем курсор запросе SELECT. Схема параметра: имя_параметра тип_параметра = значение по-умолчанию.

Неявные курсоры определяются автоматически для SQL DML-операторов (INSERT, UPDATE, DELETE, MERGE) и для оператора SELECT, который возвращает только одну строку.

Инструкция SELECT … INTO считывает одну строку данных и присваивает ее в качестве значения локальной переменной программы:

DECLARE v_salary employees.salary%TYPE; 
BEGIN 
  SELECT AVG(salary) INTO v_salary 
  FROM employees; 
  DBMS_OUTPUT.PUT_LINE('Average Salary is : '||v_salary); 
END;

Недопустимо использование неявного курсора для запроса SELECT, возвращающего более одной строки.

Неявный курсор называется SQL-курсором.
Он имеет свои атрибуты, аналогичные атрибутам явного курсора:

  • SQL%FOUND
  • SQL%NOTFOUND
  • SQL%ROWCOUNT

Однако в однострочном операторе SELECT нельзя использовать атрибут SQL%NOTFOUND, потому что, если при его выполнении информация из таблицы не будет выбрана, то сгенерируется исключение NO_DATA_FOUND. Атрибут SQL%NOTFOUND обычно используется в операторах UPDATE и DELETE для проверки, успешно или нет, выполнены соответствующие операторы.

16.

Процедуры и функции (подпрограммы) - оформленные специальным образом именованные блоки PL/SQL, которые могут быть вызваны для выполнения и которым могут быть переданы параметры.

Процедуры и функции:

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

18.

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

Обработчик исключительной ситуации – действия, выполняемые при возникновении ошибки. Раздел обработки исключительных ситуаций в PL/SQL блоке называется EXCEPTION.

Инициировать исключение – значит остановить выполнение текущего блока PL/SQL путем уведомления ядра об ошибке. Когда происходит ошибка, в PL/SQL инициируется исключение.

19. Триггеры, виды триггеров

Триггер базы данных – это оформленный специальным образом именованный блок PL/SQL, который хранится в базе данных и запускается автоматически.
Каждый триггер связан с некоторым системным событием или с событием над объектами БД (таблицей/представлением).

Триггер автоматически запускается при выполнении:

  • DML-операторов (INSERT, UPDATE, DELETE) над таблицей;
  • DML-операторов (INSERT, UPDATE, DELETE) над представлением (INSTEAD OF триггер);
  • DDL-операторов (CREATE, ALTER, DROP и др.);
  • системных событий БД (например, подключение пользователя к БД).

Триггеры могут быть использованы:

  • для реализации сложных ограничений целостности данных, которые не могут быть осуществлены стандартным образом при создании таблицы;
  • предотвращения неверных транзакций;
  • выполнения процедур комплексной проверки прав доступа и секретности данных;
  • генерации некоторых выражений на основе значений, имеющихся в столбцах таблиц;
  • реализации сложных бизнес-правил для обработки данных (возможность отследить «эхо», т.е. возможность при изменении одной таблицы, обновлять данные связанных с ней таблиц).

Пример триггера, записывающего информацию об авторизированных пользователях:

CREATE TABLE USER_LOGS (ID VARCHAR2(30), LOGON_DATE DATE);

CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE 
BEGIN 
  INSERT INTO USER_LOGS (ID, LOGON_DATE) VALUES (USER, SYSDATE); 
END;

DML-триггер – триггер, который автоматически запускается при выполнении активизирующего его DML-оператора (INSERT, UPDATE, DELETE).

Типы DML-триггеров по:

  • моменту запуска: BEFORE, AFTER или INSTEAD OF
  • характеру воздействия на строки таблицы: строковый, операторный
  • типу активизирующего DML-оператора: INSERT, DELETE, UPDATE

Схема создания DML-триггера:

CREATE  [OR  REPLACE]  TRIGGER  имя_триггера
{BEFORE  |  AFTER | INSTEAD OF }
{INSERT  |  DELETE  |  UPDATE  [OF  список_столбцов]}  
ON  имя_таблицы   [FOR  EACH  ROW]   [WHEN  условие]
< PL/SQL_блок >

При наличии OR REPLACE триггер создается заново, если он уже существует.
BEFORE означает, что триггер будет запускаться перед выполнением активизирующего DML-оператора.
AFTER означает, что триггер будет запускаться после выполнения активизирующего DML-оператора.
INSTEAD OF служит для замещения DML-оператора своим функционалом над представлением (view). INSERT | DELETE | UPDATE [OF список_столбцов] указывает тип активизирующего триггер DML-оператора.
Разрешается, используя логическую операцию OR, задать совокупность активизирующих операторов: INSERT OR DELETE.
Если при использовании UPDATE указан список столбцов, то триггер будет запускаться при модификации одного из указанных столбцов, иначе триггер будет запускаться при изменении любого из столбцов.
FOR EACH ROW присутствует - триггер является строковым, иначе - операторным.
Операторный триггер запускается один раз до или после выполнения активизирующего триггер DML-оператора независимо от того, сколько строк в связанной с триггером таблице подвергается модификации.
Строковый триггер запускается один раз для каждой из строк, которая подвергается модификации DML-оператором, активизирующим триггер.
С помощью WHEN можно задать дополнительное ограничение на строки связанной с триггером таблицы, при модификации которых может быть запущен триггер. Конструкция PL/SQL_блок представляет блок PL/SQL, который ORACLE запускает при активизации триггера.

Триггерные предикаты - логические функции, возвращающие TRUE, если тип активизирующего оператора совпадает с типом предиката, и FALSE – в противном случае. Они используются в том случае, если в триггере указана совокупность активизирующих DML-операторов с помощью OR, и для каждого из них нужно выполнить своё действие.

  • INSERTING
  • DELETING
  • UPDATING

Использование триггерных предикатов:

IF имя_предиката THEN ...

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

Использование псевдозаписей:

IF (:OLD.NAME = :NEW.NAME) THEN ...

Предложение REFERENCING позволяет назначить псевдонимы псевдозаписям OLD и NEW:

REFERENCING OLD AS название_1 NEW AS название_2

В триггерах недопустимо использование операторов COMMIT, SAVEPOINT, ROLLBACK.

Триггеры INSTEAD OF часто используются при работе со сложными представлениями, над которыми недопустимо выполнение DML-операций (INSERT, UPDATE, DELETE).

DDL-триггеры запускаются автоматически при выполнении DDL-операторов CREATE, ALTER, DROP и др.

Классификация DDL-триггеров:

  • ON DATABASE: запускается при выполнении DDL-операторов над всеми схемами базы данных
  • ON SCHEMA: запускается при выполнении DDL-операторов над объектами схемы пользователя

Триггеры событий БД запускаются автоматически, когда происходит некоторое системное событие.
Например, при открытии (STARTUP)/закрытии (SHUTDOWN) базы данных, подключении/отключении пользователя (LOGON/LOGOFF) к базе данных.

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

  • USER_OBJECTS,
  • USER_TRIGGERS,
  • USER_ERRORS.

Управление триггерами:

--временное отключение триггера
ALTER  TRIGGER  имя_триггера  DISABLE; 
--включение триггера
ALTER  TRIGGER  имя_триггера  ENABLE;
--включение/выключение всех триггеров для таблицы:
ALTER  TABLE  имя_таблицы  {DISABLE | ENABLE}  ALL  TRIGGERS;
--удаление триггера
DROP  TRIGGER  имя_триггера;
Clone this wiki locally