oracle存储过程条件替空的判断_Oracle开发

oracle存储过程条件为空的判断

SQL code

CREATE OR REPLACE PROCEDURE SP_QRY_CWHZ(BEGDATE DATE,ENDDATE DATE) isbegin 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' 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;

输入的是BEGDATE，ENDDATE 两个参数，想在没有输入参数的时候不执行按时间段查询的语句

SQL code

 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')

不知道存储过程的sql改怎么修改，请求帮助！

------解决方案--------------------
使用动态SQL语句能够实现

SQL code

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;
------解决方案--------------------
SQL codeCREATE 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'')              GROUP BY TO_CHAR(CZSJ,''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'')              GROUP BY TO_CHAR(CZSJ,''YYYY-MM-DD'')';  ELSE      NULL;  END IF ; UPDATE T_QRY_CWHZB SET DF=YCZJE,ZJE=YCZJE+KYE;END ;
------解决方案--------------------
SQL code--没有环境测试，你试下 如果有错误 把具体的错误信息发出来CREATE OR REPLACE PROCEDURE SP_QRY_CWHZ(BEGDATE DATE,ENDDATE DATE) isbeginIF(BEGDATE is NULL or ENDDATE is NULL) THEN --oracle判断是否为空不能用= 要用 is null   dbms_output.put_line('没有给变量足够的值!');else    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    AND CZSJ between BEGDATE AND ENDDATE --既然你数据库中czsj保存的是date类型 就不要用to_char()去转换 这样效率不好  GROUP BY TO_CHAR(CZSJ,'YYYY-MM-DD'); END IF ;                  UPDATE T_QRY_CWHZB SET DF=YCZJE,ZJE=YCZJE+KYE;end SP_QRY_CWHZ;