1、新增date类型数据
insert into table (date) values ('to_date('2009-06-08 23:53:17','YYYY-MM-DD HH24:MI:SS'),'
格式化日期: select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
或 TO_DATE(SYSDATE(),'YY/MM/DD HH24:MI:SS)
格式化数字: TO_NUMBER
2、oracle批量新增
insert all into t_kck_hckzb values(sys_guid(),'互斥课组1','1','admin',SYSDATE,'1')
into t_kck_hckzb values(sys_guid(),'互斥课组2','1','admin',SYSDATE,'1')
into t_kck_hckzb values(sys_guid(),'互斥课组3','1','admin',SYSDATE,'1')
select 1 from dual;
commit;
3、exists
EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False
EXISTS 指定一个子查询,检测 行 的存在。
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx)
4、复制语句
insert into t_kck_hckzkcb(id,kzid,kcid)
select sys_guid() ,#{new_kzid},t.kcid from (select * from t_kck_hckzkcb where kzid = #{old_kzid}) t
5、查询同一个字段多次条件
该sql同时查询了两个字段,是把多个字段通过 and or 合成了一个字段然后查询了两次所有后面=2
select t1.pc,t1.xjid,t1.xn,t1.xq from t_xj_xzyxstjjlb t1
where 1=1 and (t1.tj = '6' and t1.tjnr<(select tjnr from t_xj_xzyzgmdtjb where tj = '6'))
or ( t1.tj = '7' and t1.tjnr = '1' ) group by t1.pc,xjid,t1.xn,t1.xq having count(xjid) = 2