- 浏览: 574259 次
- 性别:
- 来自: 上海
文章分类
- 全部博客 (174)
- JBPM (3)
- WWF (0)
- JavaScript (11)
- J2EE (40)
- OperationSystem (11)
- 数据库 (12)
- CSS (1)
- Ajax (2)
- J2SE (30)
- Tools (10)
- 服务器中间件 (3)
- 异常 (0)
- Flex (5)
- jQuery (11)
- html (9)
- Ejb (1)
- HTML5 Shiv–让该死的IE系列支持HTML5吧 (1)
- Spring (9)
- Quartz (3)
- log4j (1)
- maven (1)
- cpdetector (1)
- JSON (1)
- log4jdbc (1)
- asm (8)
- FusionCharts (1)
- jqplot (1)
- highcharts (1)
- excanvas (1)
- html5 (1)
- jpcap介绍 (1)
- weblogic (3)
- URLURLClassLoader (0)
- URLClassLoader (1)
- ant (2)
- ivy (2)
- nexus (1)
- IT (0)
- LoadRunner (1)
- SCSS (1)
- ruby (1)
- webstorm (1)
- typescript (1)
- Jboss7 (1)
- wildfly (1)
- oracle (5)
- esb (0)
- dubbo (2)
- zookeeper (3)
- eclipse (1)
- Android (2)
- Studio (1)
- Google (1)
- 微信 (1)
- 企业号 (1)
- Linux (13)
- Oracle12c (1)
- Hadoop (1)
- InletexEMC (1)
- Windows (1)
- Netty (3)
- Marshalling (2)
- Protobuf (1)
- gcc (1)
- Git (1)
- GitLab (1)
- shell (2)
- java (3)
- Spring4 (1)
- hibernate4 (1)
- postgresql (1)
- ApacheServer (2)
- Tomcat (2)
- ApacheHttpServer (2)
- realvnc (1)
- redhat (7)
- vncviewer (1)
- LVS (4)
- LVS-DR (1)
- RedHat6.5 (5)
- LVS-NAT (1)
- LVS-IPTUNNEL (2)
- LVS-TUN (1)
- keepalived (2)
- yum (1)
- iso (1)
- VMware (1)
- redhat5 (1)
- ha (1)
- nginx (2)
- proguard (1)
- Mat (1)
- DTFJ (1)
- axis2 (1)
- web service (1)
- centos (1)
- random (1)
- urandom (1)
- apache (1)
- IBM (1)
- cve (1)
- 漏洞 (1)
- JDBC (1)
- DataSource (1)
- jdk (1)
- tuxedo (2)
- wtc (1)
最新评论
-
skying007:
好资料,谢谢分享给啊
FusionCharts在服务器端导出图片(J2EE版) -
cgnnzg:
大神好 可以发一份源码给我学习么 多谢了 978241085 ...
springmvc+dubbo+zookeeper -
jifengjianhao:
求源码:854606899@qq.com
springmvc+dubbo+zookeeper -
wdloyeu:
shihuan8@163.com邮箱网盘在哪,没找到。能给份源 ...
Java Socket长连接示例代码 -
huangshangyuanji:
求代码:45613032@qq.com
springmvc+dubbo+zookeeper
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 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;
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 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;
- Oracle函数的示例.rar (2.2 KB)
- 下载次数: 2
发表评论
-
Oracle修改内存使用情况
2015-12-15 10:49 929[oracle@pamiddle ~]$ sqlplus / ... -
Oracle12c的相关命令及操作
2015-12-10 09:27 2865[oracle@pacollector ~]$ lsnrctl ... -
RedHat6.4安装Oracle12c 12.1.0.2.0
2015-12-07 16:32 2039[root@pacollector ~]# vi /etc/h ... -
静态方式把oracle实例配置到监听文件里
2015-08-21 21:25 1417本例Oracle的实例名字为parep。 原始的listen ... -
RedHat6.4安装Oracle11g 11.2.0.4
2015-07-25 22:36 2812[root@foglight ~]# vi /etc/host ... -
mysql和Oracle在对clob和blob字段的处理
2012-08-25 18:50 30847一、MySQL与Oracle数据库如何处理Clob,Bl ... -
Oracle Flashback技术
2012-04-14 14:12 1150http://www.cnblogs.com/coohoo/a ... -
Oracle相关资料大礼包
2012-03-08 11:08 1196附件里Oracle学习资料。 -
Oracle自带连接池应用
2011-08-31 13:27 2032依赖ojdbc14.jar包 OraclePooled.ja ... -
几个Oracle procedure实例
2011-03-10 13:43 1470create or replace procedure PC_ ... -
Toad for Oracle 10下载
2010-11-11 21:12 16071Toad Development Suite for Orac ...
相关推荐
oracle删除和重建实例
Oracle PL/SQL实例精解 student模式 数据库建立代码 包括数据库的建立、索引等。样本数据的插入。
Oracle中主键自增实例
Oracle备份和恢复实例Oracle备份和恢复实例Oracle备份和恢复实例Oracle备份和恢复实例
oracle 11g 环境下,通过一个监听,对应多个实例的配置。
在linux手动新建oracle实例,或多个实例。基本步骤: 1) 指定一个SID 2) 创建所需的文件目录 3) 创建初始化参数文件pfile 4) 确定数据库认证方式,如果给予口令认证,创建口令文件 5) 编写创建数据库脚本 6) ...
oracle pl/sql实例精讲student数据库模式
oracle两个实例,配置一个监听 主要是修改listener.ora文件和tnsnames.ora文件
在一个oracle用户下创建多个实例,包括tns的配置、监听的配置方法
oracle pl sql 实例精解 oracle pl sql 实例精解 oracle pl sql 实例精解
原创:在linux 服务器上创建第二个ORACLE数据的实例,手工启动或开机自启动的设置。
修改oracle数据库的实例名,修改oracle数据库的实例名
oracle数据库触发器实例 oracle数据库触发器实例 oracle数据库触发器实例 oracle数据库触发器实例 oracle数据库触发器实例 oracle数据库触发器实例 oracle数据库触发器实例 oracle数据库触发器实例
修改数据库最大连接数以及linux系统下ORACLE数据库启动多实例参照.pdf
oracle 版本 10.2.0 再linux下创建新实例
oracle 存储过程实例 oracle存储过程实例
详细介绍了 oracle的游标使用 及 实例
几个常用的Oracle函数及实例运用 几个常用的Oracle函数及实例运用
Oracle PL/SQL实例编程 Oracle PL/SQL实例编程 Oracle PL/SQL实例编程