从SQLServer导数据到Oracle大概有以下几种方法:

        
最近简单的对oracle,mysql,sqlserver2005的数据分页查询作了研究,把各自的查询的语句贴出来供大家学习…..

(一)、** mysql的分页查询**

Oracle、MySql、SQLServer
数据分页查询

sqlserver2005的分页查询

  1. 使用SSMS的导出数据向导,使用Microsoft ODBC for Oracle或Oracle
    Provider for OLE DB连接到Oracle
  2. 导出到平面文件
  3. 导出包含数据的SQL脚本。
  4. 使用ETL工具。
  5. 自己开发软件。

       (一)、** mysql的分页查询**

        mysql的分页查询是最简单的,借助关键字limit即可实现查询

摘自:

在sqlserver2005之前一直借助top关键字来实现分页查询,不过效率低,在sqlserver2005及其之后的版本都使用row_number()解析函数来完成分页查询,效率有了很大的提高,不过sql语句比较复杂,下面给出分页查询的通式:

以下使用第2种方法来进行数据迁移的。

        mysql的分页查询是最简单的,借助关键字limit即可实现查询,查询语句通式:

/*

近简单的对oracle,mysql,sqlserver2005的数据分页查询作了研究,把各自的查询的语句贴出来供大家学习…..

/*

* firstIndex:起始索引

* pageSize:每页显示的数量

* orderColumn:排序的字段名

* sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

*/

select top pageSize o.* from (select row_number() over(order by orderColumn) as rownumber,* from(sql) as o where rownumber>firstIndex;

使用BCP合适导出大容量数据。这里导出千万级别的数据,也是很快就能成功。

/*

* sql:可以是单表的查询语句,也可以是多表的联合查询语句

* firstIndex:其实的索引

* pageSize:每页显示的记录数

*/

select o.* from (sql) o limit firstIndex,pageSize

* sql:可以是单表的查询语句,也可以是多表的联合查询语句

       (一)、** mysql的分页查询**

  

如果导出时还需要做一些数据的处理,比如多表关联,字符处理等,比较复杂的逻辑,最好是做成存储过程,BCP直接调用存储过程即可。

如下面的截图,每页显示的记录数为20:

* firstIndex:其实的索引

        mysql的分页查询是最简单的,借助关键字limit即可实现查询,查询语句通式:

四种方式实现SQLServer
分页查询

BCP "exec TestDB.dbo.export_t1 " queryout d:\export\t1.txt -c -t"||" -S"192.168.1.100" -Urpt -Prpt123
pause

USE TestDB
GO

CREATE PROC [dbo].[export_usercar]
AS
    SELECT  [carId]
           ,CONVERT(NVARCHAR(30), [addTime], 120)
           ,CONVERT(NVARCHAR(30), [lastSearchTime], 120)
           ,CONVERT(NVARCHAR(30), [updateTime], 120)
           ,[carType]
           ,[userTelephone]
           ,[isCorrect]
           ,[userId]
           ,[validFlag]
           ,[Channel]
           ,[carCode]
           ,[engineNumber]
           ,[carNumber]
    FROM    [TestDB].[dbo].[t1] WITH ( NOLOCK )
    WHERE   validFlag = 1
            AND isCorrect = 1;

                                                
 查询(1-20)这20条记录
图片 1

* pageSize:每页显示的记录数

图片 2

oracle分页查询

把导出文件上传到Oracle所在的主机上,如CentOS下。

                                             查询(21-40)这20条记录

*/

/*

* sql:可以是单表的查询语句,也可以是多表的联合查询语句

* firstIndex:其实的索引

* pageSize:每页显示的记录数

*/

select o.* from (sql) o limit firstIndex,pageSize

①ROWNUM查询分页通式:

使用Oracle的SQL*LOADER导入平面文件。假如Oracle中有已经创建好的表,与导入文件对应。

图片 3

select *
from (sql) 
limit firstIndex,pageSize

图片 4

/*
* firstIndex:起始索引
* pageSize:每页显示的数量
* sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句
*/
select * from(select a.*,ROWNUM rn from(sql) a where ROWNUM<=(firstIndex+pageSize)) where rn>firstIndex

把以下的内容用vi,写到import-t1.ctl

        mysql的分页查询就这么简单……

(二)、sqlserver2005的分页查询

如下面的截图,每页显示的记录数为20:

②row_number()解析函数分页查询通式:

load data
CHARACTERSET 'ZHS16GBK'
infile '/data/import/t1.txt' "str '\r\n'"
into table SCOTT.T1
fields terminated by '||' TRAILING NULLCOLS
(
carId, 
addTime DATE "YYYY-MM-DD HH24:MI:SS",
lastSearchTime DATE "YYYY-MM-DD HH24:MI:SS",
updateTime DATE "YYYY-MM-DD HH24:MI:SS",
carType ,
userTelephone  ,
isCorrect  ,
userId  ,
validFlag ,
Channel ,
carCode  ,
engineNumber ,
carNumber  
)

  (二)、sqlserver2005的分页查询

   
在sqlserver2005之前一直借助top关键字来实现分页查询,不过效率低,在sqlserver2005及其之后的版本都使用row_number()解析函数来完成分页查询,效率有了很大的提高,不过sql语句比较复杂

                                                
 查询(1-20)这20条记录
图片 1

 /*
 * firstIndex:起始索引
 * pageSize:每页显示的数量
 * orderColumn:排序的字段名
 * sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句
 */
select * from(select * from(select t.*,row_number() over(order by orderColumn) as rownumber from(sql) t) p where p.rownumber>firstIndex) where rownum<=pageSize

使用SQL*LOADER注意几个问题:

   
在sqlserver2005之前一直借助top关键字来实现分页查询,不过效率低,在sqlserver2005及其之后的版本都使用row_number()解析函数来完成分页查询,效率有了很大的提高,不过sql语句比较复杂,下面给出分页查询的通式:

/*

                                             查询(21-40)这20条记录

  

  • 字符编码
  • 字段分隔符
  • 行结束符
  • 日期或时间格式
  • 特殊字符
  • 导入字段的顺序
  • 导文件文件的表字段类型和长度是否合适
/*

* firstIndex:起始索引


* pageSize:每页显示的数量

* orderColumn:排序的字段名

* sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

*/

select top pageSize o.* from (select row_number() over(order by orderColumn) as rownumber,* from(sql) as o where rownumber>firstIndex;

* firstIndex:起始索引

图片 3

mysql的分页查询

使用sqlldr命令把数据导入到Oracle中。

 下面看截图,每页显示20条记录数:

* pageSize:每页显示的数量

        mysql的分页查询就这么简单……

 mysql的分页查询是最简单的,借助关键字limit即可实现查询,查询语句通式:

sqlldr user/"user_password" control=import-t1.ctl

                                                           
 查询(1-20)这20条记录

* orderColumn:排序的字段名

  (二)、sqlserver2005的分页查询

/*
* sql:可以是单表的查询语句,也可以是多表的联合查询语句
* firstIndex:其实的索引
* pageSize:每页显示的记录数
*/

select o.* from (sql) o limit firstIndex,pageSize

默认下,生成的日志文件在当前目录下。无论成功与否,一定要查看日志。看看是否导入成功或失败,或是部分成功。导入的问题一般从日志文件即可找到。

图片 7

*
sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

   
在sqlserver2005之前一直借助top关键字来实现分页查询,不过效率低,在sqlserver2005及其之后的版本都使用row_number()解析函数来完成分页查询,效率有了很大的提高,不过sql语句比较复杂,下面给出分页查询的通式:

  

如果有错误,还会生成与导入文件同名的t1.bad文件。

                                                        
查询(21-40)这20条记录

*/

图片 8

 

以下是日志文件,显示数据导入的一些信息。成功导入了18495032行记录,没有导入失败的记录。

图片 9

select top pageSize * from (select
row_number() over(order by orderColumn) as
rownumber,* from(sql) as o where
rownumber>firstIndex;

/*

* firstIndex:起始索引


* pageSize:每页显示的数量

* orderColumn:排序的字段名

* sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

*/

select top pageSize o.* from (select row_number() over(order by orderColumn) as rownumber,* from(sql) as o where rownumber>firstIndex;
[oracle@ttoracle /data/import]$ cat import-t1.log 

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 15 12:46:09 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   import-t1.ctl
Character Set ZHS16GBK specified for all input.

Data File:      /data/import/t1.txt
  File processing option string: "str '
'"
  Bad File:     t1.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table SCOTT.T1, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CARID                               FIRST     *           CHARACTER            
    Terminator string : '||'
ADDTIME                              NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
LASTSEARCHTIME                       NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
UPDATETIME                           NEXT     *           DATE YYYY-MM-DD HH24:MI:SS
    Terminator string : '||'
CARTYPE                              NEXT     *           CHARACTER            
    Terminator string : '||'
USERTELEPHONE                        NEXT     *           CHARACTER            
    Terminator string : '||'
ISCORRECT                            NEXT     *           CHARACTER            
    Terminator string : '||'
USERID                               NEXT     *           CHARACTER            
    Terminator string : '||'
VALIDFLAG                            NEXT     *           CHARACTER            
    Terminator string : '||'
CHANNEL                              NEXT     *           CHARACTER            
    Terminator string : '||'
CARCODE                              NEXT     *           CHARACTER            
    Terminator string : '||'
ENGINENUMBER                         NEXT     *           CHARACTER            
    Terminator string : '||'
CARNUMBER                            NEXT     *           CHARACTER            
    Terminator string : '||'


Table SCOTT.T1:
  18495032 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 214656 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:      18495032
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Jun 15 12:46:09 2018
Run ended on Fri Jun 15 12:55:58 2018

Elapsed time was:     00:09:48.90
CPU time was:         00:03:37.62

    知道了sqlserver中的row_number函数,分页也就简单了…..

(三)、oracle分页查询

图片 10

使用平面文件迁移数据,最大麻烦是就是特殊字符,或是有垃圾数据。如果原数据包含与字符分隔符相同的字符,如这里面的“||”,或是有一些不可见的字符,如回车,换行符,等。这些字符会造成导入时,分割字段错位,导致导入错误,数据导不全,甚至导入失败。

  (三)、oracle分页查询

   
接下来重点说说oracle的分页查询,oracle的分页查询方法相对来说要多点,ROWNUM、row_number(),今天主要将两种效率稍好的分页查询语句。

 下面看截图,每页显示20条记录数:

但从导出导入的速度来说,是最快的,平面文件可以跨不同的数据库进行迁移。如果数据不容忍丢失,只能通过工具来导了,但速度会相对较慢。

   
接下来重点说说oracle的分页查询,oracle的分页查询方法相对来说要多点,ROWNUM、row_number(),今天主要将两种效率稍好的分页查询语句。

①ROWNUM查询分页通式:

                                                           
 查询(1-20)这20条记录

    ①ROWNUM查询分页通式:

/*

图片 7

/*

* firstIndex:起始索引

* pageSize:每页显示的数量

* sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

*/
select * from(select a.*,ROWNUM rn from(sql) a where ROWNUM<=(firstIndex+pageSize)) where rn>firstIndex

* firstIndex:起始索引

                                                        
查询(21-40)这20条记录

以下截图是以这种方式进行的查询语句:

* pageSize:每页显示的数量

图片 9

                                                          查询(1-21)这20条记录*****(没有ID=6的记录,所以查询到的最大ID为21)
图片 13

*
sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

    知道了sqlserver中的row_number函数,分页也就简单了…..

                                                    
  查询(22-41)这20条记录*****(没有ID=6的记录,所以开始查询到的ID为22,以及最大ID为41)

*/
select * from(select a.*,ROWNUM rn from(sql) a where ROWNUM<=(firstIndex+pageSize)) where
rn>firstIndex

  (三)、oracle分页查询

图片 14

 
②row_number()解析函数分页查询通式:

   
接下来重点说说oracle的分页查询,oracle的分页查询方法相对来说要多点,ROWNUM、row_number(),今天主要将两种效率稍好的分页查询语句。

 

* firstIndex:起始索引

    ①ROWNUM查询分页通式:

    ②row_number()解析函数分页查询通式:

* pageSize:每页显示的数量

图片 15

 /*

 * firstIndex:起始索引

 * pageSize:每页显示的数量

 * orderColumn:排序的字段名

 * sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

 */
select * from(select * from(select t.*,row_number() over(order by orderColumn) as rownumber from(sql) t) p where p.rownumber>firstIndex) where rownum<=pageSize

* orderColumn:排序的字段名

/*

* firstIndex:起始索引

* pageSize:每页显示的数量

* sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

*/
select * from(select a.*,ROWNUM rn from(sql) a where ROWNUM<=(firstIndex+pageSize)) where rn>firstIndex

 以下截图是使用row_number()方式的分页查询效果:

* sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

图片 16

                                                         
查询(1-21)这20条记录*****(没有ID=6的记录,所以查询到的最大ID为21)
图片 17

*/
select * from(select * from(select t.*,row_number() over(order by orderColumn)
as rownumber from(sql) t) p where p.rownumber>firstIndex)
where rownum<=pageSize

以下截图是以这种方式进行的查询语句:

                                                   
查询(22-41)这20条记录*****(没有ID=6的记录,所以开始查询到的ID为22,以及最大ID为41)

对于oracle的分页查询,特地选出这两种实现方式是因为这两者各有千秋

                                                          查询(1-21)这20条记录*****(没有ID=6的记录,所以查询到的最大ID为21)
图片 13

图片 19

     首先, 我们知道在ROWNUM查询的方式中,在第二层的sql语句中有个”where
ROWNUM<firstIndex+pageSize”,根据oracle的原则,第二层查询语句会嵌入到最内层中进行查询,也就是说,最开始执行的查询语句类似于:select
* from wyuse where rownum<(firstIndex+pageSize) order by id
asc,从数据表中查询出(firstIndex+pageSize)条记录,所以如果这个值很小的话,效率会很好,如果对于大数据量的表单,这个值如果是上千,比如:select
* from wyuse where rownum<(5000) order by id
asc,这样一开始会选出5000条记录,效率自然会慢很多….

                                                    
  查询(22-41)这20条记录*****(没有ID=6的记录,所以开始查询到的ID为22,以及最大ID为41)

      对于oracle的分页查询,特地选出这两种实现方式是因为这两者各有千秋

    
不过,相对于ROWNUM,row_number()方式可能通过简化可以少一层嵌套,不过貌似对于大数量的查询,效率也高不到哪里去…..不过,对于大数量如果为表建立索引再结合row_number()效果会很好(未测试)

图片 14

     首先, 我们知道在ROWNUM查询的方式中,在第二层的sql语句中有个”where
ROWNUM<firstIndex+pageSize”,根据oracle的原则,第二层查询语句会嵌入到最内层中进行查询,也就是说,最开始执行的查询语句类似于:select
* from wyuse where rownum<(firstIndex+pageSize) order by id
asc,从数据表中查询出(firstIndex+pageSize)条记录,所以如果这个值很小的话,效率会很好,如果对于大数据量的表单,这个值如果是上千,比如:select
* from wyuse where rownum<(5000) order by id
asc,这样一开始会选出5000条记录,效率自然会慢很多….

 

 

    
不过,相对于ROWNUM,row_number()方式可能通过简化可以少一层嵌套,不过貌似对于大数量的查询,效率也高不到哪里去…..不过,对于大数量如果为表建立索引再结合row_number()效果会很好(未测试)

                                                                                                                                      
                 ===========地狱的镰刀  

    ②row_number()解析函数分页查询通式:

 

图片 21

 

 /*

 * firstIndex:起始索引

 * pageSize:每页显示的数量

 * orderColumn:排序的字段名

 * sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句

 */
select * from(select * from(select t.*,row_number() over(order by orderColumn) as rownumber from(sql) t) p where p.rownumber>firstIndex) where rownum<=pageSize

 

图片 22

 

 以下截图是使用row_number()方式的分页查询效果:

图片 23

                                                         
查询(1-21)这20条记录*****(没有ID=6的记录,所以查询到的最大ID为21)
图片 17

 

                                                   
查询(22-41)这20条记录*****(没有ID=6的记录,所以开始查询到的ID为22,以及最大ID为41)

图片 19

      对于oracle的分页查询,特地选出这两种实现方式是因为这两者各有千秋

     首先, 我们知道在ROWNUM查询的方式中,在第二层的sql语句中有个”where
ROWNUM<firstIndex+pageSize”,根据oracle的原则,第二层查询语句会嵌入到最内层中进行查询,也就是说,最开始执行的查询语句类似于:select
* from wyuse where rownum<(firstIndex+pageSize) order by id
asc,从数据表中查询出(firstIndex+pageSize)条记录,所以如果这个值很小的话,效率会很好,如果对于大数据量的表单,这个值如果是上千,比如:select
* from wyuse where rownum<(5000) order by id
asc,这样一开始会选出5000条记录,效率自然会慢很多….

    
不过,相对于ROWNUM,row_number()方式可能通过简化可以少一层嵌套,不过貌似对于大数量的查询,效率也高不到哪里去…..不过,对于大数量如果为表建立索引再结合row_number()效果会很好(未测试)

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图