时间戳

首页 > 解决方案 > oracle 从多个表中选择

oracle - oracle 从多个表中选择

问题描述

如果 p_search_string 与这些字段相同，我有一个函数将返回 mi 家或离开。

    FUNCTION SEARACH_FOR_GAMES  ( p_search_string in varchar2 )
                             return weak_cur
  IS
    SEARCH_FIXID WEAK_CUR;   
  BEGIN  
    OPEN   SEARCH_FIXID FOR
select  HOME,AWAY,COMP_NAME, M_TIME from SOCCER s
where s.HOME LIKE (:p_search_string) or s.AWAY LIKE (:p_search_string)
union all
select  HOME,AWAY,LISTS,M_TIME from BASKETBALL b
where b.HOME LIKE (:p_search_string) or b.AWAY LIKE (:p_search_string)
union all
select HOME,AWAY,COMP,M_TIME from HANDBALL h
where h.HOME LIKE (:p_search_string) or h.AWAY LIKE (:p_search_string)
union all
select  HOME,AWAY,LISTS,M_TIME from ICE_HOCKEY i
where i.HOME LIKE (:p_search_string) or i.AWAY LIKE (:p_search_string)
union all
select  HOME,AWAY,COMP,M_TIME from TENISt
where t.HOME LIKE (:p_search_string) or t.AWAY LIKE (:p_search_string)
union all
select  HOME,AWAY,LISTS,M_TIME from VOLLEYBALL v
where v.HOME LIKE (:p_search_string) or v.AWAY LIKE (:p_search_string);
    RETURN SEARCH_FIXID;
  END SEARACH_FOR_GAMES;

这很好用，但我想知道是否有一种“更好”的方式来写下这些选择？

谢谢

标签： oracleplsql

解决方案

就这一要求而言，这绝对是数据模型的一个问题。我可以建议两个选项：

选项# 1： 通过组合所有表创建单个表 SPORTS，并具有一列 sport_type。这将允许在函数中使用一个选择查询。

select  HOME,AWAY,COMP_NAME, M_TIME from SPORTS s
where s.HOME LIKE (:p_search_string) or s.AWAY LIKE (:p_search_string)
  and sport_type in 
(
'SOCCER',
'BASKETBALL',
'HANDBALL',
'ICE_HOCKEY',
'TENIST',
'VOLLEYBALL'
)

选项#2：

Create a view by combining all these tables and select from that view in this function.
CREATE OR REPLACE VIEW VW_SPORTS
AS
select HOME,AWAY,COMP_NAME, M_TIME, 'SOCCER' SPORT_TYPE from SOCCER
union all
select  HOME,AWAY,LISTS,M_TIME,'BASKETBALL' SPORT_TYPE from BASKETBALL 
union all
select HOME,AWAY,COMP,M_TIME,'HANDBALL' SPORT_TYPE from HANDBALL
union all
select  HOME,AWAY,LISTS,M_TIME,'ICE_HOCKEY' SPORT_TYPE from ICE_HOCKEY
union all
select  HOME,AWAY,COMP,M_TIME,'TENIST' SPORT_TYPE from TENISt
union all
select  HOME,AWAY,LISTS,M_TIME,'VOLLEYBALL' SPORT_TYPE  from VOLLEYBALL v;


 FUNCTION SEARACH_FOR_GAMES  ( p_search_string in varchar2 )
                             return weak_cur
  IS
    SEARCH_FIXID WEAK_CUR;   
  BEGIN  
    OPEN   SEARCH_FIXID FOR
       select  HOME,AWAY,COMP_NAME, M_TIME from VW_SPORTS s
        where s.HOME LIKE (:p_search_string) or s.AWAY LIKE (:p_search_string)
         and s.SPOR_TYPE IN 
    (
    'SOCCER',
    'BASKETBALL',
    'HANDBALL',
    'ICE_HOCKEY',
    'TENIST',
    'VOLLEYBALL'
    );
    RETURN SEARCH_FIXID;
  END SEARACH_FOR_GAMES;

推荐阅读