Oracle 问题总结

今天碰到这样一个问题，手动添加数据到数据库，然后通过程序添加数据到数据库。程序中的ID是取得序列的nextval。

但是发现程序生成的ID 在数据库中已经存在，报错，唯一性约束。

我脱开程序直接到sql developer 里，又插入了几个数据，序列的当前值比数据中的最大值小很多。

然后执行  select mytable$seq.nextval from dual 发现nextval 并没有和数据中的最大ID保持一致。

然后通过sql直接插入发现，写sql的话，序列的currval 是跟着改变的。一时想不通。

原来Oracle客户端界面新增数据 并没有改变序列的currval的值，程序或者sql是可以的。业务这是PL/sql界面的缺陷

 

问题2：create table TB2 t2 select * from TB1 t1

复制表结构不成功，解决办法： 

create table TB2 as  select * from TB1     where   rownum <1 

 

Oracle中删除重复记录:

 

delete  table1 t where t.rowid > (

    select min(t2.rowid) from table1 t2 where t.col1= t2.col1and t.col2= t2.col2

)

 

问题4 行转列：

 解决办法：wm_concat(col) 此函数如此神奇！

 

问题5： 列转行

解决办法：decode(col)如 sum(decode(course, '数学', score))  -- 计course 列为数学的  score分数综合

 

问题6：查找没有条件查询的排序结果中的位置，即第几条数据，用作排名用。(查询一个表中排序字段a在没有where提交下的位置)

 

select *from (
  select col1,col2,rownum rn from 
  (
    select col1,col2 from T t 
      order by t.sort desc
  )
)tt  
  where  tt.col1= 'xxx' and tt.col2= 'xxx'

 

 

问题7：查询表中有某个字段中有重复数据记录

 

select * from Ta where   recol
in (
  select   recol from Ta group by recol
     having count(recol)>1
)

问题8：rownum 在数据库表中一旦删除，则rownum将不存在，所以在做查询排名的时候rownum会出现各种问题。

    但是Oracle本身提供了查询排名的内部函数：rank() over  ,查询排名Java代码如下,查询content-排名：

	public String rank(String operCode) throws Exception, RemoteException {
		Connection conn = null;
		PreparedStatement ps = null;
		String str = "";
		ResultSet rs = null;
		try {
			conn = ConnManager.getConnection();
			String sql =  "select content,rn from (select content,rank() over(order by t.contentdesc) rn from T  t) where  col = ?";
	1		ps = conn.prepareStatement(sql);
			ps.setString(1, "xxx");
			rs = ps.executeQuery();
			if(rs.next()){
				str =   String.valueOf(rs.getString(1)) + "-" + String.valueOf(rs.getLong(2));
			}
		} catch (Exception e) {
			 System.out.print(e);
		}finally{
			ps.close();
			conn.close();
		}
		return str;
	}

 

   rank() over (partition col1,col2 order by col3 desc) partition按照col1和col2 进行分组，然后按照col3倒叙。

   rank() 查询出的rownum是结果集中的顺序 而非oracle 默认的rownum,即时信息和 要用rank() over() 搞定。

 

 

select * from (
       select count(*)  from t where id in (1, 2,3)) a, 
      (select   col1,col2,col3  from t
 where id in 1, 2,3)) b

 

   我们用Oracle内部函数解决：

select  count(*) over(), col1,col2,col3  from t
 where id in (1, 2,3)

 

 

 

评论

2 楼 步青龙 2011-12-06  

yekui 写道

主键报错说明此ID已经存在 你可以通过查看最大ID是多少  假设最大ID现在数据库是 888 然后删除以前的序列

drop sequence XX_SEQ；



重新创建序列就解决了
-- Create sequence
create sequence XX_SEQ
minvalue 1
maxvalue 999999999999999
start with 889
increment by 1

PL\SQL 界面操作的缺陷,一旦进行界面新增操作问题同样会出现

1 楼 yekui 2011-12-06  

主键报错说明此ID已经存在 你可以通过查看最大ID是多少  假设最大ID现在数据库是 888 然后删除以前的序列

drop sequence XX_SEQ；



重新创建序列就解决了
-- Create sequence
create sequence XX_SEQ
minvalue 1
maxvalue 999999999999999
start with 889
increment by 1