Язык управление транзакциями (TCL - Transaction Control Language)

Транзакцией в SQL называется логически неделимая последовательность операторов, рассматриваемая как единое целое. Результаты выполнения операторов, входящих в транзакцию, могут быть либо сохранены в БД при помощи оператора COMMIT, либо полностью аннулированы оператором ROLLBACK до точки сохранения. Транзакция начинается с 1-го выполняемого оператора, либо с 1-го оператора после COMMIT или ROLLBACK. Транзакция заканчивается при выполнении операторов COMMIT или ROLLBACK.

Операторы управления транзакциями:

COMMIT - фиксация транзакции

ROLLBACK – откат транзакции

SAVEPOINT – установки точки отката транзакции

 

Рассмотрим пример фиксации транзакции командой COMMIT. В уазанном примере мы производим удаление строки из таблицы «s_fiz_lic3» с кодом 5. После чего производим фиксацию выполненной операции.

 

DELETE FROM s_fiz_lic3 WHERE kod=5; COMMIT;

 

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

 

DELETE FROM s_fiz_lic3 WHERE kod=4; ROLLBACK;

 

Теперь рассмотрим более сложный пример управления транзакциями с применением точек отката транзации. При выполнении группы операций можно проименовать эти группы операций установив точки отката транзакций с помощью команды SAVEPOINT. Точки отката транзакций предназначены, для того чтобы, имелась возможность откатить транзакцию до определенного момента в группе операций. Рассмотрим нижеприведенный пример. В данном примере удаляем строку с кодом 5, затем устанавливаем точку отката транзакции с именем example, затем производим удаление строки с кодом 4 и производим откат до точки отката example, в результате операция удаления строки с кодом 4 будет аннулирована, затем мы производим фиксацию транзакции. В результате выполнения команд будет выполнено только одно удаление строки с кодом 5.

 

DELETE FROM s_fiz_lic3 WHERE kod=1; SAVEPOINT example; DELETE FROM s_fiz_lic3 WHERE kod=4; ROLLBACK WORK TO example; COMMIT;

 

Язык управления доступа к данным (DCL - Data Control Language)

Операторы Data Control Language, иногда называемые операторами Access Control Language, применяются для осуществления административных функций, присваивающих или отменяющих право (привилегию) использовать базу данных, таблицы в базе данных, а также выполнять те или иные операторы SQL над объектами БД.

Операторы управления транзакциями:

GRANT – назначение права пользователю на объект

REVOKE – отзыв права (привилегииь, роли) у пользователя на объект

C помощью этих операторов производится назначение прав пользователю на выполнение операций над объектом. Каждый объект имеет определенный перечень прав. Нижеприведенный общий перчень прав, которые могут быть назначены пользователю: SELECT, ALTER, INDEX, DELETE, UPDATE, EXECUTE, DEBUG, REFERENCES, READ, WRITE. Надо отметить, что кроме указанных прав (привилегий), пользователю могут быть назначены роли (ROLE), роли – представляют собой совокупность привилегий. Роли могут как системными, так и определенные пользователем – пользовательские.

В нижеприведенном примере мы производим наделение правами пользователя SYSTEM на осуществление операций выборки, изменения таблицы и удаления данных из нее, а следующий командой мы производим отзыв права удаления из таблицы «s_fiz_lic».

 

GRANT SELECT, ALTER, DELETE ON s_fiz_lic TO system; REVOKE DELETE ON s_fiz_lic FROM system;

 

В данном примере служебным словом ALL мы производим наделение всеми правами пользователя SYSTEM на осуществление операций над табдицей «s_fiz_lic»

 

GRANT ALL ON s_fiz_lic TO system;

Лабораторная работа №3. Основы процедурного языка PL\SQL

 

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

Задание: изучить основны языка PL/SQL; выполнить PL/SQL блоки контрольных примеров; выполнить в iSQL Plus задания преподавателя.

 

Порядок выполнения работы

PL/SQL - это развитый язык программирования, используемый для доступа к базам данных Oracle из различных сред. PL/SQL интегрирован с сервером базы данных, поэтому программы PL/SQL обрабатываются быстро и эффективно. Этот язык доступен и в некоторых клиентских инструментальных средствах Oracle.

У каждого типа языка программирования есть свои достоинства и недостатки. Языки четвертого поколения, подобные SQL, как правило, проще (по сравнению с языками третьего поколения) и содержат меньшее число команд. Кроме того, они изолируют пользователя от базовых структур данных и алгоритмов, реализуемых исполняющей системой. Однако в некоторых случаях процедурные конструкции языков 3GL полезны для более точного описания программы. Именно для этого применяется PL/SQL, который объединяет мощь и гибкость SQL (языка 4GL) и процедурные конструкции языка 3GL.

PL/SQL означает Procedural Language/SQL (процедурный язык/SQL). Как видно из названия, PL/SQL расширяет возможности SQL, добавляя в него такие конструкции процедурных языков, как:

- переменные и типы данных (как предварительно определенные, так и определяемые пользователями)

- управляющие структуры, такие как условные операторы и циклы

- процедуры и функции

- объектные типы и методы

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

 

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

Блок PL/SQL

Базовой единицей любой программы, написанной на PL/SQL, является блок. Из блоков состоят все программы PL/SQL, причем блоки могут идти один за другим (последовательно) либо вкладываться один в другой. Существуют два различных типа блоков: анонимные блоки и именованные. Анонимные блоки обычно создаются динамически и выполняются только один раз. Этот тип блоков, как правило, создается в клиентской программе для вызова подпрограммы, хранящейся в базе данных. Именованные блоки отличаются тем, что они имеют имя. Именованные блоки могут быть разбиты на категории следующим образом:

• Помеченные блоки (labeled block) являются анонимными блоками с меткой, которая дает блоку имя. Они создаются обычно динамически и выполняются только один раз. Помеченные блоки используются так же, как и анонимные блоки, но метка позволяет ссылаться на переменные, которые иначе были бы недоступны.

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

• Триггеры (triggers) — это именованные блоки, которые ассоциируются с некоторым событием, происходящим в базе данных. Они, как правило, не изменяются после своего создания и выполняются многократно неявным образом при наступлении соответствующих событий. Событием, активизирующим триггер, может быть выполнение оператора языка манипулирования данными (DML, data manipulation language) над некоторой таблицей базы данных. К операторам DML относятся INSERT (ввести), UPDATE (обновить) и DELETE (удалить). Это может также быть оператор языка определения данных (DDL, data definition language), такой как CREATE или DROP, или событие базы данных.

Создадим таблицу для выполнения примеров:

CREATE TABLE temp_table ( num_col NUMBER, char_col VARCHAR2(250));

 

Пример анонимного блока:

 

DECLARE /* Объявляем переменные, используемые в этом блоке. */ v_Num1 NUMBER:= 1; v_String1 VARCHAR2(50):= 'Hello World!'; BEGIN /* Используя значения переменных, введем строку в таблицу temp_table. */ INSERT INTO temp_table (num_col, char_col) VALUES (v_Num1, v_String1); END; /

 

Пример именованного блока:

 

CREATE OR REPLACE PROCEDURE InsertlntoTemp AS /* Объявляем переменные, используемые в этом блоке. */ v_Num1 NUMBER:= 1; v_String1 VARCHAR2(50):= 'Hello World!'; BEGIN /* Используя значения переменных, введем строку в таблицу temp_table. */ INSERT INTO temp_table (num_col, char_col) VALUES (v_Num1, v_String1); END InsertlntoTemp; /

 

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

 

BEGIN InsertlntoTemp; END; /