SQL Basics

• Transaction processing helps maintain database integrity
• A sequence of operations is either completed as a whole or not applied at all
• Adding an order may require several steps:
  • If the customer does not exist, add them
  • Store the customer information
  • Retrieve the customer ID
  • Insert a new row into Orders
  • If adding the order fails, roll back the entire transaction
  • Retrieve the new order ID
  • Insert a row into OrderItems for each item in the order
  • If adding an order item fails, roll back the entire transaction

• Transaction: a sequence of SQL statements treated as one unit
• Rollback: undo the uncommitted changes in a transaction
• Commit: confirm and make the transaction's changes permanent
• Savepoint: a point within a transaction to which a partial rollback can return

• Checks whether the customer already exists in Customers.
• Adds the customer if they do not exist.
• Obtains a unique order number using the database's own number generator.
• Adds a new order to Orders.
• Adds one or more order lines to OrderItems.
• Commits the whole transaction if every step succeeds.

-- Begin the transaction (Oracle does this implicitly unless AUTOCOMMIT is enabled)
-- Oracle starts a transaction implicitly at the first DML statement.
-- One-time preparation before running transactions: start the sequence after the current 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

    -- Add the customer if they do not exist
  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;

    -- Obtain a unique order number from the sequence
  v_order_num := Orders_Seq.NEXTVAL;

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

    -- Add order lines
  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;	
/
	

• Checks whether the customer already exists in Customers.
• Adds the customer if they do not exist.
• Obtains a unique order number using the database's own number generator.
• Adds a new order to Orders.
• Adds one or more order lines to OrderItems.
• Commits the whole transaction if every step succeeds.

-- MySQL 8: run this script in the mysql client or MySQL Workbench.
-- The tables must use a transactional engine such as InnoDB.
-- The handler rolls back the whole transaction and propagates the error.
-- One-time preparation: order_num must be the primary key.
-- For existing rows, MySQL sets AUTO_INCREMENT to MAX(order_num) + 1 automatically.
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;

  -- Step 1: add the customer if they do not exist
  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;

  -- Step 2: add a new order; MySQL generates the order number
  INSERT INTO Orders (order_date, cust_id)
  VALUES (NOW(), v_cust_id);
  SET v_order_num = LAST_INSERT_ID();

  -- Add order lines
  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();

• Adds a customer if they do not exist
• Obtains a unique order number from a SQL Server sequence and adds the order
• Adds the corresponding order lines
• Uses XACT_STATE() to determine whether the entire transaction must be rolled back

-- Begin the transaction
-- One-time preparation before running transactions: start the sequence after the current 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;

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

  -- Step 1: add the customer if they do not exist
  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

  -- Step 2: obtain a unique order number from the sequence
  SET @order_num = NEXT VALUE FOR dbo.OrderNumSeq;

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

  -- Step 3: add order lines
  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 the transaction
  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;