建表
CREATE TABLE tab_bras(
ip VARCHAR2(15) PRIMARY KEY,
city_id VARCHAR2(20) NOT NULL,
name VARCHAR2(10) default ' ',
HIGHADDR VARCHAR2(15),
LOWADDR VARCHAR2(15),
netMask VARCHAR2(15) NOT NULL,
INETMASK NUMBER(2) NOT NULL,
type VARCHAR2(5) NOT NULL,
actDevNum NUMBER(15),
fip VARCHAR2(12),
flowaddress VARCHAR2(12),
fhighaddress VARCHAR2(12),
ASSIGNTIME NUMBER(10)
);
函数
/**
* 对IP进行字符串处理,不满三位前面补0,以方便比较。
* 例子:10.10.10.10转换成010010010010
**/
create or replace function transIp(p_value varchar2)
return varchar2
as
v_arrIdx integer := 1;
v_idx integer;
v_str varchar2(500);
v_strs_last varchar2(4000) := p_value;
v_tmpStr varchar2(10);
v_rtnStr varchar2(20) :=
v_varArray varArray := new varArray();
begin
loop
dbms_output.put_line(
v_idx := instr(v_strs_last,
exit when v_idx = 0;
v_str := substr(v_strs_last, 1, v_idx - 1);
v_strs_last := substr(v_strs_last, v_idx + 1);
dbms_output.put_line(
v_varArray.EXTEND;
v_varArray(v_arrIdx) := v_str;
v_arrIdx := v_arrIdx + 1;
end loop;
v_varArray.EXTEND;
v_varArray(v_arrIdx) := v_strs_last;
for v_arrIdx in 1..v_varArray.count LOOP
v_tmpStr := v_varArray(v_arrIdx);
if length(v_tmpStr)=1 then
v_rtnStr := v_rtnStr ||
elsif length(v_tmpStr)=2 then
v_rtnStr := v_rtnStr ||
else
v_rtnStr := v_rtnStr || v_tmpStr;
end if;
end LOOP;
return v_rtnStr;
end transIp;
/
存储过程
CREATE OR REPLACE PROCEDURE BRASSTATEPROC(stime IN gw_devicestatus.last_time%type) AS
--声明语句段
cursor c_oneline is select ip,count(*) num from (select d.device_serialnumber, b.ip from tab_gw_device d, tab_bras b, gw_devicestatus ds where transIp(d.loopback_ip) <= b.FHIGHADDRESS and transIp(d.loopback_ip) >= b.FLOWADDRESS and d.device_id=ds.device_id and ds.last_time>=stime) group by ip;
v_ip tab_bras.ip%type;
v_num tab_bras.ACTDEVNUM%type;
BEGIN
--执行语句段
for v_pos in c_oneline loop
dbms_output.put_line(v_pos.ip);
v_ip := v_pos.ip;
v_num := v_pos.num;
update tab_bras set ACTDEVNUM=v_num where ip=v_ip;
end loop;
commit;
EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');
when others then
dbms_output.put_line('Other exception occured!');
END;
/