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 || ' = '
                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 [e-mail]
  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;