db2-oracle

Files (1)

• SKILL.md

SKILL.md (first lines)

# DB2 and Oracle

## 1. Oracle PL/SQL

### Basic Procedure

```sql
CREATE OR REPLACE PROCEDURE sp_create_order (
    p_user_id   IN  NUMBER,
    p_total     IN  NUMBER,
    p_order_id  OUT NUMBER
) AS
BEGIN
    INSERT INTO orders (user_id, total, status, created_at)
    VALUES (p_user_id, p_total, 'pending', SYSTIMESTAMP)
    RETURNING id INTO p_order_id;

    COMMIT;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        RAISE_APPLICATION_ERROR(-20001, 'Duplicate order detected');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/
```

### Cursor and Bulk Collect

```sql
DECLARE
    TYPE t_user_tab IS TABLE OF users%ROWTYPE;
    v_users t_user_tab;
BEGIN
    SELECT * BULK COLLECT INTO v_users
    FROM users
    WHERE is_active = 1;

    FORALL i IN 1..v_users.COUNT
        UPDATE users
        SET last_login = SYSTIMESTAMP
        WHERE id = v_users(i).id;

    COMMIT;
END;
/
```

## 2. DB2 SQL/PL

### Stored Procedure

```sql
CREATE PROCEDURE create_order (
    IN p_user_id INT,
    IN p_total DECIMAL(10,2),
    OUT p_order_id INT
)
BEGIN
    DECLARE v_status VARCHAR(20) DEFAULT 'pending';

    INSERT INTO orders (user_id, total, status, created_at)
    VALUES (p_user_id, p_total, v_status, CURRENT_TIMESTAMP);

    SET p_order_id = IDENTITY_VAL_LOCAL();
END
```

### MERGE (DB2)

```sql
MERGE INTO inventory AS target
USING (SELECT product_id, quantity FROM staging_inventory) AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET target.quantity = target.quantity + source.quantity
WHEN NOT MATCHED THEN
    INSERT (product_id, quantity) VALUES (source.product_id, source.quantity);
```

## 3. Oracle Partitioning

| Type | Use Case |
|------|----------|
| Range | Time-series data (by month/year) |
| List | Discrete categories (by region/status) |
| Hash | Even distribution for I/O balance |
| Composite | Range + Hash or Range + List |

```sql
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER(10,2)
) PARTITION BY RANGE (sale_date) (
    PARTITION p2023q1 VALUES LESS THAN (TO_DATE('2023-04-01','YYYY-MM-DD')),
    PARTITION p2023q2 VALUES LESS THAN (TO_DATE('2023-07-01','YYYY-MM-DD')),
    PARTITION p2023q3 VALUES LESS THAN (TO_DATE('2023-10-01','YYYY-MM-DD')),
    PARTITION p2023q4 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
```

## 4. DB2 Partitioning (Table Partitioning + MDC)

```sql
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date) (
    STARTING FROM '2023-01-01' ENDING AT '2024-01-01' EVERY 3 MONTHS
)
ORGANIZE BY DIMENSIONS (EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date));
```

## 5. Performance Tuning

From MathiasPaulenko/ai-toolkit, MIT.

More from MathiasPaulenko/ai-toolkit