Oracle的几个Function实例

create or replace function str_list2( key_name in varchar2,
key in varchar2,
coname in varchar2,
tname in varchar2 )
return varchar2
as
type rc is ref cursor;
str varchar2(4000);
sep varchar2(2);
val varchar2(4000);
cur rc;
begin
open cur for 'select '||coname||'
from '|| tname || '
where ' || key_name || ' = :x '
using key;
loop
fetch cur into val;
exit when cur%notfound;
str := str || sep || val;
sep := ', ';
end loop;
close cur;
return str;
end;


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

create or replace function ip_int2string(intip number) return varchar2 is
/**
* added by ty
* IP段整型格式转换为字符串格式
*
* 计算结果必须与com.zznode.inms.ipms.util.AlgorithmToolKit中的
* public static String getStringIp(int intIp)方法的计算结果一致
*/
Result varchar2(15);
min_int number;
temp number;
temp1 number;
temp2 number;
temp3 number;
temp4 number;
flag number;

begin
min_int:=-2147483648;
if intip<min_int or intip>2147483647 then
raise_application_error(-20020,'[IPMS] Illegal int IP!');
end if;

if intip<0 then
temp:=intip-min_int;
flag:=0;
else
temp:=intip;
flag:=1;
end if;

temp1:= bitand(floor(temp/(2**24)),255);--右移24位，进行与运算
temp2:= bitand(floor(temp/(2**16)),255);--右移16位，进行与运算
temp3:= bitand(floor(temp/(2**8)),255);--右移8位，进行与运算
temp4:= bitand(temp,255);--进行与运算

if flag=1 then
temp1:=temp1+128;
end if;

Result:=temp1||'.'||temp2||'.'||temp3||'.'||temp4;
dbms_output.put_line(Result);
return(Result);

exception
when others then
raise_application_error(-20020,'[IPMS] Illegal int IP!');

end ip_int2string;

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

create or replace function ip_string2int(stringip varchar2) return number is
/**
* added by ty
* IP段字符串格式转换为整型格式
* 要求一个地址大于（或小于）另一个地址时，整型格式也保持大于（或小于）关系
* 如：128.0.0.0 > 127.255.255.255，要求ip_string2int(128.0.0.0)>ip_string2int(127.255.255.255)
*
* 计算结果必须与com.zznode.inms.ipms.util.AlgorithmToolKit中的
* public static int getIntIp(String stringIp)方法的计算结果一致
*/
Result number;
min_int number;
ip1 number;
ip2 number;
ip3 number;
ip4 number;

pos1 number;
pos2 number;
pos3 number;

temp1 raw(32);
temp2 raw(32);
temp3 raw(32);
temp4 raw(32);
temp number;
flag number;
begin
min_int:=-2147483648;
Result:=0;
pos1:=instr(stringip,'.',1,1);
pos2:=instr(stringip,'.',1,2);
pos3:=instr(stringip,'.',1,3);
if pos1=0 or pos2=0 or pos3=0 then
raise_application_error(-20020,'Illegal IP format!');
else
ip1:=to_number(substr(stringip,1,pos1-1));
ip2:=to_number(substr(stringip,pos1+1,pos2-pos1));
ip3:=to_number(substr(stringip,pos2+1,pos3-pos2));
ip4:=to_number(substr(stringip,pos3+1,length(stringip)-pos3));
if ip1<0 or ip1>255 or ip2<0 or ip2>255 or ip3<0 or ip3>255 or ip4<0 or ip4>255 then
raise_application_error(-20020,'[IPMS] Illegal IP format!');
end if;

ip2:=ip2*(2**16);--左移16位
ip3:=ip3*(2**8);--左移8位

if ip1>127 then
ip1:=ip1*(2**24)+min_int;--左移24位
flag:=1;
else
ip1:=ip1*(2**24);--左移24位
flag:=0;
end if;

temp1:= utl_raw.cast_from_binary_integer(ip1);
temp2:= utl_raw.cast_from_binary_integer(ip2);
temp3:= utl_raw.cast_from_binary_integer(ip3);
temp4:= utl_raw.cast_from_binary_integer(ip4);
--或运算
temp:= utl_raw.cast_to_binary_integer(utl_raw.bit_or(utl_raw.bit_or(utl_raw.bit_or(temp1,temp2),temp3),temp4));

if flag=1 then
Result:= temp;
elsif flag=0 then
Result:= temp+min_int;
end if;

end if;
dbms_output.put_line(Result);
return(Result);

exception
when others then
raise_application_error(-20020,'[IPMS] Illegal IP format!');

end ip_string2int;

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

CREATE OR REPLACE FUNCTION get_short_name(
init_name VARCHAR2
)
RETURN VARCHAR2
IS
short_name VARCHAR2(1000);
BEGIN
short_name := init_name;
IF INSTR(short_name,'TenGigabitEthernet') >0 THEN
short_name := REPLACE(short_name,'TenGigabitEthernet','TGE');
END IF;

IF INSTR(short_name,'TenGigE') >0 THEN
short_name := REPLACE(short_name,'TenGigE','TGE');
END IF;

IF INSTR(short_name,'FastEthernet') >0 THEN
short_name := REPLACE(short_name,'FastEthernet','FE');
END IF;

IF INSTR(short_name,'port-fei') >0 THEN
short_name := REPLACE(short_name,'port-fei','"FE');
END IF;

IF INSTR(short_name,'GigabitEthernet') >0 THEN
short_name := REPLACE(short_name,'GigabitEthernet','GE');
END IF;

IF INSTR(short_name,'Gig Ethernet') >0 THEN
short_name := REPLACE(short_name,'Gig Ethernet','GE');
END IF;

IF INSTR(short_name,'Gigabit-ethernet') >0 THEN
short_name := REPLACE(short_name,'Gigabit-ethernet','GE');
END IF;

IF INSTR(short_name,'GigaEthernet') >0 THEN
short_name := REPLACE(short_name,'GigaEthernet','GE');
END IF;

IF INSTR(short_name,'port-gei') >0 THEN
short_name := REPLACE(short_name,'port-gei','GE');
END IF;

RETURN short_name;
END get_short_name;

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

CREATE OR REPLACE FUNCTION F_TEMPLETEID_TO_DEVCOLLECT (P_TEMPLETEID IN NUMBER) RETURN VARCHAR2 IS
V_DEVNAME_LIST VARCHAR2(4000);
STR VARCHAR2 (3) DEFAULT NULL;
str_length number;
CURSOR CUR IS SELECT C.DEVICENAME
FROM RPT_USER_TEMPLET A, RPT_TEMPLET_OBJECT B, MR_DEVINFO_DIM C
WHERE A.ID = B.USERTEMPLET_ID AND B.OBJID = C.DEVICEID AND A.ID = P_TEMPLETEID
order by C.DEVICENAME;
BEGIN
STR := '; ' || CHR(13);
str_length := 0;
FOR REC IN CUR LOOP
str_length:= str_length + 3 + length(REC.DEVICENAME);
if str_length < 3500 then
V_DEVNAME_LIST := V_DEVNAME_LIST || REC.DEVICENAME || STR;
end if;
EXIT WHEN CUR%NOTFOUND;
END LOOP;
RETURN V_DEVNAME_LIST;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL;
WHEN OTHERS THEN RETURN NULL;
END F_TEMPLETEID_TO_DEVCOLLECT;

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

CREATE OR REPLACE FUNCTION F_Customer_Ip(cid IN VARCHAR2) RETURN VARCHAR2 IS
V_IP_LIST VARCHAR2(500);
CURSOR CUR IS
SELECT ip_address FROM NFA_SYS_SUACCOUNT_VIEW@guankong.zznode
WHERE serial_no = cid
ORDER BY ip_address;
BEGIN
V_IP_LIST := NULL;
FOR REC IN CUR LOOP
EXIT WHEN CUR%NOTFOUND;
IF V_IP_LIST IS NULL THEN
V_IP_LIST := REC.ip_address;
ELSE
V_IP_LIST := V_IP_LIST||','||REC.ip_address;
END IF;
END LOOP;
RETURN V_IP_LIST;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL;
WHEN OTHERS THEN RETURN NULL;
END F_Customer_Ip;