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 两个参数,想在没有输入参数的时候不执行按时间段查询的语句 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改怎么修改,请求帮助!
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 两个参数,想在没有输入参数的时候不执行按时间段查询的语句 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改怎么修改,请求帮助!
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'
IF(BEGDATE=NULL AND ENDDATE=NULL) THEN
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')
ELSE
END IF
GROUP BY TO_CHAR(CZSJ,'YYYY-MM-DD');
UPDATE T_QRY_CWHZB SET DF=YCZJE,ZJE=YCZJE+KYE;
end SP_QRY_CWHZ;
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;
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 ;
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 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;
--上面漏了个'
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 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;
我是想在没有输入时间的时候查询的是所有的,也就是不按照时间段查询的那句 AND CZSJ between BEGDATE AND ENDDATE 你可能没明白我的意思,然后我自己修改后运行有问题
恩,我就是这么想的,但是对sql的存储过程不太熟悉,,不知道怎么写判断
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;
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