Aynı Sorgu, Üç Lehçe — PostgreSQL, SQL Server, Oracle

1
CREATE TABLE fx_rate (
2
    id         bigint        GENERATED ALWAYS AS IDENTITY,
3
    rate_date  date          NOT NULL,
4
    currency   char(3)       NOT NULL,          -- ISO 4217: USD, EUR, GBP
5
    buy_rate   numeric(18,6) NOT NULL,          -- TRY per 1 unit of currency
6
    sell_rate  numeric(18,6) NOT NULL,
7
    CONSTRAINT pk_fx_rate PRIMARY KEY (rate_date, currency)
8
);
9

10
-- Daily rate feed: insert today's rate, or correct it if it is already there.
11
-- ON CONFLICT waits on the conflicting row instead of failing with a unique violation.
12
INSERT INTO fx_rate (rate_date, currency, buy_rate, sell_rate)
13
VALUES (:rateDate, :currency, :buyRate, :sellRate)
14
ON CONFLICT (rate_date, currency)
15
DO UPDATE SET buy_rate  = EXCLUDED.buy_rate,
16
              sell_rate = EXCLUDED.sell_rate
17
RETURNING id;

1
CREATE TABLE fx_rate (
2
    id         bigint        IDENTITY(1,1) NOT NULL,
3
    rate_date  date          NOT NULL,
4
    currency   char(3)       NOT NULL,
5
    buy_rate   decimal(18,6) NOT NULL,
6
    sell_rate  decimal(18,6) NOT NULL,
7
    CONSTRAINT pk_fx_rate PRIMARY KEY (rate_date, currency)
8
);
9

10
-- HOLDLOCK keeps the "no such row" decision locked until the insert happens.
11
-- Without it two concurrent feeds can both take the NOT MATCHED branch.
12
MERGE fx_rate WITH (HOLDLOCK) AS t
13
USING (VALUES (@rateDate, @currency, @buyRate, @sellRate))
14
      AS s (rate_date, currency, buy_rate, sell_rate)
15
   ON t.rate_date = s.rate_date AND t.currency = s.currency
16
WHEN MATCHED THEN
17
    UPDATE SET buy_rate = s.buy_rate, sell_rate = s.sell_rate
18
WHEN NOT MATCHED THEN
19
    INSERT (rate_date, currency, buy_rate, sell_rate)
20
    VALUES (s.rate_date, s.currency, s.buy_rate, s.sell_rate)
21
OUTPUT inserted.id;                              -- MERGE must end with a semicolon

1
CREATE TABLE fx_rate (
2
    id         NUMBER(19)    GENERATED ALWAYS AS IDENTITY,   -- 12c+
3
    rate_date  DATE          NOT NULL,                       -- DATE also holds a time of day
4
    currency   CHAR(3)       NOT NULL,
5
    buy_rate   NUMBER(18,6)  NOT NULL,
6
    sell_rate  NUMBER(18,6)  NOT NULL,
7
    CONSTRAINT pk_fx_rate PRIMARY KEY (rate_date, currency),
8
    CONSTRAINT ck_fx_rate_day CHECK (rate_date = TRUNC(rate_date))
9
);
10

11
-- The ON condition needs parentheses and the source row comes from dual.
12
-- Two concurrent MERGEs can both try to insert: one gets ORA-00001 and is retried.
13
MERGE INTO fx_rate t
14
USING (SELECT :rateDate AS rate_date, :currency AS currency,
15
              :buyRate AS buy_rate, :sellRate AS sell_rate
16
         FROM dual) s
17
   ON (t.rate_date = s.rate_date AND t.currency = s.currency)
18
WHEN MATCHED THEN
19
    UPDATE SET t.buy_rate = s.buy_rate, t.sell_rate = s.sell_rate
20
WHEN NOT MATCHED THEN
21
    INSERT (rate_date, currency, buy_rate, sell_rate)
22
    VALUES (s.rate_date, s.currency, s.buy_rate, s.sell_rate);

1
-- 1. Account holder name. Oracle stored the empty middle name as NULL and || skips NULL:
2
--    Oracle gives 'Ayse  Yilmaz', PostgreSQL gives NULL for the whole name.
3
SELECT first_name || ' ' || middle_name || ' ' || last_name AS holder_name
4
  FROM customer;
5
-- PostgreSQL: concat_ws skips NULLs, NULLIF turns '' into NULL first.
6
SELECT concat_ws(' ', first_name, NULLIF(middle_name, ''), last_name) AS holder_name
7
  FROM customer;
8

9
-- 2. Transfers without a description. Migrated Oracle rows hold NULL, new rows hold ''.
10
SELECT count(*) FROM transfer WHERE description IS NULL;             -- only the old ones
11
SELECT count(*) FROM transfer WHERE COALESCE(description, '') = '';  -- both