postgreSQL 存储过程

CREATE OR REPLACE FUNCTION mt_f_avl_oee_period_e(i_station character varying, i_date character varying, i_start_time character varying, i_end_time character varying)
  RETURNS integer AS
$BODY$
DECLARE


-- v_start_hour  character varying;
-- v_end_hour character varying;

 
-- v_start_time  character varying;
-- v_end_time character varying;

 v_start_datetime  timestamp;
 v_end_datetime timestamp;

 v_test9_count int;
 v_test9_success int;
 v_test30_count int;
 v_test30_success int;
 
 v_runningtime double precision;
 v_availablerate double precision;

BEGIN

-- hour = even, minute > 30
-- exists
-- 

-- SELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '2 hours' ) into v_start_hour;
-- SELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour;

-- select v_start_hour || ':30' into v_start_time;

-- select v_end_hour || ':30' into v_end_time;


select to_timestamp(i_date|| ' ' || i_start_time , 'YYYY-MM-DD HH24:MI') into v_start_datetime;

select to_timestamp(i_date|| ' ' || i_end_time , 'YYYY-MM-DD HH24:MI') into v_end_datetime;

---------------------------------
-- 9M

select count(1) into v_test9_count from mt_t_serialno_test_data where testcell = i_station and testtype = '9M'
     and createdon >=   v_start_datetime
     and createdon < v_end_datetime    ;
     
-- 9M success
select count(1)into v_test9_success from mt_t_serialno_test t,mt_t_serialno_test_data d 
where d.serialno=t.serialno 
and d.testcell = i_station
and t.testtype='9M' 
and t.teststatus='P'
and d.createdon >=   v_start_datetime
and d.createdon < v_end_datetime  ;

-- 30M
select count(1) into v_test30_count from mt_t_serialno_test_data 
     where testcell = i_station and testtype = '30M'
     and createdon >=   v_start_datetime
     and createdon < v_end_datetime ;
     
-- 30M success
select count(1)into v_test30_success from mt_t_serialno_test t,mt_t_serialno_test_data d 
where d.serialno=t.serialno 
and d.testcell = i_station
and t.testtype='30M' 
and t.teststatus='P'
and d.createdon >=   v_start_datetime
and d.createdon < v_end_datetime  ;

-- running time
select coalesce( sum(duration) , 0) into v_runningtime  from engine_test  where station = 'GCIC_' || i_station  /* and type = 1 */
     and state_start >=   v_start_datetime
     and state_stop < v_end_datetime    ;


-- available rate
select v_runningtime/7200 into v_availablerate;


insert into avl_oee_period
          (station, start_time, end_time, test9_count, test9_success, test30_count,test30_success,runningtime,availablerate,date_,
          active, createdon, createdby, rowversionstamp)
          values
          (i_station, to_char( v_start_datetime, 'HH24:MI'), to_char( v_end_datetime, 'HH24:MI'), v_test9_count, v_test9_success, v_test30_count, v_test30_success, v_runningtime/60, v_availablerate, now(),
          1, now(), 'mt',  1);

---------------------------------

RETURN 1;


EXCEPTION

WHEN others THEN
    RAISE;

RETURN 0;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION mt_f_avl_oee_period_e(character varying, character varying, character varying, character varying)
  OWNER TO postgres;

************************************************************************************************************************************************************

-- Function: mt_f_avl_oee_period_midnight(character varying, character varying, character varying, character varying)

-- DROP FUNCTION mt_f_avl_oee_period_midnight(character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION mt_f_avl_oee_period_midnight(i_station character varying, i_date character varying, i_start_time character varying, i_end_time character varying)
  RETURNS integer AS
$BODY$
DECLARE


-- v_start_hour  character varying;
-- v_end_hour character varying;

 
-- v_start_time  character varying;
-- v_end_time character varying;

 v_start_datetime  timestamp;
 v_end_datetime timestamp;

 v_test9_count int;
 v_test9_success int;
 v_test30_count int;
 v_test30_success int;
 
 v_runningtime double precision;
 v_runtime double precision;
 v_endtime_interval double precision;
 v_starttime_interval double precision;
 
 v_availablerate double precision;

BEGIN

-- hour = even, minute > 30
-- exists
-- 

-- SELECT EXTRACT(HOUR FROM CURRENT_TIME - interval '2 hours' ) into v_start_hour;
-- SELECT EXTRACT(HOUR FROM CURRENT_TIME ) into v_end_hour;

-- select v_start_hour || ':30' into v_start_time;

-- select v_end_hour || ':30' into v_end_time;


-- select to_timestamp((timestamp i_date ) , 'YYYY-MM-DD HH24:MI') into v_start_datetime;

-- select to_timestamp(to_char((date i_date - interval '1 day' ), 'YYYY-MM-DD')|| ' ' || i_start_time , 'YYYY-MM-DD HH24:MI') into v_start_datetime;

select to_timestamp(i_date || ' ' || i_end_time , 'YYYY-MM-DD HH24:MI') into v_end_datetime;

select v_end_datetime - interval '450 minutes' into v_start_datetime;

---------------------------------
-- 9M

select count(1) into v_test9_count from mt_t_serialno_test_data where testcell = i_station and testtype = '9M'
     and createdon >= v_start_datetime
     and createdon < v_end_datetime ;
     
-- 9M success
select count(1)into v_test9_success from mt_t_serialno_test t,mt_t_serialno_test_data d 
where d.serialno=t.serialno 
and d.testcell = i_station
and t.testtype='9M' 
and t.teststatus='P'
and d.createdon >=  v_start_datetime
and d.createdon < v_end_datetime  ;

-- 30M
select count(1) into v_test30_count from mt_t_serialno_test_data 
     where testcell = i_station and testtype = '30M'
     and createdon >=   v_start_datetime
     and createdon < v_end_datetime ;
     
-- 30M success
select count(1)into v_test30_success from mt_t_serialno_test t,mt_t_serialno_test_data d 
where d.serialno=t.serialno 
and d.testcell = i_station
and t.testtype='30M' 
and t.teststatus='P'
and d.createdon >=   v_start_datetime
and d.createdon < v_end_datetime  ;

-- running time

--select date_part('epoch', timestamp '2013-11-28 11:00'- timestamp '2013-11-28 01:30' );  

select coalesce( sum(duration) , 0) into v_runningtime  from engine_test  where station = 'GCIC_' || i_station  /* and type = 1 */
     and state_start >= v_start_datetime
     and state_stop < v_end_datetime    ;

--select state_start - v_start_datetime into v_fronttime;
--select v_end_datetime - state_stop into v_endtime;
-- v_runtime := v_runningtime + v_fronttime + v_endtime

--starttime_interval
select state_stop - v_start_datetime into v_starttime_interval from engine_test 
where state_start <= v_start_datetime and state_stop > v_start_datetime
and station = 'GCIC_' || i_station and type = 1 ;

--endtime_interval
select  v_end_datetime - state_start into v_endtime_interval from engine_test 
where state_start <= v_end_datetime and state_stop > v_end_datetime 
and type = 1 and station = 'GCIC_' || i_station
union 
select v_end_datetime - createdon from engine_test 
where createdon < v_end_datetime and next_state =1 
and id = (select max(id) from engine_test where station = 'GCIC_'|| i_station); /*the last item in engine_test table for the certain station */

v_runtime := v_start_datetime + v_runningtime + v_endtime_interval;

-- available rate
select v_runtime/72 into v_availablerate;


insert into avl_oee_period
          (station, start_time, end_time, test9_count, test9_success, test30_count,test30_success,runningtime,availablerate,date_,
          active, createdon, createdby, rowversionstamp)
          values
          (i_station, to_char( v_start_datetime, 'HH24:MI'), to_char( v_end_datetime, 'HH24:MI'), v_test9_count, v_test9_success, v_test30_count, v_test30_success, v_runningtime/60, v_availablerate, now(),
          1, now(), 'mt',  1);

---------------------------------

RETURN 1;


EXCEPTION

WHEN others THEN
    RAISE;

RETURN 0;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION mt_f_avl_oee_period_midnight(character varying, character varying, character varying, character varying)
  OWNER TO postgres;