100分求一sql语句

Question

有人帮我写了ms-sql的,谁能该成oracle的:
这个应该可以静态处理：if not exists (
select 1 from tablename
where department_type   =@department_type   
)
select * from tablename
where department_type   ='其他'
and (
(low_sign='<'
and low_account<@account
) or
(low_sign='<='
and low_account<=@account
)
)
and (
(high_sign='>'
and high_account>@account
) or
(high_sign='>='
and high_account<=@account
)
)
else
select * from tablename
where department_type   =@department_type
and (
(low_sign='<'
and low_account<@account
) or
(low_sign='<='
and low_account<=@account
)
)
and (
(high_sign='>'
and high_account>@account
) or
(high_sign='>='
and high_account<=@account
)
)

Answer 1

对了,最好写成函数,我需要返回persons和operator_type,这两个字段的值.

Answer 2

请问楼主你要什么结果!
说了半天,我真不知道你要什么!!当然就没发帮你了Thanks
Hima

Answer 3

我也不知道楼主到底想要什么结果。随便改了一下，试了一下F_GET_VAL('D3',2,'PERSONS')，还是行的通的。
--已知 department_type(可能是d3)和account,如果department_type=d3,按照其他处理
--返回persons和operator_type
create or replace function f_get_val
       (
        pi_department_type varchar2,   
        pi_account number,
        pi_type_str varchar2    --类别persons和operator_type，判断返回值是persons或operator_type
       )
    return varchar2 is
        v_str varchar2(20);   --返回结果
        v_i number(6);
        v_j number(6);
begin
    v_str := '';
    select count(*) into v_j 
      from tablename 
      where department_type=pi_department_type;
      dbms_output.put_line('v_j='||v_j||'  pi_department_type='||pi_department_type||'  pi_type_str='||pi_type_str);
      --取得persons||'|'||operator_type,无符合条件的记录时返回''
    if v_j=0
       then
           dbms_output.put_line('department_type不存在');
           select persons||'|'||operator_type into v_str 
             from tablename 
             where department_type='其他'
              and (
                   (low_sign='<'
                     and low_account<pi_account
                   ) 
                  or
                   (low_sign='=<'
                     and low_account<=pi_account
                   )
                  )
             and (
                  (high_sign='<'
                    and high_account>pi_account
                  )
                 or
                  (high_sign='<='
                    and high_account<=pi_account
                  )
                 );
    else
           dbms_output.put_line('department_type存在');
           select persons||'|'||operator_type into v_str 
             from tablename 
             where department_type=pi_department_type
              and (
                   (low_sign='<'
                     and low_account<pi_account
                   ) 
                  or
                   (low_sign='=<'
                     and low_account<=pi_account
                   )
                  )
              and (
                   (high_sign='<'
                    and high_account>pi_account
                   )
                  or
                   (high_sign='<='
                    and high_account<=pi_account
                   )
                  );
    end if;
    dbms_output.put_line('得到v_str:'||v_str);
    v_i := instr(v_str,'|');
    if pi_type_str='persons' then
       v_str := substr(v_str,0,v_i-1);
    elsif pi_type_str='operator_type' then
       v_str := substr(v_str,v_i+1,length(v_str));
    else
       v_str := '';
    end if;
        dbms_output.put_line('返回v_str:'||v_str);
    return v_str;
    exception
      when others then
       dbms_output.put_line('error------');
       return '';
end f_get_val;
/
show error;