Comprehensive comparison for Database technology in Software Development applications

-- Oracle PL/SQL Package for E-Commerce Product Management API
-- This package demonstrates production-ready patterns for web application database layer

CREATE OR REPLACE PACKAGE product_api_pkg AS
  -- Custom exception types
  e_product_not_found EXCEPTION;
  e_invalid_price EXCEPTION;
  e_insufficient_stock EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_product_not_found, -20001);
  PRAGMA EXCEPTION_INIT(e_invalid_price, -20002);
  PRAGMA EXCEPTION_INIT(e_insufficient_stock, -20003);
  
  -- Type definitions for JSON response
  TYPE product_rec IS RECORD (
    product_id NUMBER,
    product_name VARCHAR2(200),
    price NUMBER(10,2),
    stock_quantity NUMBER,
    category VARCHAR2(100),
    last_updated TIMESTAMP
  );
  
  -- Public procedures
  PROCEDURE get_product(
    p_product_id IN NUMBER,
    p_result OUT SYS_REFCURSOR
  );
  
  PROCEDURE update_product_price(
    p_product_id IN NUMBER,
    p_new_price IN NUMBER,
    p_updated_by IN VARCHAR2,
    p_success OUT NUMBER
  );
  
  PROCEDURE process_order(
    p_product_id IN NUMBER,
    p_quantity IN NUMBER,
    p_customer_id IN NUMBER,
    p_order_id OUT NUMBER
  );
END product_api_pkg;
/

CREATE OR REPLACE PACKAGE BODY product_api_pkg AS

  -- Get product details with caching hint
  PROCEDURE get_product(
    p_product_id IN NUMBER,
    p_result OUT SYS_REFCURSOR
  ) IS
    v_count NUMBER;
  BEGIN
    -- Validate product exists
    SELECT COUNT(*) INTO v_count
    FROM products
    WHERE product_id = p_product_id
    AND is_active = 1;
    
    IF v_count = 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Product not found or inactive');
    END IF;
    
    -- Return product details with related data
    OPEN p_result FOR
      SELECT 
        p.product_id,
        p.product_name,
        p.price,
        p.stock_quantity,
        c.category_name,
        p.description,
        p.last_updated
      FROM products p
      INNER JOIN categories c ON p.category_id = c.category_id
      WHERE p.product_id = p_product_id;
      
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20000, 'Error retrieving product: ' || SQLERRM);
  END get_product;

  -- Update product price with audit trail
  PROCEDURE update_product_price(
    p_product_id IN NUMBER,
    p_new_price IN NUMBER,
    p_updated_by IN VARCHAR2,
    p_success OUT NUMBER
  ) IS
    v_old_price NUMBER;
  BEGIN
    -- Validate price
    IF p_new_price <= 0 THEN
      RAISE_APPLICATION_ERROR(-20002, 'Price must be greater than zero');
    END IF;
    
    -- Get current price for audit
    SELECT price INTO v_old_price
    FROM products
    WHERE product_id = p_product_id
    FOR UPDATE NOWAIT;
    
    -- Update price
    UPDATE products
    SET price = p_new_price,
        last_updated = SYSTIMESTAMP,
        updated_by = p_updated_by
    WHERE product_id = p_product_id;
    
    -- Insert audit record
    INSERT INTO price_audit_log (
      audit_id,
      product_id,
      old_price,
      new_price,
      updated_by,
      updated_at
    ) VALUES (
      price_audit_seq.NEXTVAL,
      p_product_id,
      v_old_price,
      p_new_price,
      p_updated_by,
      SYSTIMESTAMP
    );
    
    COMMIT;
    p_success := 1;
    
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      ROLLBACK;
      RAISE_APPLICATION_ERROR(-20001, 'Product not found');
    WHEN OTHERS THEN
      ROLLBACK;
      p_success := 0;
      RAISE_APPLICATION_ERROR(-20000, 'Error updating price: ' || SQLERRM);
  END update_product_price;

  -- Process order with stock validation and transaction handling
  PROCEDURE process_order(
    p_product_id IN NUMBER,
    p_quantity IN NUMBER,
    p_customer_id IN NUMBER,
    p_order_id OUT NUMBER
  ) IS
    v_current_stock NUMBER;
    v_price NUMBER;
  BEGIN
    -- Lock row and check stock availability
    SELECT stock_quantity, price
    INTO v_current_stock, v_price
    FROM products
    WHERE product_id = p_product_id
    FOR UPDATE NOWAIT;
    
    IF v_current_stock < p_quantity THEN
      RAISE_APPLICATION_ERROR(-20003, 
        'Insufficient stock. Available: ' || v_current_stock);
    END IF;
    
    -- Create order record
    INSERT INTO orders (
      order_id,
      customer_id,
      order_date,
      total_amount,
      status
    ) VALUES (
      order_seq.NEXTVAL,
      p_customer_id,
      SYSTIMESTAMP,
      v_price * p_quantity,
      'PENDING'
    ) RETURNING order_id INTO p_order_id;
    
    -- Create order line item
    INSERT INTO order_items (
      order_item_id,
      order_id,
      product_id,
      quantity,
      unit_price
    ) VALUES (
      order_item_seq.NEXTVAL,
      p_order_id,
      p_product_id,
      p_quantity,
      v_price
    );
    
    -- Update stock
    UPDATE products
    SET stock_quantity = stock_quantity - p_quantity,
        last_updated = SYSTIMESTAMP
    WHERE product_id = p_product_id;
    
    COMMIT;
    
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      ROLLBACK;
      RAISE_APPLICATION_ERROR(-20001, 'Product not found');
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE_APPLICATION_ERROR(-20000, 'Order processing failed: ' || SQLERRM);
  END process_order;

END product_api_pkg;
/