Oracle单SQL语句实现按条件跨表查询（非PL/SQL块）

Got it, let's work through this. You need a single Oracle SQL statement (no PL/SQL blocks) that switches between tables T1 and T2 based on the prefix of your :TARGET parameter. Here's a clean, efficient way to do it using UNION ALL:

SELECT x, y AS secondary_column
FROM T1
WHERE :TARGET LIKE 'A%' OR :TARGET LIKE 'B%'
UNION ALL
SELECT x, z AS secondary_column
FROM T2
WHERE :TARGET LIKE 'C%' OR :TARGET LIKE 'D%' OR :TARGET LIKE 'E%';

Breakdown of the Logic: #

• UNION ALL instead of UNION: Since a :TARGET value can't start with both A/B and C/D/E at the same time, we skip the duplicate-checking step that UNION does. This makes the query run faster.
• Consistent column naming: We alias y and z to the same name (secondary_column here—you can use whatever fits your use case) so the final result set has uniform column headers. Just make sure y (from T1) and z (from T2) have compatible data types; if not, wrap them in CAST to align, e.g., CAST(y AS VARCHAR2(200)) AS secondary_column.
• Direct bind variable use: There's no need to query dual to get the :TARGET value—Oracle lets you reference bind variables directly in the WHERE clause.

If you prefer a more concise condition for longer prefix lists, you can use regex instead:

-- T1 branch condition
WHERE REGEXP_LIKE(:TARGET, '^[AB]')
-- T2 branch condition
WHERE REGEXP_LIKE(:TARGET, '^[CDE]')

This achieves the same matching logic but cuts down on repetitive LIKE clauses.

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