oracle存储过程条件替空的判断-Oracle教程-爱易网页

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

日期：2014-05-17　浏览次数：20958 次

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

SQL code


CREATE 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 
  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 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'') 
             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) is
begin
IF(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 C

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

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

相关资料更多>

推荐阅读更多>