Базы данных

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

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

Содержание

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

  • Фрагмент кода, хранящийся в БД
    • Процедуры
    • Функции
  • Достоинства
    • Обработка данных на стороне БД
    • Возможность компиляции при объявлении
    • Защита данных
  • Недостатки
    • Специфичный язык

Создание хранимой процедуры

  • Синтаксис
    • create procedure имя(параметры)
      [language {sql|...}]
      [[not] deterministic]
      {contains sql|reads sql data|modifies sql data|no sql}
      [{returns null|called} on null input]
      [sql security {definer|invoker}]
      тело
      
  • Другие поддерживаемые языки
    Ada, C, COBOL, Fortran, MUMPS, Pascal, PLI

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

  • create procedure s() 
    language sql reads sql data
    select * from Students;
    
  • create procedure d(id int)
    delete from Students where SId = id
    
  • create procedure t(id int)
    begin
        ...
    end
    

Параметры

  • Синтаксис
    • {in|out|inout|} имя тип
      
  • Типы параметров
    • in — входной (по умолчанию)
    • out — результат
    • inout — входной и результат
  • Примеры
    • in sid int
      
    • out StudentName varchar(20)
      

Вызов процедуры

  • Синтаксис
    • exec имя(аргументы)
      
  • Пример
    • exec ChangeName('Иванов', 'Петров');
      

Управление доступом

  • Привилегия execute
  • Права доступа
    • sql security definer — создателя
    • sql security invoker — вызывающего

Разделители

  • Разделитель по умолчанию
    • ;
      
  • Тело содержит несколько операторов
    • Что делать с разделителями?
  • Сделать другой разделитель
    • delimiter //
      

Составной и присваивания

Содержание

Составной оператор

  • Синтаксис
    • [метка:] begin
          определения
          тело
      end
      
  • Пример
    • begin
          update Accounts set Balance = Balance - Amount
              where Id = fromId;
          update Accounts set Balance = Balance + Amount
              where Id = toId;
      end;
      

Определение переменных

  • Синтаксис
    • declare имя тип [default значение];
      
  • Примеры
    • declare id int;
      
    • declare finish datetime default now();
      

Присваивание

  • Синтаксис
    • set переменная = значение
          [, переменная = значение]*;
      
  • Примеры
    • set idx = 0;
      
    • set idx = idx + 1;
      
    • set name = (select FirstName from Students
                          where SId = id);
      

Запрос

  • Синтаксис
    • select ...
      into переменные
      from ...
      
  • Пример
    • select FirstName, LastName
      into FN, LN
      from Students
      where SId = id;
      

Ветвление 1

  • Синтаксис
    • if условие then операторы
      [else if условие then операторы]*
      [else операторы]
      end if;
      
  • Пример
    • if (select Balance from Accounts) > amount then
          update Accounts set Balance = Balance - amount
      end if;
      

Ветвление 2

  • Синтаксис
    • case выражение
          [when значение then операторы]+
          [else операторы]
      end case;
      
  • Пример
    • case name
          when 'Иван' then set name = 'Ивана'
          when 'Пётр' then set name = 'Петра'
          else set name = '???'
      end case;
      

Ветвление 3

  • case не только оператор, но и операция
  • Пример
    • set name = case name
         when 'Иван' then 'Ивана'
         when 'Пётр' then 'Петра'
         else '???'
      end case;
      

Цикл while

  • Синтаксис
    • [метка:] while условие do
          операторы
      end while [метка];
      
  • Примеры
    • while i < 0 do
          set i = i + 1;
      end while;
      
    • declare i int default 1;
      ins: while i <= 10 do
          insert into Students (Id) values (i);
          set i = i + 1;
      end while ins;
      

Цикл repeat

  • Синтаксис
    • [метка:] repeat
          операторы
      until условие
      end repeat [метка];
      
  • Пример
    • declare i int default 1;
      repeat
          insert into Students (Id) values (i);
          set i = i + 1;
      until i >= 10
      end repeat;
      

Цикл loop

  • Синтаксис
    • [метка:] loop
          операторы
      end loop [метка];
      
  • Пример
    • declare i int default 1;
      loop
          insert into Students (Id) values (i);
          set i = i + 1;
      end loop;
      

break и continue

  • Синтаксис
    • leave метка;
      
    • iterate метка;
      
  • Пример
    • declare i int default 1;
      l: loop
          insert into Students (Id) values (i);
          if i > 10 then
              leave l;
          end if;
          set i = i + 1;
      end loop;
      

Обработчики ошибок

  • Синтаксис
    • declare {continue|exit|undo} handler
      for условие
      оператор
      
    • continue — продолжить выполнение
    • exit — выйти из блока
    • undo — выйти из блока с откатом изменений
  • Условия
    • sqlstate [value] 'номер'
      
    • sqlwarning    -- sqlstate 01xxx
      
    • not found      -- sqlstate 02xxx
      
    • sqlexception -- sqlstate yyxxx
      

Пример обработчика

  • Подавление ошибки
    • begin
          declare exit handler for not found begin end;
          ...
      end;
      
  • Подмена значения
    • begin
          declare continue handler for not found set name = '?';
          set name = (select FirstName from students
              where SId = id);
          ...
      end;
      

Курсоры

Содержание

Курсор

  • Способ перебора результатов запроса
    • Много строк
  • Аналог итераторов

Объявление курсора

  • Синтаксис
    • declare имя
      {sensitive|insensitive|asensitive} 
      [[no] scroll]
      cursor select...
      [ for {read only|update [of столбцы]}];
      
  • Примеры
    • declare SCursor cursor
      select * from Students
      order by SId;
      
    • declare SCursor cursor
      select * from Students where GId = 'M3439'
      for update of SName;
      

Открытие и закрытие

  • Синтаксис
    • open курсор;
      
    • close курсор;
      
  • Переинициализация при повторном открытии
  • Автоматическое закрытие при выходе

Получение данных

  • Синтаксис
    • fetch
      [next|prior|last|first|absolute n|relative n]
      курсор into переменные;
      
  • Отсутствие данных
    • not found
    • sqlstate '02000'
  • Пример
    • fetch SCursor into SId, FirstName, LastName;
      

Позиционированные изменения

  • Синтаксис
    • update таблица set что where current of курсор;
      
    • delete from таблица where current of курсор;
      
  • Пример
    • if FirstName = 'Иван' then
          delete from Students where current of SCursor;
      end if;
      

Пример применения курсора

  • create procedure updateNameStat(name)
    begin
        declare cnt int default 0;
        declare exit handler for sqlstate not found begin
            insert into Stats (Name, Cnt) values (name, cnt);
        end;
        declare SCursor cursor
            select FirstName from Students;
        open SCursor;
        loop
            declare sname varchar(20);
            fetch SCursor into sname;
            if name = sname then set cnt = cnt + 1; end if;
        end loop;
    end;
    

Особенности курсоров

  • Внешние курсоры
  • Размер выборки
    • Малый — много повторных обращений
    • Большой — требуется много памяти
  • Блокировка ресурсов

Хранимые функции

Содержание

Хранимые функции

  • Возвращает значение
    • Вызов в выражениях

Создание

  • Синтаксис
    • create [aggregate] function
      имя(параметры)
      returns тип
      тело
      
  • Пример
    • create function add(a int, b int) returns int
          return a + b;
      

Особенности

  • Обращение к таблицам
    • Часто запрещено
  • Возвращаемые значения
    • Встроенные типы
    • Редко UDT и таблицы

Литература

Содержание

Основная литература