Protseduurid SQL-is - Darja Suhhanova (Portnova)

IN Microsoft SQL Server Managment Studio

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;

Creating a procedure (In new Query):

USE suhhanovaDatabase;
GO
CREATE PROCEDURE helloKool
AS
BEGIN
	SELECT * FROM kool;
END;

Calling a procedure:

USE suhhanovaDatabase;
EXEC helloKool;

Deleting a procedure:

drop procedure helloKool;

The user enter the first letter of the school, and then all the schools data is displayed:

USE suhhanovaDatabase;
GO
CREATE PROCEDURE kooliOtsing
	@taht varchar(1)
AS
BEGIN
	SELECT * FROM kool
	WHERE koolNimi LIKE @taht + '%';
END;

--Calling a procedure:
EXEC kooliOtsing @taht='T';

The user enters only table values and then the table is filled:

USE suhhanovaDatabase;
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;

--calling
EXEC lisaKool 'Aianduskool', 'Räpina', '12345678', '8456484';

Deletes one record or row by ID:

USE suhhanovaDatabase;
GO
CREATE PROCEDURE kustutaKool
	@deleteID int
AS
BEGIN
	SELECT * FROM kool;
	DELETE FROM kool
	WHERE id=@deleteID;
	SELECT * FROM kool;
END;

--removing
EXEC kustutaKool 1;

Request to delete a column (DROP COLUMN) or add a column (ADD):

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='opilasteArv', @tyyp='int';
EXEC muudatus @tegevus='add', @tabelinimi='kool', @veerunimi='test', @tyyp='int';

Update data in column ‘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=5000, @uuendatudID=5
EXEC uuendaKool @arv=3040, @uuendatudID=4
EXEC uuendaKool @arv=180, @uuendatudID=2
...

IN XAMPP:

CREATE TABLE kool(
id int primary key AUTO_INCREMENT,
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;

select the baas => funktsioonid => create new procedure (in creating procedure will be automaticly variables, if there isn’t any var then you can delete that varable)

BEGIN
	SELECT * FROM kool;
END;

‘teosta’:

BEGIN
	SELECT * FROM kool
	WHERE koolNimi LIKE CONCAT(taht, '%');
END;

Deleting:

Updating a table:

‘TEOSTA’: