-- Opdracht 1 | CURSOR /* 1. Voeg een column toe aan de table Customers genaamd cust_birth_date; 2. Voeg verschillende fictieve geboortedata toe m.b.v. een cursor; 3. Bijvoorbeeld: jaar = 200 + rechter positie cust_id maand = linker positie cust_address dag = rechter positie cust_zip */ ALTER TABLE Customers ADD cust_birth_date datetime; DECLARE DateCursor CURSOR FOR SELECT cust_id, cust_address, cust_zip FROM Customers; DECLARE @cust_id CHAR(10), @cust_address CHAR(50), @cust_zip CHAR(10) OPEN DateCursor FETCH NEXT FROM DateCursor INTO @cust_id, @cust_address, @cust_zip WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @BirthDate datetime SET @BirthDate = CONVERT(datetime,'200' + RIGHT(@cust_id,1) + '/' + LEFT(@cust_address,1) + '/' +RIGHT(RTRIM(@cust_zip),1)) UPDATE Customers SET cust_birth_date = @BirthDate WHERE cust_id = @cust_id; FETCH NEXT FROM DateCursor INTO @cust_id, @cust_address, @cust_zip END CLOSE DateCursor; DEALLOCATE DateCursor; -- Opdracht 2 | FUNCTION /* 1. Maak een functie die de leeftijd berekend a.d.h.v. een geboortedatum; 2. Toon alle customers inclusief de leeftijd. */ CREATE FUNCTION Age (@BirthDate datetime) RETURNS int AS BEGIN DECLARE @Output NUMERIC SET @Output = DATEDIFF(YEAR, @BirthDate, GETDATE()) RETURN @Output END SELECT *, DBO.Age(cust_birth_date) FROM Customers; -- Opdracht 3 | TRIGGER /* 1. Bij aankoop van meer dan 50 producten wordt standaard 10% korting verleend. Maak een trigger. 2. Gebruik de stored procedure FirstOrder (H19 Antwoord 4) om te testen! */ CREATE TRIGGER update_price ON OrderItems FOR INSERT, UPDATE AS UPDATE OrderItems SET item_price = inserted.item_price * 0.9 FROM inserted WHERE OrderItems.order_num = inserted.order_num AND OrderItems.quantity > 50; Execute FirstOrder 'Nieuwe klant', 75; -- Opdracht 4 | TRANSACTION /* Pas de FirstOrder (H19 Antwoord 4) stored procedure zodanig aan dat alles of niets wordt ingevoegd. */ ALTER PROCEDURE FirstOrder @cust_name char(50), @quantity int AS BEGIN TRY BEGIN TRAN DECLARE @prod_id char(10) DECLARE @cust_id char(10) DECLARE @order_num int DECLARE @item_price decimal(8,2) SET @prod_id = 'BR02' -- Customers SELECT @cust_id=max(cust_id) FROM Customers SELECT @cust_id=@cust_id+1 INSERT INTO Customers (cust_id, cust_name) VALUES(@cust_id, @cust_name) -- Orders SELECT @order_num=max(order_num) FROM Orders SELECT @order_num=@order_num+1 INSERT INTO Orders (order_num, order_date, cust_id) VALUES(@order_num,GETDATE(),@cust_id) -- OrderItems SELECT @item_price = prod_price FROM Products WHERE prod_id=@prod_id INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price) VALUES(@order_num,1, @prod_id,@quantity,@item_price); COMMIT TRAN END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN PRINT ERROR_MESSAGE() END CATCH Execute FirstOrder 'Nieuwe klant', 75;
Download hier het bestand.