Oracle视图条件逻辑：货币转换反向计算实现咨询

Hey there! Let me break down exactly how to create that view to handle both direct and reverse currency conversions— it’s straightforward once you see the pattern.

First, let’s assume your MY_TABLE has a structure like this (adjust if your columns are named differently):

from_currency VARCHAR(3),  -- e.g., 'USD'
to_currency VARCHAR(3),    -- e.g., 'EUR'
exchange_rate DECIMAL(10,4) -- e.g., 2.10

The Core Idea #

We’ll build a view that combines two sets of data:

1. All existing direct exchange rates from your table.
2. Auto-generated reverse rates for any currency pair that doesn’t already have a reverse entry (using the reciprocal of the direct rate).

The View SQL Code #

Here’s the full code to create the view— I’ll explain each part below:

CREATE OR REPLACE VIEW currency_exchange_rates AS
-- 1. Keep all existing direct conversion rates
SELECT
    from_currency,
    to_currency,
    exchange_rate
FROM MY_TABLE

UNION ALL

-- 2. Generate reverse rates for pairs that don't already have them
SELECT
    to_currency AS from_currency,
    from_currency AS to_currency,
    1 / exchange_rate AS exchange_rate
FROM MY_TABLE
WHERE NOT EXISTS (
    -- Only create a reverse rate if no direct reverse entry exists
    SELECT 1
    FROM MY_TABLE t2
    WHERE t2.from_currency = MY_TABLE.to_currency
      AND t2.to_currency = MY_TABLE.from_currency
);

Let’s Break It Down #

• First SELECT block: This just pulls all the existing direct rates you already have in MY_TABLE— no changes here, we’re keeping all your original data.
• UNION ALL: This combines the direct rates with our generated reverse rates (we use UNION ALL instead of UNION because it’s faster and we’re already avoiding duplicates with the NOT EXISTS check).
• Second SELECT block: This flips the from_currency and to_currency columns, and calculates the reverse rate as 1 / exchange_rate. The NOT EXISTS clause ensures we don’t create a reverse rate if one already exists in your table (so you won’t get duplicate entries for pairs that have both directions stored).

Optional: Handle Same-Currency Conversions #

If you want to add support for conversions like USD → USD (which should always return 1.0), you can add this third block to the view (append it with another UNION ALL):

-- Optional: Add 1:1 rate for same-currency conversions
SELECT
    currency AS from_currency,
    currency AS to_currency,
    1.0 AS exchange_rate
FROM (
    -- Get all unique currencies from your table
    SELECT DISTINCT from_currency AS currency FROM MY_TABLE
    UNION
    SELECT DISTINCT to_currency AS currency FROM MY_TABLE
) AS all_currencies
WHERE NOT EXISTS (
    -- Only add if no same-currency entry already exists
    SELECT 1
    FROM MY_TABLE t2
    WHERE t2.from_currency = currency
      AND t2.to_currency = currency
)

How to Use the View #

Once the view is created, you can query it just like a regular table to get the rate you need, regardless of direction:

-- Get USD → EUR rate (direct if exists, reverse if not)
SELECT exchange_rate 
FROM currency_exchange_rates 
WHERE from_currency = 'USD' AND to_currency = 'EUR';

-- Get EUR → USD rate (auto-generated if not in original table)
SELECT exchange_rate 
FROM currency_exchange_rates 
WHERE from_currency = 'EUR' AND to_currency = 'USD';

Quick Notes #

• If your table ever has a 0 exchange rate (unlikely for real currencies), add AND exchange_rate != 0 to the second SELECT block to avoid division by zero errors.
• The DECIMAL(10,4) type is just a suggestion— adjust the precision to match what your application needs.

内容的提问来源于stack exchange，提问作者TheCoder