• Процедуры
• Функции

• Обработка данных на стороне БД
• Возможность компиляции при объявлении
• Защита данных

• Специфичный язык

• 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}]
  тело
  

call имя(аргументы);

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('Иванов', 'Петров');
  

• sql security definer — создателя
• sql security invoker — вызывающего

• ;
  

• Что делать с разделителями?

• [метка:] 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;
  

• if условие then операторы
  [else if условие then операторы]*
  [else операторы]
  end if;
  

• if (select Balance from Accounts) > amount then
      update Accounts set Balance = Balance - amount
  end if;
  

• case выражение
      [when значение then операторы]+
      [else операторы]
  end case;
  

• case name
      when 'Иван' then set name = 'Ивана'
      when 'Пётр' then set name = 'Петра'
      else set name = '???'
  end case;
  

• set name = case name
     when 'Иван' then 'Ивана'
     when 'Пётр' then 'Петра'
     else '???'
  end case;
  

• [метка:] 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
      операторы
  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
      операторы
  end loop [метка];
  

• declare i int default 1;
  loop
      insert into Students (Id) values (i);
      set i = i + 1;
  end loop;
  

• 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 {exit|undo|continue} handler
  for условие
  оператор
  

• exit — выйти из блока
• undo — выйти из блока с откатом изменений
• continue — продолжить выполнение

• 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;
  

signal sqlstate 'номер'

resignal [sqlstate 'номер']

• Много строк

• 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 и таблицы

• Chapter 25 – SQL-Invoked Routine
• Chapter 26 – PSM: Not Just Persistent Stored Modules