SYBASE (988809), страница 11

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 11)

select "Ошибка при выполнении FETCH"

return /* останов процедуры в случае ошибки */

end

select "специальность ", @sp, " - ", @it2 /* печать последнего итога по специальности */

select "учебное заведение ", @yz, " - ", @it3 /* печать последнего итога по учебн. заведению */

select "всего по запросу - ", @itall /* печать общего итога по запросу */

deallocate cursor y /* освобождение курсора */

return

Задания, реализуемые с помощью хранимых процедур, приведены в приложениях I - V.

Использование временных таблиц

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

1) создать временную таблицу;

2) выполнить операторы insert, update или delete в этой таблице;

3) выполнить запросы в ней;

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

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

Пример:

create table #tempstores

(stor_id char(4),

amount money)

Множественные результаты

По умолчанию, по команде fetch выбирается только одна запись. С помощью команды set cursor rows можно изменить число выбираемых записей:

set cursor rows <число> for <имя курсора>

Например:

set cursor rows 3 for poss_crsr

Тогда команда: fetch poss_crsr выбирается сразу три записи, а указатель курсора перемещается на последнюю третью запись.

6. ТРИГГЕРЫ

Общие понятия

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

Главное преимущество триггеров в том, что они срабатывают автоматически при выполнении операторов модификации данных: update, insert или delete.

Триггеры можно подразделить на три типа:

- триггеры добавления;

- триггеры удаления;

- триггеры обновления.

Триггеры наиболее эффективны в следующих ситуациях:

1) с помощью триггеров можно "покаскадно" вносить изменения во взаимосвязанные таблицы БД.

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

3) с помощью триггеров можно ввести более сложные ограничения, чем ограничения, созданные с помощью правил.

4) с помощью триггеров можно организовать простой анализ типа "что если".

Замечание: Для каждой таблицы можно создать только один триггер добавления, один триггер удаления и один триггер обновления, т.е. всего три.

Триггеры создаются с помощью команды create trigger:

create trigger [<владелец>.]<имя триггера>

on [<владелец>.]<имя таблицы>

for {insert, update, delete}

as <SQL-предложения>

Для триггера обновления можно использовать структуру if update:

create trigger [<владелец>.]<имя триггера>

on [<владелец>.]<имя таблицы>

for {insert, update}

as

[if update (<имя колонки>)

[{and|or} update (<имя колонки>)]...]...

<SQL - предложения>}

Следующие SQL - команды нельзя использовать в триггерах:

1) все команды create: create database, create table, create index, create procedure, create default, create rule, create trigger, create view;

2) все команды drop;

3) alter table и alter database;

4) truncate table;

5) grant и revoke;

6) update statistics;

7) reconfigure;

8) load database и load transaction;

9) disk init, disk mirror, disk refit, disk reinit, disk remirror, disk unmirror;

1. select into

Команда уничтожения триггеров:

drop trigger [<владелец>.]<имя триггера>

[,[<владелец>.]<имя триггера>]...

Как работают триггеры?

Таблица, для которой строится триггер, называется триггерной таблицей. Для такой таблицы строятся две временные таблицы с именами deleted и inserted, называемых предопределенными. Эти таблицы используются для проверки результатов изменения данных и установки условий действий триггера. Нельзя напрямую изменять данные в предопределенных таблицах, но можно использовать эти таблицы в операторах select для обнаружения результатов работы операторов insert, update или delete.

В таблице deleted сохраняются копии полученных операторами delete или update записей. При запуске операторов deleted или update, записи передаются из триггерной таблицы в таблицу deleted. В таблице inserted сохраняются копии помеченных операторами insert и update записей. При выполнении insert или update новые записи добавляются и в таблицу inserted и в триггерную таблицу одновременно.

Оператор update выполняется за два шага:

1) удаление - старые записи переписываются в deleted;

2) вставка - новые записи копируются и в триггерную таблицу и в таблицу inserted.

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

Примеры использования триггеров:

1) Триггер срабатывает, когда выполняется любой из трех операторов: update, delete, insert в таблице VUZ:

create trigger vuztrig1 on vuz

for insert, update, delete as

select "Необходимо выполнить такую же коррекцию в таблице vuz_gorod"

2) Триггер срабатывает при добавлении новых записей в таблицу vuz_gorod:

create trigger vuz trig2 on vuz_gorod

for insert as

if (select count(*) from inserted)>0

select "Необходимо добавить запись в таблицу vuz"

3) Триггер срабатывает при удалении записей из таблицы vuz_gorod:

create trigger vuztrig3 on vuz_gorod for

delete as

if (select count(*) from deleted)>0

select "Необходимо удалить старую запись из таблицы vuz"

Для создания первичных и внешних ключей таблиц можно использовать системные процедуры:

1) sp_primarykey - для первичного ключа;

2) sp_foreignkey - для внешнего ключа.

Первичный ключ создается всегда перед созданием внешнего.

Примеры создания первичного и внешнего ключей:

1) создание первичного ключа в таблице vuz по полю код учебного заведения:

sp_primarykey vuz, cod

2) создание внешнего ключа в таблице vuz_gorod по полю код учебного заведения с ссылкой на первичный ключ:

sp_foreignkey vuz_gorod, vuz, cod_vuza

Ограничения использования триггеров

1) Одна таблица может иметь максимум три триггера: один триггер обновления, один триггер добавления и один триггер удаления записей.

2) Каждый триггер может использоваться только одной таблицей, однако в триггере можно использовать все три оператора: update, insert и delete.

3) Нельзя создавать триггер на основе представления или временной таблицы, хотя в триггерах можно ссылаться на представления или временные таблицы.

4) Хотя оператор truncate table подобен по действию оператору delete без предложения where (т. е. удаляются все записи), он не может активизировать триггер из-за отсутствия регистрации удалений отдельных записей.

5) Нельзя использовать триггеры на системных таблицах. Если вы создадите триггер на системной таблице, то хотя не появится никакого сообщения об ошибке, этот триггер работать не будет.

1. АДМИНИСТРИРОВАНИЕ

ПОЛЬЗОВАТЕЛИ И БАЗЫ ДАННЫХ

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

1) системный администратор(СА);

2) владельцы БД(ВБД);

3) владельцы объектов БД(ВОБД);

4) конечные пользователи(КП).

Каждая роль имеет свои полномочия для работы с БД. Кроме этого, существуют специальные роли для работы с сервером:

- системный администратор,

- управляющий системной защитой(УСЗ),

- оператор(О).

Полномочия и роли

Специальные серверные роли

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

Системный администратор выполняет административные задачи, не связанные с специальными приложениями. Это следующие задачи:

1) инсталляция SQL-сервера;

1. управление дисковой и оперативной памятью;

2. предоставление полномочий пользователям SQL-сервера;

3. передача внешних данных между SQL-сервером и другими программами;

5) уничтожение, изменение и захват имеющихся ресурсов сервера;

1. управление процедурой автоматического восстановления SQL-сервера;

2. диагностика системных проблем и печать отчетов о них;

3. настройка SQL-сервера путем изменения параметров конфигурации системы;

4. создание пользовательских БД и определение их владельцев;

10) предоставление и отмена роли системного администратора;

1. формирование групп, обладающих привилегиями.

Полномочия СА

Существует несколько команд, которые могут быть выполнены только системным

администратором и никаким другим пользователем. Это команды:

1. reconfigure;

2. disk init;

3. disk refit;

4. disk reinit;

5. команды копирования дисков;

6. shutdown;

7. kill.

Только СА может предоставить полномочие на выполнение команды create database. Команда grant на полномочие выполнения команды create database должна быть выдана из БД master.

Во многих инсталляциях СА имеет монополию на команду create database. В этом случае СА сначала выполняет команду create database, затем подключается к созданной БД командой use и, наконец, выполняет системную процедуру sp_changedbowner (передает право владения БД другому пользователю).

Полномочия УСЗ

УСЗ выполняет защитные задачи в SQL-сервере:

1. управление клиентами (включая псевдонимы и группы);

2. предоставление и отмена ролей УСЗ и оператора;

3. изменение паролей любых клиентов;

4. установка срока действия пароля;

5. управление системой контроля.

Но, УСЗ не имеют специальных полномочий на использование объектов БД. УСЗ может исправить любое повреждение системы, неумышленно сделанное пользователем. Например, если владелец БД забыл свой пароль, то УСЗ может изменить его, что позволит владельцу БД войти в систему с новым паролем.

Полномочия оператора

Оператор - это пользователь, который может дампировать и восстанавливать БД на сервере без права владения этой БД. Оператор имеет полномочия на использование следующих команд:

1. dump database;

2. dump transaction;

3. load database;

4. load transaction.

Эти же команды могут выполнять владелец БД и СА.

Полномочия владельцев БД

ВБД и СА - это пользователи, которые имеют полномочия на предоставление полномочий другим пользователям. ВБД - это или создатель БД, или пользователь, которому было передано владение БД. СА предоставляет пользователям полномочие на создание БД с помощью команды grant.

ВБД записывает на SQL-сервер свое входное имя и пароль. Кроме этого, он может:

1. с помощью хранимой процедуры sp_adduser организовать доступ к БД других пользователей;

2. предоставить привилегию другим пользователям на создание объектов БД и использование команды execute.

ВБД имеет полные полномочия на использование объектов своей БД. Кроме этого, ВБД автоматически предоставляются полномочия на следующие команды:

1. checkpoint;

2. dbcc;

3. setuser;

4. dump database;

5. dump transaction;

6. load database;

7. load transaction;

8. drop database;

9. grant/revoke.

ВБД могут предоставить другим пользователям полномочия на выполнение следующих команд:

1. create table;

2. create default;

3. create rule;

4. create procedure;

5. create view;

6. grant/revoke-привилегии на системные таблицы;

7. grant/revoke-привилегии на выполнение команд на объектах БД:

• select;

• insert;

• delete;

• update;

• references;

• execute.

Привилегии на системные таблицы

После инсталляции SQL-сервера пользователи БД имеют полномочия на чтение

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

СА с помощью системной процедуры sp_configure и команды reconfigure может разрешить вносить изменения напрямую (ad hoc) в системные таблицы.

Привилегии на системные процедуры

Системные процедуры хранятся в БД sybsystemprocs. Системные процедуры, связанные с защитой системы, запускаются УСЗ, а остальные системные процедуры - СА. Кроме этого, несколько процедур могут быть выполнены только владельцами БД. Что касается остальных пользователей, то им должно быть предоставлено полномочие на выполнение системных процедур (например, все процедуры sp_help). Это полномочие предоставляется в БД sybsystemprocs.

Пользователи, отсутствующие в таблице sybsystemprocs..sysusers, объявляются пользователями типа “гость” в БД sybsystemprocs и автоматически получают привилегию на выполнение многих системных процедур. Для того, чтобы отменить полномочие пользователя на выполнение системной процедуры, СА должен добавить его в таблицу sybsystemprocs..sysusers и выполнить команду revoke с указанием этой процедуры. Владелец пользовательской БД не может прямо контролировать полномочия на использование системных процедур в своей БД.

Характеристики

Список файлов учебной работы