SQL Basis

• Transaction Processing wordt gebruikt om de integriteit van de database te bewaken
• Een reeks handelingen (queries) wordt volledig uitgevoerd (als geheel) of er wordt niets uitgevoerd
• Voor het toevoegen van een order zijn mogelijk meerdere stappen nodig:
  • Bestaat de klant nog niet, voeg deze dan toe
  • Leg de klantinformatie vast
  • Haal het klantID op
  • Voeg een nieuwe rij toe in de Orders-tabel
  • Als er iets mis gaat met het toevoegen van een rij aan Orders, draai dan alles terug (rollback)
  • Haal het nieuwe OrderID op
  • Voeg voor elk item van de Order een nieuwe rij toe aan OrderItems
  • Als er iets mis gaat met het toevoegen van een rij in OrderItems, draai dan alles terug (Roll back)

• Transaction: Een reeks SQL-statements
• Rollback: Het ongedaanmaken van een specifieke SQL-statement
• Commit: het schrijven en vastleggen van SQL-statements in de database
• Savepoint: Een punt in een reeks handelingen van een transactie tot waar je een rollback kunt uitvoeren

• Controleert of de klant al bestaat in de Customers-tabel.
• Voegt een nieuwe klant toe als deze nog niet bestaat.
• Haalt een uniek ordernummer op uit een database-eigen nummergenerator.
• Voegt een nieuwe order toe aan de Orders-tabel.
• Voegt één of meerdere orderregels toe aan de OrderItems-tabel.
• Bevestigt de hele transactie met een COMMIT als alles goed gaat.

-- Begin de transactie (Oracle doet dit impliciet tenzij AUTOCOMMIT aanstaat)
-- Oracle start een transactie impliciet bij de eerste DML-instructie.
-- Eenmalige voorbereiding vóór de transacties: sequence starten na het huidige maximum.
DECLARE
  v_sequence_count PLS_INTEGER;
  v_start NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_sequence_count
  FROM user_sequences
  WHERE sequence_name = 'ORDERS_SEQ';

  IF v_sequence_count = 0 THEN
    SELECT NVL(MAX(order_num), 20000) + 1 INTO v_start FROM Orders;
    EXECUTE IMMEDIATE 'CREATE SEQUENCE Orders_Seq START WITH ' || TO_CHAR(v_start) || ' INCREMENT BY 1';
  END IF;
END;
/

DECLARE
  v_cust_id VARCHAR2(10) := '1000009999';
  v_order_num NUMBER;
  v_order_item NUMBER := 1;
BEGIN

    -- Klant toevoegen als die nog niet bestaat
  BEGIN
    SELECT cust_id
      INTO v_cust_id
      FROM Customers
      WHERE cust_id = v_cust_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO Customers (
        cust_id, cust_name, cust_address, cust_city,
        cust_state, cust_zip, cust_country,
        cust_contact, cust_email
      ) VALUES (
        v_cust_id, 'New Customer Inc.', '123 New Street',
        'Newville', 'NY', '99999',
        'USA', 'Jane Doe', 'jane@example.com'
      );
  END;

    -- Haal een uniek ordernummer uit de sequence
  v_order_num := Orders_Seq.NEXTVAL;

  INSERT INTO Orders (
      order_num, order_date, cust_id
    ) VALUES (
      v_order_num, SYSDATE, v_cust_id
    );

    -- Orderregels toevoegen
  INSERT INTO OrderItems (
      order_num, order_item, prod_id, quantity, item_price
    ) VALUES (
      v_order_num, v_order_item, 'BR01', 10, 5.99
    );

  v_order_item := v_order_item + 1;

  INSERT INTO OrderItems (
      order_num, order_item, prod_id, quantity, item_price
    ) VALUES (
      v_order_num, v_order_item, 'BNBG01', 5, 3.49
    );

  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Order en items succesvol toegevoegd.');

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Fout: ' || SQLERRM);
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Transactie teruggedraaid.');
END;	
/
	

• Controleert of de klant al bestaat in de Customers-tabel.
• Voegt een nieuwe klant toe als deze nog niet bestaat.
• Haalt een uniek ordernummer op uit een database-eigen nummergenerator.
• Voegt een nieuwe order toe aan de Orders-tabel.
• Voegt één of meerdere orderregels toe aan de OrderItems-tabel.
• Bevestigt de hele transactie met een COMMIT als alles goed gaat.

-- MySQL 8: voer dit script uit in de mysql-client of MySQL Workbench.
-- De tabellen moeten een transactionele engine gebruiken, zoals InnoDB.
-- De handler rolt de volledige transactie terug en geeft de fout door.
-- Eenmalige voorbereiding: order_num moet de primaire sleutel zijn.
-- MySQL zet AUTO_INCREMENT bij bestaande rijen automatisch op MAX(order_num) + 1.
ALTER TABLE Orders MODIFY order_num INT NOT NULL AUTO_INCREMENT;

DELIMITER //
CREATE PROCEDURE PlaceSampleOrder()
BEGIN
  DECLARE v_cust_id CHAR(10) DEFAULT '1000009999';
  DECLARE v_order_num INT;
  DECLARE v_order_item INT DEFAULT 1;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;

  -- Stap 1: Klant toevoegen indien nog niet aanwezig
  IF NOT EXISTS (
    SELECT 1 FROM Customers WHERE cust_id = v_cust_id
  ) THEN
    INSERT INTO Customers (
      cust_id, cust_name, cust_address, cust_city, cust_state,
      cust_zip, cust_country, cust_contact, cust_email
    ) VALUES (
      v_cust_id, 'New Customer Inc.', '123 New Street',
      'Newville', 'NY', '99999', 'USA',
      'Jane Doe', 'jane@example.com'
    );
  END IF;

  -- Stap 2: Nieuwe order toevoegen; MySQL genereert het ordernummer
  INSERT INTO Orders (order_date, cust_id)
  VALUES (NOW(), v_cust_id);
  SET v_order_num = LAST_INSERT_ID();

  -- Orderregels toevoegen
  INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
  VALUES (v_order_num, v_order_item, 'BR01', 10, 5.99);

  SET v_order_item = v_order_item + 1;

  INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
  VALUES (v_order_num, v_order_item, 'BNBG01', 5, 3.49);

  COMMIT;
END//
DELIMITER ;

CALL PlaceSampleOrder();

• Een nieuwe klant toevoegt als die nog niet bestaat
• Een uniek ordernummer uit een SQL Server-sequence ophalen en de order toevoegen
• Bijbehorende orderregels toevoegen
• XACT_STATE() gebruikt om te bepalen of de volledige transactie moet worden teruggedraaid

-- Begin de transactie
-- Eenmalige voorbereiding vóór de transacties: sequence starten na het huidige maximum.
DECLARE @next_order_num INT;
SELECT @next_order_num = ISNULL(MAX(order_num), 20000) + 1 FROM Orders;
IF NOT EXISTS (
  SELECT 1 FROM sys.sequences
  WHERE name = N'OrderNumSeq' AND schema_id = SCHEMA_ID(N'dbo')
)
BEGIN
  DECLARE @create_sequence NVARCHAR(200) =
    N'CREATE SEQUENCE dbo.OrderNumSeq AS INT START WITH '
    + CONVERT(NVARCHAR(20), @next_order_num) + N' INCREMENT BY 1;';
  EXEC sys.sp_executesql @create_sequence;
END;
GO

BEGIN TRY
  BEGIN TRANSACTION;

  -- Variabelen
  DECLARE @cust_id CHAR(10) = '1000009999';
  DECLARE @order_num INT;
  DECLARE @order_item INT = 1;

  -- Stap 1: Klant toevoegen indien niet aanwezig
  IF NOT EXISTS (SELECT 1 FROM Customers WHERE cust_id = @cust_id)
  BEGIN
    INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    VALUES(@cust_id, 'New Customer Inc.', '123 New Street', 'Newville', 'NY', '99999', 'USA', 'Jane Doe', 'jane@example.com');
  END

  -- Stap 2: Haal een uniek ordernummer uit de sequence
  SET @order_num = NEXT VALUE FOR dbo.OrderNumSeq;

  INSERT INTO Orders(order_num, order_date, cust_id)
  VALUES(@order_num, GETDATE(), @cust_id);

  -- Stap 3: Orderregels toevoegen
  INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
  VALUES(@order_num, @order_item, 'BR01', 10, 5.99);

  SET @order_item = @order_item + 1;

  INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
  VALUES(@order_num, @order_item, 'BNBG01', 5, 3.49);

  -- Commit transactie
  COMMIT;

  PRINT 'Order en items succesvol toegevoegd.';
END TRY

BEGIN CATCH
  PRINT 'Er is een fout opgetreden: ' + ERROR_MESSAGE();
  IF XACT_STATE() <> 0
  BEGIN
    ROLLBACK;
    PRINT 'Transactie volledig teruggedraaid.';
  END
END CATCH;