Oracle多表查询函数优化咨询：简化UNION ALL多表查询写法

嘿，你的这个函数功能正常，但确实存在不少重复冗余的代码，还有两个小拼写错误：函数名SEARACH_FOR_GAMES里的SEARACH多了个字母A，应该是SEARCH_FOR_GAMES；另外TENISt表名末尾多了个t，正确应该是TENNIS。下面给你几种更简洁优雅的实现方式，你可以根据维护需求和场景选择：

方案1：统一字段别名+提取重复条件 #

首先，把每个表查询里的第三个字段统一成相同的别名（比如COMPETITION_NAME），这样调用方处理返回结果时更一致；同时把搜索模式提取到变量中，避免重复写相同的LIKE条件：

FUNCTION SEARCH_FOR_GAMES (p_search_string IN VARCHAR2) RETURN weak_cur IS
    -- 如果需要前后模糊匹配，这里提前拼接通配符；不需要的话直接用p_search_string即可
    v_search_pattern VARCHAR2(100) := '%' || p_search_string || '%';
    SEARCH_FIXID WEAK_CUR;
BEGIN
    OPEN SEARCH_FIXID FOR
        SELECT HOME, AWAY, COMP_NAME AS COMPETITION_NAME, M_TIME FROM SOCCER
        WHERE HOME LIKE v_search_pattern OR AWAY LIKE v_search_pattern
        UNION ALL
        SELECT HOME, AWAY, LISTS AS COMPETITION_NAME, M_TIME FROM BASKETBALL
        WHERE HOME LIKE v_search_pattern OR AWAY LIKE v_search_pattern
        UNION ALL
        SELECT HOME, AWAY, COMP AS COMPETITION_NAME, M_TIME FROM HANDBALL
        WHERE HOME LIKE v_search_pattern OR AWAY LIKE v_search_pattern
        UNION ALL
        SELECT HOME, AWAY, LISTS AS COMPETITION_NAME, M_TIME FROM ICE_HOCKEY
        WHERE HOME LIKE v_search_pattern OR AWAY LIKE v_search_pattern
        UNION ALL
        SELECT HOME, AWAY, COMP AS COMPETITION_NAME, M_TIME FROM TENNIS -- 修正表名拼写
        WHERE HOME LIKE v_search_pattern OR AWAY LIKE v_search_pattern
        UNION ALL
        SELECT HOME, AWAY, LISTS AS COMPETITION_NAME, M_TIME FROM VOLLEYBALL
        WHERE HOME LIKE v_search_pattern OR AWAY LIKE v_search_pattern;
    RETURN SEARCH_FIXID;
END SEARCH_FOR_GAMES;

这个改动的好处：

• 统一返回字段别名，减少调用方的适配成本
• 把重复的搜索逻辑集中到变量里，后续修改匹配规则（比如只加前缀%）只需要改一处
• 修正了原代码的拼写错误，避免运行时异常

方案2：动态SQL（适合后续会新增同类表的场景） #

如果之后还要添加更多结构类似的运动项目表，用动态SQL可以大幅降低维护成本——你只需要维护一个表和字段的映射列表，不用重复写SELECT和WHERE语句：

FUNCTION SEARCH_FOR_GAMES (p_search_string IN VARCHAR2) RETURN weak_cur IS
    v_search_pattern VARCHAR2(100) := '%' || p_search_string || '%';
    v_sql_query VARCHAR2(4000);
    SEARCH_FIXID WEAK_CUR;
    
    -- 定义表名和对应的赛事名称字段的映射类型
    TYPE table_comp_map IS RECORD (
        table_name VARCHAR2(30),
        comp_column VARCHAR2(30)
    );
    TYPE table_list IS TABLE OF table_comp_map;
    
    -- 维护这个列表即可，新增表时加一行
    v_sport_tables table_list := table_list(
        table_comp_map('SOCCER', 'COMP_NAME'),
        table_comp_map('BASKETBALL', 'LISTS'),
        table_comp_map('HANDBALL', 'COMP'),
        table_comp_map('ICE_HOCKEY', 'LISTS'),
        table_comp_map('TENNIS', 'COMP'),
        table_comp_map('VOLLEYBALL', 'LISTS')
    );
BEGIN
    -- 循环拼接每个表的查询语句
    FOR i IN v_sport_tables.FIRST .. v_sport_tables.LAST LOOP
        IF v_sql_query IS NOT NULL THEN
            v_sql_query := v_sql_query || ' UNION ALL ';
        END IF;
        
        v_sql_query := v_sql_query || 
            'SELECT HOME, AWAY, ' || v_sport_tables(i).comp_column || ' AS COMPETITION_NAME, M_TIME ' ||
            'FROM ' || v_sport_tables(i).table_name || ' ' ||
            'WHERE HOME LIKE :search_pattern OR AWAY LIKE :search_pattern';
    END LOOP;
    
    -- 使用绑定变量执行动态SQL，避免SQL注入风险
    OPEN SEARCH_FIXID FOR v_sql_query USING v_search_pattern;
    RETURN SEARCH_FIXID;
END SEARCH_FOR_GAMES;

这个方案的优势：

• 新增表时只需要在v_sport_tables里加一条记录，不用复制粘贴大量重复代码
• 代码结构更清晰，所有表的映射关系一目了然
• 用绑定变量:search_pattern确保了SQL安全，不会有注入风险

方案3：创建视图封装多表联合（适合查询逻辑固定的场景） #

如果这个多表联合的查询逻辑会被多个地方复用，建议先创建一个视图，然后函数里直接查询视图即可：

CREATE OR REPLACE VIEW ALL_SPORTS_GAMES AS
    SELECT 'SOCCER' AS SPORT_TYPE, HOME, AWAY, COMP_NAME AS COMPETITION_NAME, M_TIME FROM SOCCER
    UNION ALL
    SELECT 'BASKETBALL' AS SPORT_TYPE, HOME, AWAY, LISTS AS COMPETITION_NAME, M_TIME FROM BASKETBALL
    UNION ALL
    SELECT 'HANDBALL' AS SPORT_TYPE, HOME, AWAY, COMP AS COMPETITION_NAME, M_TIME FROM HANDBALL
    UNION ALL
    SELECT 'ICE_HOCKEY' AS SPORT_TYPE, HOME, AWAY, LISTS AS COMPETITION_NAME, M_TIME FROM ICE_HOCKEY
    UNION ALL
    SELECT 'TENNIS' AS SPORT_TYPE, HOME, AWAY, COMP AS COMPETITION_NAME, M_TIME FROM TENNIS
    UNION ALL
    SELECT 'VOLLEYBALL' AS SPORT_TYPE, HOME, AWAY, LISTS AS COMPETITION_NAME, M_TIME FROM VOLLEYBALL;

然后函数可以简化到极致：

FUNCTION SEARCH_FOR_GAMES (p_search_string IN VARCHAR2) RETURN weak_cur IS
    v_search_pattern VARCHAR2(100) := '%' || p_search_string || '%';
    SEARCH_FIXID WEAK_CUR;
BEGIN
    OPEN SEARCH_FIXID FOR
        SELECT HOME, AWAY, COMPETITION_NAME, M_TIME
        FROM ALL_SPORTS_GAMES
        WHERE HOME LIKE v_search_pattern OR AWAY LIKE v_search_pattern;
    RETURN SEARCH_FIXID;
END SEARCH_FOR_GAMES;

这个方案的好处：

• 函数代码极度简洁，逻辑一目了然
• 视图可以被其他PL/SQL程序或SQL查询复用
• 后续修改表结构或添加表，只需要更新视图，不用修改函数

小提醒 #

• 如果原代码的LIKE不需要前后通配符，直接去掉v_search_pattern里的%即可，保持原匹配逻辑
• 动态SQL方案要确保v_sport_tables里的表名和字段名拼写正确，避免运行时错误
• 如果表数据量很大，记得给HOME和AWAY字段创建合适的索引，提升查询性能

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