存儲過程--例子

create or replace procedure cust_xchgsta_byday_proc(p_day varchar2) is

--by day type_all

v_cnt_401 t_console_daysta_tab.cnt_401_snd%type;

v_cnt_301e t_console_daysta_tab.cnt_301e_rcv%type;

v_cnt_301h t_console_daysta_tab.cnt_301h_rcv%type;

v_cnt_402 t_console_daysta_tab.cnt_402_snd%type;

v_cnt_302e t_console_daysta_tab.cnt_302e_rcv%type;

v_cnt_302h t_console_daysta_tab.cnt_302h_rcv%type;

v_cnt_403 t_console_daysta_tab.cnt_403_snd%type;

v_cnt_303e t_console_daysta_tab.cnt_303e_rcv%type;

v_cnt_303h t_console_daysta_tab.cnt_303h_rcv%type;

v_cnt_404 t_console_daysta_tab.cnt_404_snd%type;

v_cnt_304e t_console_daysta_tab.cnt_304e_rcv%type;

v_cnt_304h t_console_daysta_tab.cnt_304h_rcv%type;

v_cnt_411 t_console_daysta_tab.cnt_411_snd%type;

v_cnt_311 t_console_daysta_tab.cnt_311_rcv%type;

v_cnt_412 t_console_daysta_tab.cnt_412_snd%type;

v_cnt_312 t_console_daysta_tab.cnt_312_rcv%type;

--add up type_all

v_cnt_401_addup t_console_daysta_tab.addup_401_snd%type;

v_cnt_301e_addup t_console_daysta_tab.addup_301e_rcv%type;

v_cnt_301h_addup t_console_daysta_tab.addup_301h_rcv%type;

v_cnt_402_addup t_console_daysta_tab.addup_402_snd%type;

v_cnt_302e_addup t_console_daysta_tab.addup_302e_rcv%type;

v_cnt_302h_addup t_console_daysta_tab.addup_302h_rcv%type;

v_cnt_403_addup t_console_daysta_tab.addup_403_snd%type;

v_cnt_303e_addup t_console_daysta_tab.addup_303e_rcv%type;

v_cnt_303h_addup t_console_daysta_tab.addup_303h_rcv%type;

v_cnt_404_addup t_console_daysta_tab.addup_404_snd%type;

v_cnt_304e_addup t_console_daysta_tab.addup_304e_rcv%type;

v_cnt_304h_addup t_console_daysta_tab.addup_304h_rcv%type;

v_cnt_411_addup t_console_daysta_tab.addup_411_snd%type;

v_cnt_311_addup t_console_daysta_tab.addup_311_rcv%type;

v_cnt_412_addup t_console_daysta_tab.addup_412_snd%type;

v_cnt_312_addup t_console_daysta_tab.addup_312_rcv%type;

cur_date date;

sta_date date;

begin

select sysdate into cur_date from dual; --目前日期

select to_date(p_day, 'yyyymmdd') into sta_date from dual; --統計日期

dbms_output.put_line('cur_date is ' || cur_date);

dbms_output.put_line('sta_date is ' || sta_date);

--count by day

--xx1

select count(*) c

into v_cnt_401

from t_send_datagrams t

where t.message_type = 'bac401'

and to_date(to_char(t.last_oper_time, 'yyyymmdd'), 'yyyymmdd') =

sta_date;

into v_cnt_301e

from t_bac301_prcess_state t

where t.channel in ('3', '4')

into v_cnt_301h

where t.channel in ('1', '2')

--xx2

into v_cnt_402

where t.message_type = 'bac402'

into v_cnt_302e

from t_bac302_prcess_state t

into v_cnt_302h

--xx3

into v_cnt_403

where t.message_type = 'bac403'

into v_cnt_303e

from t_bac303_prcess_state t

into v_cnt_303h

--xx4

into v_cnt_404

where t.message_type = 'bac404'

into v_cnt_304e

from t_bac304_prcess_state t

into v_cnt_304h

--x11

into v_cnt_311

from t_bac311_prcess_state t

where to_date(to_char(t.last_oper_time, 'yyyymmdd'), 'yyyymmdd') =

into v_cnt_411

where t.message_type = 'bac411'

--x12

into v_cnt_312

from t_bac312_prcess_state t

into v_cnt_412

where t.message_type = 'bac412'

--type all add up count

into v_cnt_401_addup

where t.message_type = 'bac401';

into v_cnt_301e_addup

where t.channel in ('3', '4');

into v_cnt_301h_addup

where t.channel in ('1', '2');

into v_cnt_402_addup

where t.message_type = 'bac402';

into v_cnt_302e_addup

into v_cnt_302h_addup

into v_cnt_403_addup

where t.message_type = 'bac403';

into v_cnt_303e_addup

into v_cnt_303h_addup

into v_cnt_404_addup

where t.message_type = 'bac404';

into v_cnt_304e_addup

into v_cnt_304h_addup

select count(*) c into v_cnt_311_addup from t_bac311_prcess_state t;

into v_cnt_411_addup

where t.message_type = 'bac411';

select count(*) c into v_cnt_312_addup from t_bac312_prcess_state t;

into v_cnt_412_addup

where t.message_type = 'bac412';

--remove old data

delete from t_console_daysta_tab where sta_day = sta_date;

insert into t_console_daysta_tab k

(k.id,

k.sta_day,

k.cnt_401_snd,

k.cnt_301e_rcv,

k.cnt_301h_rcv,

k.cnt_402_snd,

k.cnt_302e_rcv,

k.cnt_302h_rcv,

k.cnt_403_snd,

k.cnt_303e_rcv,

k.cnt_303h_rcv,

k.cnt_404_snd,

k.cnt_304e_rcv,

k.cnt_304h_rcv,

k.cnt_311_rcv,

k.cnt_411_snd,

k.cnt_312_rcv,

k.cnt_412_snd,

k.addup_401_snd,

k.addup_301e_rcv,

k.addup_301h_rcv,

k.addup_402_snd,

k.addup_302e_rcv,

k.addup_302h_rcv,

k.addup_403_snd,

k.addup_303e_rcv,

k.addup_303h_rcv,

k.addup_404_snd,

k.addup_304e_rcv,

k.addup_304h_rcv,

k.addup_311_rcv,

k.addup_411_snd,

k.addup_312_rcv,

k.addup_412_snd,

k.sta_time)

values

(sys_guid(),

sta_date,

v_cnt_401,

v_cnt_301e,

v_cnt_301h,

v_cnt_402,

v_cnt_302e,

v_cnt_302h,

v_cnt_403,

v_cnt_303e,

v_cnt_303h,

v_cnt_404,

v_cnt_304e,

v_cnt_304h,

v_cnt_311,

v_cnt_411,

v_cnt_312,

v_cnt_412,

v_cnt_401_addup,

v_cnt_301e_addup,

v_cnt_301h_addup,

v_cnt_402_addup,

v_cnt_302e_addup,

v_cnt_302h_addup,

v_cnt_403_addup,

v_cnt_303e_addup,

v_cnt_303h_addup,

v_cnt_404_addup,

v_cnt_304e_addup,

v_cnt_304h_addup,

v_cnt_311_addup,

v_cnt_411_addup,

v_cnt_312_addup,

v_cnt_412_addup,

cur_date);

commit;

end cust_xchgsta_byday_proc;

未來星開發團隊--狒狒

QQ：9715234