带参数的游标函数一

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;

/