plpgSQL 过程存储 游标

------------------------------
-------函数入口在最后---------
------------------------------

drop table if exists remote_analyze;

create table remote_analyze(--统计表
	timeBegin timestamp not null primary key ,
	controlNum int default 0,
	controlSuccessNum int default 0,
	controlFailseNum int default 0,
	controlFailseRate real default 0,

	---具体功能-------------------------------
	cmd_00101 INTEGER,--总数
	cmd_00101_T INTEGER,--成功次数
	cmd_00101_F INTEGER,--车门上锁 1
	cmd_00101_falseRate real--车门上锁 失败率
	
	
);
-------注释
COMMENT ON COLUMN remote_analyze.timeBegin IS '时间';
COMMENT ON COLUMN remote_analyze.controlNum IS '远控次数';

--建立索引
--create index index_ on remote_send_cmd_history(send_id);


--功能实现
create or replace FUNCTION sear(IN startTime timestamp, IN endTime timestamp, IN str text )
returns void
AS
$$
declare
	timeAt timestamp; --时间戳类型
	
	cur refcursor;--动态游标，查找更改，类似指针
	rec_cur RECORD;--记录，从游标取出一条记录
	
	--静态游标
	cur1 cursor(
		star timestamp
	)for select count(send_id) from remote_send_cmd_history
	 where remote_time between star and star + 	cast(str||''  as interval);
	
	
	
	---------------变量-------------------------------
	--远控次数
	_controlNum remote_analyze.controlNum%type;--%type 类型引用
	
	--远控成功次数
	_controlSuccessNum remote_analyze.controlSuccessNum%type;
	
	--远控失败次数
	_controlFailseNum remote_analyze.controlFailseNum%type;
	
	--远控失败率
	_controlFailseRate remote_analyze.controlFailseRate%type;
	
	
	------------------------具体功能-------------------------------
	_cmd_00101 INTEGER;--总数
	_cmd_00101_T INTEGER;--成功次数
	_cmd_00101_F INTEGER;--车门上锁 1
	_cmd_00101_F_falseRate real;--车门上锁 失败率
	
	
	
	num INTEGER;
 
begin
	timeAt := startTime;
	
	
	while timeAt < endTime loop --循环体  多条件：exp AND/OR exp
	
            --变量清零    
			_controlNum := 0;
			_controlSuccessNum := 0;
			_controlFailseNum  := 0;
			_controlFailseRate := 0;
			
			
			_cmd_00101_F := 0;--车门上锁 1
			_cmd_00101 := 0;--总数
			_cmd_00101_T := 0;--成功次数	
				
			--打开游标
			open cur for select * from remote_send_cmd_history 
            --cast( str ||''  as interval )为时间间隔  可有：timeAt + ‘1 DAY’ 
			where remote_time between timeAt and timeAt + cast( str ||''  as interval ); 
			--统计数值
			loop 
				fetch cur into rec_cur; --取出下条记录
				exit when NOT FOUND;
					--远控次数
					_controlNum := _controlNum + 1;
					
					--远控成功次数
					if rec_cur.status = 'CMD_SUCCEED' then
						_controlSuccessNum := _controlSuccessNum + 1;
					end if;
					
					--远控失败次数
					if rec_cur.status != 'CMD_SUCCEED' then
						_controlFailseNum := _controlFailseNum + 1;
					end if;
					
					
					-----------具体功能----失败次数---------------------------
					--车门上锁 1
					if rec_cur.cmd_code = '00101' then
						_cmd_00101 := _cmd_00101 + 1;
						if rec_cur.status != 'CMD_SUCCEED' then 
							_cmd_00101_F := _cmd_00101_F + 1;
						else _cmd_00101_T := _cmd_00101_T + 1;
						end if;
					end if;
					
					
				
				---------------------------具体功能-------------------------------
				--车门上锁 失败率
				if _controlNum != 0 then
					_cmd_00101_F_falseRate := _cmd_00101_F * 1.0 / _controlNum;
				else _cmd_00101_F_falseRate := 0;
				end if;	
				

			---=========================插入统计数据============================---
			insert into remote_analyze(
				timeBegin ,
				controlNum ,
				controlSuccessNum ,
				controlFailseNum,
				controlFailseRate,
				-----------具体功能-------------------------------
				cmd_00101 ,--总数
				cmd_00101_T ,--成功次数
				cmd_00101_F ,--车门上锁 1
				cmd_00101_falseRate --车门上锁 失败率
				
			)values(
				timeAt,
				_controlNum,
				_controlSuccessNum,
				_controlFailseNum,
				_controlFailseRate,
				-----------具体功能-------------------------------
				_cmd_00101 ,--总数
				_cmd_00101_T ,--成功次数
				_cmd_00101_F ,--车门上锁 1
				_cmd_00101_F_falseRate --车门上锁 失败率
			
			);
			
			
		timeAt := timeAt + cast( str ||''  as interval);
	end loop;

end;
$$

language plpgsql;

--功能：统计param1到param2的每小时内的远控记录，
--@param startTime : 开始时间
--@param endTime : 结束时间
--@param str : 时间间隔
select sear('2019-09-01 00:00:00', '2019-10-17 00:00:00', '1 HOUR');