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

СУБД/лекция 1 семестр 2 — различия между версиями

Материал из Wiki
Перейти к: навигация, поиск
(Источник)
(Содержимое страницы заменено на «== Слайды == * Файл:Лекция_13.rar»)
Строка 1: Строка 1:
 
== Слайды ==
 
== Слайды ==
* [[Файл:Лекция_14.rar]]
+
* [[Файл:Лекция_13.rar]]
== Источник ==
+
[http://ruseller.com/lessons.php?rub=28&id=1189 Исходный материал]
+
 
+
== Введение в хранимые процедуры MySQL 5 ==
+
В MySQL 5 есть много новых функций, одной из самых весомых из которых является создание хранимых процедур. В этом уроке я расскажу о том, что они из себя представляют, а также о том, как они могут облегчить вам жизнь.
+
 
+
== Введение ==
+
 
+
'''Хранимая процедура''' - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.
+
 
+
Причина их создания ясна и подтверждается частым использованием. С другой стороны, если вы поговорите с теми, кто работает с ними нерегулярно, то мнения разделятся на два совершенно противоположных фланга. Не забывайте об этом.
+
== За (преимущества)==
+
Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
+
Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
+
Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
+
Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.
+
== Против (недостатки)==
+
Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
+
Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
+
Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
+
Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.
+
 
+
Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL - это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.
+
 
+
Кстати, я использую элементарную структуру таблиц, чтобы вам было легче разобраться в этой теме. Я ведь рассказываю о хранимых процедурах, а они достаточно сложны, чтобы вникать еще и в громоздкую структуру таблиц.
+
== Шаг 1: Ставим ограничитель ==
+
 
+
Ограничитель - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.
+
== Шаг 2: Как работать с хранимыми процедурами ==
+
Создание хранимой процедуры
+
<source lang="sql">
+
DELIMITER // 
+
 
+
CREATE PROCEDURE `p2` () 
+
LANGUAGE SQL 
+
DETERMINISTIC 
+
SQL SECURITY DEFINER 
+
COMMENT 'A procedure'
+
BEGIN
+
    SELECT 'Hello World !'; 
+
END//</source> 
+
 
+
 
+
Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.
+
 
+
Названия хранимых процедур чувствительны к регистру. Вам также нельзя создавать несколько процедур с одинаковым названием. Внутри хранимой процедуры не может быть выражений, изменяющих саму базу данных.
+
 
+
== Характеристики хранимой процедуры: ==
+
Language: в целях обеспечения переносимости, по умолчанию указан SQL.
+
Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
+
SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
+
Comment: в целях документирования, значение по умолчанию - ""
+
== Вызов хранимой процедуры ==
+
 
+
Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.
+
<source lang="sql"> CALL stored_procedure_name (param1, param2, ....) 
+
 
+
CALL procedure1(10 , 'string parameter' , @parameter_var);</source>
+
 
+
Изменение хранимой процедуры
+
 
+
В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.
+
Удаление хранимой процедуры1 DROP PROCEDURE IF EXISTS p2;
+
 
+
 
+
Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.
+
== Шаг 3: Параметры ==
+
 
+
Давайте посмотрим, как можно передавать в хранимую процедуру параметры.
+
 
+
'''CREATE PROCEDURE proc1 ()''': пустой список параметров
+
'''CREATE PROCEDURE proc1''' (IN varname DATA-TYPE): один входящий параметр. Слово IN необязательно, потому что параметры по умолчанию - IN (входящие).
+
'''CREATE PROCEDURE proc1''' (OUT varname DATA-TYPE): один возвращаемый параметр.
+
'''CREATE PROCEDURE proc1''' (INOUT varname DATA-TYPE): один параметр, одновременно входящий и возвращаемый.
+
 
+
Естественно, вы можете задавать несколько параметров разных типов.
+
Пример параметра IN
+
<source lang="sql">   DELIMITER // 
+
 
+
CREATE PROCEDURE `proc_IN` (IN var1 INT) 
+
BEGIN
+
    SELECT var1 + 2 AS result; 
+
END//</source> 
+
 
+
Пример параметра OUT
+
<source lang="sql">
+
DELIMITER // 
+
 
+
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) 
+
BEGIN
+
    SET var1 = 'This is a test'; 
+
END //</source> 
+
 
+
Пример параметра INOUT
+
<source lang="sql"> DELIMITER // 
+
 
+
CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) 
+
BEGIN
+
    SET var1 = var1 * 2; 
+
END //</source>
+
 
+
== Шаг 4: Переменные ==
+
 
+
Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока '''BEGIN/END''', вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.
+
 
+
Синтаксис объявления переменной выглядит так:
+
 
+
<source lang="sql"> DECLARE varname DATA-TYPE DEFAULT defaultvalue;</source> 
+
 
+
 
+
Давайте объявим несколько переменных:
+
<source lang="sql">
+
DECLARE a, b INT DEFAULT 5; 
+
 
+
DECLARE str VARCHAR(50); 
+
 
+
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; 
+
 
+
DECLARE v1, v2, v3 TINYINT;</source>
+
 
+
=== Работа с переменными ===
+
 
+
Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:
+
 
+
<source lang="sql">
+
DELIMITER // 
+
 
+
CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) 
+
BEGIN
+
    DECLARE a, b INT DEFAULT 5; 
+
    DECLARE str VARCHAR(50); 
+
    DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; 
+
    DECLARE v1, v2, v3 TINYINT;     
+
 
+
    INSERT INTO table1 VALUES (a); 
+
    SET str = 'I am a string'; 
+
    SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; 
+
END //</source> 
+
 
+
== Шаг 5: Структуры управления потоками ==
+
 
+
MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.
+
Конструкция IF
+
 
+
С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:
+
<source lang="sql"> DELIMITER // 
+
 
+
CREATE PROCEDURE `proc_IF` (IN param1 INT) 
+
BEGIN
+
    DECLARE variable1 INT; 
+
    SET variable1 = param1 + 1; 
+
 
+
    IF variable1 = 0 THEN
+
        SELECT variable1; 
+
    END IF; 
+
 
+
    IF param1 = 0 THEN
+
        SELECT 'Parameter value = 0'; 
+
    ELSE
+
        SELECT 'Parameter value <> 0'; 
+
    END IF; 
+
END //</source> 
+
 
+
Конструкция CASE
+
 
+
CASE - это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.
+
 
+
<source lang="sql"> DELIMITER // 
+
 
+
CREATE PROCEDURE `proc_CASE` (IN param1 INT) 
+
BEGIN
+
    DECLARE variable1 INT; 
+
    SET variable1 = param1 + 1; 
+
 
+
    CASE variable1 
+
        WHEN 0 THEN
+
            INSERT INTO table1 VALUES (param1); 
+
        WHEN 1 THEN
+
            INSERT INTO table1 VALUES (variable1); 
+
        ELSE
+
            INSERT INTO table1 VALUES (99); 
+
    END CASE; 
+
 
+
END // 
+
</source>
+
 
+
или:
+
<source lang="sql"> DELIMITER // 
+
 
+
CREATE PROCEDURE `proc_CASE` (IN param1 INT) 
+
BEGIN
+
    DECLARE variable1 INT; 
+
    SET variable1 = param1 + 1; 
+
 
+
    CASE
+
        WHEN variable1 = 0 THEN
+
            INSERT INTO table1 VALUES (param1); 
+
        WHEN variable1 = 1 THEN
+
            INSERT INTO table1 VALUES (variable1); 
+
        ELSE
+
            INSERT INTO table1 VALUES (99); 
+
    END CASE; 
+
 
+
END //</source>
+
 
+
Конструкция WHILE
+
 
+
Технически, существует три вида циклов: цикл '''WHILE''', цикл '''LOOP''' и цикл '''REPEAT'''. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения '''GOTO'''. Вот пример цикла:
+
 
+
<source lang="sql">
+
DELIMITER // 
+
 
+
CREATE PROCEDURE `proc_WHILE` (IN param1 INT) 
+
BEGIN
+
    DECLARE variable1, variable2 INT; 
+
    SET variable1 = 0; 
+
 
+
    WHILE variable1 < param1 DO 
+
        INSERT INTO table1 VALUES (param1); 
+
        SELECT COUNT(*) INTO variable2 FROM table1; 
+
        SET variable1 = variable1 + 1; 
+
    END WHILE; 
+
END //</source> 
+
 
+
== Шаг 6: Курсоры ==
+
 
+
Курсоры используются для прохождения по набору строк, возвращенному запросом, а также обработки каждой строки.
+
 
+
MySQL поддерживает курсоры в хранимых процедурах. Вот краткий синтаксис создания и использования курсора.
+
 
+
<source lang="sql">1 DECLARE cursor-name CURSOR FOR SELECT ...;      /*Объявление курсора и его заполнение */ 
+
2 DECLARE  CONTINUE HANDLER FOR NOT FOUND          /*Что делать, когда больше нет записей*/ 
+
3 OPEN cursor-name;                                /*Открыть курсор*/ 
+
4 FETCH cursor-name INTO variable [, variable];    /*Назначить значение переменной, равной текущему значению столбца*/ 
+
5 CLOSE cursor-name;                              /*Закрыть курсор*/ 
+
 
+
</source>
+
 
+
В этом примере мы проведем кое-какие простые операции с использованием курсора:
+
 
+
<source lang="sql">
+
DELIMITER // 
+
 
+
CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) 
+
BEGIN
+
    DECLARE a, b, c INT; 
+
    DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; 
+
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; 
+
    OPEN cur1; 
+
 
+
    SET b = 0; 
+
    SET c = 0; 
+
 
+
    WHILE b = 0 DO 
+
        FETCH cur1 INTO a; 
+
        IF b = 0 THEN
+
            SET c = c + a; 
+
    END IF; 
+
    END WHILE; 
+
 
+
    CLOSE cur1; 
+
    SET param1 = c; 
+
 
+
END // 
+
</source>
+
 
+
У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:
+
*'''Не чувствительный:''' открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
+
*'''Доступен только для чтения:''' курсоры нельзя изменять.
+
*'''Без перемотки:''' курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.
+
== Заключение ==
+
 
+
В этом уроке я ознакомил вас с основами работы с хранимыми процедурами и с некоторыми специфическими свойствами, связанными с ней. Конечно, вам нужно будет углубить знания в таких областях, как безопасность, выражения SQL и оптимизация, прежде чем стать настоящим гуру MySQL процедур.
+
 
+
Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.
+
 
+
Данный урок подготовлен для вас командой сайта [ruseller.com]
+
Источник урока: [www.net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/]
+
Перевел: '''''Станислав Протасевич'''''
+
Урок создан: 7 Июля 2011
+

Версия 12:55, 3 марта 2014

Слайды