Саратовский Государственный университет им. Н.Г. Чернышевского
Курсовая работа
на тему: «Особенности разработки триггеров и хранимых процедур в СУБД»
(на примере базы данных отдела кадров)
студентки I V курса заочного отделения КН и ИТ
(прикладная математика и информатика)
ФРОЛОВОЙ Марии Александровны
Содержание
1. Ведение
2. Реляционная база данных
3. Хранимые процедуры
4. Триггеры
Заключение
Список использованной литературы
ВВЕДЕНИЕ
· Базы данных
С появлением магнитных дисков началась история систем управления данными во внешней памяти. До этого каждая прикладная программа, которой требовалось хранить данные во внешней памяти, сама определяла расположение каждой порции данных на магнитной ленте или барабане и выполняла обмены между оперативной и внешней памятью с помощью программно-аппаратных средств низкого уровня (машинных команд или вызовов соответствующих программ операционной системы). Такой режим работы не позволяет или очень затрудняет поддержание на одном внешнем носителе нескольких архивов долговременно хранимой информации. Кроме того, каждой прикладной программе приходилось решать проблемы именования частей данных и структуризации данных во внешней памяти.
База данных (БД) – это структурированный набор постоянно хранимых данных. Постоянность означает, что данные не уничтожаются по завершении программы или пользовательского сеанса, в котором они были созданы.
База данных- это набор, совокупность файлов, в которых находится информация. Программная система (приложение), обеспечивающая работу с базой данных (файлами данных) называется системой управления базой данных (СУБД).
В зависимости от расположения программы, которая использует данные, и самих данных, а также от способа разделения данных между несколькими пользователями различают локальные и удаленные базы данных.
По структуре организации данных базы делятся на реляционные и нереляционные.
· Поддержка языков БД
Для работы с базами данных используются специальные языки, в целом называемые языками баз данных. В ранних СУБД поддерживалось несколько специализированных по своим функциям языков. Чаще всего выделялись два языка - язык определения схемы БД (SDL - Schema Definition Language) и язык манипулирования данными (DML - Data Manipulation Language). SDL служил главным образом для определения логической структуры БД, т.е. той структуры БД, какой она представляется пользователям. DML содержал набор операторов манипулирования данными, т.е. операторов, позволяющих заносить данные в БД, удалять, модифицировать или выбирать существующие данные.
В современных СУБД обычно поддерживается единый интегрированный язык, содержащий все необходимые средства для работы с БД, начиная от ее создания, и обеспечивающий базовый пользовательский интерфейс с базами данных. Стандартным языком наиболее распространенных в настоящее время реляционных СУБД является язык SQL (Structured Query Language).
Прежде всего, язык SQL сочетает средства SDL и DML, т.е. позволяет определять схему реляционной БД и манипулировать данными. При этом именование объектов БД (для реляционной БД - именование таблиц и их столбцов) поддерживается на языковом уровне в том смысле, что компилятор языка SQL производит преобразование имен объектов в их внутренние идентификаторы на основании специально поддерживаемых служебных таблиц-каталогов. Внутренняя часть СУБД (ядро) вообще не работает с именами таблиц и их столбцов.
РЕЛЯЦИОННЫЕ БАЗА ДАННЫХ (РБД)
Реляционная база данных - это тело связанной информации, сохраняемой в двумерных таблицах. Напоминает адресную или телефонную книгу.
Были созданы таблицы DAN (Interbase) и данные (MS Access) со столбцами:
| Interbase | MS Access |
| TAB_NO – табельный номер FAM – фамилия работника IMIA – имя работника OTSH – отчество работника G_R - год рождения работника ADRES - адрес работника TEL - телефон DATA_P – дата приема DATA_U - дата увольнения OBRAZ - образование SEM_POLOJ – семейное положение S_N_PASPORT – паспортные данные K_V - код выслуги K_O - код оклада K_P - код профессии | табельный номер фамилия работника имя работника отчество работника год рождения работника адрес работника телефон дата приема дата увольнения образование семейное положение паспортные данные код выслуги код оклада код профессии |
и заполненными строками. Каждая строка (называемая также записью - основной элемент БД) будет соответствовать определенной особенности; каждый столбец будет содержать значение для каждого типа данных - имени, телефонного номера, и адреса представляемого в каждой строке.
То что мы получили - является основой реляционной базы данных как и было определенно, а именно, двумерной (строка и столбец) таблицей информации. Однако реляционные базы данных редко состоят из одной таблицы. Такая таблица меньше чем файловая система. Создав несколько таблиц взаимосвязанной информации, мы сможем выполнить более сложные и мощные операции с данными. Мощность базы данных зависит от связи, созданной между фрагментами информации, а не от самого фрагмента информации.
· Связывание одной таблицы с другой
Но на примере наших таблиц можно показать реальное использование в деловой ситуации. Предположим, что персонажи в наших первых таблицах - это работники МЧС России. В другой таблице, мы могли бы запомнить дополнительную информацию них. Столбцы второй таблицы PROF (Interbase) и профессия (MS Access) профессия выглядят так:
| SELECT * FROM PROF; | SELECT профессия.[код профессии], профессия.[название профессии] FROM профессия; |
| Interbase | MS Access |
| K_P NAZ ==== ======== 1 пожарный 2 водитель 3 бухгалтер 4 инспектор 5 начальник караула 6 диспетчер 7 секретарь 8 водометчик 9 командир отделения 10 начальник части 11 заместитель по тылу 12 наводчик | код название профессии профессии = = = = = = = = = = = == = = = = = = = == 1 пожарный 2 водитель 3 бухгалтер 4 инспектор 5 начальник караула 6 диспетчер 7 секретарь 8 водометчик 9 командир отделения 10 начальник части 11 заместитель по тылу 12 наводчик |
Вся таблица:
Много мощных функций можно выполнить, извлекая информацию из этих таблиц согласно указанным параметрам, особенно когда эти параметры включают в себя фрагменты информации связанные в различных таблицах друг с другом. Например, возьмем запрос: Вывод сотрудников с соответствующими им профессиями:
| Interbase | SELECT TAB_NO,FAM,IMIA,OTSH,G_R,PROF.NAZ FROM DAN,PROF WHERE DAN.K_P=PROF.K_P; | |||||||||||||||||||||||||||||||||||||||||||||||||||
| MS Access | SELECT данные.[табельный номер], данные.фамилия, данные.имя, данные.отчество, данные.[год рождения], профессия.[название профессии] FROM профессия INNER JOIN данные ON профессия.[код профессии] = данные.[код профессии]; | |||||||||||||||||||||||||||||||||||||||||||||||||||
| Interbase | TAB_NO FAM IMIA OTSH G_R NAZ ====== ========= ========== =============== ==== ================ 1001 Петров Петр Петрович 01.12.1971 инспектор 1002 Сидоров Павел Сергеевич 10.03.1975 начальник караула 1003 Кортунов Сергей Владимирович 17.07.1963 начальник части 1004 Семенова Пелагея Никитична 01.06.1985 диспетчер 1005 Романова Евлампия Апполинарьевна 04.12.1982 диспетчер 1006 Несмелая Агрофена Агриповна 04.11.1976 секретарь 1007 Сердюков Игорь Игнатьевич 27.05.1978 пожарный 1008 Любимов Петр Николаевич 17.02.1967 водометчик 1009 Шарипов Руслан Имранович 14.08.1960 пожарный 1010 Ухабистова Авдотья Владиленовна 07.10.1956 бухгалтер 1011 Игумнов Андрей Дмитриевич 25.03.1962 заместитель по тылу 1012 Ильюшин Дмитрий Александрович 19.11.1958 наводчик 1013 Туполев Эдуард Валентинович 06.01.1969 пожарный 1014 Рабинович Ицхак Абрамович 23.09.1984 водитель 1000 Иванов Иван Иванович 12.04.1968 водитель | |||||||||||||||||||||||||||||||||||||||||||||||||||
| MS Access |
|
Схемы баз данных (Database Diagrams) — это тип объектов, который присутствует только в проектах Access. Они являются аналогом схемы данных в базах данных Access, однако в проектах Access это понятие существенно расширено.
Во-первых, таких схем в одном проекте может быть несколько. Это значит, что всю совокупность таблиц можно разделить на логические области и построить для каждой из них свою схему данных. Это очень важно, т. к. количество таблиц в базе данных на сервере может быть очень велико, и, если разместить их все на одной схеме, она может быть просто необозрима. Кроме того, могут быть созданы схемы данных для определенных целей, когда нужно выделить и выразить специфический взгляд на подмножество таблиц базы.
Во-вторых, сам инструментарий для создания схем баз данных более богат: он позволяет не только определять связи между существующими таблицами, но и изменять структуру и свойства таблиц, создавать новые таблицы и т. д. В этом смысле он является альтернативой обычному способу описания таблиц — с помощью режима Конструктора.
Вы можете выполнять операции как с отдельным объектом этой структуры, так и с группой объектов. Причем все изменения, которые вносятся в структуру базы данных, не сохраняются не сервере до тех пор, пока вы не сохраните измененную схему. Это удобно, когда нужно внести много изменений. Тогда сохранить можно только конечный результат, когда вы убедились, что все изменения согласованы.
Программы реляционной базы данных разрабатывались для того чтобы обрабатывать большие и сложные совокупности данных такого типа, что очевидно является более универсальным методом в деловом мире. Даже если бы база данных отдела кадров содержала сотни или тысячи имен - как это вероятно и бывает на практике - одна команда SQL может выдать мне информацию в которой он нуждался почти немедленно.
В отличии от строк, столбцы таблицы упорядочиваются и нумеруются, так в таблице № 3 OCLAD первый столбец слева K_O содержит код оклада. Во избежание неоднозначности все столбцы таблицы должны иметь разные имена. Кроме того, он является первичным ключом - важным элементом в структуре базы данных. Каждый столбец имеет определенный тип данных. Все данные конкретного столбца относятся к одному типу: текст, число, дата и т.д., так как содержит однотипную информацию.
//Вывод общее количество кодов оклада // Interbase
SELECT COUNT(*)
FROM OCLAD;
COUNT
=====
// Вывод максимального оклада //MS Access
ELECT DISTINCTROW Max([оклады].[оклад]) AS [Max - оклад]
FROM оклады;
| оклады max |
| Max - оклад |
| 5 999,55р. |
Таблицы DAN, PROF, OCLAD образуют простую реляционную БД, она мала, с ней легко работать, но содержит все необходимые компоненты для иллюстрации основных принципов и приемов применения SQL. Первый столбец каждой таблицы содержит №№, разные для каждой строки (первичные ключи). Некоторые из номеров встречаются в других столбцах таблиц. Это внешние ключи, которые ссылаются на первичные ключи (для удобства имеют одинаковые имена). Выполнение условия «прикрепления» таблиц, говорит, что система находится в состоянии «ссылочной целостности»
Хранимые процедуры
база файл триггер информация
Это тоже элемент базы данных, наряду с таблицами. Это специальная программа, написанная на так называемом языке процедур и триггеров и хранимая в качестве части базы данных. Поскольку мы создали хранимую процедуру, мы можем напрямую вызвать на выполнение из своего приложения. Хранимая процедура может получать входные параметры и возвращать определенные значения приложению. Язык процедур и триггеров включает в себя операторы SQL и некоторые расширения, такие как IF THEN ELSE, MHILE DO, FOR SELECT DO, исключения и обработку ошибок.
Преимущества использования хранимых процедур:
· Модульное оформление;
· Приложения, обращающиеся к одной и той же базе данных, могут делит между собой хранимые процедуры (использовать одну и ту же хранимую процедуру), избегая дублирования кода (т.е. нет необходимости в повторном коде хранимой процедуры) и снижая таким образом размер самих приложений;
· Рациональная поддержка. Когда процедура обновляется, ее изменения автоматически отражаются во всех приложениях, которые ее используют, и новая компиляция этих приложений не требуется. Приложения только однажды компилируются для каждого клиента.
· Улучшение работы. И это – основное достоинство этого инструмента. Хранимые процедуры выполняются сервером, а не клиентом, что снижает загрузку сети и улучшает её работу, особенно в условиях клиентского доступа. Например, в системе управления кадрами, когда общая база данных хранится на сервере и работники отдела кадров и руководство предприятия пользуются ее услугами, у некоторого клиента возникает потребность получить аналитические данные в некотором разрезе. При наличии в БД хранимой процедуры, получающей требуемую аналитику, нет нужды клиенту вытаскивать к себе все необходимые данные для расчета, а достаточно вызвать на сервере соответствующую хранимую процедуру, передав ей необходимые параметры, и эта процедура, на месте исполнив всю работу и не перегоняя большое количества данных по сети клиенту, выдаст ему только результат в виде небольшой таблицы. Этой же процедурой может воспользоваться и другой клиент и получить для себя такую же аналитику.
· Работа с хранимыми процедурами
С помощью интерактивного SQL можно создавать, модифицировать и удалять процедуры и исключения. Существуют два способа создавать, видоизменять и удалять хранимые процедуры с помощью интерактивного SQL:
· Интерактивно;
· С помощью входного файла, содержащего операторы определения данных.
Обычно предпочтение отдают файлам, им легче модифицировать и обеспечивать для них соответствующую документацию. Для простых модификаций существующих процедур более подходит интерактивный режим. Пользователь, который создает процедуру, является собственником и может передавать привилегии исполнения процедуры другим пользователем, триггерам и хранимым процедурам.
Хранимые процедуры в базах данных, преимущества использования, CREATE PROC, мастер Create Stored Procedure Wizard, макросы и модули VBA в Microsoft Access
Хранимые процедуры - это сохраненные как объекты баз данных блоки кода на языке SQL. В важных базах хранимые процедуры используются очень активно. Часто на них реализуют всю бизнес-логику приложений и практически любое внесение изменений в базы данных должно производиться только при помощи хранимых процедур. Что дают нам хранимые процедуры:
модульность разработки приложений;
так же, как и представления, позволяют защитить клиентские приложения от изменений в структуре таблиц в базах данных;
позволяют реализовывать дополнительную логику проверок, протоколирования действий пользователей и т.п.;
позволяют производить каскадные обновления;
в отличие от представлений, хранимые процедуры могут принимать и возвращать параметры;
обеспечивают очень мощные механизмы обеспечения безопасности;
позволяют повысить производительность - за счет использования откомпилированных планов хранимых процедур из кэша и за счет сокращения сетевого трафика при отправке больших наборов команд;
при помощи хранимых процедур можно реализовывать изощренные схемы обработки ошибок на сервере.
Создание хранимых процедур производится на SQL Server командой CREATE PROC или - на графическом интерфейсе - в Enterprise Manager из контейнера Stored Procedures или при помощи мастера Create Stored Procedure Wizard.
В MS Access как таковых хранимых процедур нет, но их роль могут выполнять:
макросы (типа "Запуск запроса SQL")
модули VBA.
· Использование файла определения данных
Для создания и изменения процедуры с использованием файла определения данных используют текстовой редактор, что бы записать такой файл, затем сохраняют файл и исполняют с помощью интерактивного SQL утилиты IBConsole, в которой вставляют данные из файла, либо используют команду isgl в командной строке (режим DOS), задавая команду в виде:
C:\ISGL-INPUT FILENAME DATABASE_NAME.
Здесь FILENAME – это имя файла определения данных, а DATABASE_NAME – имя используемой БД. Файл определения данных должен включать:
· Операторы создания, модификации и удаления процедур и исключений;
· Любые другие ISGL – операторы. Исключения должны созданы до ссылки на них в процедурах.
Хранимая процедура меняет код профессии в таблице DAN соответствующий переменной MEN на код соответствующий переменной NOW.
SET TERM ^;