SQL*Loader的使用总结(二)

2024-06-06 06:38
文章标签 sql 总结 使用 loader database

本文主要是介绍SQL*Loader的使用总结(二)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

SQL*Loader对不同文件及格式的处理方法

1.Excel文件

一般的Excel文件最大行数不超过65536行，说明数据处理量并不大，处理Excel的方式是将其另存为CSV格式文件,然后即可按照正常方式导入即可。


2.要加载的文件不是以逗号分隔
有两种方式可以参考：
1)修改数据文件，将分隔符替换为逗号。
2)修改控制文件，将FIELDS TERMINATED BY的值修改为实际的分隔符。


3.要加载的数据中包含分隔符
例如,要向scott.bonus表插入数据提供的数据格式如下：
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523
修改控制文件,注意下列示例代码中的粗体字符,OPTIONALLY ENCLOSED BY参数指明定界符为双引号(CSV格式文件默认定界符就是双引号,你可以根据实际情况修改OPTIONALLY的参数值),如下所示： 
--控制文件
[oracle@cancer ~]$ cat ldr_case2.ctl 
LOAD DATA
INFILE ldr_case2.dat
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(ENAME,JOB,SAL)--数据文件
[oracle@cancer ~]$ cat ldr_case2.dat
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523
 

 

sqlldr运行如上代码，并查询结果如下：
 

 

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case2.ctl 
Commit point reached - logical record count 4--查询结果
SQL> select * from bonus;
ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
SMITH	   CLEAK	   3904
ALLEN	   SALER,M	   2891
WARD	   SALER,"S"   3128
KING	   PRESIDENT   2523

 

 

 

4.数据文件没有分隔符
 

如下的数据文件专业叫做定长字符串，sqlldr中处理定长字符串也轻而易举。针对此例，我们将控制文件修改如下：
 

 

--控制文件
[oracle@cancer ~]$ cat ldr_case3.ctl
LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26)
)--数据文件
[oracle@cancer ~]$ cat ldr_case3.dat 
SMITH    CLEAK        3904
ALLEN    SALESMAN     2891
WARD     SALESMAN     3128
KING     PRESIDENT    252
 

 

position关键字用来指定列的开始和结束位置，如JOB position(10:18)是指从第10个字符开始截止到第18个字符作为ENAME列的列值。position的写法也很灵活，要实现上述功能还可以换成下列几种形式：
 

①position(*+2:18):直接指定数值的方式叫作绝对偏移量，如果使用*号，专业名词叫相对偏移量，表示上一个字段从哪里结束，这次就从哪里开始，相对偏移量也 可以再做运算，比如Position(*+2:15)就表示从上次结束的位置+2的地方开始。
 

②position(*) char(9):这种相对偏移量+类型和长度的优势在于，你只需要为第一列 指定开始位置，其他列只需要指定列长度就可以了，实际使用中比较省事。
 

sqlldr运行如上代码，并查询结果如下：
 

 

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case3.ctl
Commit point reached - logical record count 4--查询结果
SQL> select * from bonus;
ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
SMITH	   CLEAK	   3904
ALLEN	   SALESMAN	   2891
WARD	   SALESMAN	   3128
KING	   PRESIDENT   2523

 

5.数据文件中的列比要导入的表中的列少
 

在前面几个例子中，数文件中的列比表中的列要少的环境中演示的，这说明列少不怕，关键是看控制文件中的配置。但是如果缺少的列必须赋值又怎么办呢？只需稍改下控制文件即可，直接指定COMM列，并赋初始值0(这里仍然引用ldr_case3.dat中的数据）：
 
 

--控制文件
[oracle@cancer ~]$ cat ldr_case4.ctl 
LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "0"
)

 

查看演示结果
 

 

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case4.ctl
Commit point reached - logical record count 4--查看结果
SQL> select * from bonus;
ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
SMITH	   CLEAK	   3904 	 0
ALLEN	   SALESMAN	   2891 	 0
WARD	   SALESMAN	   3128 	 0
KING	   PRESIDENT   2523 	 0

 
COMM的值也可以根据其他列的值而定，修改控制文件如下 

 

--控制文件
[oracle@cancer ~]$ cat ldr_case5.ctl 
LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:5),
JOB position(10:18),
SAL position(23:26),
COMM "substr(:SAL,1,1)"
)

 
sqlldr执行上述代码,结果如下，很明显发现COMM的值是根据SAL的值的第1位数字获得 

 

 

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case5.ctl 
Commit point reached - logical record count 4--执行结果
SQL> select * from bonus;
ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
SMITH	   CLEAK	   3904 	 3
ALLEN	   SALESMAN	   2891 	 2
WARD	   SALESMAN	   3128 	 3
KING       PRESIDENT   2523      2

 

这里COMM列的值根据SAL列值而定，我们通过一个SQL中的函数substr取SAL值的第一列，赋予COMM列，当然这只是一个示例，DBA可以根据实际需求进行适当的 修改，通过SQL中的函数可以实现很多很有意思的转换，也许能够为你省下很大力气，而且如果现有函数无法实现，甚至可以通过PL/SQL编写自定义的函数，然后在sqlldr的 控制文件中调用，调用方式与系统自带函数方式完全相同，这样就可以根据需求对要加载 的列做审灵活的处理。
 

6.数据文件中的列比要导入的表中列多
 
 
如果数据文件中的列比要导入的表中的列少，处理的时候可能麻烦些，多了反倒更简单，针对不同情况，一般有以下两种处理方式：
 方式一：修改数据文件，将多余的数据删除，不过以这种方式处理，小数据量时还可行，一旦数据文件较大，几百甚至上千兆，修改数据文件耗时耗力。
 方式二：使用sqlldr中控制文件FILLER来排除不需要的列 

 
1）演示数据文件如下 

 

 

--数据文件
[oracle@cancer ~]$ cat ldr_case6.dat 
SMITH    7369   CLERK      1020   20
ALLEN    7499   SALESMAN   1930   30
WARD     7521   SALESMAN   1580   30
JONES    7566   MANAGER    3195   20
MARTIN   7654   SALESMAN   1580   30
BLAKE    7698   MANAGER    3180   30
CLARK    7782   MANAGER    2172   10
SCOTT    7788   ANALYST    3220   20
KING     7839   PRESIDENT  4722   10
TURNER   7844   SALESMAN   1830   30
ADAMS    7876   CLERK      1320   20
JAMES    7900   CLERK      1280   30
FORD     7902   ANALYST    3220   20
MILLER   7934   CLERK      1022   10
 
此时我们的需求希望我们导入第1、3、4列而跳过2、5列，创建控制文件如下 

 

 

--控制文件
[oracle@cancer ~]$ cat ldr_case6.ctl
LOAD DATA
INFILE ldr_case6.dat
TRUNCATE INTO TABLE BONUS
(
ENAME position(1:6),
TCOL FILLER position(10:13),
JOB position(17:25),
SAL position(28:31)
)

 
sqlldr的控制文件中对列定义时支持FILLER关键字，可以用来指定过滤列，在上述控制文件中，我们就使用该关键字来过滤列，相当于第10到第13列之间的数据不导入。
 事实上由于此处为定长字串，我们在控制文件中指定的position参数，己经限定了读取的内容，你甚至可以删除控制文件中TCOL FILLER position (10:13)那行。
 执行sqlldr命令： 

 

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case6.ctl 
Commit point reached - logical record count 14--查询结果
SQL> select * from bonus;
ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
SMITH	   CLERK	   1020
ALLEN	   SALESMAN	   1930
WARD	   SALESMAN	   1580
JONES	   MANAGER	   3195
MARTIN	   SALESMAN	   1580
BLAKE	   MANAGER	   3180
CLARK	   MANAGER	   2172
SCOTT	   ANALYST	   3220
KING	   PRESIDENT   4722
TURNER	   SALESMAN	   1830
ADAMS	   CLERK	   1320ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
JAMES	   CLERK	   1280
FORD	   ANALYST	   3220
MILLER	   CLERK	   102214 rows selected.

 
2）如果数据文件中字符串不是定长格式，而是通过分隔符来处理的，那控制文件中就需要注意，如数据文件如下：
  

--数据文件
[oracle@cancer ~]$ cat ldr_case7.dat 
SMITH,7369,CLERK,1020,20
ALLEN,7499,SALESMAN,1930,30
WARD,7521,SALESMAN,1580,30
JONES,7566,MANAGER,3195,20
MARTIN,7654,SALESMAN,1580,30
BLAKE,7698,MANAGER,3180,30
CLARK,7782,MANAGER,2172,10
SCOTT,7788,ANALYST,3220,20
KING,7839,PRESIDENT,4722,10
TURNER,7844,SALESMAN,1830,30
ADAMS,7876,CLERK,1320,20
JAMES,7900,CLERK,1280,30
FORD,7902,ANALYST,3220,20
MILLER,7934,CLERK,1022,10

 

此时创建控制文件时，控制文件中就必须制定FILLER，不然列中的值可能不对应，创建控制文件如下
 

 

--控制文件
[oracle@cancer ~]$ vim ldr_case7.ctl 
LOAD DATA
INFILE ldr_case7.dat
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,XCOL FILLER,JOB,SAL)
 
执行sqlldr命令，并查看结果 

 

--sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case7.ctl 
Commit point reached - logical record count 14--查看结果
[oracle@cancer ~]$ sqlplus scott/tiger;
SQL> select * from bonus;
ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
SMITH	   CLERK	   1020
ALLEN	   SALESMAN	   1930
WARD	   SALESMAN	   1580
JONES	   MANAGER	   3195
MARTIN	   SALESMAN	   1580
BLAKE	   MANAGER	   3180
CLARK	   MANAGER	   2172
SCOTT	   ANALYST	   3220
KING	   PRESIDENT   4722
TURNER	   SALESMAN	   1830
ADAMS	   CLERK	   1320ENAME	   JOB		    SAL       COMM
---------- --------- ---------- ----------
JAMES	   CLERK	   1280
FORD	   ANALYST	   3220
MILLER	   CLERK	   102214 rows selected.
 

 
本文内容参考<涂抹Oracle-三思笔记>一书，该书是基于Windows，本文引用了该书的脚本和结论的整理在Linux亲自测试通过，并对一些小问题进行了处理 

 

 

 


                                    

                                        这篇关于SQL*Loader的使用总结(二)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
                                    

                                    

                                

                            

                            

                        

                    

                    

                        

                            

                            http://www.chinasem.cn/article/1035362。
                            
                        

                    

                    
                    

                        
                            
                        
                    

                

                

                    相关文章
                

                    

                        

                            

                                

                                    
                                        HarmonyOS学习(七)——UI（五）常用布局总结
                                    
                                

                            

                            

                                

                                    HarmonyOS学习(七)——UI（五）常用布局总结
                                

                                

                                    自适应布局 1.1、线性布局（LinearLayout） 通过线性容器Row和Column实现线性布局。Column容器内的子组件按照垂直方向排列，Row组件中的子组件按照水平方向排列。 属性说明space通过space参数设置主轴上子组件的间距，达到各子组件在排列上的等间距效果alignItems设置子组件在交叉轴上的对齐方式，且在各类尺寸屏幕上表现一致，其中交叉轴为垂直时，取值为Vert
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        SQL中的外键约束
                                    
                                

                            

                            

                                

                                    SQL中的外键约束
                                

                                

                                    外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表  以下是一个外键约束的实例展示
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        基于MySQL Binlog的Elasticsearch数据同步实践
                                    
                                

                            

                            

                                

                                    基于MySQL Binlog的Elasticsearch数据同步实践
                                

                                

                                    一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        如何去写一手好SQL
                                    
                                

                            

                            

                                

                                    如何去写一手好SQL
                                

                                

                                    MySQL性能  最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。  《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        中文分词jieba库的使用与实景应用（一）
                                    
                                

                            

                            

                                

                                    中文分词jieba库的使用与实景应用（一）
                                

                                

                                    知识星球：https://articles.zsxq.com/id_fxvgc803qmr2.html 目录 一.定义： 精确模式（默认模式）： 全模式： 搜索引擎模式： paddle 模式（基于深度学习的分词模式）： 二 自定义词典 三.文本解析   调整词出现的频率 四. 关键词提取 A. 基于TF-IDF算法的关键词提取 B. 基于TextRank算法的关键词提取
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        使用SecondaryNameNode恢复NameNode的数据
                                    
                                

                            

                            

                                

                                    使用SecondaryNameNode恢复NameNode的数据
                                

                                

                                    1）需求： NameNode进程挂了并且存储的数据也丢失了，如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。  2）故障模拟 （1）kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 （2）删除NameNode存储的数据（/opt/module/hadoop-3.1.4/data/tmp/dfs/na
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        Hadoop数据压缩使用介绍
                                    
                                

                            

                            

                                

                                    Hadoop数据压缩使用介绍
                                

                                

                                    一、压缩原则 （1）运算密集型的Job，少用压缩 （2）IO密集型的Job，多用压缩  二、压缩算法比较  三、压缩位置选择  四、压缩参数配置 1）为了支持多种压缩/解压缩算法，Hadoop引入了编码/解码器  2）要在Hadoop中启用压缩，可以配置如下参数
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        Makefile简明使用教程
                                    
                                

                            

                            

                                

                                    Makefile简明使用教程
                                

                                

                                    文章目录 规则makefile文件的基本语法：加在命令前的特殊符号：.PHONY伪目标: Makefilev1 直观写法v2 加上中间过程v3 伪目标v4 变量 make 选项-f-n-C     Make 是一种流行的构建工具，常用于将源代码转换成可执行文件或者其他形式的输出文件(如库文件、文档等)。Make 可以自动化地执行编译、链接等一系列操作。  规则 makefile文件
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        学习hash总结
                                    
                                

                            

                            

                                

                                    学习hash总结
                                

                                

                                    2014/1/29/   最近刚开始学hash，名字很陌生，但是hash的思想却很熟悉，以前早就做过此类的题，但是不知道这就是hash思想而已，说白了hash就是一个映射，往往灵活利用数组的下标来实现算法，hash的作用：1、判重；2、统计次数；
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        性能分析之MySQL索引实战案例
                                    
                                

                            

                            

                                

                                    性能分析之MySQL索引实战案例
                                

                                

                                    文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结   一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
                                

                                

                                    阅读更多...
                                

                            

                        

                    

            

        

    


    


    

        

            Copyright China编程   23002807@qq.com
        

        

            沪ICP备2023033072号-2