Агрегированные представления

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

CREATE VIEW TOTALDAY AS

SELECT EXAM_DATE, COUNT(DISTINCT SUBJ_ID) AS SUBJ_CNT,

COUNT(STUDENT_ID) AS STUD_CNT,

COUNT(MARK; AS MARK_CNT,

AVG(MARK) AS MARK_AVG, SUM(MARK) AS MARK_SUM

FROM EXAM_MARKS

GROUP BY EXAM_DATE;

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

SELECT * FROM TOTALDAY;

Представления, основанные на нескольких таблицах

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

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

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

• позволяет использовать при формировании отчетов более надежный модульный подход;

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

Можно, например, создать представление, которое показывает имена и названия сданных предметов для каждого студента:

CREATE VIEW STUD_SUBJ AS

SELECT A.STUDENT_ID, C.SUBJ_ID, A.SURNAME, C.SUBJ_NAME

FROM STUDENT A, EXAM_MARKS B, SUBJECT С

WHERE A.STUDENT_ID = B.STUDENT_ID

AND B.SUBJ_ID = C.SUBJ_ID;

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

SELECT SUBJ_NAME

FROM STUD_SUBJ

WHERE SURNAME = 'Иванов';

Лабораторная работа №8

Разработка хранимых процедур

Хранимые процедуры

Хранимая процедура(Stored procedure) – программа, которая выполняется внутри базы данных и может предпринимать сложные действия на основе информации, задаваемой пользователем. Поскольку хранимые процедуры выполняются непосредственно на сервере базы данных, обеспечивается более высокое быстродействие, чем при выполнении тех же операций средствами клиента базы данных.

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

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

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

- необходимые команды уже содержатся в базе данных;

- все они прошли этап синтаксического анализа и находятся в исполняемом формате;

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

- хранимые процедуры могут вызывать другие хранимые процедуры и функции;

- хранимые процедуры могут быть вызваны из прикладных программ других типов;

- как правило, хранимые процедуры выполняются быстрее, чем последовательность отдельных команд;

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

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