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的数据装载
一、数据装载概述
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
更多精彩文章可扫码关注公主号查看:
若需要各种相关资源可关注公众号留言