oracle sql

Update t_oms_pop set field=2,field=3 where id=2;

Select * from t_oms_pop; Select field1,field2 from t_oms_pop;

Insert into t_oms_pop values(value1,value2,value3); Insert into t_oms_pop (field1,field2) values(value1,value2);

Delete from t_oms_pop where id=2;

自己写的函数，亲测 maxnumber constant int := 1000; aa number:=1; start1 int:=1; systemid number:=0; begin for start1 in 1..maxnumber loop select S_OSS_SUBSYSTEM.Nextval into systemid FROM dual; --创建设备 insert into T_OSS_SUBSYSTEM (SYSTEM_ID, NAME, IP, PORTAL, ESN, TYPE, STATUS, POP_ID, CREATE_TIME) values (systemId, 'testi=' || aa, '1.1.1.' || aa, '8085', 'esn1116' || aa,21,1,1,sysdate); --订阅1005报文 insert into T_OSS_SUBSYSTEM_MESSAGE(CUSTOM_ID,SYSTEM_ID,MESSAGE_ID,CUSTOM_TIME) values(S_OSS_SUBSYSTEM_MESSAGE.nextval,systemId,'1005',sysdate); aa:=aa+1; end loop; commit; end;

函数 declare maxnumber constant int := 65530; ip1 number := 172; ip2 number := 0; ip3 number := 0; ip4 number := 2; tmp t_oms_cache_blacklist.record_content%type; ipaddr t_oms_cache_blacklist.record_content%type; i int:=1; j int :=0; begin for i in 1..maxnumber loop if ip4 < 255 then ip4 := ip4 + 1; elsif ip4 = 255 and ip3 < 255 then ip3 := ip3 + 1; ip4 := 0; elsif ip4 = 255 and ip3 = 255 and ip2 < 255 then ip2 := ip2 + 1; ip3 := 0; ip4 := 0; elsif ip4 = 255 and ip3 = 255 and ip2 = 255 and ip1 < 255 then ip1 := ip1 + 1; ip2 := 0; ip3 := 0; ip4 := 0; end if; tmp := ip1||'.'||ip2||'.'||ip3||'.'||ip4; ipaddr := tmp||'-'||tmp; --dbms_output.put_line(ipaddr); insert into t_oms_cache_blacklist values (SEQ_T_OMS_CACHE_BLACKLIST.NEXTVAL,4,ipaddr,'IPV4','','','','',0,to_date('2019-04-01','yyyy-mm-dd'),''); --for j in 0..9 loop insert into t_oms_cache_blacklist_node values (SEQ_T_OMS_CACHE_BLACKLIST.CURRVAL,1); --end loop; end loop; dbms_output.put_line(ipaddr);

commit; end;

查询是否有锁表 亲测：select object_name, machine, s.sid, s.serial# from gv$locked_object l, dba_objects o, gv$session s where l.object_id = o.object_id and l.session_id = s.sid;

快速复制一张表的数据 create table t_oss_ip_scope_new as select * from t_oss_ip_scope

级联删除 --ip服务范围和节点的关系表 DROP TABLE T_NODE CASCADE CONSTRAINTS; CREATE TABLE T_NODE ( ID NUMBER(10) not null primary key, IP_SCOPE_ID NUMBER(10) not null, POP_ID NUMBER(10) not null, DEAL_TYPE VARCHAR2(125) not null ); alter table T_NODE add constraint FK_IP_SCOPE_NODE1 foreign key (IP_SCOPE_ID) references T_NODE (SCOPE_ID) on delete cascade; alter table T_NODE add constraint FK_IP_SCOPE_NODE2 foreign key (POP_ID) references T_OSS_POP (POP_ID) on delete cascade;

多行数据显示在一行中 https://blog.csdn.net/sinat_36257389/article/details/81004843

不管该字段是number还是string不为1查询出来的结果集不包括type为null的结果。 select * from t_scope where type != 1;

1：for循环 select distinct n.node_id, nn.report_node_type as node_type, nn.server_ip as node_ip, nn.server_port as node_port from t_config n left join t_node nn on nn.id=n.node_id where n.node_id in :{nodeInfo.nodeId}

5表里数据假如存在则执行更新数据的语句假如不存则执行新增的语句（merge INTO） https://blog.csdn.net/yuzhic/article/details/1896878