20. Advanced Techniques

-- 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

-- 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 
		
-- 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 

-- Opdracht 3 | TRIGGER

/*	Bij aankoop van meer dan 50 producten wordt standaard 10% korting verleend. Maak een trigger. 
	Tip: gebruik de stored procedure FirstOrder (H19 Antwoord 4) om te testen! */

CREATE TRIGGER 

-- Opdracht 4 | TRANSACTION

/*	Pas de FirstOrder (H19 Antwoord 4) stored procedure zodanig aan dat alles of niets wordt ingevoegd. */

ALTER PROCEDURE

-- 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;