Oracle其它应用综合.doc

Oracle 其它应用综合目录Oracle 其它应用综合1一、ROWID，ROWNUM，ROW_NUMBER()的区别1Oracle XSQL的应用技巧3关于事务处理（Transaction）的概念3一、 ROWID，ROWNUM，ROW_NUMBER()的区别(1) rowid是指记录的物理序号；(2) rownum仅指记录的序号；(3) row_numer(order by colunmx) 是一个函数，可能通过它来给记录指定序号；并且 row_numer(partition by colunmx order by colunmy)指为列colunmx按colunmy的顺序指定序号，每一个colunmx的值都是从1开始到最后一个，因此这很方便找重复记录！下面是一个常见的rowid和row_number的“查重”应用：SELECT * FROM table1 WHERE ROWID != (SELECT MAX(ROWID) FROM table1 D WHERE table1.colunm1 = D.colunm1 AND table1.colunm2 = D.colunm2);SELECT * FROM ( SELECT * FROM (SELECT a.*,ROW_NUMBER() OVER(PARTITION BY colunm1,colunm2 order by colunmx) b FROM table1 a) aaWHERE aa.b=1;注：查询table1中column1和column2中重复的记录。问题实例分析：有一个表 t ，字段 c1, c2, c3, c4，要求从c1的重复值记录中随机选一条，构成结果集。怎么实现？比如记录如下：aa1 bb1 cc1 dd1aa1 bb2 cc2 dd2aa1 bb3 cc3 dd3aa2 bb4 cc4 dd4aa2 bb5 cc5 dd5aa2 bb6 cc6 dd6.结果是从值aa1中随机选取一条记录， aa2中随机选取一条记录，依此类推，得出结果集。用单条 sql 语句怎么实现？lastwinner的语句是：select b.c1, b.c2, b.c3, b.c4 from (select c1, 0 as lb, count(*) as ub from t2 group by c1) a, (select c1, c2, c3, c4, row_number() over(partition by c1 order by c2, c3, c4) rn from t2) b where a.c1 = b.c1 and ceil(dbms_random.value(a.lb, a.ub) = b.rn;这个实现思路很妙。第一个子查询按字段c1分组，得出c1每个值对应记录数的上下限；第二个字查询利用row_number()分析函数，按字段c1分区，c1值相 同的记录编号；外层查询把两个子查询的结果集用c1字段关联，在每个c1值对记录数上下限之间随机取值，并且利用ceil函数可以取到两端的边界值。分析了上面的查询语句之后，看到 jackywood 的语句更简单：select * from (select C1, C2, C3, C4, row_number() over(partition by c1 order by dbms_random.value) as rn from t2) where rn = 1;把随机值直接用到 row_number() 分析函数的order by的参数中，好，更简单。两个方案都很好，执行的效率都可以，第二个更直接、更简单。 其实这样的需求还是挺常见的，比如有一个歌曲库song，每首歌曲有唯一的一个songid，每个演唱者singer有多首歌曲，现在想要从一个歌手及歌 曲的列表，要求从每个歌手演唱的歌曲中随机选出一首歌曲，就可以这样写：select * from (select t.*, row_number() over(partition by singer order by dbms_random.value) as rn from songs t) where rn = 1;或者select * from (select singer, 0 as lb, count(*) as ub from songs group by singer) a, (select t.*, row_number() over(partition by singer order by songid) rn from songs t) b where a.singer = b.singer and ceil(dbms_random.value(a.lb, a.ub) = b.rn;解决问题的关键是 row_number() 分析函数的使用。Oracle XSQL的应用技巧一、 获得XSQL的最终的执行SQL语句其方法就是在XSQL中加入错误信息（比如，在正确的SQL语句中加入一些干扰信息，使其无法正常执行）。然后我们通过在浏览器地址栏输入XSQL URL来直接请求XSQL，XSQL不仅列出错误信息，还会将错误的SQL语句呈现，这样我们就可以看到最终将执行的SQL语句，这样方便我们找出SQL的问题。关于事务处理（Transaction）的概念什么是事务 事务(Transaction)是并发控制的基本单位。所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个 不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务 是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。 在大多数环境中，事务处理其实就是一个业务过程，它由一组组合成逻辑工作单元的数据库操作组成，通常需要跨对象操作，或访问多个资源的数据 如： 1、在 A 表中插入新的记录。 2、在 B 表中插入新的记录。 3、更新 C 表某几条记录 .等一系列操作如果运行过程中数据库系统没有遇到错误，事务中的所有的修改都将生效，永久性提交到数据库。如果系统遇到错误或发生崩溃，则事务中所有的操作都会回滚（ROLLBACK），不会对数据库里数据作出任何修改。 一句话就是：事务中所有的的操作，要么全部生效，要么全部不生效。事务的 ACID 属性，担保一个事务是永远不会不完整，数据永远不会不一致，并发事务是独立的，一个事务的效果是持久的。原子性(ATOMICITY): 一个事务要被完全的无二义性的做完或撤消。在任何操作出现一个错误的情况下，构成事务的所有操作的效果必须被撤消，数据应被回滚到以前的状态。 一致性(CONSISTENCY): 一个事务应该保护所有定义在数据上的不变的属性(例如完整性约束)。在完成了一个成功的事务时，数据应处于一致的状态。换句话说，一个事务应该把系统从一 个一致状态转换到另一个一致状态。举个例子，在关系数据库的情况下， 一个一致的事务将保护定义在数据上的所有完整性约束。 隔离性(ISOLATION): 在同一个环境中可能有多个事务并发执行，而每个事务都应表现为独