当前位置: 首页 > 教程频道 > 数据库 > oracle >

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

2013-03-26
oracle存储过程条件为空的判断SQL codeCREATE OR REPLACE PROCEDURE SP_QRY_CWHZ(BEGDATE DATE,ENDDATE DA

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 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) 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; 
 
[解决办法]
探讨
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 
  db……

[解决办法]
在过程里加个判断咯
判断这2个入参是否为空咯
为空的话就给个初始化值咯
[解决办法]
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   --条件用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
阅读人气排行