oracle存储过程多条件判断,oracle存储过程条件替空的判断(2)

SQL codeCREATE OR REPLACE PROCEDURE SP_QRY_CWHZ(BEGDATE DATE,ENDDATE DATE) is

begin

INSERT INTO T_QRY_CWHZB(ID,ZSL,SJ,YCZJE,KYE)

SELECT MAX(ID),COUNT(DISTINCT ZFHM),TO_CHAR(CZSJ,'YYYY-MM-DD'),SUM(NVL(CZJE,0)),SUM(NVL(CZHYE,0))

FROM T_RECHARGE

--条件用OR来连接

WHERE ZFBZ='F' AND

((BEGDATE IS NULL AND ENDDATE IS NULL)

OR

( BEGDATE IS NOT NULL AND ENDDATE IS NOT NULL AND TO_CHAR(CZSJ,'YYYY-MM-DD') >= TO_CHAR(BEGDATE,'YYYY-MM-DD') AND TO_CHAR(CZSJ,'YYYY-MM-DD') <= TO_CHAR(ENDDATE,'YYYY-MM-DD')))

GROUP BY TO_CHAR(CZSJ,'YYYY-MM-DD');

UPDATE T_QRY_CWHZB SET DF=YCZJE,ZJE=YCZJE+KYE;

end SP_QRY_CWHZ;

------解决方案--------------------

CREATE OR REPLACE PROCEDURE SP_QRY_CWHZ(BEGDATE DATE, ENDDATE DATE) IS

SQL VARCHAR2(200);

BEGIN

SQL := 'INSERT INTO T_QRY_CWHZB(ID,ZSL,SJ,YCZJE,KYE)

SELECT MAX(ID),COUNT(DISTINCT ZFHM),TO_CHAR(CZSJ,''YYYY-MM-DD''),SUM(NVL(CZJE,0)),SUM(NVL(CZHYE,0))

FROM T_RECHARGE

WHERE ZFBZ=''F''';

IF (BEGDATE IS NOT NULL AND ENDDATE IS NOT NULL) THEN

SQL := SQL || ' AND TO_CHAR(CZSJ,''YYYY-MM-DD'') >= TO_CHAR(BEGDATE,''YYYY-MM-DD'')

AND TO_CHAR(CZSJ,''YYYY-MM-DD'') <= TO_CHAR(ENDDATE,''YYYY-MM-DD'')

GROUP BY TO_CHAR(CZSJ,''YYYY-MM-DD'')';

ELSE

IF (BEGDATE IS NULL AND ENDDATE IS NOT NULL) THEN

SQL := SQL ||

' AND TO_CHAR(CZSJ,''YYYY-MM-DD'') <= TO_CHAR(ENDDATE,''YYYY-MM-DD'')';

ELSE IF (BEGDATE IS NOT NULL AND ENDDATE IS NULL) THEN

SQL:=SQL||' AND TO_CHAR(CZSJ,''YYYY-MM-DD'') >= TO_CHAR(BEGDATE,''YYYY-MM-DD'')';

ELSE

NULL;

END IF ;

execute immediate sql;

UPDATE T_QRY_CWHZB SET DF=YCZJE,ZJE=YCZJE+KYE;

commit;

end SP_QRY_CWHZ;

Ok heeeeeeeeeeeeeeeeeeeh