Структура SQL.

История развития и стандарты SQL.

Сегодня для большинства СУБД языком манипулирования данными явл. SQL. Его появление и развитие как средства описания доступа к БД связано с созданием теории РБД. Прообраз языка SQL возник в 1970 году в рамках научно-исследовательского проекта IBM, наз. «System/R». Сегодня SQL — стандарт интерфейса с реляционными СУБД.

1-й международный стандарт языка SQL был принят в 1989 г. (SQL/89, SQL1), в 1992 г. — 2-й стандарт (SQL/92, SQL2). В 1999 г. появился стандарт SQL3, в кот. были введены новые типы данных. Также предостав. возможность задания сложных структурир. типов данных, что более соотв. объектной ориентации. Впервые появились стандарты на события и триггеры.

Запрос SQL состоит из 1 или больше операторов, идущих друг за другом и разделенных т-кой с запятой.

Каждый столбец в любой таблице хранит данные определенных типов. Различают базовые типы данных:

· строки символов фиксированной длины;

· целые и вещественные числа;

· доп. типы данных: строки переменной длины, денеж. единицы, дату и время, логич. данные.

Структура SQL.

Язык SQL делится на подъязыки (подмножества):

1) Язык определения данных (Data Definition Language, DDL) предостав. пользователям средства указания типа данных и их структуры, средства задания ограничений для инфы, хранимой в БД.

Операторы: Create, Alter, Drop.

2) Язык манипулирования данными (Data Manipulation Language, DML) позволяет вставлять, обновлять и извлекать информацию из БД.

Операторы: Select, Insert, Delete, Update.

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

Операторы: Grant, Revoke.

4) Язык управления транзакциями (Transaction Control Language, TCL) состоит из операторов, предназнач. для управ. ходом выполнения транзакций.

Операторы: Commit, Rollback, Savepoint.

Запросы на получение данных из таблиц (1+) выполняются с помощью оператора Select. Синтаксис:

Select [Distinct] <список_полей> | *

from <список_таблиц>

[Where <условие_фильтрации_строк>]

[Group by <условия_группировки_строк>]

[Having <условие_фильтрации_групп>]

[Order by <условие_сортировки>];

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

Select * from <имя_таблицы>; — выборка всей инфы из таблицы. Символ * заменяет необх. указ. всех полей.

Select <поле1>[, <поле2>, …] from <имя_таблицы>; — вертикальная фильтрация с указанием порядка вывода атрибутов. В результат попадут данные только из указ. полей, причем в том же порядке, в каком указаны поля в операторе Select.

Для исключения дубликатов из результата исп. параметр distinct, кот. указ. сразу после Select.

#: Select distinct Prod_ID from Outgoing; — вывод номеров хотя бы раз проданных товаров.

Для сортировки результата исп. параметр Order by.

Select * from Dealers Order by Name;

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

· операторы сравнения: =, <, >, <=, >=, <>;

· булевы операторы: and, or, not;

· оператор проверки на вхожд. в множество: in;

· оператор проверки на вхожд. в диапазон: Between;

· оператор проверки на существование: Exists;

· оператор проверки удовлетворению шаблону (для символьных полей): Like;

· операторы сравн. с Null: is Null, is not Null;

· агрегатные ф-ии: Count, Sum, Avg, Max, Min;

· константы и выражения;

· подзапросы.

Count — возвращает количество записей в группе. Возможно 3 варианта использования функции:

Count(*) — подсчет количества записей;

Count(<поле>) — подсчет кол-ва знач. (отличных от Null) в указанном поле;

Count(distinct <поле>) — подсчет кол-ва уник. знач. (отличных от Null) в указанном поле.

Любая новая инфа попадает в БД с помощью оператора Insert. Cинтаксис:

Insert into <имя_таблицы>[(<атрибуты>)]

values (<значения>);

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

Если при добавлении строки атрибут не был указан, то СУБД попытается присвоить ему значение по умолчанию (если было указано при создании таблицы). Если значения по умолчанию нет, СУБД попытается указать для данного атрибута значение Null (если это не противоречит ограничениям целостности). Иначе оператор Insert будет завершен с ошибкой.

Insert into Dealers values(1, ‘Иванов А.И.’, 3, null); — вставка строки с указанием всех атрибутов.

Вставка множества записей осуществляется с помощью подзапроса внутри оператора Insert:

Insert into <имя_таблицы> Select;

Оператор Update используется для изменения существующих строк таблиц. Синтаксис:

Update <имя_таблицы> Set <поле1>=<значение1>,[<поле2>=<значение2>,…]

[Where <условие_фильтрации_строк>];

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

Update Managers Set Percent=5 Where Man_ID=1; — изм. знач. поля в 1 строке таблицы.

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

Update Managers Set Percent=Percent*0.9

Where Man_ID Not in

(Select distinct Man_ID from Outgoing

Where Out_Date>=sysdate-7); — понижение комиссионных на 10% у менеджеров, кот. не продали ни одного товара за последнюю неделю.

Оператор удаления строк Delete.

Delete from <имя_таблицы>

[Where <условие_фильтрации_строк>];

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

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

Delete from Managers Where Man_ID=1; — удаление из Managers инфы о менеджере с номером 1.

Подзапросы исп. аналогично оператору Update.

7. Подмножество языка DDL: операторы CREATE, ALTER, DROP. Представления, их значение; обновляемые представления.

Оператор Create служит для создания любого типа объектов, из которых состоит БД, в том числе таблиц.

Create Table <имя_таблицы>(

<поле1> <тип1> [<ограничения>],

[<поле2> <тип2> [<ограничения>],…]);

Возможные ограничения в таблицах:

· Not Null — знач. атрибута не мб неопред.;

· Unique — уникальный ключ; знач. полей уник.;

· Primary Key — первичный ключ; поле явл. ПК;

· Check — домен; условие, которому должны удовлетворять значения атрибута;

· Default — присвоение значения «по умолчанию».

При создании таблицы можно сразу задать и ограничения декларативной и ссылочной целостности (если они касаются только одного атрибута). Если ключ должен быть составным, для его создания нужно применять команду Alter Table.

Create Table Dealers1(

D_ID Number,

Name Varchar2(30),

Percent Number (4,2),

Comments Varchar2(50) Default 'no comments');

Оператор Alter служит для изменения структуры любых объектов, из которых состоит БД.

Alter Table <имя_таблицы> Add <поле> <тип> [ограничения]; — добавление атрибута в таблицу.

Alter Table <имя_таблицы> Drop Column <поле>; — удаление атрибута таблицы.

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

Drop <тип_объекта> <имя_объекта>;

Таблица успешно удалится только в том случае, если на нее не ссылаются внешние ключи других таблиц. Если внешние ключи существуют, но нужно удалить таблицу, можно удалить все внешние ключи, ссылающиеся на удаляемую таблицу, после чего удалить ее. То же самое можно сделать с помощью параметра cascade constraints:

Drop Table Dealers cascade constraints;

Если внешние ключи, ссылающиеся на удаляемую таблицу, существуют, они будут удалены без изм. в полях внешних ключей подчиненных таблиц.

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

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

Create View Londonstaff as

Select * from Salespeople

Where City = 'London';

Обновляемые представления — получ. из одной таблицы исключ. некоторых строк и / или столбцов.

8. Подмножество языка DCL: операторы GRANT, REVOKE. Системные привилегии, привилегии на объекты, роли.

Сервер БД позволяет админу контролировать какие операции доступны каждому пользователю. Т. е. можно полностью контролир. доступ всех юзеров к инфе, а также разрешать и запрещать вып. к.-л. действий.

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

Объектная привилегия (object privilege) разрешает выполнение определенной операции над конкретным объектом (#, таблицей). Название каждой привилегии совпадает с названием оператора, который она разрешает выполнять пользователю над конкретным объектом. #: Select, Delete, Update, ….

Системная привилегия (system privilege) разрешает выполнение операций над всем классом объектов (объектами какого-то типа), принадлежащими конкретному пользователю, или вообще над всеми объектами какого-то типа во всей базе данных.

Предоставление и изъятие привилегий осущ. с помощью операторов DCL Grant (предоставлять, дарить) и Revoke (аннулировать, отзывать, лишать). Оба могут исп. для привилегий обоих видов.

Синтаксис Grant для объектных привилегий:

Grant <привилегия> on < объект> to < обладатель_привилегии> [with Grant Option];

Синтаксис Grant для системных привилегий:

Grant < привилегия > to < обладатель_привилегии> [with Admin Option];

По умолчанию пользователь, получивший привилегию, не может передать ее другому пользователю. Право передачи регулируется наличием необязательного параметра with Grant Option для объектных и with Admin Option для системных привилегий. Пользователь, получивший привилегию с указанием данной опции, имеет право передавать привилегию далее.

Синтаксис Revoke для объектных привилегий:

Revoke <привилегия> on <объект> from <обладатель_привилегии>

[cascade constraints];

Параметр cascade constraints имеет то же значение, что и в операторе Drop Table — если Grant отменяет привилегию References (разрешение создавать внешние ключи), то наличие параметра cascade constraints удалит все внешние ключи, созданные благодаря наличию данной привилегии.

Синтаксис Revoke для системных привилегий:

Revoke < привилегия> from < обладатель_привилегий >;

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

Роль — именованный набор привилегий.

В БД, состоящей из 100 или более таблиц при появлении нового пользователя, которому нужно предоставить привилегии к разным объектам, администратор должен выполнить довольно большой объем работы и проконтролировать, что выданы все необходимые привилегии. Если пользователей много, раздача привилегий лишь командами Grant и Revoke может занять очень много времени и усилий. Похожая проблема возникает при появлении нового объект.

Эти проблемы решаются с помощью ролей. Обычно поступают так: сначала выделяют категории пользователей, которые должны иметь различные привилегии доступа к объектам БД. Для каждой из выделенных категорий пользователей формируется набор привилегий и объединяется в роль. Далее роль предоставляется всем пользователям, относящимся к к.-л. категории. Выдача или изъятие роли, независимо от кол-ва привилегий в ней, осуществляется с помощью тех же операторов Grant и Revoke.

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

Create Role student; — создание роли.

Grant select on TimeTable, Books to student; — выдача привилегий роли student.

GRANT student to PetrovAV, IvanovAA; — назначение роли student для пользователей. При включении привилегии в роль все, обладающие ей, получат включенную привилегию.

Revoke Select on Teachers from student; — при изъятии привилегии из роли все, облад. ей, перестанут владеть изъятой привилегией.

Встроенные роли:

Connect — роль, включ. привилегии, дающие возможность организации соединения (сессии) с БД.

Resource — роль, включ. привилегии, дающие возможность создавать любые объекты.

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

9. Транзакции, операторы управления транзакциями: COMMIT, ROLLBACK, SAVEPOINT; журнал транзакций, уровни блокировок.

Транзакция — совокуп. команд DML, выполняющих логически неделимые операции (действие считается совершенным, когда выполнятся все команды).

#: перевод денег в банковской системе. Данная операция может состоять минимум из двух операций: снятие денег с одного счета и принятие денег на др. Если после вып. 1-го действия окажется, что 2-ое совершить невозможно (#: счет закрыт), то возникает неоднознач. ситуация — что будет с деньгами? Для разреш. подобных ситуаций исп. механизм транзакций.

Традиционные транзакции хар-зуются 4 классич. свойствами: атомарностью, согласованностью, изолированностью, долговечностью (прочности) — ACID (Atomicity, Consistency, Isolation, Durability). Иногда их наз. ACID-транзакциями.

Св-во атомарности: транзакция должна быть выполнена в целом или не выполнена вовсе.

Св-во согласованности: при вып. транзакций данные переходят из одного согласованного состояния в др.

Св-во изолированности: транзакции обрабатываются последовательно, изолированно друг от друга (для пользователей — как будто они вып. параллельно).

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

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

Откат транзакции — возвращение БД в состояние до выполнения транзакции. Происходит, если в процессе выполнения транзакции случилось что-то, делающее невозможным ее нормальное завершение.

Кроме выполнения явной фиксации (Commit) или отката (Rollback) транзакции, существует возможность неявного выполнения этих команд, т. е. без указания пользователем.

#: неявная фиксация транзакции осуществляется СУБД после выполнения оператора DDL.

#: неявный откат транзакции осуществляется СУБД при неожиданном завершении сессии (#: разрыв сетевого соединения) или при внезапном выключении сервера БД. В последнем случае БД после перезагрузки ОС вернется в последнее согласованное состояние.

3 оператора, предназнач. для управ. транзакциями:

Commit — явная фиксация транзакции.

Rollback — явный откат транзакции.

Savepoint — создание контрольной точки внутри транзакции (промежуточная фиксация транзакции с возможностью отката к этой точке).

# вып. операторов с фиксацией и откатом транзакций:

1. Транзакция начинается автоматически после установки соединения с БД.

Insert into Managers(Man_ID, Name, Percent) Values(100, ‘Иванов М.А.’, 5);

2. Промежуточная фиксация транзакции.

Savepoint Insert1OK;

3. Выполнение очередной команды DML.

Update Managers Set Percent=15 Where Man_ID=100;

4. Откат транзакции до точки Insert1OK.

Rollback to Insert1OK;

5. Откорректированная команда DML.

Update Managers Set Percent=16 Where Man_ID=100;

6. Фиксация транзакции и начало новой транзакции.

Commit;

7. Очередная команда DML.

Delete from Managers;

8. Отмена всех изменений в текущей транзакции (возврат БД к состоянию до пункта 6).

Rollback;

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

Журналы транзакций работают по циклич. принципу. Когда 1-й заполнен, происходит переключение журналов и изменения заносятся во 2-й журнал.

10. PL/SQL, структура, основные операторы.

Programming Language for SQL, Procedured SQL — процедурный SQL, расширение стандартного языка SQL, предназнач. для создания более сложных бизнес-правил (ограничения, процедуры, функции и триггеры) на сервере Oracle. Их общая цель — реализация сложной бизнес-логики модульным способом (компонент за компонентом). Размещение бизнес-правил на сервере значительно повышает производительность, т. к. в большинстве случаев СУБД не интерпретирует SQL-запросы, а выполняет заранее скомпилированные хранимые процедуры.

Блок PL/SQL состоит из четырех секций:

· Секция заголовка (header section). Содержит спецификацию, которая включает название блока, а также описание входных и выходных параметров.

· Секция объявления (declaration). Предназначена для объявления переменных, констант, курсоров.

· Выполняемая секция (execution, тело блока). Содержит операторы PL/SQL. Начинается со слова Begin и заканчивается словом Exception, если есть секция исключений, иначе — словом End.

· Секция исключений (exception). Содержит обработчики исключительных ситуаций.

* Исключительная ситуация — когда дальнейшее выполнение тела блока не имеет смысла.

Обязательной является только выполняемая секция.

Простейший блок PL/SQL:

Declare
string1 Varchar2(50);

Begin

string1:= 'Hello, world!';

dbms_output.put_line(string1);
End;

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

Виды блоков:

· именованные (имеют секцию заголовка),

· анонимные (не имеют секции заголовка),

· базовые;

· вложенные (для объявления временных переменных и обработки исключений).

Если блок базовый именованный, то он хранится на сервере; если анонимный, то выполнится сразу. В анонимных блоках и триггерах для создания секции заголовка указывается ключевое слово Declare.

Операторы управления PL/SQL:

· операторы выбора:

· If … Then … End If;

· If … Then … Else … End If;

· If … Then … Elsif … End If;

· операторы цикла:

· Loop … End Loop;

· While … Loop … End Loop;

· For … Loop … End Loop;

· Exit;

· Exit When;

· операторы безусловного перехода:

· Goto;

· Null;

В Oracle автоматически применяются следующие основные классы блокировок:

· Блокировки данных (на уровне DML). Применяется для таблиц и используется для защиты данных (обеспечение целостности). К этому типу относятся блокировки строки или блокировка на уровне таблицы, затрагивающая все строки таблицы. Генерируются при выполнении операторов Insert, Delete, Update, Select for Update.

· Блокировки словаря (на уровне DDL). Используется для защиты структуры объектов. Генерируется при выполнении операторов Create, Alter.

· Внутренняя блокировка и защелка. Защищает внутреннюю структуру данных.

11. Курсоры, операторы работы с курсором, оператор SELECT INTO.

В зависимости от числа строк, возвращаемых запросом, используется один из методов.

1. Когда возвращ. 1 строка — Select … into …. #:

Select OrderDate into ResultDate from Orders

Where Order_ID=453;

2. Когда возвращ. 1 или больше строк — курсор.

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

Курсоры могут объявляться явно и неявно.

Курсор, как и переменная, объявляется в секции объявлений базового блока. Синтаксис:

Cursor <имя_курсора> [(<параметр1> [,<параметр2>, …])]

[Return <спецификация_возврата>]

is

Select

[for Update

[of <таблица_или_столбец1>

[, <таблица_или_столбец2>, …]

]

]

Параметры курсора явл. входными. Значения переданных параметров могут исп. в разделе Where связанного с курсором оператора Select.

Явное объявление курсора производится в секции Declare, причем указанный в определении SQL-оператор может содержать команды Select.

Команды Insert, Update, Delete здесь не допускаются. Явные курсоры используются для обработки операторов, кот. возвращают более 1 строки.

Для обработки явного курсора в PL/SQL необходимо выполнить 4 шага:

· объявление курсора;

· открытик курсора оператором Open;

· выборка данных оператором Fetch;

· закрытие курсора оператором Close;

При открытии оператором Open курсор выполняет связанный с ним оператор Select. Если он возвращает непустой набор записей, курсор автоматически указывает на первую строку в наборе.

Каждая команда Fetch перемещает данные из курсора в указанные переменные, после чего перемещает указатель на следующую запись в наборе записей, возвращенным оператором Select. Если записей больше нет, указатель все время будет указывать на последнюю запись, а атрибуты курсора %Found и %NotFound становятся равными, соответственно, False и True соответственно.

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

Курсор имеет ряд индикаторов, показывающих его состояние. Они называются атрибутами курсора.

имя_курсора%IsOpen	Проверяет, открыт ли курсор. Возвращается значение True, если открыт.
имя_курсора%RowCount	Номер строки в наборе данных, на кот. указ. курсор.
имя_курсора%Found	Проверяет, была ли успешной последняя попытка получения записи из курсора. Если успешна, возвращ. True.
имя_курсора%NotFound	Противоположен атрибуту Found. Если записей больше не найдено, возвращ. True.

Неявные курсоры используются для обработки операторов Insert, Update, Delete, а также однострочных операторов Select … into.

Оператор Select указывается в теле блока, и PL/SQL берет на себя всю заботу об определении курсора, выполняя соответствующие действия неявно.

Каждый оператор Select имеет курсор, указ. на конкретную контекстную область, т. к. выполняется в пределах контекстной области. Такой курсор называется SQL-курсором. Он неявно открывается, обрабатывается SQL-оператор и закрывается, поэтому команды Open, Fetch, Close не нужны.

Обработка записей из курсора обычно выполняется в цикле. При написании такого цикла обычно производится проверка, была ли найдена хотя бы одна запись (проверяется состояние атрибутов %Found или %NotFound). Если да, то можно продолжить ее обработку. Иначе следует выйти из цикла.

# вывода пронумерованного списка менеджеров:

Declare

Cursor ShowMng is — Объявление курсора.

Select * from managers;

m Managers%RowType; — Объявление временной переменной для хранения текущей строки курсора.

Begin

Open ShowMng; — Открытие курсора.

Fetch ShowMng into m; — Выборка первой записи.

While ShowMng%Found Loop — Продолжать цикл, пока курсор указывает на необработанную запись.

dbms_output.put_line(to_char(ShowMng%RowCount)||’ ’||m.Name);

Fetch ShowMng into m; — Выбрать след. запись.

End Loop; — Конец цикла.

Close ShowMng; — Закрытие курсора.

End; — Конец блока (курсора).

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

Синтаксис курсорного цикла For:

For <запись_курсора> in <имя_курсора> [(<параметр1> [, <параметр2>,…])] Loop

<операторы>;

End Loop;

Этот цикл выбирает записи из курсора в переменную запись_курсора. Поля записи_курсора можно использовать для доступа к данным из операторов PL/SQL, выполняемых в цикле. Когда все записи выбраны, цикл завершается.

Declare

Cursor ShowMng is

Select * from Managers;

Begin

For m in ShowMng Loop

dbms_output.put_line(to_char(ShowMng%RowCount)||’ ’||m.Name);

End Loop;

End;

12. Процедуры, функции, пакеты.

Процедуры — набор инструкций, написанных на языке PL/SQL, и операторов DML. Вызов процедуры приводит к выполнению содержащихся в ней инструкций. Процедуры хранятся в БД.

Процедура — именованный блок PL/SQL, т. е. секция заголовка непустая. Секция заголовка включает в себя имя процедуры и описание ее входных и выходных данных. После спецификации идет секция объявлений, тело процедуры (выполняемая) и секция исключений.

Создается процедура при помощи оператора Create Procedure или Create or Replace Procedure.

Между именем параметра и его типом можно указывать два специальных слова — in и/или out. Указание in будет означать, что переменной было передано входное значение. Указание out означает, что эту переменную можно использовать для возврата значения. Можно указать одновременно оба. Если ничего не указано, подразумевается in.

Процедура выз. оператором execute или exec. #:

execute increase_prices(5);

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

Процедуру можно вызывать внутри других блоков.

Удаление: Drop Procedure <имя_процедуры>.

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

Как уже было отмечено выше, функции, в отличие от процедур, не могут вызываться при помощи оператора execute, они всегда являются частью более сложного SQL-оператора. #:

Exec increase_prices(get_max_price/100);

Однако, для проверки функции исп. выражение:

Select get_max_price from dual;

Удаление: Drop Function <имя_функции>;

Обычно, если возвращается несколько значений, используется процедура, если одно — функция.

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

Пакет также имеет спецификацию и тело, однако они создаются отдельно друг от друга.

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

Вызов процедуры, переменной или функции пакета:

exec <имя_пакета>.<объект_пакета>;

Создание спецификации пакета:

Create Package <имя_пакета> is

[<объявления_переменных>]

[<спецификации_курсоров>]
[<спецификации_функций_и_процедур>]
End [<имя_пакета>];

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

Переменные в спецификации пакета — переменные пакета (package variables), инициализируются только при первом обращении к нему. Когда производится вызов какой-либо составляющей пакета, Oracle загружает пакет в память, где он остается все время, пока пользователь соединен с БД. При наличии нескольких сеансов переменные пакета и их значения становятся разделяемыми. Обращение к объектам пакета в последующих сеансах происходит быстрее, поскольку пакет уже присутствует в памяти сервера.

Создание тела пакета:

Create or Replace Package Body <имя_пакета> is

[<локальные_переменные>]
[<полные_спецификации_курсоров_пакета>]
[<полные_спецификации функций_и_процедур_пакета>]
Begin
[<выполняемые_операторы>]

[Exception [<обработчики_исключений>]]

End [<имя_пакета>];

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

В приведенном ниже примере будет создан пакет, в котором собраны функции и процедуры, связанные с объектом менеджер. Далее будут приведены пояснения и комментарии к этому примеру.

Create or Replace PackManager as

pCount Number; — Текущее кол-во менеджеров.

Procedure DeleteManager(Man_ID Number); — Процедура удаления менеджера.

End PackManager;

Create or Replace Package Body PackManager as

Procedure DeleteManager(man_id number)

is

mcount Number;

Begin

Select Manager_ID into mcount from Managers Where Manager_ID=Man_ID;

Delete from Outgoing Where Manager_ID=Man_ID;

Delete from Incoming Where Manager_ID=Man_ID;

Delete from Managers Where Manager_ID=Man_ID;

Exception

When no_data_found Then

Raise_application_error(-20001, 'Manager dont found');

End;

Function mCount — Функция возвращения общего кол-ва менеджеров (видна только внутри пакета).

Return Number

is

m Number;

Begin

Select count(*) into m from Managers;

Return m;

End;

Begin — При инициализации пакета в переменную pCount помещается текущее число менеджеров.

pCount:= mCount;

End;

13. Триггеры, их основные свойства и значение.

Триггер — особого вида процедура, которая выполняется автоматически при возникновении события, указанного в спецификации триггера — триггерного события (triggering event).

В PL/SQL можно создать триггеры, которые будут срабатывать в одной из следующих ситуаций:

· примен. оператора DML (Insert, Update, Delete) к определенному объекту схемы;

· примен. оператора DDL (Create, Alter, Drop);

· вход или выход пользователя в / из системы, ошибка сервера, запуск и останов БД.

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

Триггеры используются для реализации сложных бизнес-правил. #: триггер может проверять, истек ли срок продаваемого товара к моменту осуществления сделки продажи, или нет. Если истек, то триггер может предотвратить продажу данного товара.

Триггеры могут обеспечивать прозрачное протоколирование событий; #: отслеживать сколько раз каждый из пользователей обращался к таблице.

Момент срабатывания определяет, когда будет срабатывать триггер: до (Before), после (After) и вместо (Instead of) наступления триггерного события (выполнения запускающего оператора).

Если указано значение Before, триггер выполняется до каких-либо проверок ограничений на строки, затрагиваемые триггерным событием. Никакие строки не блокируются. Триггер этого типа называется, соответственно, Before-триггером (Before trigger).

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

Триггеры Instead of применяются только для представлений. Проблема состоит в том, что операторы DML не применимы к представлениям. Нельзя добавлять, изменять или удалять записи из представлений. Триггеры Instead of вызываются вместо выполнения DML-выражений. Если вы выполните оператор DML к представлению, то в соответствующем триггере можно выполнить какие-либо действия, и тогда у пользователя создастся впечатление, что он работает с таблицей.

Триггерное событие мб = Insert, Update, Delete.

Различные триггерные события можно комбинировать с помощью оператора or.

При использовании Update можно указать список столбцов, при изменении которых будет срабатывать триггер: Update of <столбец1>[,<столбец2>,…].

Триггер, запрещающий изменять имя менеджера:

Create or Replace Trigger onManagersUpdate

Before Update of Name on Managers

For each row

Declare

cant_change_name Exception;

Begin

If:new.Name <>:old.Name Then

Raise cant_change_name;

End If;

Exception

When cant_change_name Then

dbms_output.put_line('Нельзя изменить имя менеджера');

:new.Name:=:old.Name;

End;

При попытке изменить значение поля Name сработает триггер, кот. сравнивает старое и новое значения поля Name. Если они различаются, то активизируется исключение, при обработке которого на экране появится соответствующее сообщение

Удаление: Drop Trigger <имя_триггера>;

Alter Trigger <имя_триггера> Disable; — Отключение триггера.

Alter Table <имя_таблицы> Disable All Triggers; — Отключение всех триггеров, связанных с таблицей.

14. Параллельные архитектуры БД; масштабируемость, надежность, производительность.

3 архитектурных направления:

1. Симметричные многопроцессорные системы (SMP) — форма сильносвяз. многопроц. систем, раздел. единую опер. память и дисковую подсистему;

2. Слабосвязанные многопроц. системы (кластеры) — совокуп. компьютеров, объединенных в единую систему быстродействующей сетью и имеющих общую дисковую подсистему;

3. Системы с массовым параллелизмом (MPP) — системы с сотнями и более процессоров, имеющие многоуровневую структуру оперативной памяти.

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

Требования, опред. качества соврем. СУБД:

· масштабируемость;

· производительность;

· возможность загрузки разными типами задач;

· обеспечение постоянной доступности данных (надежность или катастрофоустойчивость).

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

В случае сервера СУБД можно рассм. 2 способа масштабирования — вертикальный и горизонтальный:

При гориз. увелич. кол-во серверов СУБД.

При вертик. увелич. мощность одного сервера СУБД.

При оценке сервера СУБД на базе SMP платформы стоит обратить внимание на 2 хар-ки расширяемости архитектуры: адекватность и прозрачность.

Св-во адекватности требует, чтобы архитектура сервера равно поддерживала 1 или 10 процессоров без переинсталяции или существенных изменений в конфигурации, а также доп. программных модулей;

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

Факторы, влияющие на производительность СУБД:

· поддержка параллелизма (параллельный ввод / вывод, параллельные средства и утилиты администрир., параллельная обработка запросов);

· реализация многопотоковой архитектуры.

Эволюция в области информационных систем все отчетливее направлена в сторону объединения задач: оперативной обработки транзакций (OLTP), поддержки принятия решений (DSS)

Осн. факторы реализ. системы со смеш. загрузкой:

· оптимизация запросов;

· эффективное управление ресурсами (поддержка прозрачности доступа к ресурсам и эффективное использование каждого из них в отдельности);

· параллельная обработка запросов.

Надежность обеспечивается с помощью:

· оперативного администрирования;

· функциональной насыщенности СУБД.

15. Распределенные базы данных, фрагментация, тиражирование.

Распределённые базы данных — совокупность логически взаимосвязанных БД, распред. в сети.

РБД состоит из набора узлов, связанных коммуникационной сетью, в которой:

· каждый узел — полноценная СУБД сама по себе;

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

Распред. систему БД можно рассм. как партнерство между локальными СУБД на локальных узлах.

Ядром системы управления распределенными инф-ными ресурсами явл. РБД и система управления РБД.

Система управления распределенной БД — программная система, обеспеч. управление РБД и прозрачность ее распределенности для пользователей.

Проектирование РБД «сверху вниз» аналогично проектированию централизованных БД:

· создание концептуальной модели БД;

· отображение ее в логическую модель данных;

· создание и настройка специфических структур.

Однако при проектировании РБД предполагается, что объекты не будут сосредоточены в одном месте, а распределятся по нескольким вычсистемам.

Распределение проводится путем фрагментации и тиражирования.

Фрагментация — декомпозиция объектов БД (#, таблицы) на несколько частей, которые размещаются на разных системах.

Существуют горизонтальная и вертикальная фрагментация (по строкам или по столбцам). В любом случае поддерживается глобальная схема, позволяющая воссоздать из фрагментов логически централизованную таблицу или другую структуру.

Тиражирование (репликация) — создание дубликатов данных. Дубликаты (репликаты) — множ. различ. копий объекта, для которых в соотв. с опред. правилами поддерж. синхронизация с главной копией.

Недостатки распределенных СУБД.

· Слабое представление сущностей реального мира.

· Семантическая перегрузка.

· Слабая поддержка ограничений целостности и корпоративных ограничений.

· Однородная структура данных.

· Ограниченный набор операций.

· Трудности организации рекурсивных запросов.

· Проблема рассогласования.

· Др. проблемы РСУБД, связ. с параллельностью, изменениями схемы и слабыми средствами доступа.

16. Средства защиты данных в СУБД.

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

Субъект — активная единица информационного обмена (#: процесс, приложение ОС).

Основные направления борьбы с потенциальными угрозами конфиденциальности и целостности данных:

· идентификация и проверка подлинности (аутентификация) пользователей;

· управление доступом к данным (привилегии);

· отчетность действий, влияющих на безопасность (протоколирование и аудит);

· анализ и защита регистрац. инфы от искажений;

· очистка объектов перед повторным использ.;

· защита (шифровка / криптография) инфы при передаче по линиям связи;

· экранирование.

Для поддержания режима инф-ной безопасности особенно важны программно-технические меры, поскольку основная угроза компьютерным системам исходит от них самих (сбои оборудования, ошибки ПО, промахи пользователей и администраторов и т.п.).

Инкапсуляция инфы в спец. протоколах обмена.

1. Инфраструктуры с открытыми ключами. Использование подобных методов в коммуникациях основано на алгоритмах шифрования с открытым ключом. На этапе инициализации происходит создание пары ключей - открытого, который становится общеизвестным, и закрытого, имеющегося только у того, кто публикует открытый ключ. Суть алгоритмов шифрования с открытым ключом заключается в том, что операции шифрования и дешифрования производятся разными ключами (открытым и закрытым соответственно). Наиболее широко распространены следующие системы такого рода: ISO X.509 (в особенности его реализация для WWW,- Secure Socket Layer – SSL) - шифрование трафика транспортного уровня; Pretty Good Privacy (PGP) - общецелевая система шифрования с открытым ключом, наиболее широко используемая в системах электронной почты.

2. Secure Shell protocol (ssh). Протокол ssh используется для шифрования многих видов коммуникаций между удаленными системами (таких как копирование файлов или протокол X11). Данный протокол также использует шифрование с открытым ключом, но только на этапе установления соединений. Непосредственно транспортный трафик шифруется обычными алгоритмами: DES, 3DES, RC4 и др.

3. Комбинированные методы

Ограничение информационных потоков.

1. Firewalls. Метод подразумевает создание между локальной и глобальной сетями специальных промежуточных серверов, которые инспектируют и фильтруют весь проходящий через них трафик сетевого/транспортного уровней. Более защищенная разновидность метода — это способ маскарада (masquerading), когда весь исходящий из локальной сети трафик посылается от имени firewall-сервера, делая локальную сеть практически невидимой.

2. Proxy-servers. При данном методе весь трафик сетевого/транспортного уровней между локальной и глобальной сетями запрещается полностью - попросту отсутствует маршрутизация как таковая, а обращения из локальной сети в глобальную происходят через специальные серверы-посредники. Очевидно, что при этом методе обращения из глобальной сети в локальную становятся невозможными в принципе. Очевидно также, что этот метод не дает достаточной защиты против атак на более высоких уровнях — #, на уровне приложения (вирусы, код Java и JavaScript).

Метки безопасности

Для реализации принудительного управления доступом с субъектами и объектами ассоциируются метки безопасности. Метка субъекта описывает его благонадежность, метка объекта - степень закрытости содержащейся в нем информации.

Согласно "Оранжевой книге", метки безопасности состоят из двух частей: уровня секретности и списка категорий. Уровни секретности, поддерживаемые системой, образуют упорядоченное множество:

· совершенно секретно;

· секретно;

· конфиденциально;

· несекретно.

Категории образуют неупорядоченный набор. Их назначение - описать предметную область, к которой относятся данные. В военной области каждая категория может соответствовать, например, определенному виду вооружений. Механизм категорий позволяет разделить информацию "по отсекам", что способствует лучшей защищенности. Главная проблема, которую необходимо решать в связи с метками, - это обеспечение их целостности.

17. Шлюзы к базам данных. Архитектура ODBC. WWW-интерфейс к БД.

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

В зависимости от конкретного продукта преобразование выполняется либо на уровне API-интерфейса, либо на уровне протоколов коммуникаций, либо на обоих уровнях сразу.

Универсальный API-интерфейс — интерфейс к источнику данных, отрывающий приложение от определенной базы данных, обеспечивая единообразный интерфейс независимо от специфической архитектуры используемой СУБД. Три наиболее известных стандарта универсальных API-интерфейса СУБД: Open Database Connectivity (ODBC), Java Database Connectivity (JDBC) и Object Linking and Embedding Database (OLE DB).

Архитектура ODBC (Open Database Connectivity):

· SQL – приложение

· Администратор ODBC

· Драйверы ODBC для различных СУБД

· локальные или удаленные БД

Основная идея:

· все операции с БД идут через специальный программный слой, не зависящий от СУБД;

· конфигурация ODBC для каждого источника данных (alias) определяет его драйвер и местоположение;

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

Уровни драйверов ODBC: минимальный, базовый, расширенный.

Дата добавления: 2015-11-16