标签：oracle

Oracle中关于rownum和分页的详细描述(翻译)

原文为:http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
Limiting Result Sets
rownum是一个很让人疑惑的oracle概念，这会导致很多人不会使用它，一般情况下，在以下的两个场合，我们会使用到rownum伪列:

  • 执行一个TOP-N查询，如查询最大或最多，最靠前的某些记录，与其它数据库(如mysql)的limit很相似.
  • 对查询进行分页，这个很常见，就不说了。

How ROWNUM Works
rownum是一个伪列，在一个查询中，它并不是真正的列，但在查询结果中就会存在这个列数据。在查询的结果中，rownum会依次从1开始赋值给查询的每一条记录。但这个值并不是固定的，会随着不同的查询发生变化，也就是说在一个数据表中，并不存在rownum这个列，即你不能在一个表中查询rownum为5的数据，没有这种数据。

那么rownum是什么时候被赋值的呢，了解这个时机会让你在一些查询中了解一个sql为什么要这样写。
rownum是在当数据查询一定的条件被查询出来但并没有被排序或者聚合的时候赋值的
也就是说，rownum是在有数据的时候才会递增，如果没有数据被查询出来，则不会递增。如下面的sql语句，则永远不会返回任何数据:

select * 
  from t 
 where ROWNUM > 1;

因为对于第一条数据来说,rowum > 1并不为真,因此rownum也不会递增到2，所以不会有数据的rownum大于1。
我们可以通过下面的一个标准的数据查询，来了解rownum是何时被赋值的。如下所示：

select ..., ROWNUM
  from t
 where <where clause>
 group by <columns>
having <having clause>
 order by <columns>;

那么，一般来说，它会按照以下的顺序来工作:

  1. The FROM/WHERE clause goes first.
  2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
  3. SELECT is applied.
  4. GROUP BY is applied.
  5. HAVING is applied.
  6. ORDER BY is applied.

继续阅读“Oracle中关于rownum和分页的详细描述(翻译)”

oracle中row_number和rownum的区别和联系(翻译)

附问题:有以下一个SQL语句:

SELECT  *
FROM    (
        SELECT  t.*, row_number() OVER (ORDER BY ID) rn
        FROM    mytable t
        )
WHERE   rn BETWEEN :start and :end

sql中的order by语句大大降低了处理的速度，如果把order by去掉，相应的执行计划会大大地提高。如果换成下面的sql:

SELECT  t.*, row_number() OVER (ORDER BY ID) rn
FROM    mytable t
WHERE   rownum BETWEEN :start and :end

很明显，这个sql是错的，根本查询不了正确的数据信息。是否有其它的方法可以提高查询速度？
针对以上问题，就必须要了解一下关于row_number和rownum的区别，以及如何来运用这些信息。

首先了解一下rownum是如何进行工作的，根据oracle的官方文档：
如果对rownum进行大于比较，这个比较将直接返回false。如，下列sql语句将不能返回任何数据信息:

继续阅读“oracle中row_number和rownum的区别和联系(翻译)”

在windows x64环境下使用oracle instant client配置pl/sql developer

在x64的环境下，即便安装了oracle数据库，但是如果使用pl/sql developer，那么是使用不了的。原因很简单，pl/sql 是32位的，而所安装的oracle为64位的。当然，如果再安装一个32位的客户端工具，那么就可以使用了。但是，仅仅为使用pl/sql而安装一个600M左右的oracle客户端，这个代价是不是太大了。

而实际上，pl/sql工具只需要依赖一个叫oci.dll的动态链接库就可以了。然后就是基于tnsname.ora的配置文件，以让pl/sql知道需要以何种方式访问数据库信息，即可以了。那么，哪些东西可以满足这两个条件呢，那就是oracle instant client。

网上的大多数的配置都需要建立developer.bat文件，或者修改环境变量，或者修改注册表。这些修改都对现有的使用方式以及会有潜在的问题，比如本机已有oracle数据库的情况。本文仅从pl/sql本身所支持的信息出发，不修改文件，环境变量以及注册表信息，采用最常用的惯例配置达到让pl/sql自动发现配置信息的目的。

1    首先下载oracle instant client文件，下载地址为 http://download.oracle.com/otn/nt/instantclient/112010/instantclient-basic-win32-11.2.0.1.0.zip。将其解压在某一个目录，暂称之为 F:\plsql\instantclient_11_2目录。

2    打开pl/sql，在tools->Preference->Connection里，修改配置里面的Oracle Home项，将其修改为我们刚才解压的目录。同时修改下面的OCI Library项，将其修改为解压目录\oci.dll。即可。保存配置

3    从别处或者已安装oracle的安装目录copy一个tnsname.ora文件，将其放在 解压目录\NETWORK\ADMIN目录下即可。

经过以上3个步骤，再重新打开pl/sql，我们就会发现pl/sql已经能够自动地从我们刚copy的tnsname.ora文件中读取数据库配置信息了，输入帐号名/密码，就和平时所使用的情况一样了。
这里需要注意的是，pl/sql读取的不是已安装的数据库的tnsnames.ora，而是我们copy过去的新的tnsnames.ora文件。一个系统中有2个tnsnames.ora是不是很难维护，那么还有办法。如下所示.

如果你的电脑是windows 7,windows 8或者windows 2008，那么恭喜你，你可以使用linux中的link，即软连接。何为软连接，这里不再叙述。我们只需要在新的解压目录下的NETWORK\ADMIN目录下，创建一个名为tnsnames.ora的软连接，并连接到64位的oracle下的tnsnames.ora文件，就可以了。这样，修改其中1个，都修改了（实际就是一个文件），就不需要再费心维护2个文件了。以下代码即可(请在cmd下运行)：

F:\plsql\instantclient_11_2\NETWORK\ADMIN>mklink tnsnames.ora F:\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

这样就创建一个软连接，所有的问题都解决了。

后记：其实道理很简单，在pl/sql里面，我们配置的ORACLE_HOME目录，即解压目录。所以，按照默认的惯例，在ORACLE_HOME\NETWORK\ADMIN\tnsnames.ora即是相应的oracle访问信息配置。我们所做的，只不过按照oracle的配置方式将文件放到正确的地方而已。

使用impdp在同一个数据库之间复制用户数据(转)

之前有同事问如何在一个数据库中复制一个用户信息，即将一个用户下的所有数据复制到另一个用户下，以方便进行开发测试，而不直接操作原有数据。当前，想到如何导出数据再导入之外，还没有其他方法，今天看到一个通过network_link来使用impdp复制数据的方法，可以解决这个问题，并且还不需要将数据先导出到本地中。

以下内容转自:http://tomszrp.itpub.net/post/11835/293879,原文为 使用数据泵复制schema，作者: Thomas Zhang.

impdp 的数据源既可以是expdp 导出来的DMP文件，也可以是一数据库.
通过network_link 参数来指定数据源
schemas 参数说明要复制的用户
remap_schema 参数说明源用户与目标用户的匹配关系
remap_tablepace 来调整原schemas使用的表空间与目标数据库用户要表空间的对应关系。

首先创建一个dblink: 
我是在一个db自身上创建的dblink，用来模拟2个不同的库。同样的道理也说明了一种在同一个库上复制一个schema的方法

继续阅读“使用impdp在同一个数据库之间复制用户数据(转)”

hibernate中的hql不支持sum(distinct)语句

在进行数据统计时，经常会使用聚合函数，在hibernate中也支持聚合函数。如进行以下的统计查询：

select sum(distinct 奖牌) from 成绩 inner join 参赛运动员 inner join 运动员的学校 group by 学校

在上面的数据模型中，成绩与参赛运动员为一对多关系，即可能为多个运动员以团体参赛的形式参加一个项目，最终取得一条成绩。运动员与学校为多对一关系。在上面的查询中，为避免在同一个成绩中，由团体参赛的运动员来自同一个学校，因为只能记为一个奖牌，而不是N(N为参赛运动员数量）。因此，需要使用distinct对重复的成绩进行过滤，并按学校分组。

在正常的sql查询下，现在的数据库已经支持在sum聚合函数中进行distinct操作了。而在hibernate中，使用如此的查询会报一个如下的错误：

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: distinct

即不支持在sum函数中带有distinct语句，在相关的bug列表中。网页：https://hibernate.onjira.com/browse/HHH-6311 也描述了这一问题，且值到现在还没有解决。那么为什么会产生这个错误呢，这是由hibernate将hql转换为sql的过程中产生的，即在hibernate的语法树中，就不支持这样的写法。相关的语法描述中，针对聚合函数是这样的（在源文件hql.g中）

:aggregate
	: ( SUM^ | AVG^ | MAX^ | MIN^ ) OPEN! additiveExpression CLOSE! { #aggregate.setType(AGGREGATE); }
	// Special case for count - It's 'parameters' can be keywords.
	|  COUNT^ OPEN! ( STAR { #STAR.setType(ROW_STAR); } | ( ( DISTINCT | ALL )? ( path | collectionExpr ) ) ) CLOSE!
	|  collectionExpr
	;

在上面的描述中，只有count支持带distinct或all描述语句，而其它的如sum,avg,max,min均不支持，因此在进行转换时即会产生异常。因为，只能采用sql的方法实现上面的统计查询了。好在使用session.createSQLQuery,只需要将hql进行简单的人工转换再加上addScalar进行结果类型转换即能实现操作，实现上难度不大。

参考文章：Antlr–看Hibernate3如何解释HQL语言

oracle中使用oo4o(vc)添加图片

使用oo4o已经是很多年的事情了，前段时间有同事使用这个东西往oracle中添加图片等blob数据，一直找不到方法。经本人测试，找到一个很简单的方法，现记录如下。

过程就是先添加一个空的blob数据，然后再去修改这个blob数据，在oracle的oo4o的workbook中，也采用了这种方式，不过是源代码的blob填充数据没有使用copyFromFile的方法，而是采用数据读写，这相对于日常存照片文件，还是过于繁琐。本段代码直接使用copyFromFile，简单一点。代码总共不超过10行（核心代码）。

#include "stdafx.h"
#include "obound.h"
#include "ORACL.H"

#include 
using namespace std;

//oracle定义了表为xa，两个字段id和vv，其中id为主键，vv为blob类型。
	OStartup();//启动，必须要

	ODatabase x;
//打开数据库
	x.Open("tnsname", "用户名", "密码");
//取得一个结果集，这个结果集没数据，为了添加使用的
	ODynaset os(x,"select id,vv from xa where 1 = 0");//专门使用1=0不查询任何数据
	os.AddNewRecord();
	os.SetFieldValue("id",i);
	OValue ov;
	ov.SetEmpty();
	os.SetFieldValue("vv", ov);
	os.Update();	
	//以上为添加一个图片为空的记录，下面准备添加图片了。

	os.StartEdit();
	OBlob ob;
	os.GetFieldValue("vv", &ob);//读取数据
	ob.CopyFromFile("d:/x.jpg");//将文件写入blob
	os.Update();
	ob.Close();//关闭blob，必须调用，否则在oshutdown之后，会出现内存出错

	os.Close();//关闭记录集
	x.Close();//关闭会话
	OShutdown();

以上代码相当简单，需要注意的是，由于oracle把所有权力都交由了开发者，开发者必须在分配内存之后，关闭内存。否则就会在运行时出现"内存报错"的情况。以上的close方法，一个都不能少，少一个都会报错，需要注意。
以上代码，在vs2005下运行通过