-- Oefening 1 | CURSOR DECLARE CustCursor CURSOR FOR SELECT cust_id, cust_name FROM Customers DECLARE @cust_id CHAR(10), @cust_name CHAR(50) OPEN CustCursor FETCH NEXT FROM CustCursor INTO @cust_id, @cust_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT @cust_id + ' | ' + @cust_name FETCH NEXT FROM CustCursor INTO @cust_id, @cust_name END CLOSE CustCursor DEALLOCATE DateCursor -- Oefening 2 | FUNCTION CREATE FUNCTION IntervalMonth (@input datetime) RETURNS int AS BEGIN DECLARE @Output int SET @Output = DATEDIFF(MM,@input,GETDATE()) RETURN @Output END SELECT order_date, dbo.IntervalMonth(order_date) FROM Orders; -- Oefening 3 | TRIGGER CREATE TRIGGER customer_state ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET cust_state = Upper(inserted.cust_state) FROM inserted WHERE Customers.cust_id = inserted.cust_id; INSERT INTO Customers(cust_id, cust_name, cust_state) VALUES('1000000009', 'Village Toys', 'ny'); SELECT * FROM Customers -- Oefening 4 | TRANSACTION BEGIN TRY BEGIN TRAN DECLARE @cust_id CHAR(10) SET @cust_id = '1000000004' DELETE FROM OrderItems WHERE order_num IN (SELECT order_num FROM orders WHERE cust_id = @cust_id); DELETE FROM Customers WHERE cust_id = @cust_id; DELETE FROM Orders WHERE order_num IN (SELECT order_num FROM orders WHERE cust_id = @cust_id); COMMIT TRAN END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN PRINT ERROR_MESSAGE() END CATCH
Download hier het bestand.