1. MySQL – пример со следующей страницы:
–1. Loomine
CREATE TABLE developers(
ID INT PRIMARY KEY,
NAME VARCHAR(100),
SPECIALTY VARCHAR(50),
EXPERIENCE INT,
SALARY INT
);
INSERT INTO developers(ID, NAME, SPECIALTY, EXPERIENCE, SALARY)
VALUES
(1, 'Eugene Suleimanov', 'Java', 2, 2500),
(2, 'Peter Romanenko', 'Java', 3, 3500),
(3, 'Andrei Komarov', 'C++', 3, 2500),
(4, 'Konstantin Geiko', 'C#', 2, 2000),
(5, 'Asya Suleimanova', 'UI/UX', 2, 1800),
(6, 'Ludmila Geiko', 'UI/UX', 2, 1800),
(7, 'Ivan Ivanov', 'C#', 1, 900);
–2. Transaction Rollback
begin Transaction
DELETE FROM developers
WHERE SPECIALTY = 'C#';
select * from developers
ROLLBACK;
select * from developers
–3. Savepoint Rollback
BEGIN TRANSACTION;
SELECT * FROM developers
SAVE TRANSACTION SP1;
DELETE FROM developers WHERE ID = 7;
DELETE FROM developers WHERE ID = 6;
DELETE FROM developers WHERE ID = 5;
SELECT * FROM developers
ROLLBACK TRANSACTION SP1;
SELECT * FROM developers
2.SQL Server / XAMPP – видео относительно SQL транзакции и применить его к своей БД.
hinne – 5
SQL (VAJUTA SIIA)
TABEL
CREATE TABLE developers(
ID INT PRIMARY KEY,
NAME VARCHAR(100),
SPECIALTY VARCHAR(50),
EXPERIENCE INT,
SALARY INT
);
INSERT INTO developers(ID, NAME, SPECIALTY, EXPERIENCE, SALARY)
VALUES
(1, 'Eugene Suleimanov', 'Java', 2, 2500),
(2, 'Peter Romanenko', 'Java', 3, 3500),
(3, 'Andrei Komarov', 'C++', 3, 2500),
(4, 'Konstantin Geiko', 'C#', 2, 2000),
(5, 'Asya Suleimanova', 'UI/UX', 2, 1800),
(6, 'Ludmila Geiko', 'UI/UX', 2, 1800),
(7, 'Ivan Ivanov', 'C#', 1, 900);
PROCEDUUR
CREATE PROCEDURE UpdateDeveloper
@ID INT,
@NewSalary INT,
@NewExperience INT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
SELECT * from developers WHERE ID = @ID
UPDATE developers
SET
SALARY = @NewSalary,
EXPERIENCE = @NewExperience
WHERE ID = @ID;
COMMIT TRANSACTION;
SELECT * from developers WHERE ID = @ID
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH
END;
--Proceduur kasutamine
EXEC UpdateDeveloper
@ID = 3,
@NewSalary = 500,
@NewExperience = 2;
CREATE PROCEDURE UpdateDeveloper — loob protseduuri.
AS BEGIN — protseduuri algus.
BEGIN TRY — plokk, kus teostatakse toimingud, millel on viga töötlemise võimalus.
BEGIN TRANSACTION — tehingu algus operatsioonide rühmitamiseks.
SELECT * FROM developers WHERE ID = @ID — valib arendaja andmed enne uuendamist.
COMMIT TRANSACTION — fikseerib muudatused, kui kõik on õnnestunud.
SELECT * FROM developers WHERE ID = @ID — valib uuendatud arendaja andmed.
END TRY — lõppeb TRY plokk.
BEGIN CATCH — algab viga töötlemise plokk.
ROLLBACK TRANSACTION — tagastab muudatused, kui ilmneb viga.
END CATCH — lõppeb CATCH plokk.
END — protseduuri lõpp.
XAMPP (VAJUTA SIIA)
TABEL
CREATE TABLE developers (
ID INT PRIMARY KEY,
NAME VARCHAR(100),
SPECIALTY VARCHAR(50),
EXPERIENCE INT,
SALARY INT
);
INSERT INTO developers (ID, NAME, SPECIALTY, EXPERIENCE, SALARY)
VALUES
(1, 'Eugene Suleimanov', 'Java', 2, 2500),
(2, 'Peter Romanenko', 'Java', 3, 3500),
(3, 'Andrei Komarov', 'C++', 3, 2500),
(4, 'Konstantin Geiko', 'C#', 2, 2000),
(5, 'Asya Suleimanova', 'UI/UX', 2, 1800),
(6, 'Ludmila Geiko', 'UI/UX', 2, 1800),
(7, 'Ivan Ivanov', 'C#', 1, 900);
PROCEDUUR
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;
START TRANSACTION;
SELECT * FROM developers WHERE ID = p_ID;
UPDATE developers
SET SALARY = p_NewSalary, EXPERIENCE = p_NewExperience
WHERE ID = p_ID;
COMMIT;
SELECT * FROM developers WHERE ID = p_ID;
END
DECLARE EXIT HANDLER FOR SQLEXCEPTION — loob veateate töötleja, mis käivitub, kui SQL-päringutes tekib viga.
ROLLBACK — tühistab kõik muudatused juhul, kui viga esineb, taastades andmed algsesse olekusse enne tehingu algust.
START TRANSACTION — alustab uut tehingut, mis sisaldab mitut SQL-operatsiooni, mis viiakse läbi ühe tervikuna.
COMMIT — kinnitab tehingu, rakendades kõik muudatused andmebaasi.
Lisa kommentaar