Базы данных Тема 4. 1 СУБД. Встроенный и процедурный SQL
Классификация СУБД
СУБД n n Система управления базами данных (СУБД) – это комплекс программных и языковых средств, необходимых для создания баз данных, поддержания их в актуальном состоянии и организации поиска в них необходимой информации. Мир современных СУБД весьма обширен. СУБД можно различать по компаниям-разработчикам, которые их произвели, по моделям данных, которые они используют, по производительности и мощности, по комплексу услуг, которые они предоставляют пользователям. Рассмотрим классификацию СУБД по возможностям работы в сети. Соответственно, можно различить так называемые настольные СУБД, которые не рассчитаны для работы в сети и многопользовательские СУБД или серверы баз данных. Настольные СУБД изначально предназначались для работы одного пользователя за одним компьютером. Они могут работать в сети, но при увеличении количества пользователей их производительность резко падает. Это СУБД хорошего качества, предназначенные для работы с небольшими централизованными базами данных. К ним относятся такие СУБД, как Access, For. Pro, Paradox и т. д. Средства СУБД Access и Fox. Pro могут быть использованы для создания клиентской части приложений для серверов баз данных. Серверы баз данных это очень мощные профессиональные СУБД, изначально предназначенные для работы большого количества пользователей в сети с одними и теми же данными. При этом сами данные могут быть распределены по разным узлам сети. К серверам баз данных относятся: Oracle, MS SQL Server, Informix, Interbase, My. SQL и т. д.
СУБД n n Понятие сервера и клиента. Архитектура клиент-сервер. Под сервером понимается целый компьютер или процесс(т. е. программа), который обслуживает другие компьютеры или процессы, называемые клиентами. В зависимости от вида предоставляемых услуг различают разные виды серверов: Web- сервер, файловый сервер, сервер баз данных, почтовый сервер, вычислительный сервер и т. д. Таким образом, сервер баз данных это программа, которая предоставляет услуги по работе с базами данных. Сервер баз данных это ядро СУБД, которое постоянно находится в оперативной памяти и выполняет основные функции СУБД по обработке данных. Клиентские программы с нескольких рабочих станций обращаются к программе сервера за услугами по обработке данных. Языком запросов к серверу является язык SQL.
SQL Формы SQL. Особенности встроенного SQL n n Интерактивный SQL; Встроенный SQL: Ø Статический SQL; Ø Динамический SQL.
SQL Схема выполнения операторов SQL •
SQL n n n На первом этапе выполняется синтаксический анализ оператора SQL. На этом этапе проверяется корректность записи SQL-оператора в соответствии с правилами синтаксиса. На втором этапе проверяется корректность параметров оператора SQL: имен отношений, имен полей данных, привилегий пользователя по работе с указанными объектами. Здесь обнаруживаются семантические ошибки. На третьем этапе проводится оптимизация запроса. СУБД проводит разделение целостного запроса на ряд минимальных операций и оптимизирует последовательность их выполнения с точки зрения стоимости выполнения запроса. На этом этапе строится несколько планов выполнения запроса и выбирается из них один — оптимальный для данного состояния БД На четвертом этапе СУБД генерирует двоичную версию оптимального плана запроса, подготовленного на этапе 3. Двоичный план выполнения запроса в СУБД фактически является эквивалентом объектного кода программы. И наконец, только на пятом этапе СУБД реализует (выполняет) разработанный план, тем самым выполняя оператор SQL. Этапы выполнения операторов SQL одни и те же как в интерактивном режиме, так и внутри приложений. Однако при работе с готовым приложением многие этапы СУБД может выполнить заранее.
SQL Формы SQL. Особенности встроенного SQL n n Интерактивный SQL; Встроенный SQL: Ø Статический SQL; Ø Динамический SQL.
SQL n При объединении операторов SQL с базовым языком программирования должны соблюдаться следующие принципы: n n n Операторы SQL включаются непосредственно в текст программы на исходном языке программирования. Исходная программа поступает на вход препроцессора SQL, который компилирует операторы SQL. Встроенные операторы SQL могут ссылаться на переменные базового языка программирования. Встроенные операторы SQL получают результаты SQL-запросов с помощью переменных базового языка программирования. Для присвоения неопределенных значений (NULL) атрибутам отношений БД используются специальные функции. Для обеспечения построчной обработки результатов запросов во встроенный SQL добавляются несколько новых операторов, которые отсутствуют в интерактивном SQL. Операторы манипулирования данными не требуют изменения для их встраивания в программный SQL. Однако оператор поиска (SELECT) потребовал изменений.
![SQL Особенности встроенного SQL • Синтаксис однострочного запроса: SELECT [{ALL | DISTINCT}] <список](https://present5.com/presentation/-42593636_287293987/image-10.jpg "SQL Особенности встроенного SQL • Синтаксис однострочного запроса: SELECT [{ALL | DISTINCT}] <список")
SQL Особенности встроенного SQL • Синтаксис однострочного запроса: SELECT [{ALL | DISTINCT}] <список возвращаемых столбцов> INTO <список переменных базового языка> FROM <список исходных таблиц> [WHERE <условия соединения и поиска>] • Операторы, связанные с многострочными запросами: Ø DECLARE CURSOR Ø OPEN Ø FETCH Ø CLOSE
SQL n Стандартный оператор SELECT возвращает набор данных, релевантный сформированным условиям запроса. В интерактивном SQL этот полученный набор данных просто выводится на консоль пользователя и он может просмотреть полученные результаты. Встроенный оператор SELECT должен создавать структуры данных, которые согласуются с базовыми языками программирования. Во встроенном SQL запросы делятся на 2 типа: n n Однострочные запросы, где ожидаемые результаты соответствуют одной строке данных. Эта строка может содержать значения нескольких столбцов. Многострочные запросы, результатом которых является получение целого набора строк. При этом приложение должно иметь возможность проработать все полученные строки. Значит, должен существовать механизм, который поддерживает просмотр и обработку полученного набора строк. Во встроенный SELECT добавился новый для нас раздел, содержащий список переменных базового языка. Именно в эти переменные будет помещен результат однострочного запроса, поэтому список переменных базового языка должен быть согласован как по порядку, так и по типу и размеру данных со списком возвращаемых столбцов. По правилам любого языка программирования все базовые переменные предварительно описаны в прикладной программе. Для реализации многострочных запросов вводится новое понятие — понятие курсора или указателя набора записей. Для работы с курсором добавляется несколько новых операторов SQL.
SQL n n Оператор DECLARE CURSOR — определяет выполняемый запрос, задает имя курсора и связывает результаты запроса с заданным курсором. Этот оператор не является исполняемым для запроса, он только определяет структуру будущего множества записей и связывает ее с уникальным именем курсора. Этот оператор подобен операторам описания данных в языках программирования. Оператор OPEN дает команду СУБД выполнить описанный запрос, создать виртуальный набор строк, который соответствует заданному запросу. Оператор OPEN устанавливает указатель записей (курсор) перед первой строкой виртуального набора строк результата Оператор FETCH продвигает указатель записей на следующую позицию в виртуальном наборе записей. В большинстве коммерческих СУБД оператор перемещения FETCH реализует более широкие функции перемещения, он позволяет перемещать указатель на произвольную запись, вперед и назад, допускает как абсолютную адресацию, так и относительную адресацию, позволяет установить курсор на первую или последнюю запись виртуального набора. Оператор CLOSE закрывает курсор и прекращает доступ к виртуальному набору записей. Он фактически ликвидирует связь между курсором и результатом выполнения базового запроса. Однако в коммерческих СУБД оператор CLOSE не всегда означает уничтожение виртуального набора записей.
SQL n n Возможности операторов встроенного SQL, описанные ранее, относятся к статическому SQL. В статическом SQL вся информация об операторе SQL известна на момент компиляции. Однако очень часто в диалоговых программах требуется более гибкая форма выполнения операторов SQL. Фактически, сам текст оператора SQL формируется уже во время выполнения программы. Сформированный таким образом текст SQL-оператора поступает в СУБД, которая должна его скомпилировать и выполнить «на лету» , в процессе работы приложения. Если мы снова вернемся к этапам выполнения SQL-операторов, то первые четыре действия, связанные с синтаксическим анализом, семантическим анализом, построением и оптимизацией плана выполнения запроса, выполняются на этапе компиляции. В момент исполнения этого оператора СУБД просто изымает хранимый план выполнения этого оператора и исполняет его. В случае динамического SQL ситуация абсолютно иная. На момент компиляции мы не видим и не знаем текст оператора SQL и не можем выполнить ни одного из четырех обозначенных этапов. Все этапы СУБД должна будет выполнять с ходу, без предварительной подготовки в момент исполнения программы. Конечно, динамический SQL гораздо менее эффективен в смысле производительности, по сравнению со статическим SQL. Поэтому во всех случаях, когда это возможно, необходимо избегать динамического SQL. Но бывают случаи, когда отказ от динамического SQL серьезно усложняет приложение. Например, в случае с поиском по произвольному множеству параметров невозможно заранее предусмотреть все возможные комбинации запросов, даже если возможных параметров два десятка. А если их больше, то именно динамический SQL становится наиболее удобным методом решения необъятной проблемы.
SQL Динамический SQL • Синтаксис оператора непосредственного выполнения: EXECUTE IMMEDIATE <имя_базовой переменной> • Базовая переменная содержит текст SQL оператора. • Синтаксис оператора подготовительного этапа: PREPARE <имя_оператора> FROM <имя_базовой переменной> <имя_оператора> - идентификатор базового языка. • Синтаксис оператора этапа выполнения: EXECUTE <имя__оператора> DEALLOCATE PREPARE <имя_оператора>
SQL n n Оператор непосредственного выполнения пригоден для выполнения операции, которые не возвращают результаты. Так же как в статическом SQL, для работы с множеством записей вводится понятие курсора и добавляются операторы по работе с курсором, и в динамическом SQL должны быть определены подобные структуры. Прежде всего было предложено разделить выполнение SQL-оператора в динамическом SQL на два отдельных этапа. Первый этап называется подготовительным, он фактически включает 4 первых этапа выполнения SQL-операторов, рассмотренные нами ранее: синтаксический и семантический анализ, построение и оптимизация плана выполнения оператора. Этот этан выполняется оператором PREPARE. Далее на втором этапе этот определенный на первом этапе оператор может быть выполнен операцией EXECUTE. Оператор DEALLOCATE PREPARE уничтожает ранее подготовленный план выполнения оператора SQL и освобождает разделяемую память СУБД, связанную с хранением этого плана. Этот оператор имеет смысл применять, если вы не будете больше применять команду выполнения к подготовленному ранее оператору SQL.
SQL Динамический SQL. Пример CREATE DEFINER = 'root'@'localhost' PROCEDURE `new_proc 2`( IN `table_name` CHAR(20), IN `field_name` CHAR(20), IN `maxim` INTEGER ) NOT DETERMINISTIC CONTAINS SQL SECURITY DEFINER COMMENT '' BEGIN SET @dynquery : = CONCAT("select MAX(", field_name, ") into", maxim, " from ", table_name); PREPARE dynamic_query FROM @dynquery; EXECUTE dynamic_query; DEALLOCATE PREPARE dynamic_query; END;
SQL Хранимые процедуры и функции n Cинтаксис хранимой процедуры: CREATE PROCEDURE proc_name ([Параметр. Пр [, …]]) proc _body • Cинтаксис хранимой функции: CREATE FUNCTION func_name ([Параметр. Ф [, …]]) RETURNS тип func _body • • Параметр. Пр : [IN |OUT|INOUT] Имя Тип Параметр. Ф : Имя Тип – любой тип My. SQL proc _body и func _body : Begin …. . End;
SQL n n Хранимые процедуры представляют собой набор команд SQL, которые могут компилироваться и храниться на сервере. Таким образом, вместо того, чтобы хранить часто используемый запрос, клиенты могут ссылаться на соответствующую хранимую процедуру. Это обеспечивает лучшую производительность, поскольку данный запрос должен анализироваться только однажды и уменьшается трафик между сервером и клиентом. Хранимые программы (процедуры и функции) поддерживаются начиная с My. SQL 5. 0. 3 My. SQL следует в синтаксисе за SQL: 2003 для хранимых процедур, который уже используется в IBM's DB 2. Начиная с My. SQL 5. 0. 3 требуются следующие привилегии: n n CREATE ROUTINE для создания хранимых процедур ALTER ROUTINE для изменения или удаления процедур. Эта привилегия автоматически назначается создателю процедуры (функции) EXECUTE привилегия потребуется для выполнения подпрограммы. Тем не менее, автоматически назначается создателю процедуры (функции). Также, по умолчанию, SQL SECURITY параметр для подпрограммы DEFINER , который разрешает пользователям, имеющим доступ к БД вызывать подпрограммы, ассоциированные с этой БД. Хранимые подпрограммы создаются с помощью выражений CREATE PROCEDURE или CREATE FUNCTION.
SQL n Объявление и работа с процедурами и функциями отличаются в следующем: q q n n в заголовке функции помимо описания формальных параметров обязательно указывается тип возвращаемого ею результата; для возврата функцией значения в точку вызова среди ее операторов должен быть хотя бы один, в котором имени функции или переменной Result присваивается значение результата; вызов процедуры выполняется отдельным оператором; вызов функции может выполняться там, где допускается ставить выражение, в частности, в правой части оператора присваивания. Как процедуры, так и функции могут возвращать значения (в виде набора записей). Различие состоит в том, что функция вызывается из запроса, а процедура из отдельной команды. IN-параметр передает значение в процедуру (функцию). В теле подпрограммы он может модифицироваться, но извне эти изменения не видны; OUT-параметр – значение этого параметра возвращается процедурой. Его начальное значение равно NULL. INOUT-параметр – имеет входное значение, может модифицироваться процедурой, возвращается процедурой по ее окончании.
SQL Хранимые процедуры и функции n Простая хранимая процедура с параметром: mysql> DELIMITER // mysql> CREATE PROCEDURE simpleproc (OUT param 1 INT) -> BEGIN -> SELECT COUNT(*) INTO param 1 FROM t; -> END; -> // mysql> DELIMITER ; mysql> CALL simpleproc(@a); mysql> SELECT @a;
SQL Хранимые процедуры и функции Простая функция с параметром: mysql> DELIMITER // mysql> CREATE FUNCTION hello ( s CHAR(20)) RETURNS CHAR(50) -> BEGIN -> RETURN CONCAT(‘Hello, ‘, s , ’!’); -> END; -> // mysql> DELIMITER ; mysql> SELECT hello(‘world’); # 1 -й способ вызова mysql> SET @r = hello(‘world’); # 2 -й способ вызова mysql> SELECT @r; n
SQL Хранимые процедуры и функции n локальные переменные: DECLARE i. Var INT DEFAULT 0; SET i. Var = 5; # 1 -й способ присвоения значения SELECT * FROM `data` WHERE `id` = i. Var; DECLARE i. Var INT DEFAULT 0; SELECT COUNT(*) INTO i. Var # 2 -й способ присвоения значения FROM `data`;
SQL Хранимые процедуры и функции n Операторы, используемые в теле хранимых подпрограмм : IF условие 1 THEN операторы 1; [ELSEIF условие 2 THEN операторы 2] … [ ELSE операторы 3]; END IF; q WHILE условие DO операторы; END WHILE; n И др. q
SQL Хранимые процедуры и функции. Пример CREATE PROCEDURE curdemo() BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE b, c INT; DECLARE cur 1 CURSOR FOR SELECT id, data FROM test. t 1; DECLARE cur 2 CURSOR FOR SELECT i FROM test. t 2; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur 2;
SQL Хранимые процедуры и функции. Пример REPEAT FETCH cur 1 INTO a, b; FETCH cur 2 INTO c; IF NOT done THEN IF b < c THEN INSERT INTO test. t 3 VALUES (a, b); ELSE INSERT INTO test. t 3 VALUES (a, c); END IF; UNTIL done END REPEAT; CLOSE cur 1; CLOSE cur 2; END
SQL n Применение курсора в процедурах осуществляется путем последовательного выполнения следующих шагов: n n n При помощи оператора DECLARE объявляется курсор для отдельного оператора SELECT или для отдельной процедуры. Оператором OPEN производится открытие курсора. Используя оператор FETCH, осуществляется установление указателя на требуемую запись курсора. При этом значения полей текущей записи присваиваются переменным, указываемым в операторе FETCH. Обычно это конструкция помещается в итеративный элемент (проще говоря цикл), который прерывается по некоторому условию. См. пример. В процессе перемещения указателя текущей записи курсора при выходе указателя за пределы курсора устанавливается значение SQLSTATE = 02000. После того как курсор становится ненужным, он закрывается оператором CLOSE.
![SQL Триггеры n Cинтаксис триггера: CREATE [DEFINER = { user | CURRENT_USER }]](https://present5.com/presentation/-42593636_287293987/image-27.jpg "SQL Триггеры n Cинтаксис триггера: CREATE [DEFINER = { user | CURRENT_USER }]")
SQL Триггеры n Cинтаксис триггера: CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body где trigger_name — имя триггера; trigger_time — время наступления события ( BEFORE — до события, AFTER — после события) trigger_event — событие активации триггера (insert , update, delete ). tbl_name – имя таблицы, к которой привязан триггер. trigger_body – действия, которые выполняет триггер.
SQL Триггеры Время наступления события: Ø BEFORE — до события (имя ссылки на запись – OLD); Ø AFTER — после события (имя ссылки на запись – NEW); trigger_event — событие активации триггера n n Cобытия: Ø INSERT — добавление строки (только NEW); Ø UPDATE — изменение строки (OLD и NEW); Ø DELETE — удаление строки (только OLD).
SQL n Триггер (англ. trigger) — это хранимая откомпилированная SQL-процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события внутри базы данных (вставки, удаления, обновления записей). n Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. n Ограничения использования триггеров: n Поддержка триггеров в My. SQL началась с версии 5. 0. 2. n n n Не может быть двух триггеров для данной таблицы, которые имеют те же самые время действия и событие. Например, можно иметь BEFORE UPDATE и BEFORE INSERT или BEFORE UPDATE и AFTER UPDATE. Триггеры в My. SQL может создавать только пользователь с правами SUPER. Нельзя редактировать таблицу, в момент выполнения триггера. В таком случае нужно использовать оператор SET для задания значений необходимым полям или будет ошибка : #1442 -Can’t update table ‘tablename’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger. n при использовании запроса, затрагивающего N - записей, триггер будет запускаться N - раз; n после удаления таблицы, СУБД My. SQL автоматически удаляет привязанные к ней триггеры.
SQL Триггеры n Пример : CREATE TABLE `test` ( `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `content` TEXT NOT NULL ); CREATE TABLE `log` ( `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `msg` VARCHAR( 255 ) NOT NULL, `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `row_id` INT( 11 ) NOT NULL ); CREATE TRIGGER `update_test` AFTER INSERT ON `test` FOR EACH ROW BEGIN INSERT INTO log Set msg = 'insert', row_id = NEW. id; END;
SQL n n n Пример: создадим две таблицы test и log, напишем триггер, который после добавления каждой записи в 1 -ю таблицу будет вести лог этого события. На столбцы таблицы, к которой привязан триггер можно ссылаться с помощью псевдонимов OLD и NEW. OLD. col_name указывает на столбец с именем col_name до изменения или удаления данных. NEW. col_name относится к колонке новой строке после вставки или существующей - сразу после её обновления. Для удаления триггера необходимо выполнить запрос: DROP TRIGGER `update_test`; n Для просмотра триггеров в базе данных используется оператор: SHOW TRIGGERS;
SQL Триггеры
Контрольные вопросы 1. Понятие СУБД. Классификация СУБД. Основные функции реляционной СУБД. 2. Встроенный SQL: назначение, синтаксис оператора SELECT, понятие и назначение курсора, операторы работы с курсором, пример. 3. Встроенный SQL. Статический и динамический SQL: назначение, примеры. 4. Процедурный SQL. Хранимые процедуры: понятие и назначение, синтаксис описания, примеры. 5. Процедурный SQL. Триггеры: понятие и назначение, синтаксис описания, примеры.
Скачать презентацию Базы данных Тема 4 1 СУБД Встроенный и
4 1 СУБД. Встроенный и процедурный SQL.ppt