当前位置：首页 > article >正文

26.Oracle11g的数据装载

article 2024/11/15 5:36:23

oracle基础系统学习目录

01.CentOS7静默安装oracle11g
02.Oracle的启动过程
03.从简单的sql开始
04.Oracle的体系架构
05.Oracle数据库对象
06.Oracle数据备份与恢复
07.用户和权限管理
08.Oracle的表
09.Oracle表的分区
10.Oracle的同义词与序列
11.Oracle的视图
12.Oracle的索引
13.Oracle通过JDBC连接Java
14.Oracle中的事务
15.Oracle11g的归档方式和日志文件的相关操作
16.Oracle的数据字典和动态性能视图
17.Oracle11g的PL/SQL基础
18.Oracle的过程和函数
19.Oracle11g中的游标
20.Oracle11g中的触发器
21.Oracle的程序包(Package)
22.Oracle中的临时表空间
23.Oracle11g的UNDO表空间
24.Oracle11g的逻辑备份与恢复
25. Oracle的回收站
26.Oracle11g的数据装载
27.Oracle11g的闪回Flashback
28.Oracle11g物化视图

Oracle11g的数据装载

oracle基础系统学习目录
一、数据装载概述
- 1、数据装载是什么
- 2、使用SQL*Loader进行数据装载
二、外部表
- 1、外部表概念
- 2、外部表的使用
三、使用外部表数据装载案例
- 1、创建目标表
- 2、准备数据源
- 3、装载数据
- 4、验证数据
点击此处跳转下一节：27.Oracle11g的闪回Flashback

一、数据装载概述

1、数据装载是什么

数据装载是指将数据从一个数据源加载到Oracle 11g数据库中的过程。这个过程通常涉及从外部数据文件、其他数据库或数据源中提取数据，然后将其插入到Oracle 11g数据库的表中。数据装载是ETL（抽取、转换、加载）过程的一部分，用于将外部数据整合到数据库中，以便进行分析、报告和其他数据处理操作。

在Oracle 11g中，数据装载可以通过多种方式实现，包括使用SQL*Loader工具、外部表、PL/SQL程序、以及各种ETL工具（如Oracle Data Integrator）等。无论使用哪种方式，数据装载的基本目标都是将外部数据加载到Oracle 11g数据库中，并确保数据的完整性和准确性。

数据装载通常包括以下步骤：

准备数据源：准备外部数据文件、其他数据库或数据源，以便从中提取数据。
创建目标表：在Oracle 11g数据库中创建目标表，用于存储从数据源中提取的数据。
数据转换：根据需要对数据进行转换和清洗，以确保它符合目标表的结构和数据质量要求。
数据加载：将转换后的数据插入到目标表中，可以使用SQL*Loader、外部表、PL/SQL程序等工具和技术。
验证数据：验证目标表中的数据，确保它们与预期的结果一致。

通过数据装载，可以将外部数据整合到Oracle 11g数据库中，为用户和应用程序提供可靠的数据来源，以支持业务决策和分析需求。

2、使用SQL*Loader进行数据装载

创建一个包含要装载数据的文本文件（例如data.txt），文件内容如下：
```
1001,John,Smith,30
1002,Jane,Doe,25
1003,Michael,Johnson,35
```
创建一个控制文件（例如load.ctl），用于指定要装载的数据文件和目标表结构，文件内容如下：

创建语法

在Oracle 11g中，使用SQL*Loader工具进行数据装载时，需要编写一个控制文件（control file），用于描述数据文件的格式、数据装载的规则和目标表的映射关系。以下是控制文件的语法和主要部分的介绍：
- LOAD DATA：这是控制文件的起始部分，用于指定数据装载的开始。
- INFILE：指定要装载的数据文件的路径和名称。
- INTO TABLE：指定要将数据装载到的目标表的名称。
- FIELDS TERMINATED BY：指定字段之间的分隔符，如逗号、制表符等。
- TRAILING NULLCOLS：指示在数据文件中如果没有值时，应将其视为NULL值。
- (列名1, 列名2, …)：指定目标表中要装载数据的列，以及它们在数据文件中的顺序。
- BOUNDFILLER：用于指示在数据文件中存在但在目标表中不需要的列。
- BEGINDATA：标识数据装载的开始，其后是实际的数据行。
需要注意的是，控制文件的语法还包括了很多其他的选项和功能，例如条件装载、数据转换、错误处理等。以上仅是一个简单的示例，实际的控制文件可能会更加复杂和灵活。建议在编写控制文件时，参考Oracle官方文档以获取更详细的语法和选项说明。
具体使用
```
LOAD DATA
INFILE 'data.txt'
INTO TABLE employee
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( 
  emp_id,
  first_name,
  last_name,
  age
)
```
在这个示例中，控制文件指定了从名为"data.txt"的数据文件中装载数据到名为"employee"的表中。数据文件中的字段由逗号分隔，分别对应目标表中的EMP_ID、FIRST_NAME、LAST_NAME和AGE列。TRAILING NULLCOLS指示在数据文件中如果某一列的值为空时，应将其视为NULL值。

创建目标表employee
您可以使用以下SQL语句在Oracle数据库中创建一个名为"employee"的表，并将提供的数据插入到该表中：
```
-- 创建名为"employee"的表
CREATE TABLE employee (
  EMP_ID NUMBER(4),      -- 员工ID，数据类型为NUMBER，最大长度为4
  FIRST_NAME VARCHAR2(50), -- 员工名，数据类型为VARCHAR2，最大长度为50
  LAST_NAME VARCHAR2(50),  -- 员工姓，数据类型为VARCHAR2，最大长度为50
  AGE NUMBER(3)           -- 员工年龄，数据类型为NUMBER，最大长度为3
);
```
以上SQL语句首先创建了一个名为"employee"的表，该表包含了EMP_ID、FIRST_NAME、LAST_NAME和AGE等列。然后，使用INSERT INTO语句将提供的数据逐条插入到"employee"表中。这样就完成了在Oracle数据库中创建名为"employee"的表，并将数据插入到该表中的操作。
使用SQL*Loader命令进行数据装载：
```
sqlldr username/password@database control=load.ctl
```
以上代码中的username、password和database分别是数据库的用户名、密码和数据库名称，load.ctl是上一步创建的控制文件。

通过以上步骤，可以将data.txt文件中的数据装载到名为employee的表中。

除了SQL*Loader外，还可以使用外部表和数据泵等方法进行数据装载。使用外部表可以将外部数据源（如文本文件、Excel文件）映射为数据库中的表，而数据泵是Oracle数据库中用于高速导入和导出数据的工具。

总之，在Oracle 11g中进行数据装载有多种方法可供选择，开发人员可以根据实际需求选择合适的方法进行数据装载操作。

二、外部表

1、外部表概念

在Oracle 11g中，外部表（External Table）是一种特殊的数据库对象，用于将外部数据文件（如文本文件、CSV文件等）以表的形式引入数据库中进行查询和分析。外部表并不存储实际的数据，而是提供了一个结构化的视图，使得用户可以通过SQL语句来访问和操作外部数据文件。

2、外部表的使用

以下是外部表的一些特点和使用方法：

定义外部表：在Oracle 11g中，可以使用CREATE TABLE语句来定义外部表，与普通表不同的是，外部表需要使用ORGANIZATION EXTERNAL关键字来指定其类型为外部表，并且需要指定外部数据文件的存储路径、格式和结构。
```
CREATE TABLE ext_sales
   (sale_id NUMBER,
    sale_date DATE,
    customer_id NUMBER,
    amount_sold NUMBER)
   ORGANIZATION EXTERNAL  -- 指定了这是一个外部表，而不是普通的数据库表。
   (TYPE ORACLE_LOADER    -- 指定了外部表的类型为ORACLE_LOADER，表示使用Oracle Loader来访问外部数据文件。
    DEFAULT DIRECTORY ext_dir 
    ACCESS PARAMETERS
    (FIELDS TERMINATED BY ','
     LOCATION ('sales_data.csv'))
   )
   REJECT LIMIT UNLIMITED;
```
这是一个用于创建外部表的SQL语句，下面是对每个部分的详细注释：
- CREATE TABLE ext_sales: 这部分定义了要创建的外部表的名称为ext_sales。
- (sale_id NUMBER, sale_date DATE, customer_id NUMBER, amount_sold NUMBER): 这部分定义了外部表的列，包括sale_id（销售ID）、sale_date（销售日期）、customer_id（客户ID）和amount_sold（销售金额）。每个列都有相应的数据类型。
- ORGANIZATION EXTERNAL: 这部分指定了这是一个外部表，而不是普通的数据库表。
- (TYPE ORACLE_LOADER: 这部分指定了外部表的类型为ORACLE_LOADER，表示使用Oracle Loader来访问外部数据文件。
- DEFAULT DIRECTORY ext_dir: 这部分指定了外部数据文件所在的默认目录名称为ext_dir。在Oracle数据库中，DEFAULT DIRECTORY是一个数据库对象，用于指定外部文件所在的目录。
- ACCESS PARAMETERS: 这部分指定了外部表的存取参数，用于定义如何访问外部数据文件。
- (FIELDS TERMINATED BY ',': 这部分定义了外部数据文件中字段的分隔符为逗号。这表示外部数据文件中的每一行数据都是由逗号分隔的字段组成。
- LOCATION ('sales_data.csv')): 这部分指定了外部数据文件的位置为sales_data.csv。这表示外部表将从sales_data.csv文件中读取数据。
- REJECT LIMIT UNLIMITED: 这部分指定了在数据装载时的拒绝记录限制，这里设置为UNLIMITED表示没有限制，即允许所有记录都被装载。
综合起来，这个SQL语句创建了一个名为ext_sales的外部表，它使用Oracle Loader来访问名为sales_data.csv的外部数据文件，文件存储在ext_dir目录中。外部表的列和外部数据文件的字段之间通过字段分隔符进行对应，以便正确地读取和解析数据。
在上面的示例中，ext_sales是一个外部表，它使用了Oracle Loader来访问名为sales_data.csv的数据文件，该文件存储在名为ext_dir的目录中。外部表的列和数据文件的字段之间需要进行对应，以便正确地读取和解析数据。
查询外部表：一旦定义了外部表，就可以像普通表一样使用SELECT语句来查询外部数据文件中的数据。
```
SELECT * FROM ext_sales;
```
通过这种方式，用户可以直接在数据库中使用SQL语句来对外部数据文件中的数据进行查询和分析，而无需将数据导入到数据库表中。
数据装载：外部表也可以用来进行数据装载，类似于SQL*Loader的功能。通过外部表，可以将外部数据文件中的数据装载到数据库中的普通表中，实现数据的导入和整合。
要查看已存在的外部表，您可以使用以下SQL查询：
```
SELECT table_name
FROM user_tables
WHERE table_type = 'EXTERNAL TABLE';
```
这条查询将返回当前用户拥有的所有外部表的名称。您也可以根据需要修改查询条件，以适应您的权限和数据库架构。

总的来说，外部表是Oracle 11g中一个非常有用的功能，它使得用户可以方便地访问和操作外部数据文件中的数据，同时也可以用于数据装载和整合。需要注意的是，外部表并不适合用于大规模的数据分析和处理，因为它并不会将数据存储在数据库中，而是在查询时动态地读取外部数据文件。

三、使用外部表数据装载案例

Oracle 11g数据装载的具体过程分为以下几个步骤：

1、创建目标表

在Oracle 11g中，我们可以使用CREATE TABLE语句来创建目标表。例如，以下是创建一个名为EMPLOYEE的表的示例代码：

CREATE TABLE EMPLOYEE
(
	EMP_ID NUMBER(10),
	EMP_NAME VARCHAR2(50),
	EMP_DEPT VARCHAR2(50),
	EMP_SALARY NUMBER(10,2)
	);

2、准备数据源

数据源可以是一个文本文件、Excel文件、CSV文件、XML文件、Oracle数据库或其他数据库等。在Oracle 11g中，我们可以使用外部表来访问数据源。外部表是一个虚拟表，它与实际数据源中的数据相关联。以下是创建一个名为EMPLOYEE_EXT的外部表的示例代码：

-- 创建外部表EMPLOYEE_EXT用于访问外部数据源
CREATE TABLE EMPLOYEE_EXT
(
  EMP_ID NUMBER(10),     -- 员工ID，数据类型为NUMBER，最大长度为10
  EMP_NAME VARCHAR2(50), -- 员工姓名，数据类型为VARCHAR2，最大长度为50
  EMP_DEPT VARCHAR2(50), -- 员工部门，数据类型为VARCHAR2，最大长度为50
  EMP_SALARY NUMBER(10,2) -- 员工薪水，数据类型为NUMBER，总长度为10，小数位为2
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER     -- 外部表类型为ORACLE_LOADER，表示使用Oracle的加载程序来访问外部数据
  DEFAULT DIRECTORY DATA_DIR  -- 指定默认目录为DATA_DIR，用于存放外部数据文件
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE  -- 指定记录分隔符为换行符
    FIELDS TERMINATED BY ','      -- 指定字段分隔符为逗号
    MISSING FIELD VALUES ARE NULL -- 指定缺失字段值为NULL
    (
      EMP_ID,        -- 映射外部数据文件中的EMP_ID列
      EMP_NAME,      -- 映射外部数据文件中的EMP_NAME列
      EMP_DEPT,      -- 映射外部数据文件中的EMP_DEPT列
      EMP_SALARY     -- 映射外部数据文件中的EMP_SALARY列
    )
  )
  LOCATION ('employee.csv')  -- 指定外部数据文件的位置为employee.csv
);

在上面的代码中，我们使用ORACLE_LOADER访问一个名为employee.csv的CSV文件。数据文件位于DATA_DIR目录中。我们还指定了记录分隔符和字段分隔符。

3、装载数据

在Oracle 11g中，我们可以使用INSERT INTO SELECT语句将数据从外部表插入到目标表中。以下是一个示例代码：

INSERT INTO EMPLOYEE
	SELECT EMP_ID, EMP_NAME, EMP_DEPT, EMP_SALARY
	FROM EMPLOYEE_EXT;

在上面的代码中，我们将EMPLOYEE_EXT外部表中的数据插入到EMPLOYEE目标表中。

4、验证数据

在完成数据装载后，我们应该验证目标表中的数据是否正确。我们可以使用SELECT语句查询目标表中的数据，以确保它们与预期的结果一致。

以上是Oracle 11g数据装载外部表的基本使用案例。具体的数据装载过程可能因数据源类型、数据量、数据质量等因素而有所不同。但是，以上步骤和示例代码可以作为一个起点，帮助您开始使用Oracle 11g进行数据装载。

点击此处跳转下一节：27.Oracle11g的闪回Flashback

查看全文

http://www.kler.cn/a/161403.html

Zabbix 6.0部署+自定义监控项+自动发现与自动注册+部署zabbix代理服务器

【跨境营商】创新科技助力数码转型增强大湾区企业核心竞争力

08-中介者模式-C语言实现

HarmonyOS4.0系列——03、声明式UI、链式编程、事件方法、以及自定义组件简单案例

如何配置WinDbg和VMware实现内核的调试

appium ：输入框控件为android.view.View 时输入内容（如：验证码、密码输入框）

Docker容器中的OpenCV：轻松构建可移植的计算机视觉环境

Java SpringBoot Controller常见写法

SpringMvc集成开源流量监控、限流、熔断降级、负载保护组件Sentinel | 京东云技术团队

【开源视频联动物联网平台】视频接入网关的用法

关于Kotlin Coroutines你可能会犯的 7 个错误

JVM 运行时参数

Linux C语言 40-进程间通信IPC之消息队列

【微服务】springboot整合quartz使用详解

基于Java+Swing+Mysql图书管理系统(含实训报告)

Linux-进程之间的通信

【UE5】使用场系统炸毁一堵墙

C# 使用FluentScheduler触发定时任务

视频分割方法：批量剪辑高效分割视频，提取m3u8视频技巧

什么是数据架构