两个过程，只作参考。
一、存储过程.调用存储过程
CREATE OR REPLACE procedure SEDATUM.SP_HISSTA (nDate NUMBER ) AS -- 声明变量，存放游标值 nTime NUMBER ( 12) ; nData NUMBER ( 12) ; nOpertime NUMBER ( 12) ;
nStartDay NUMBER (12); nCalcType NUMBER (12); strSQL CHAR ( 500) ; BEGIN nStartDay := 1; nCalcType := 1; -- 结算日 SELECT F4050_INTVAL INTO nStartDay FROM SEMODEL.TB4050_SYSTEMINFO WHERE F4050_CODE= 'START_DAY' ; -- 结算方式：0－月初结算(结算上月)；1－月末结算（结算本月） SELECT F4050_INTVAL INTO nCalcType FROM SEMODEL.TB4050_SYSTEMINFO WHERE F4050_CODE = 'CALC_TYPE' ; nTime := (TO_DATE (nDate, 'YYYYMMDD' ) - TO_DATE ('19700101' , 'YYYYMMDD' )) * 86400 - 8 * 3600 ; nOpertime := nTime + (SYSDATE - TO_DATE (nDate, 'YYYYMMDD' )) * 86400; -- 执行日统计 调用存储过程 SP_DAYDATA YYYYMMM strSQL := ' CALL SP_DAYDATA ' || TO_CHAR (TO_DATE (nDdate, 'YYYYMMDD '), 'YYYYMM ')|| '(' || ''' HISSTA'',' || '1' || ',' || TO_CHAR (nTime)|| ',' || TO_CHAR (nDate)|| ',' || TO_CHAR (nOpertime)|| ', NULL )' ; EXECUTE IMMEDIATE strSQL; END SP_HISSTA;
/
二、存储过程.游标操作使用
create or replace procedure SP_DAYDATA201104(seuser VARCHAR2,nOption NUMBER, nTime NUMBER, nDate NUMBER, nTimeStamp NUMBER, nSavePoint VARCHAR2) AS -- 声明游标保存所有要处理的点的代码 -- 如果点不存在或不存盘，则不统计 CURSOR cursor_save_point IS SELECT DISTINCT F4007_POINTCODE, F4004_MEASTYPEID, F4007_SAVEINTERVAL FROM SEMODEL.TB4007_ANAPOINT WHERE (nOption = 1) AND (F4007_SAVEINTERVAL > 0) -- 声明游标保存指定点指定时间段的所有瞬时值 CURSOR cursor_real_data(SavePt VARCHAR2, nTimeStart NUMBER, nTimeEnd NUMBER) IS SELECT POINTTIME, STATUS,VALUE, OPERTIME FROM SEDATUM.se_datum201104 WHERE POINTCODE = SavePt AND FLAG = 1 AND POINTTIME >= nTimeStart AND POINTTIME < nTimeEnd ORDER BY POINTTIME ASC; -- 声明变量，存放游标值 wk_save_point CHAR(24); wk_meastype NUMBER(6); wk_interval NUMBER(6); wk_kdtime NUMBER(12); wk_kddata NUMBER(19,6); wk_status NUMBER(12); wk_opertime NUMBER(12); -- 声明当地变量 wk_day_max NUMBER; wk_day_min NUMBER; wk_day_max_at NUMBER; wk_day_min_at NUMBER; wk_start_time NUMBER; wk_end_time NUMBER; wk_date NUMBER; wk_flag NUMBER; v_dummyval NUMBER; wk_factor NUMBER; BEGIN wk_start_time := nTime; wk_end_time := nTime + 86400; wk_date := nDate; OPEN cursor_save_point; LOOP BEGIN FETCH cursor_save_point INTO wk_save_point,wk_meastype,wk_interval; EXIT WHEN cursor_save_point%NOTFOUND; wk_day_max := 0; wk_day_min := 0; wk_day_max_at := 0; wk_day_min_at := 0; OPEN cursor_real_data(wk_save_point,wk_start_time,wk_end_time); LOOP BEGIN FETCH cursor_real_data INTO wk_kdtime, wk_status, wk_kddata, wk_opertime; EXIT WHEN cursor_real_data%NOTFOUND; IF (wk_day_count = 0) THEN wk_day_max := wk_kddata; wk_day_max_at := wk_kdtime; wk_day_min := wk_kddata; wk_day_min_at := wk_kdtime; END IF; IF (abs(wk_kddata) > abs(wk_day_max)) THEN BEGIN wk_day_max := wk_kddata; wk_day_max_at := wk_kdtime; END; END IF; IF (abs(wk_kddata) < abs(wk_day_min)) THEN BEGIN wk_day_min := wk_kddata; wk_day_min_at := wk_kdtime; END; END IF; END; END LOOP; CLOSE cursor_real_data; -- 删除原有数据 BEGIN DELETE SEDATUM.SE_DAYDATA201104 T WHERE T.POINTCODE = wk_save_point AND T.POINTTIME = wk_date AND T.FLAG IN (100,101,103); EXCEPTION WHEN OTHERS THEN v_dummyval := NULL; END; COMMIT; -- 日统计最大值(包括发生时间) IF (wk_day_max_at > 0) THEN BEGIN INSERT INTO SEDATUM.SE_DAYDATA201104( POINTCODE, POINTTIME, FLAG, VALUE, APPEND, OPERTIME) VALUES(wk_save_point, wk_date, 100, wk_day_max, wk_day_max_at, i_timestamp); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN BEGIN v_dummyval := NULL; END; END; END IF; -- 日统计最小值(包括发生时间) BEGIN INSERT INTO SEDATUM.SE_DAYDATA201104( POINTCODE, POINTTIME, FLAG, VALUE, APPEND, OPERTIME) VALUES(wk_save_point, wk_date, 101, wk_day_min, wk_day_min_at, i_timestamp); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN BEGIN v_dummyval := NULL; END; END; END LOOP; CLOSE cursor_save_point; /* 必须提交上次游标操作，否则下次游标操作失败 */ COMMIT; END SP_DAYDATA201104; /
