使用if语句的Oracle游标错误-Java 学习之路

尝试在包中创建此过程时出错 .

FUNCTION SEARACH_FOR_GAMES (p_search_string in varchar2, p_match_type in number )
                             return weak_cur
  IS
    SEARCH_FIXID WEAK_CUR;   
  BEGIN  


    if p_match_type = 2
    then 
    OPEN   SEARCH_FIXID FOR
        select  FIXID, 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  FIXID,HOME,AWAY,LISTS,M_TIME from BASKETBALLb
        where b.HOME LIKE (p_search_string) or b.AWAY LIKE (p_search_string)
        union all
        select FIXID,HOME,AWAY,COMP,M_TIME from HANDBALL h
        where h.HOME LIKE (p_search_string) or h.AWAY LIKE (p_search_string);
   elsif p_match_type = 1
    then
    OPEN   SEARCH_FIXID FOR
        select  FIXID,HOME,AWAY,COMP_NAME, TIME from LIVE_MATCHES_TZ s
        where s.HOME LIKE (p_search_string) or s.AWAY LIKE (p_search_string)
        union all
        select  FIXID,HOME,AWAY,COMP_NAME,TIME from LIVE_BASKETBALL_MATCHES b
        where b.HOME LIKE (p_search_string) or b.AWAY LIKE (p_search_string)
        union all
        select FIXID,HOME,AWAY,COMP_NAME,TIME from LIVE_HANDBALL_MATCHES h
        where h.HOME LIKE (p_search_string) or h.AWAY LIKE (p_search_string);
    end if;

    RETURN SEARCH_FIXID;
  END SEARACH_FOR_GAMES;

我得到两个错误当遇到以下其中一个时遇到符号“IF”并遇到符号“文件结束” . 可能是因为它的光标？

2 回答

一定是这样的：

if p_match_type = 0 then
    OPEN SEARCH_FIXID FOR
    select  FIXID,HOME,AWAY,COMP_NAME, M_TIME 
    from SOCCERs
    where s.HOME LIKE (p_search_string) or s.AWAY LIKE (p_search_string)
        ...

要使用光标，您必须使用它进行检查

cur := PCK_BET.SEARACH_FOR_GAMES('xyz', 1);
IF cur%IS_OPEN THEN
    ...
ELSE
    -- No cursor opened
END ID;

回复于 2024-05-03T11:20:25+08:00

它的

open cursorname for select...;

不

open cursorname;

for
    [...procedural logic...]

既然我们可以依赖优化器将这样的谓词推送到视图中，我会把它写成这样的东西：

create or replace function search_for_games
    ( p_search_string   in varchar2
    , p_match_type      in number )
    return sys_refcursor
is
    search_results sys_refcursor;
begin
    open search_results for
        select fixid, home, away, comp_name, m_time
        from   (
                 -- Type 0 matches:
                 select 0 as matchtype, fixid, home, away, comp_name, m_time from soccers
                 union all
                 select 0 as matchtype, fixid, home, away, comp_name, m_time from basketballb
                 union all
                 select 0 as matchtype, fixid, home, away, comp_name, m_time from handball h
                 union all
                 select 0 as matchtype, fixid, home, away, comp_name, m_time from ice_hockey i
                 union all
                 select 0 as matchtype, fixid, home, away, comp_name, m_time from tenis t
                 union all
                 select 0 as matchtype, fixid, home, away, comp_name, m_time from volleyball v
                 union all
                 -- Type 1 matches:
                 select 1 as matchtype, fixid, home, away, comp_name, m_time from table1
                 union all
                 select 1 as matchtype, fixid, home, away, comp_name, m_time from table2
               ) m
        where  m.matchtype = p_match_type
        and    ( m.home like p_search_string or m.away like p_search_string );

    return search_results;
end search_for_games;

where 子句将根据需要复制到各个部分 .

我不确定你需要用 p_match_type 参数实现什么逻辑，但无论如何，上面的结构应该是一个开始 .

回复于 2024-05-03T11:20:25+08:00

使用if语句的Oracle游标错误

2 回答

相关问题