常用ORACLE知识

1.左连接

学生表A：aid，aname

课程表B：bid，bname

学生课程关系表C：

aid,bid

想通过关系表C查出所有学生所选课程对应的课程名

select a.aid,a.name,(select b.bname from B b where b.bid=c.bid) from A a left join C c on a.aid=c.aid order by a.aid

2.java向oracle表中插入数据时，如果表主键用sequence自动生成，除了在代码中用sequenc.nextval查询出主键然后插入数据库外，也可以采取触发器方式，这样在java代码中就不需要先查询一次主键。

CREATE OR REPLACE TRIGGER TG BEFORE INSERT ON TB FOR EACH ROW
BEGIN
  SELECT SEQ_TB.NEXTVAL INTO :new.MANUFACTORYID FROM DUAL;
  END;

3.关联删除，如上面1中的例子，当删除A中某个数据时，需要关联删除关系表C中的数据，除了在java代码中进行删除以外，也可以用触发器来处理。

CREATE OR REPLACE TRIGGER UNION_DELETE_TG AFTER DELETE ON A FOR EACH ROW
BEGIN
  DELETE FROM C c WHERE  c.aid=:old.manufactoryid;
  END;

4.创建表空间

create tablespace testSpace 

logging  datafile 'D:/oraclespace/user_data.dbf' 

size 50m   autoextend on   next 50m maxsize 2048m  

extent management local;

5.分组和substr，假设数据库createdate时间格式为yyyyMMddHHmmss,要完成某个月的错误次数按天统计，那么可以采取如下sql：

select SUBSTR(createdate,1,8),sum(error) from record where name='xxx' and SUBSTR(createdate,1,6) like 'yyyymm' group by SUBSTR(createdate,1,8) order by SUBSTR(createdate,1,8);

6.结果集中移动游标。

通常情况下，我们在java类中采取如下方式获得结果集：

ps = conn.prepareStatement(sql);

......

rs = ps.executeQuery();

这样出来的结果集能满足我们一般的应用，但如果我们想再结果集中自由移动游标，比如rs.last(),rs.beforeFirst();那么就会抛出异常，大致内容如下：对只转发结果集的无效操作:...

这是因为默认的查询出来的结果集只能向前移动，如果想自由移动游标，需要为ps添加以下参数：

ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

第一个参数表示结果集可以自由滚动，第2个参数表示结果集只读，结果集的数据发生变化的时候不回写到数据库。

 

7.在使用上面的方法操作结果集的时候，如果遇到了rs为空，或者游标在结果集第一条前，或者游标在最后一条结果集之后，此时调用了rs.getxxx()方法的话会出现“用尽的ResultSet”异常。比如下面的代码：

rs.last();String name = rs.getString("name");在rs不为空的情况下，该代码执行不会有问题，但如果rs为空，就会抛出上面所说的异常，这里可以先检查判断一下rs.last()方法的返回值，只有为true的情况下才去取值可以避免上面错误。

 

8.多表关联结果集作为表查询：

select * from (select c.*,d.orgname from (select a.*,b.orgid from (select to_char(sysdate,'WW') CURWEEK,to_char(sysdate,'YYYYMMDDHH24MISS') curTime,x.*,y.* from dual,(select y.userid,y.username,y.telephone,y.mobil1 from SYS_USERINFO y where y.userid =(select t.userid from tb_flowweeklyuser t where t.orgid=17)) x,(select * from TB_FLOWWEEKLYREPORTCOMM) y ) a left join tb_flowweeklyuser b on a.userid = b.userid) c left join SYS_ORGINFO d on c.orgid=d.orgid) e, (select e.userid,e.username  opname,e.telephone opphone from SYS_USERINFO e where e.userid=901) f

 

9.将查询的多条结果插入表：

insert into TB_FLOWWEEKLYREPORT(WEEKLYID,ORGID,ORGNAME,CURWEEK,OPERATIONINFO,SECURITYINFO,ADVISEMENT,FLAG,STATUS,MEMO,OPID,OPTIME)
 select 'WR'||to_char(sysdate,'YYYY')||'_'||to_char(sysdate,'WW')||'_'||x.orgid,x.orgid,a.orgname,to_char(sysdate,'WW'),'正常','正常','无',1,2,'',x.userid,to_char(sysdate,'YYYYMMDDHH24MISS') from (
                 select t.* from TB_FLOWWEEKLYUSER t where t.orgid not in(select y.orgid from TB_FLOWWEEKLYREPORT y where y.curweek=to_char(sysdate,'WW'))) x left join sys_orginfo a on x.orgid=a.orgid;

 

10.执行批量操作的时候，如果未正常插入或者删除，可以在代码中加入事务控制手动提交。

conn.setAutoCommit(false);
    ps = conn.prepareStatement(sql);
    for(SLA_MAN_DATA data:dataList){
     ps.setInt(1, data.getCOMPID());
     ......

     ps.addBatch();
    }
    ps.executeBatch();
    conn.commit();

   conn.setAutoCommit(true);

 

11,根据条件分类汇总：

一个系统下有很子系统，每个子系统每个月都会产生监控日志，现在要统计出2011年年份和2011年10月份汇总数据，该sql涉及到跨库查询，having对结果集分组，decode函数，case语句等用法。

select x5.parentid,x5.text1,x4.merror,x4.werror,x4.merror*300/500000.0 museRate, x4.werror*300/100000.0 wuseRate  from (
        select x0.pathid,decode(x0.merror+x3.merror,null,0,x0.merror+x3.merror) merror, decode(x0.werror+x3.werror,null,0,x0.werror+x3.werror) werror from (
        select t.pathid,substr(t.createdate,0,4) tc ,0 as merror,0 as werror from tb_robot_perf t group by t.pathid,substr(t.createdate,0,4) having substr(t.createdate,0,4)='2011') x0
        left join (having SUM(CASE WHEN t1.createdate like '201110%' THEN t1.costtime ELSE 0 END)>0 select t1.pathid,
        SUM(CASE WHEN t1.createdate like '201110%' THEN t1.costtime ELSE 0 END) merror,
        count(*) werror from tb_robot_perf t1 where t1.error='TOTAL_ERROR' and t1.costtime=1 and t1.createdate like '2011%'
        group by t1.pathid) x3 on x0.pathid=x3.pathid) x4 left join TB_ITASSERTRELATIONSHIP@LiNK_67 x5 on x4.pathid=x5.pathid

 

12,oracle的字符串处理函数substr(str,start,len)函数和java的字符串处理函数subString(stard,end)有些区别:oracle是从指定的start位置开始朝后取len长度的字符串，java是从指定的start位置取到end位置，比如"20111215000000”这个时间我们要取出中间的月份，oracle就是substr(‘20111215000000’,5,2)，java是"20111215000000".subString(4,6)。