Базы данных
Хранимые процедуры
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}] тело
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 sid int
out StudentName varchar(20)
exec имя(аргументы)
exec ChangeName('Иванов', 'Петров');
;
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;
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 {continue|exit|undo} handler for условие оператор
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;