лабораторный практикум. Русакова М.С. Лабораторный практикум. Практикум по базам данных самара 2015 министерство образования и науки российской федерации
 Скачать 0.72 Mb.
|
|
Пример DROP SYNONYM my_lease / Вопросы Какая привилегия требуется пользователю для входа на сервер? Системная это привилегия или привилегия на объект? Что такое системная привилегия? Какие они бывают? Какая привилегия требуется пользователю для создания, удаления таблиц? Вы – администратор базы данных и регистрируете много пользователей, которым требуются одни и те же системные привилегии. Что вы можете сделать для облегчения своей работы? Как изменить свой пароль? Что значит опция WITH GRANT OPTION? Кто может передавать привилегии на ваши объекты другим пользователям? Задания Предоставьте другим пользователям право доступа к своей таблице tenant (разумеется, без права DELETE, подумайте сами, какие привилегии стоит предоставлять, а какие нет). Выберите все строки из чужой таблицы tenant. Добавьте туда 2 новые записи. Сделайте внесенные изменения постоянными. Напишите запрос для чужой таблицы tenant и выполните его. Создайте синоним чужой таблицы tenant. С его помощью выведите на экран данные из чужой таблицы. Просмотрите привилегии на таблицы, предоставленные вам. Отмените привилегию SELECT, которую вы предоставили другим. После этого попробуйте выполнить команду SELECT над чужой таблицей. Отмените все привилегии, которые вы предоставили другим пользователям, удалите синоним. Требования к сдаче лабораторной работы Продемонстрировать задания 1-3. Ответить на контрольные вопросы. 51 Лабораторная работа №7 Обработка данных. Управление транзакциями Совокупность команд языка манипулирования данными (DML), результаты действия которых еще не стали постоянными, называется транзакцией или логической единицей работы. Таблица 26. Команды обработки данных и управления транзакциями Команда Описание INSERT Добавляет новую строку в таблицу UPDATE Изменяет существующие строки таблицы DELETE Удаляет строки из таблицы COMMIT Делает все незафиксированные изменения постоянными SAVEPOINT Позволяет произвести откат до определенной точки сохранения ROLLBACK Отменяет все незафиксированные изменения данных Вставка строк в таблицу Добавить новую строку в таблицу можно при помощи команды INSERT. Синтаксис INSERT INTO таблица [(столбец [, столбец…])] VALUES (значение [, значение…]); Здесь: таблица имя таблицы; столбец имена столбцов таблицы, в которые вносятся значения; значение соответствующее значение столбцов. Примечание. Эта команда с предложением VALUES добавляет только одну строку. Таблица 27. Способы вставки неопределенных значений Метод Описание Неявный Опустить столбец в списке столбцов Явный Задать ключевое слово NULL в списке VALUES Задать пустую строку в списке VALUES (только для символьных строк и дат) Для вставки в таблицу специальных значений можно применять псевдостолбцы. Используйте USERID для ввода имени текущего пользователя и SYSDATE для ввода текущей даты и времени. Проверку успешной вставки значений можно осуществить при помощи команды SELECT. 52 Пример INSERT INTO Rent (Type, Rn) VALUES (‘коттедж’, NULL) / INSERT INTO owner (Non, Ow, USERID, AdO) VALUES (10, ‘Петрованов П.Г.’, USER, ‘Ставропольская, 35-43’) / INSERT INTO lease (NLease, NTn, Non, AdR, LDate) VALUES (5,4,7, ‘Ставропольская 11’, SYSDATE) / При вставке любого значения даты обычно используют формат DD-MON-YY. В этом формате стандартным значением столетия считается текущее столетие; времени – полночь. Если требуется задать другой век и конкретное время, применяется функция TO_DATE. Пример INSERT INTO lease (NLease, NTn, Non, AdR, LDate) VALUES (6,4,2, ‘Самарская 1’, TO_DATE (’01-JAN-96’,’DD-MON-YY’)) / Команда INSERT позволяет пользователю вводить значения в интерактивном режиме при помощи переменных подстановки SQL*Plus Пример INSERT INTO owner (Non, Ow, AdO) VALUES (&owner_number, ‘&owner_name’, ‘&owner_address’) / Примечание. Для дат и символьных значений амперсанд и имя переменной должны быть заключены в апострофы. Свою команду с переменными подстановки можно сохранить в файле для последующего использования. При каждом выполнении такой команды будет предложено ввести новые значения переменных. Команда ACCEPT запоминает введенное значение в переменной. Команда PROMPT позволяет выводить на экран текст, удобный для пользователя. 53 Пример ACCEPT owner_nmb PROMPT ‘Enter owner number:’ ACCEPT owner_name PROMPT ‘Enter owner name:’ ACCEPT owner_adr PROMPT ‘Enter owner address:’ INSERT INTO owner (Non, Ow, AdO) VALUES (&owner_nmb, ‘&owner_name’, ‘&owner_adr’) / Команду INSERT можно использовать для вставки в таблицу новых строк, которые копируются из уже существующих таблиц. Для этого вместо VALUES в строке помещается подзапрос. Обновление строк Существующие строки таблицы можно обновить командой UPDATE. Синтаксис UPDATE таблица SET столбец = значение [, столбец = значение] [WHERE условие]; Здесь: таблица имя таблицы; столбец имя обновляемого столбца таблицы; значение соответствующее значение или подзапрос для этого столбца; условие задает строки, которые необходимо изменить, и состоит из имен столбцов, выражений, констант, подзапросов и операторов сравнения. Пример UPDATE owner SET AdO = ‘Самарская 111-112’ WHERE Non = 3 / Если в команде UPDATE отсутствует предложение WHERE, изменяются все строки таблицы. Если при попытке обновления новое значение столбца противоречит ограничению, выдается сообщение об ошибке. 54 Удаление строк Существующие строки удаляются командой DELETE. Синтаксис: DELETE [FROM] таблица [WHERE условие]; Здесь: таблица имя таблицы; условие задает строки, которые необходимо удалить, и состоит из имен столбцов, выражений, констант, подзапросов и операторов сравнения. Если WHERE отсутствует, то удаляются все строки таблицы. Если вы пытаетесь удалить запись, на значение которой имеется ограничение, выдается сообщение об ошибке. Так, при попытке удалить строку с первичным ключом, который одновременно является внешним ключом в другой таблице, выдается сообщение об ошибке в связи с нарушением ограничения. Транзакции базы данных: содержат что-либо из следующего: o команды DML, выполняющие единое согласованное изменение данных; o одна команда DDL; o одна команда DCL; начинаются с выполнения первой исполняемой команды SQL; заканчиваются одним из следующих событий: o выполнение команды COMMIT или ROLLBACK; o выполнение команды DDL или DCL (автоматическая фиксация); o ошибка, завершение сеанса работы или аварийный останов системы. Обработка транзакций Транзакции обеспечивают большую гибкость, более широкий спектр средств управления при изменении данных в случае ошибки в пользовательском процессе или сбоя системы. Транзакции состоят из команд DML, которые вносят в данные одно согласованное изменение. Например, перевод средств между двумя счетами включает дебетование одного счета и кредитование другого на одну и ту же сумму. Успешно или неудачно выполненными должны быть одновременно признаны оба эти действия. Оставлять в базе данные о кредитовании без данных о дебетовании нельзя. Таблица 28. Типы транзакций Тип Описание Манипулирование данными (DML) Состоит из произвольного количества предложений DML, воспринимаемых сервером Oracle как одно целое или как логическая единица таблицы Определение данных (DDL) Состоит из одного предложения DDL Управление данными (DCL) Состоит из одного предложения DCL 55 Таблица 29. Команды управления транзакциями Команда Описание COMMIT Завершает текущую транзакцию, делая постоянными все произведенные изменения данных SAVEPOINT Устанавливает в текущей транзакции маркер точки сохранения (savepoint) ROLLBACK [TO SAVEPOINT имя] Прекращает текущую транзакцию, отменяя все произведенные изменения в данных Каждое изменение данных, выполненное в ходе транзакции, является временным до тех пор, пока транзакция не будет зафиксирована. Состояние данных, предшествующее выполнению команд COMMIT или ROLLBACK: операции по обработке данных изменяют только буфер базы данных. Следовательно, предыдущее состояние данных может быть восстановлено; текущий пользователь может просмотреть результаты своих операции по обработке данных, запрашивая данные из таблиц; другие пользователи не могут видеть результаты операций обработки данных, выполняемых текущим пользователем; изменяемые строки блокируются, и другие пользователи не имеют возможности менять их содержимое. Все внесенные изменения фиксируются с помощью COMMIT, в результате чего: измененные данные записываются в базу данных. Предшествующее состояние базы данных теряется; все пользователи могут видеть результаты выполненной операции; блокировка с измененных строк снимается, другие пользователи получают возможность вносить в них изменения; все точки сохранения удаляются. Пример INSERT INTO owner (Non, Ow, AdO) VALUES (15, ‘Федоров Ф.Ф.’, ‘Фадеева 45-54’) / UPDATE lease SET Non=54 WHERE NLease = 3 / COMMIT / Все незафиксированные изменения отменяются командой ROLLBACK, в результате чего: сделанные изменения теряются; разблокируются строки, с которыми производились операции. 56 Пример DELETE FROM rent / ROLLBACK / Команда SAVEPOINT позволяет создавать в текущей транзакции маркеры, разбивающие ее на единицы меньшего размера. После этого с помощью команды ROLLBACK TO SAVEPOINT можно отменять незафиксированные изменения до этого маркера. Примечание. Создание второй точки сохранения с тем же именем, что и первая, вызывает удаление первой. Пример UPDATE rent SET Rn = Rn*1.5 WHERE Type = ‘дом’ / SAVEPOINT Raising / INSERT INTO realty (AdR, Type) VALUES (‘Проспект Ленина, 13’,’дом’) / SELECT * FROM realty WHERE Type = ‘дом’ / ROLLBACK TO Raising / SELECT * FROM realty WHERE Type = ‘дом’ / Результаты работы запросов до и после отката будут различными. Если при выполнении команды обнаружена ошибка, то часть транзакции можно отменить при помощи неявного отката. Если в ходе транзакции возникла ошибка в одной-единственной команде DML, отменяется только результат этой команды, т.е. производится откат на уровне этой команды. Но изменения, внесенные во время этой транзакции предыдущими командами DML, сохраняются. Завершать транзакции рекомендуется явно. 57 Задания 1. Добавьте по одной новой записи в каждую таблицу. В таблице Rent для значения последнего типа недвижимости не устанавливайте пока плату. 2. Обновите данные таблиц Lease и Rent – в таблице Lease установите дату договора на 01.01.05 для всех договоров, срок начала которых раньше 01.01.05. Установите плату за каждый вид недвижимости на 50 % выше. 3. Убедитесь, что внесенные вами изменения сохранены в таблице. Сделайте эти изменения постоянными. 4. Создайте точку отката. Удалите все данные из таблицы Lease для арендатора с номером 4. Обновите данные таблицы Lease, потом сделайте откат. Убедитесь, что восстановлены исходные данные. Добавьте туда еще 2 строки, сделайте эти изменения постоянными. Требования к сдаче лабораторной работы 1. Продемонстрировать измененные данные. 2. Продемонстрировать умение обрабатывать данные. 58 Лабораторная работа №8 Создание последовательностей Многим приложениям требуются уникальные числа в качестве первичных ключей. Для генерации уникальных чисел можно включить необходимый код в само приложение или воспользоваться последовательностями. Последовательность: автоматически генерирует уникальные числа; является совместно используемым объектом; обычно применяется для получения значений первичного ключа; заменяет код в прикладной программе; ускоряет доступ к числам последовательности, если они находятся в сверхоперативной памяти (кэш-памяти). Числа последовательности хранятся и генерируются независимо от таблиц. Следовательно, одна и та же последовательность может быть применена одновременно к нескольким таблицам. Создание последовательности Последовательность для автоматической генерации чисел создается командой CREATE SEQUENCE. Синтаксис: CREATE SEQUENCE последовательность [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}] Здесь: последовательность имя генератора последовательности; INCREMENT BY n интервал между двумя последовательными номерами; n – целое. По умолчанию n=1; START WITH n первое генерируемое число в последовательности. По умолчанию это 1; MAXVALUE n максимальное значение, которое может генерировать последовательность; NOMAXVALUE максимальное значение по умолчанию, равное 1027; MINVALUE n минимальное значение; NOMINVALUE задает минимальное значение, равное 1; 59 CYCLE | NOCYCLE продолжается ли циклическая генерация чисел после достижения максимального или минимального значения. По умолчанию – NOCYCLE; CACHE | NOCACHE количество чисел, которое сервер Oracle распределяет предварительно и хранит в памяти. По умолчанию сервер хранит в кэш-памяти 20 значений. Пример CREATE SEQUENCE N_OW_ID INCREMENT BY 1 START WITH 10 MAXVALUE 1000 NOCACHE NOCYCLE / Созданная нами последовательность документируется в словаре данных. Поскольку последовательность является объектом базы данных, информацию о ней можно найти в таблице словаря данных USER_OBJECTS. Проверить параметры последовательности можно также путем выборки данных из таблицы словаря данных USER_SEQUENCES. Пример SELECT sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences / Числа созданной последовательности можно использовать в качестве порядковых номеров для таблиц. Обращение к числам последовательности производится с помощью псевдостолбцов NEXTVAL и CURVAL. Псевдостолбцы NEXTVAL и CURVAL Псевдостолбец NEXTVAL используется для выборки следующего свободного числа последовательности. Имя NEXTVAL необходимо дополнить именем последовательности. Если вы ссылаетесь на последовательность.NEXTVAL, генерируется следующее число, и текущее число помещается в CURVAL. Псевдостолбец CURVAL используется для ссылки на число, только что сгенерированное текущим пользователем. Прежде, чем обращаться к CURVAL, необходимо использовать NEXTVAL для генерации числа в текущем сеансе пользователя. Имя CURVAL необходимо дополнить именем последовательности. При ссылке на последовательность.CURVAL выдается последнее значение, возвращенное процессу этого пользователя. 60 Команды и предложения, где используются NEXTVAL и CURVAL: список SELECT команды SELECT, которая не является частью подзапроса; список SELECT подзапроса в команде INSERT; предложение VALUES команды INSERT; предложение SET команды UPDATE. Команды и предложения, где НЕ используются NEXTVAL и CURVAL: список SELECT представления (view); команда SELECT с ключевым словом DISTINCT; команда SELECT с предложениями GROUP BY, HAVING, ORDER BY; подзапрос в команде SELECT, UPDATE или DELETE; предложение DEFAULT команды CREATE TABLE или ALTER TABLE. Кэширование последовательностей в памяти ускоряет доступ к их значениям. Кэш-память заполняется при первой ссылке на последовательность. В ответ на каждый последующий запрос выдается одно из чисел, находящихся в кэш-памяти. Если последнее из этих значений использовано, запрос следующего значения приводит к записи в память очередной партии значений. Хотя генераторы последовательностей выдают числа без пропусков, это действие выполняется независимо от операций COMMIT и ROLLBACK. Следовательно, при откате команды, содержащей ссылку на последовательность, это число теряется. Еще одна возможная причина пропусков в последовательности – это сбой системы. Значения последовательности, находящиеся в кэш-памяти, при сбоях теряются. Поскольку последовательности не связаны прямо с таблицами, одна и та же последовательность может использоваться в нескольких таблицах. В этом случае пропуски в последовательности чисел могут быть в каждой таблице. Увидеть следующее свободное значение последовательности, не увеличив его, можно только в случае, если последовательность создана с параметром NOCACHE. Для этого выполняется запрос к таблице USER_SEQUENCES. Изменение параметров последовательности Если последовательность достигла верхнего предела (MAXVALUE), дополнительные значения не предоставляются, и возникнет ошибка. Чтобы продолжать пользоваться последовательностью, можно изменить ее параметры с помощью команды ALTER SEQUENCE. Синтаксис: ALTER SEQUENCE последовательность [INCREMENT BY n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}] 61 Указания Для изменения параметров необходимо быть владельцем последовательности или иметь для нее привилегию ALTER. Команда ALTER SEQUENCE влияет только на числа, генерируемые после введения изменения. Выполняются некоторые проверки. Например, новое значение MAXVALUE не может быть меньше текущего числа в последовательности. Изменить параметр START WITH командой ALTER SEQUENCE нельзя. Чтобы начать последовательность с другого числа, необходимо ее удалить (drop) и создать заново. Для удаления последовательности из словаря данных используется команда DROP SEQUENCE. Чтобы удалить последовательность, необходимо быть ее владельцем или иметь привилегию DROP ANY SEQUENCE. |