CASE
               WHEN A.INAIM = 'O' AND A.SPECIALREASONCODE_T IS NULL THEN
                A.BL
               WHEN A.INAIM = 'O' AND A.SPECIALREASONCODE_T IS NOT NULL THEN
                NVL(GET_EIRBILLNO(A.RECORDID), A.BL)
               WHEN A.INAIM = 'I' THEN
                GET_EIRBILLNO(A.RECORDID)
               WHEN A.INAIM = 'D' THEN
                NVL(GET_EIRBILLNO(A.RECORDID), GET_EIRBILLNO_S(A.RECORDID))
               ELSE
                A.BL
             END 如上,我想把上面的语句做成一个函数,怎么改CREATE OR REPLACE FUNCTION get_BOOKING_NBR()
...

解决方案 »

  1.   

    -- 要加四个参数,感觉还不如这样写,看着舒服;create function fn(INAIM               varchar2,
                       SPECIALREASONCODE_T varchar2,
                       RECORDID            varchar2,
                       BL                  varchar2) 
    return varchar2 
    as
        ret varchar2(200);
    begin
        select CASE
                   WHEN INAIM = 'O' AND SPECIALREASONCODE_T IS NULL THEN
                    BL
                   WHEN INAIM = 'O' AND SPECIALREASONCODE_T IS NOT NULL THEN
                    NVL(GET_EIRBILLNO(RECORDID), BL)
                   WHEN INAIM = 'I' THEN
                    GET_EIRBILLNO(RECORDID)
                   WHEN INAIM = 'D' THEN
                    NVL(GET_EIRBILLNO(RECORDID), GET_EIRBILLNO_S(RECORDID))
                   ELSE
                    BL
               END
          into ret
          from dual;
        return ret;
    end;
    /
      

  2.   

    CREATE OR REPLACE FUNCTION get_BOOKING_NBR() return varchar2
    is
    变量定义
    begin
    select 字段 into 变量 from 表
    if 判断相关变量 then
    return 。。
    elsif 判断变量 then
    return 。。
    ………………
    end if;
    end;