Stored Procedures-Examples

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 to cur_date from dual; --Current date
Select To_date (p_day, ' YYYYMMDD ') into the sta_date from dual; --Date of statistics
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;

Select COUNT (*) c
Into v_cnt_301e
From T_bac301_prcess_state t
where T.channel in (' 3 ', ' 4 ')
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

Select COUNT (*) c
Into v_cnt_301h
From T_bac301_prcess_state t
where T.channel in (' 1 ', ' 2 ')
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;
--xx2
Select COUNT (*) c
Into v_cnt_402
From T_send_datagrams t
where T.message_type = ' bac402 '
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

Select COUNT (*) c
Into v_cnt_302e
From T_bac302_prcess_state t
where T.channel in (' 3 ', ' 4 ')
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

Select COUNT (*) c
Into v_cnt_302h
From T_bac302_prcess_state t
where T.channel in (' 1 ', ' 2 ')
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;
--xx3
Select COUNT (*) c
Into v_cnt_403
From T_send_datagrams t
where T.message_type = ' bac403 '
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

Select COUNT (*) c
Into v_cnt_303e
From T_bac303_prcess_state t
where T.channel in (' 3 ', ' 4 ')
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

Select COUNT (*) c
Into v_cnt_303h
From T_bac303_prcess_state t
where T.channel in (' 1 ', ' 2 ')
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

--xx4
Select COUNT (*) c
Into v_cnt_404
From T_send_datagrams t
where T.message_type = ' bac404 '
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

Select COUNT (*) c
Into v_cnt_304e
From T_bac304_prcess_state t
where T.channel in (' 3 ', ' 4 ')
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

Select COUNT (*) c
Into v_cnt_304h
From T_bac304_prcess_state t
where T.channel in (' 1 ', ' 2 ')
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;
--x11
Select COUNT (*) c
Into v_cnt_311
From T_bac311_prcess_state t

where To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

Select COUNT (*) c
Into v_cnt_411
From T_send_datagrams t
where T.message_type = ' bac411 '
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

--x12
Select COUNT (*) c
Into v_cnt_312
From T_bac312_prcess_state t
where To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

Select COUNT (*) c
Into v_cnt_412
From T_send_datagrams t
where T.message_type = ' bac412 '
and To_date (To_char (t.last_oper_time, ' yyyymmdd '), ' yyyymmdd ') =
Sta_date;

--type all add up Count
--xx1
Select COUNT (*) c
Into V_cnt_401_addup
From T_send_datagrams t
where T.message_type = ' bac401 ';

Select COUNT (*) c
Into V_cnt_301e_addup
From T_bac301_prcess_state t
where T.channel in (' 3 ', ' 4 ');

Select COUNT (*) c
Into V_cnt_301h_addup
From T_bac301_prcess_state t
where T.channel in (' 1 ', ' 2 ');
--xx2
Select COUNT (*) c
Into V_cnt_402_addup
From T_send_datagrams t
where T.message_type = ' bac402 ';

Select COUNT (*) c
Into V_cnt_302e_addup
From T_bac302_prcess_state t
where T.channel in (' 3 ', ' 4 ');

Select COUNT (*) c
Into V_cnt_302h_addup
From T_bac302_prcess_state t
where T.channel in (' 1 ', ' 2 ');
--xx3
Select COUNT (*) c
Into V_cnt_403_addup
From T_send_datagrams t
where T.message_type = ' bac403 ';

Select COUNT (*) c
Into V_cnt_303e_addup
From T_bac303_prcess_state t
where T.channel in (' 3 ', ' 4 ');

Select COUNT (*) c
Into V_cnt_303h_addup
From T_bac303_prcess_state t
where T.channel in (' 1 ', ' 2 ');

--xx4
Select COUNT (*) c
Into V_cnt_404_addup
From T_send_datagrams t
where T.message_type = ' bac404 ';

Select COUNT (*) c
Into V_cnt_304e_addup
From T_bac304_prcess_state t
where T.channel in (' 3 ', ' 4 ');

Select COUNT (*) c
Into V_cnt_304h_addup
From T_bac304_prcess_state t
where T.channel in (' 1 ', ' 2 ');
--x11
Select COUNT (*) c into v_cnt_311_addup from T_bac311_prcess_state t;

Select COUNT (*) c
Into V_cnt_411_addup
From T_send_datagrams t
where T.message_type = ' bac411 ';

--x12
Select COUNT (*) c into v_cnt_312_addup from T_bac312_prcess_state t;

Select COUNT (*) c
Into V_cnt_412_addup
From T_send_datagrams t
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;

Stored Procedures-Examples