oracle从多个表中选择 [英] oracle select from multiple tables
本文介绍了oracle从多个表中选择的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果p_search_string与这些字段相同,我将具有返回mi的功能.
I have function which will return mi home or away if the p_search_string is same as those fields.
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;
这很好用,但是我想知道是否有一种更精细"的方式来写下这些选择?
This works fine, but i m wondering is there a "nicer" way to write down these selects ?
谢谢
推荐答案
就此要求而言,这绝对是数据模型的问题.我可以建议两个选择:
This is definitely an issue with the data model as far as this requirement is concerned. I can suggest two options:
选项#1: 通过组合所有表创建单个表SPORTS,并具有一列sport_type.这将允许在函数中使用一个选择查询.
Option# 1: Create a single table SPORTS by combining all tables and have a column sport_type. This will allow to use one select query in function.
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;
这篇关于oracle从多个表中选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文