17,086
社区成员
发帖
与我相关
我的任务
分享
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;
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')
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
--条件用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;
AND CZSJ between BEGDATE AND ENDDATE
--上面漏了个'
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;
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
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
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;