Проектирование. 380305БИ_Проектирование баз данных_лабораторные_ПоповВБ.docx. Лабораторная работа 1. Интерфейс субд mysql. Предоставление доступа и привилегий. Утилиты, входящие в состав субд цель работы
 Скачать 1.15 Mb.
|
|
Создание процедур и функций CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name type type: Any valid MySQL data type characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' routine_body: Внесение изменений ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...] characteristic: { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' Удаление процедур и функций DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name Вызов процедур и функций CALL sp_name([parameter[,...]]) CALL sp_name[()] Оператор CALL позволяет вызвать ранее определенную процедуру. Пример1 CREATE PROCEDURE p1 (OUT ver_param VARCHAR(25), INOUT incr_param INT) BEGIN # Set value of OUT parameter SELECT VERSION() INTO ver_param; # Increment value of INOUT parameter SET incr_param = incr_param + 1; END; Перед вызовом процедуры инициализируйте переменную указанные в параметрах INOUT . После вызова процедуры значения будут установлены или изменены. mysql> SET @increment = 10; mysql> CALL p(@version, @increment); mysql> SELECT @version, @increment; +-----------------+------------+ | @version | @increment | +-----------------+------------+ | 5.1.12-beta-log | 11 | Пример2 CREATE PROCEDURE `p2`(IN param1 CHAR(2) ) NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT '' BEGIN select * from s where snum=param1; END; Вызов процедуры call p2 ('S1') Пример3 CREATE PROCEDURE `My_proc2`(IN param1 CHAR(2) ) BEGIN /* start of block */ DECLARE variable1 CHAR(10); /* variables */ IF param1 = 17 THEN /* start of IF */ SET variable1 = 'birds'; /* assignment */ ELSE SET variable1 = 'beasts'; /* assignment */ END IF; /* end of IF */ select variable1; /* statement */ END Вызов процедуры call p3 (10) Триггеры Три́ггер (англ. trigger) — это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено наступлением определенного события (действием) — по сути добавлением INSERT или удалением DELETE строки в заданной таблице, или модификаци UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции. Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанного с ним события; например, до добавления записи) или AFTER (после события). В случае, если триггер вызывается до события, он может внести изменения в модифицируемую событием запись (конечно, при условии, что событие — не удаление записи). Некоторые СУБД накладывают ограничения на операторы, которые могут быть использованы в триггере (например, может быть запрещено вносить изменения в таблицу, на которой «висит» триггер, и т. п.) Кроме того, триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления». В этом случае ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит. CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt Пример создания и работы триггера: CREATE TABLE t22 (s1 INTEGER) CREATE TRIGGER t22_bi BEFORE INSERT ON t22 FOR EACH ROW BEGIN SET @x = ‘Trigger was activated!’; SETNEW.s1 = 55; END; После этого при выполнении запросов получим: mysql> INSERT INTO t22 VALUES (1) mysql> SELECT @x, t22.* FROM t22 // вызывается триггер +------------------------+------+ | @x | s1 | +------------------------+------+ | Trigger was activated! | 55 | +------------------------+------+ 1 row in set (0.00 sec) Словарь данных Иметь доступ к значениям метаданных – совершенно необходимое требование к современной СУБД. Ранее такая возможность в MySQL достигалась различными SHOW-командами, но такой подход имеет очевидные недостатки. Эти команды нельзя использовать в простых запросах с соединениями, и, что существенно, они не соответствовали стандартам, будучи специфичными для MySQL. В новой версии СУБД появилась новая служебная база данных – INFORMATION_SCHEMA. Её наличие продиктовано тем же стандартом SQL:2003, и именно она решает задачу реализации словаря данных (data dictionary). INFORMATION_SCHEMA содержит таблицы, описывающие состояние и параметры сервера, в том числе определения и сущности таблиц. Это виртуальная база данных – физически (в виде файлов на диске) она не существует, вся информация динамически предоставляется сервером. Пример использования этой таблицы: mysql> SELECT table_name, table_type, engine -> FROM INFORMATION_SCHEMA.tables -> WHERE table_schema = ‘tp’ -> ORDER BY table_type ASC, table_name DESC; +------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | t2 | BASE TABLE | MyISAM | | t1 | BASE TABLE | InnoDB | | v1 | VIEW | NULL | +------------+------------+--------+ Другой пример работы со словарём данных – просмотр привелегий: mysql> SELECT * FROM -> INFORMATION_SCHEMA.COLUMN_PRIVILEGES\G ************************ 1. row ************************ GRANTEE: 'peter'@'%' TABLE_CATALOG: NULL TABLE_SCHEMA: tp TABLE_NAME: t1 COLUMN_NAME: col1 PRIVILEGE_TYPE: UPDATE IS_GRANTABLE: NO ************************ 2. row ************************ GRANTEE: 'trudy'@'%' TABLE_CATALOG: NULL TABLE_SCHEMA: tp TABLE_NAME: t2 COLUMN_NAME: col1 PRIVILEGE_TYPE: SELECT IS_GRANTABLE: YES Объявление переменных Объявление. DECLARE Local Variables Следующая команда позволяет объявлять локальные переменные, содержит возможность задания значения по умолчанию. Переменная может быть объявлена как выражения, не обязательна константа. Если значение по умолчанию не определено то равно NULL. DECLARE var_name[,...] type [DEFAULT value] Присваивание Variable SET Statement SET var_name = expr [, var_name = expr] ... SELECT ... INTO Statement Оператор SELECT может перенаправить результат в переменные. Таким образом может быть преобразована только одна строка. ПРИМЕР SELECT col_name[,...] INTO var_name[,...] table_expr SELECT id,data INTO x,y FROM test.t1 LIMIT 1; Условия и ограничения Объявление условий DECLARE condition_name CONDITION FOR condition_value condition_value: SQLSTATE [VALUE] sqlstate_value | mysql_error_code Объявление ограничений DECLARE handler_type HANDLER FOR condition_value[,...] statement handler_type: CONTINUE | EXIT | UNDO condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code Пример mysql> CREATE TABLE test.t (s1 int,primary key (s1)); Query OK, 0 rows affected (0.00 sec) mysql> delimiter // mysql> CREATE PROCEDURE handlerdemo () -> BEGIN -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; -> SET @x = 1; -> INSERT INTO test.t VALUES (1); -> SET @x = 2; -> INSERT INTO test.t VALUES (1); -> SET @x = 3; -> END; -> // Query OK, 0 rows affected (0.00 sec) mysql> CALL handlerdemo()// Query OK, 0 rows affected (0.00 sec) mysql> SELECT @x// +------+ | @x | +------+ | 3 | +------+ 1 row in set (0.00 sec) Если вы хотите игнорировать условие вы должны сгенерировать ограничение и ассоциировать его с пустым блоком . DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END; Пример CREATE PROCEDURE p () BEGIN DECLARE i INT DEFAULT 3; retry: REPEAT BEGIN DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN ITERATE retry; # illegal END; END; IF i < 0 THEN LEAVE retry; # legal END IF; SET i = i - 1; UNTIL FALSE END REPEAT; END; Курсоры Курсор — в некоторых реализациях языка программирования SQL (Oracle, Microsoft SQL Server) — получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. Курсор может возвращать одну строку, несколько строк или ни одной строки. Для запросов, возвращающих более одной строки, можно использовать только явный курсор. Для повторного создания результирующего набора для других значений параметров курсор следует закрыть, а затем повторно открыть. Курсор может быть объявлен в секциях объявлений любого блока PL/SQL, подпрограммы или пакета. Операторы управления явным курсором Оператор CURSOR выполняет объявление явного курсора. Оператор OPEN открывает курсор, создавая новый результирующий набор на базе указанного запроса. Оператор FETCH выполняет последовательное извлечение строк из результирующего набора от начала до конца. Оператор CLOSE закрывает курсор и освобождает занимаемые им ресурсы Курсоры поддерживают хранимые процедуры и функции. Сейчас курсоры имеют три свойства: Asensitive: The server may or may not make a copy of its result table Read only: Not updatable Non-scrollable: Can be traversed only in one direction and cannot skip rows Курсоры должны быть объявлены перед объявлением ограничений. Переменные и условия должны быть объявлены перед курсором. Объявление курсоров Оператор объявления курсора. М программе можно объявлять несколько курсоров, каждый курсор в блоке должен иметь уникальное имя. DECLARE cursor_name CURSOR FOR select_statement Условие открытия Cursor OPEN Statement Оператор открывает ранее объявленный курсор. OPEN cursor_name Выполнение курсора Cursor FETCH Statement FETCH cursor_name INTO var_name [, var_name] ... Условия закрытия Cursor CLOSE Statement CLOSE cursor_name Пример CREATE PROCEDURE curdemo() BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE b,c INT; DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; DECLARE cur2 CURSOR FOR SELECT i FROM test.t2; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; OPEN cur2; REPEAT FETCH cur1 INTO a, b; FETCH cur2 INTO c; IF NOT done THEN IF b < c THEN INSERT INTO test.t3 VALUES (a,b); ELSE INSERT INTO test.t3 VALUES (a,c); END IF; END IF; UNTIL done END REPEAT; CLOSE cur1; CLOSE cur2; END Задание Представления Составить представление, возвращающее объем поставок деталей для изделий за заданный календарный месяц Добавить столбец стоимость детали в таблицу SPJ. Создать соответствующее представление (наименование поставщика, наименование детали, наименование изделия, стоимость детали, количество, стоимость поставки. Добавить столбец стоимость детали в таблицу P. Создать представление отражающее стоимость поставки. Процедуры Составить процедуру, отражающую состав изделия (детали изделия). Составить процедуру, возвращающую расчетную стоимость изделия, учитывая, что для изделия требуется K деталей каждого требуемого наименования (см. табл 1). Составить процедуру, отражающую вес изделия (п4) учитывая что для изделия требуется K деталей каждого требуемого наименования (см. табл 1). С помощью условных операторов разделить всех поставщиков на три категории по количеству поставляемых деталей (ABC анализ) 20 40 60 % Тоже, но по стоимости поставки Определить оптимального поставщика для изделия (см табл 1) для производства максимального количества изделий за период Определить оптимального поставщика для изделия (см табл 1) для производства максимального количества изделий по минимальной стоимости При условии, что поставщик может поставлять не более одной поставки в неделю, а максимальное количество деталей в поставке не выше среднего за период Функции С помощью функций получить таблицу, отражающую информацию о перечне изделий, для которого выполняется поставка
Тоже, но с наименованиями изделий. С помощью функций получить таблицу, отражающую информацию о перечне деталей из которых состоит дневная поставка Тоже, но с наименованиями деталей Получить наименование поставщика поставляемого самое большое количество деталей Получить наименование поставщика поставляемого самое большое количество деталей, для какого либо изделия Работа с текстовым файлом Создать текстовый файл, содержащий информацию о поставщике, поставившего за последний месяц деталей на большую сумму и меньшего веса. Курсоры При заполнении поставки поле дата всегда заполнять текущей датой. Таблица 11 -Варианты
|