CREATEORREPLACEFUNCTION fn_ipaddr_to_province (p_ipaddr NUMBER) RETURNVARCHAR2 IS v_ret VARCHAR2 (100) :=''; BEGIN BEGIN SELECT province INTO v_ret FROM ipdb WHERE start_ip <= p_ipaddr AND end_ip >= p_ipaddr AND ROWNUM =1; EXCEPTION WHEN NO_DATA_FOUND THEN v_ret :=''; END; RETURN v_ret; EXCEPTION WHEN OTHERS THEN RAISE; END fn_ipaddr_to_province; /
根据省份确定地址,domainname 中有35条数据。
方法一:
... BEGIN SELECT serverip INTO v_serverip FROM domainname WHERE province = fn_ipaddr_to_province (p_ip) AND ROWNUM =1; EXCEPTION WHEN NO_DATA_FOUND THEN v_serverip :='mp3.u-vv.com'; END; ...
这种方法 CPU 使用率 90% 以上
方法二:
... v_province := fn_ipaddr_to_province (p_ip); BEGIN SELECT serverip INTO v_serverip FROM domainname WHERE province = v_province AND ROWNUM =1; EXCEPTION WHEN NO_DATA_FOUND THEN v_serverip :='default domain'; END; ...