SQL procedures | martin rosman blog

Хранимые процедуры – это набор операторов SQL, созданный для удобства использования в программах. Сохраненную процедуру использовать проще, чем каждый раз записывать весь набор входящих в нее операторов SQL. Использование хранимых процедур позволяет ограничить или вообще исключить непосредственный доступ пользователей к таблицам базы данных, оставив пользователям только разрешения на выполнение хранимых процедур, обеспечивающих косвенный и строго регламентированный доступ к данным.

Создание процедур в phpMyAdmin

Пользователь вводит первый символ названия школы и получает все школы с таким названием

Добавление школы в базу данных

Удаление школы из базы данных по указанию ID школы

Измение таблицы. Добавление или удаление колонок

--Создание таблицы kool
create table kool(
id int primary key identity(1,1),
koolNimi varchar(50) unique,
aadress text,
telefon varchar(13),
regNumber varchar(10)
);
insert into kool(koolNimi, aadress)
values ('Tallinna Kool', 'Tehnika 18, Tallinn');
select * from kool;

--Создание процедуры
use procedures;
GO
create procedure helloKool 
AS
BEGIN
	select * from kool;
end;

--Вставка процедуры
use procedures;
exec helloKool;

--Удаление процедуры
drop procedure helloKool;

--Пользователь вводит первый символ названия школы и получает все школы с таким названием
use procedures;
GO
create procedure kooliOtsing
	@taht varchar(1)
AS
BEGIN
	select * from kool
	WHERE koolNimi LIKE @taht + '%';
end;

--Вставка процедуры
EXEC kooliOtsing @taht='T';

--Пользователь вводит значения для таблицы
use procedures;
go
create procedure lisaKool
	@kNimi varchar(50),
	@aadress TEXT,
	@telefon varchar(13),
	@reg varchar(10)
AS
BEGIN
	insert into kool(koolNimi, aadress, telefon, regNumber)
	values (@kNimi, @aadress,@telefon, @reg);
	select * from kool;
END;

--Вставка процедуры
exec lisaKool 'Aianduskool', 'Rapina', '37256841075', '1234567';
exec lisaKool 'TehnikaKool', 'Maardu', '37256474859', '1234568';
exec lisaKool 'AutoKool', 'Narva', '37256474549', '1234569';

--Удаление одной колонки
use procedures;
go
create procedure kustutaKool
	@deleteId int
as
begin
	select * from kool;
	delete from kool
	where id=@deleteId
	select * from kool;
end;

--Вставка процедуры
exec kustutaKool 1;

--Процедура запрашивает действие
--Удалить или добавить колонку
CREATE PROCEDURE muudatus
	@tegevus varchar(10),
	@tabelinimi varchar(25),
	@veerunimi varchar(25),
	@tyyp varchar(25) =null
AS
BEGIN
	DECLARE @sqltegevus as varchar(max)
set @sqltegevus=case 
	when @tegevus='add' then concat('ALTER TABLE ', @tabelinimi, ' ADD ', @veerunimi, ' ', @tyyp)
	when @tegevus='drop' then concat('ALTER TABLE ', @tabelinimi, ' DROP COLUMN ', @veerunimi)
END;
	print @sqltegevus;
begin 
	EXEC (@sqltegevus);
END
END;
--добавление столбца
EXEC muudatus @tegevus='add', @tabelinimi='kool', @veerunimi='test', @tyyp='int';
--удаление столбца
EXEC muudatus @tegevus='drop', @tabelinimi='kool', @veerunimi='test';
select * from kool;

--update opilasteArv by ID
create procedure uuendaKool
	@arv int,
	@uuendatudid int
AS
BEGIN
	select * from kool;
	update kool set opilasteArv = @arv WHERE id=@uuendatudid;
	select * FROM kool;
END;

exec uuendaKool @arv=1000, @uuendatudid=2