1
回答
oracle 存储过程不执行
极速云服务器,低至1.04元/天>>>   

CREATE OR REPLACE PROCEDURE "UUMS"."STATIS_SOURCE_TYPE"
AS

    CURSOR source_cur is
  select a.SOURCE_ID, count(a.SOURCE_ID)as cnt ,to_char(a.REG_TIME,'yyyymmdd')as REG_TIME
  from users a ,(select NVL(max(d.REG_DATE),'20000101')as maxdate from SOURCE_STATIS d)d
  where
        TO_DATE(to_char(a.REG_TIME,'yyyy-mm-dd'),'yyyy-mm-dd') >=  TO_DATE(d.maxdate,'yyyy-mm-dd')
    and
        a.USER_STATUS=1
  and a.SOURCE_ID is not null
  group by a.SOURCE_ID,to_char(a.REG_TIME,'yyyymmdd')
  order by to_char(a.REG_TIME,'yyyymmdd') ;

BEGIN
        insert into job_call_table(name ,parameter,DESCRIPTION)
        values('statis_source_type',NULL,'统计用户注册来源');
    
        for source_row in source_cur
        loop
            dbms_output.put_line(source_row.REG_TIME);

        delete from    SOURCE_STATIS a
                where a.REG_DATE = source_row.REG_TIME and a.SOURCE_ID= source_row.SOURCE_ID;

        insert into  SOURCE_STATIS(ID,REG_DATE,USER_CNT,SOURCE_ID)values(
                source_statis_seq.nextval,
                source_row.REG_TIME,
                source_row.cnt,
                source_row.SOURCE_ID
            );
        end loop;
commit;
end;

 

CREATE OR REPLACE PROCEDURE "UUMS"."STATIS_CALL" as
begin
  /*
    生产需要将统计报表相关存储过程加入
  */
  statis_source_type();
end;

最后oracle里     statis_call;    trunc(sysdate+1)+1/24........

 

 

:  蓝色的部分可以执行

<无标签>
举报
enjoyoschina
发帖于2个月前 1回/25阅

你open cursor了?为啥不采用fetch into 呢?你的游标都没打开能取到游标里的值,我看这有问题吧。

你可以试一下这么写,

CREATE OR REPLACE PROCEDURE "UUMS"."STATIS_SOURCE_TYPE" AS

	CURSOR SOURCE_CUR IS
		SELECT A.SOURCE_ID,
					 COUNT(A.SOURCE_ID) AS CNT,
					 TO_CHAR(A.REG_TIME, 'yyyymmdd') AS REG_TIME
			FROM USERS A,
					 (SELECT NVL(MAX(D.REG_DATE), '20000101') AS MAXDATE
							FROM SOURCE_STATIS D) D
		 WHERE TO_DATE(TO_CHAR(A.REG_TIME, 'yyyy-mm-dd'), 'yyyy-mm-dd') >=
					 TO_DATE(D.MAXDATE, 'yyyy-mm-dd')
			 AND A.USER_STATUS = 1
			 AND A.SOURCE_ID IS NOT NULL
		 GROUP BY A.SOURCE_ID, TO_CHAR(A.REG_TIME, 'yyyymmdd')
		 ORDER BY TO_CHAR(A.REG_TIME, 'yyyymmdd');
	SOURCEID NUMBER;
	CONTS    NUMBER;
	REGTIME  VARCHAR2(20);

BEGIN
	INSERT INTO JOB_CALL_TABLE
		(NAME, PARAMETER, DESCRIPTION)
	VALUES
		('statis_source_type', NULL, '统计用户注册来源');

	OPEN SOURCE_CUR;
	LOOP
		FETCH SOURCE_CUR
			INTO SOURCEID, CONTS, REGTIME;
		EXIT WHEN SOURCE_CUR %NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(REGTIME);
	
		DELETE FROM SOURCE_STATIS A
		 WHERE A.REG_DATE = REGTIME
			 AND A.SOURCE_ID = SOURCEID;
	
		INSERT INTO SOURCE_STATIS
			(ID, REG_DATE, USER_CNT, SOURCE_ID)
		VALUES
			(REGTIME, SOURCE_ROW.REG_TIME, CONTS, SOURCEID);
	END LOOP;
	COMMIT;
	CLOSE SOURCE_CUR;
END;

 

顶部