6238 Databaser Agenda/Stored procedures og Triggers

Stored procedure

Med stored procedure kan man automatiserer nogle processer. Det minder meget om metoder og funktioner i andre programmeringssprog.

Der er igen en lille forskel på MS-Sql og MySql.

Her er f.eks. vist hvordan vi indsætter Saelger og telefon nr i en og samme arbejdsgang.

MS-Sql

CREATE PROCEDURE InsertSaelger @navn NVARCHAR(20), @email NVARCHAR(20), @startDato DATETIME,  @saelgerNr INT, @telefonNr NVARCHAR(8)
AS
  IF NOT EXISTS (SELECT Navn FROM Saelger WHERE Saelger.MedarbNr = @saelgerNr) 
  BEGIN
    INSERT INTO	Saelger(Navn,Email,StartDato,MedarbNr)VALUES(@navn,@email,@startDato,@saelgerNr);
  END
  INSERT INTO TelefonNr(Nr,Saelger) VALUES (@telefonNr,@saelgerNr);
GO

Bemærk hvordan alle variable navne har @ foran sig og at scope ikke er markeret med {} som vi kender fra C# men i stedet med Begin End som i Basic.

Proceduren kaldes således.

EXECUTE InsertSaelger 'Jens', 'jens@mail.dk', '2001-1-1',4,'67891234';

MySql MySql er lidt speciel da den vil forsøge at afvikle koden hver gang den ser karakteren ; Den karakter kaldes en DELIMITER

Løsningen er at lave dens DELIMITER om til noget andet en ; i viste tilfælde // I slutningen af proceduren laves den tilbage til ;

Der ud over anvendes ikke @ foran variable navne, de parametrene der overføres skal stå i parantes og IF skal efterfølges af THEN og afsluttes med END IF.

DELIMITER //
CREATE PROCEDURE InsertSaelger 
(IN navn NVARCHAR(20), IN email NVARCHAR(20), IN startDato DATETIME,  IN saelgerNr INT, IN telefonNr NVARCHAR(8))
BEGIN
  IF NOT EXISTS (SELECT Navn FROM Saelger WHERE Saelger.MedarbNr = saelgerNr) THEN
    INSERT INTO	Saelger(Navn,Email,StartDato,MedarbNr)VALUES(navn,email,startDato,saelgerNr);
  END IF;
  INSERT INTO TelefonNr(Nr,Saelger) VALUES (@telefonNr,@saelgerNr);
END //
DELIMITER ;

Når proceduren kaldes anvendes CALL ikke EXECUTE.

Call InsertSaelger ('Jens', 'jens@mail.dk', '2001-1-1',4,'67891234');

Opgave

Lav på samme måde en stored procedure der kan indsætte en vare og købesaf i en arbejdsgang.

Løs også følgende

En database til registrering af ægteskaber mellem to personer.

Lav tabellerne Person og Aegteskab.

Lav nu en StoredProcedure der sikre at en person ikke kan blive gift hvis personen allerede er gift med en anden.