带参数的游标函数一

此函数根据ODRORGID、ODRDLVRID、ODRITEMID和ODRORGTYPE参数,从ODRM021_AUTOORG表中查询对应的方案ID。它通过不同的查询逻辑来覆盖门店和物流规则,确保在不同条件下正确返回方案ID。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

create or replacefunction FUN_ODR_AUTOORGSCHMID(ODRORGID in VARCHAR2,

                                                ODRDLVRID in VARCHAR2,

                                                ODRITEMID in VARCHAR2,

                                                ODRORGTYPE in VARCHAR2)

  return varchar2 is

  TMP_ODRM021_SCHMID VARCHAR2(13) := NULL; --用于不同条件下的方案名称

  --门店

  CURSOR ODR_A(ORGID varchar2,ITEMID varchar2) IS SELECT ODRM021_SCHMID FROM ODRM021_AUTOORG

                  WHEREODRM021_AUTOORG.ODRM021_ORGID = ORGID

                        ANDODRM021_AUTOORG.ODRM021_ITEMID = ITEMID

                        ANDODRM021_AUTOORG.ODRM021_AUTOFLG = '1';

 

  CURSOR ODR_B(ORGIDvarchar2) IS SELECT ODRM021_SCHMID FROM ODRM021_AUTOORG

                  WHEREODRM021_AUTOORG.ODRM021_ORGID = ORGID

                        ANDODRM021_AUTOORG.ODRM021_ITEMID = '-1'

                        ANDODRM021_AUTOORG.ODRM021_AUTOFLG = '1';

 

  --物流

  CURSOR ODR_WA(ORGID varchar2,DLVRIDvarchar2,ITEMID varchar2) IS SELECT ODRM021_SCHMID FROM ODRM021_AUTOORG

                  WHEREODRM021_AUTOORG.ODRM021_ORGID = ORGID

                         ANDODRM021_AUTOORG.ODRM021_DLVRID = DLVRID

                         AND ODRM021_AUTOORG.ODRM021_ITEMID =ITEMID

                         ANDODRM021_AUTOORG.ODRM021_AUTOFLG = '1';

 

  CURSOR ODR_WB(ORGID varchar2,ITEMID varchar2)IS SELECT ODRM021_SCHMID FROM ODRM021_AUTOORG

                   WHERE ODRM021_AUTOORG.ODRM021_ORGID= ORGID

                         ANDODRM021_AUTOORG.ODRM021_ITEMID = ITEMID

                         ANDODRM021_AUTOORG.ODRM021_DLVRID = '-1'

                         ANDODRM021_AUTOORG.ODRM021_AUTOFLG = '1';

 

  CURSOR ODR_WC(ORGID varchar2,DLVRID varchar2)IS SELECT ODRM021_SCHMID FROM ODRM021_AUTOORG

                   WHEREODRM021_AUTOORG.ODRM021_ORGID = ORGID

                         ANDODRM021_AUTOORG.ODRM021_DLVRID = DLVRID

                         ANDODRM021_AUTOORG.ODRM021_ITEMID = '-1'

                         ANDODRM021_AUTOORG.ODRM021_AUTOFLG = '1';

 

  CURSOR ODR_WD(ORGID varchar2) IS SELECTODRM021_SCHMID FROM ODRM021_AUTOORG

                   WHEREODRM021_AUTOORG.ODRM021_ORGID = ORGID

                         AND ODRM021_AUTOORG.ODRM021_DLVRID= '-1'

                         ANDODRM021_AUTOORG.ODRM021_ITEMID = '-1'

                         ANDODRM021_AUTOORG.ODRM021_AUTOFLG = '1';

  /*

     门店规则:对于门店采用该变量是,ODRORGTYPE= '01'

     而对应物流规则:是当前的ODRORGTYPE = '02'

  */

begin

  --门店和物流规则

  IF ODRORGTYPE = '01' THEN

     OPEN ODR_A(ODRORGID,ODRITEMID);

       LOOP

         FETCH ODR_AINTO TMP_ODRM021_SCHMID;

         EXIT WHENODR_A%NOTFOUND;

       END LOOP;

      CLOSE ODR_A;

        if TMP_ODRM021_SCHMID IS NULL then

           OPEN ODR_B(ODRORGID);

             LOOP

               FETCH ODR_B INTOTMP_ODRM021_SCHMID;

               EXIT WHEN ODR_B%NOTFOUND;

             END LOOP;

            CLOSE ODR_B;

         end if;

  end if;

 

  if ODRORGTYPE = '02' THEN

    OPEN ODR_WA(ODRORGID,ODRDLVRID,ODRITEMID);

       LOOP

         FETCH ODR_WA INTO TMP_ODRM021_SCHMID;

         EXIT WHEN ODR_WA%NOTFOUND;

       END LOOP;

      CLOSE ODR_WA;

            if TMP_ODRM021_SCHMID IS NULL then

               OPEN ODR_WB(ODRORGID,ODRITEMID);

                 LOOP

                   FETCH ODR_WB INTOTMP_ODRM021_SCHMID;

                   EXIT WHEN ODR_WB%NOTFOUND;

                 END LOOP;

                CLOSE ODR_WB;

                     if TMP_ODRM021_SCHMID ISNULL then

                         OPENODR_WC(ODRORGID,ODRDLVRID);

                           LOOP

                             FETCH ODR_WC INTOTMP_ODRM021_SCHMID;

                             EXIT WHENODR_WC%NOTFOUND;

                           END LOOP;

                          CLOSE ODR_WC;

                              ifTMP_ODRM021_SCHMID IS NULL then

                                 OPENODR_WD(ODRORGID);

                                   LOOP

                                     FETCH ODR_WD INTO TMP_ODRM021_SCHMID;

                                     EXIT WHENODR_WD%NOTFOUND;

                                   END LOOP;

                                  CLOSE ODR_WD;

                               end if;

                       end if;

             end if;

  END IF;

  return(TMP_ODRM021_SCHMID);

endFUN_ODR_AUTOORGSCHMID;

/
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值