Хранимые процедуры
Процедура на доб. Записей
CREATE TABLE city(
cityID int PRIMARY key AUTO_INCREMENT,
cityname varchar(20) UNIQUE,
population int);
Insert into city (cityname, population)
VALUES(‘Tallinn’, 650000);
select* from City
cityadd
BEGIN
INSERT INTO city(cityname, population)
VALUES(name,num );
SELECT * from city;
END
Call procedure
CALL cityadd(‘Narva’, 50000);
Searc all city name by the first letter
BEGIN
SELECT cityname, papulation
FROM city
WHERE cityname like concat(letter, ‘%’);
Sql query
Call City(’T’) find a City by first name
END
BEGIN
SELECT * FROM city;
DELETE from city
WHERE cityID=deleteID
SELECT * FROM city;
END
Sql query
Call citydelete (’3’)
Population update
BEGIN
SELECT * from city;
UPDATE city SET population=newnum
WHERE cityname=city
select * from city
END
Classwork
1.Procedure of finding genre of movie by the first letter
BEGIN
SELECT ZanrNimi,zanrKirjeldus
FROM zanr
WHERE ZanrNimi like concat (letter, ‘%’) ;
END
Screen shows up that genre with the first letter K is found (Comedy and Criminal)
2.We can delete price of tickets
BEGIN
SELECT * FROM kinokava;
DELETE from kinokava
WHERE kinokavaID=deleteID
SELECT * FROM kinokava;
2.Price ticket with ID 3 was delete with the help of the query call priceticket_delete(’3’)
3.Opprtunity to add new lnguage
BEGIN
INSERT INTO keel(keelNimi)
VALUES(name );
SELECT * from keel;
END
No responses yet