福建二哥 › 门户 › 查看主题

Oracle批量投入数据方法总结

发布者: 土豆服务器 | 发布时间: 2025-6-18 09:24| 查看数: 119| 评论数: 0|帖子模式

零. 待投入数据的表结构

create table DB_USER."PERSON_TABLE" (
ID NUMBER not null
, NAME VARCHAR2(50)
, AGE NUMBER
, EMAIL VARCHAR2(100)
, CREATED_DATE DATE
)

复制代码

一. INSERT INTO ... SELECT投入数据

INSERT INTO ... SELECT

复制代码

的这种方式相当于把数据加载到内存中之后再插入数据库，只适合投入小规模的数据。

1.1 普通的方式投入数据

当数据量不是很多的时候，可以使用这种方式

先从DUAL虚拟表中检索后造出指定条数的数据后，再插入到指定的表中。
除了主键之类的关键字段之外，其余字段写固定值即可。

INSERT INTO PERSON_TABLE
SELECT
-- 因为该字段为字符串形式,所以使用TO_CHAR转换
-- TO_CHAR(100000000 + LEVEL) || 'TEST_ID' AS id,
LEVEL AS id,
'Name_' || ROWNUM AS name,
TRUNC(DBMS_RANDOM.VALUE(18, 60)) AS age,
'user' || ROWNUM || '@example.com' AS email,
SYSDATE - DBMS_RANDOM.VALUE(0, 365) AS created_date
FROM
DUAL
CONNECT BY LEVEL <= 1000000;

复制代码

1.2 并行插入（Parallel Insert）投入数据

ALTER SESSION ENABLE PARALLEL DML;

复制代码

INSERT /*+ PARALLEL(PERSON_TABLE, 4) */ INTO PERSON_TABLE
SELECT LEVEL AS id,
'Name_' || ROWNUM AS name,
TRUNC(DBMS_RANDOM.VALUE(18, 60)) AS age,
'user' || ROWNUM || '@example.com' AS email,
SYSDATE - DBMS_RANDOM.VALUE(0, 365) AS created_date
FROM DUAL
CONNECT BY LEVEL <= 1000000;

复制代码

二. PL/SQL 循环投入数据

2.1 脚本介绍

灵活，支持动态生成数据，适合
1. 中小数据量
复制代码
。
数据量大时性能较差，容易导致
1. 上下文切换开销
复制代码
。

BEGIN
FOR i IN 1..5000000 LOOP
INSERT INTO PERSON_TABLE (id, name, age, email, created_date)
VALUES (
i,
'Name_' || i,
-- 随机年龄
TRUNC(DBMS_RANDOM.VALUE(18, 60)),
'user' || i || '@example.com',
-- 随机日期
SYSDATE - DBMS_RANDOM.VALUE(0, 365)
);
-- 每 100000 条提交一次
IF MOD(i, 100000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/

复制代码

2.2 效果

投入500万条数据，耗时5分钟。

三. PL/SQL FORALL 批量操作

3.1 脚本介绍

这种方式可以
1. 减少上下文切换
复制代码
，性能比普通的循环插入要好。

DECLARE
TYPE person_array IS TABLE OF PERSON_TABLE%ROWTYPE;
v_data person_array := person_array();
BEGIN
FOR i IN 1..5000000 LOOP
v_data.EXTEND;
v_data(v_data.COUNT).id := i;
v_data(v_data.COUNT).name := 'Name_' || i;
v_data(v_data.COUNT).age := TRUNC(DBMS_RANDOM.VALUE(18, 60));
v_data(v_data.COUNT).email := 'user' || i || '@example.com';
v_data(v_data.COUNT).created_date := SYSDATE - DBMS_RANDOM.VALUE(0, 365);
-- 每 100000 条批量插入一次
IF MOD(i, 100000) = 0 THEN
FORALL j IN 1..v_data.COUNT
INSERT INTO PERSON_TABLE VALUES v_data(j);
COMMIT;
v_data.DELETE; -- 清空数组
END IF;
END LOOP;
-- 插入剩余数据
FORALL j IN 1..v_data.COUNT
INSERT INTO PERSON_TABLE VALUES v_data(j);
COMMIT;
END;
/

复制代码

3.2 效果

投入500万条数据，耗时1分钟18秒。

四. SQL*Loader 工具加载外部文件

写一个PowerShell脚本，根据数据库的表结构来生成csv文件

该脚本执行后，会在桌面上生成一个csv文件。

# 文件名称
$file_name = 'person_data.csv'
# 路径
$outputFile = "$Home\Desktop\$file_name"
# csv 文件的总行数
$rows = 5000000
# 并行线程数
$threadCount = 4
# 每个线程生成的记录数量
$chunkSize = [math]::Ceiling($rows / $threadCount)
# 判断文件是否存在，存在的话就删除
if (Test-Path -Path $outputFile) {
Remove-Item -Path $outputFile -Force
}
# 写入 CSV 表头
# "`"ID`",`"NAME`",`"AGE`",`"EMAIL`",`"CREATED_DATE`"" | Out-File -FilePath $outputFile -Encoding UTF8 -Append
# 定义脚本块
$scriptblock = {
param($startRow, $endRow, $tempFile)
# 在后台作业中定义 Generate-Chunk 函数
function Generate-Chunk {
param (
[int]$startRow,
[int]$endRow,
[string]$filePath
)
$random = [System.Random]::new()
$currentDate = Get-Date
$sb = [System.Text.StringBuilder]::new()
# 循环生成csv数据
for ($i = $startRow; $i -le $endRow; $i++) {
# =========================对应数据库的各字段值=========================
$id = $i
$name = "Name_$i"
$age = $random.Next(18, 60)
$email = "user$i@example.com"
$createdDate = $currentDate.AddDays(- $random.Next(0, 365)).ToString("yyyy/MM/dd HH:mm:ss")
# =========================对应数据库的各字段值=========================
# =========================一行csv=========================
$line = "`"$id`",`"$name`",`"$age`",`"$email`",`"$createdDate`""
# =========================一行csv=========================
$sb.AppendLine($line) | Out-Null
}
<#
将生成的内容写入文件
-NoNewline 的作用是为了防止csv文件的最后一行被追加空行
#>
$sb.ToString() | Out-File -FilePath $filePath -Encoding UTF8 -Append -NoNewline
}
# 调用 Generate-Chunk 函数，多线程生成临时csv文件
Generate-Chunk -startRow $startRow -endRow $endRow -filePath $tempFile
}
# CSV文件合成
function Merge-CSV {
param (
[string]$outputFile,
[bool]$IsReadAllDataToMemory
)
# 获取所有分段文件，按名称排序
$partFiles = Get-ChildItem -Path "$outputFile.*.part" | Sort-Object Name
if ($IsReadAllDataToMemory) {
# 将所有内容加载到内存中，然后一次性写入
$partFiles | ForEach-Object { Get-Content $_.FullName } | Out-File -FilePath $outputFile -Encoding UTF8 -Force
# 删除所有分段文件
$partFiles | ForEach-Object { Remove-Item $_.FullName }
return;
}
$partFiles | ForEach-Object {
Get-Content -Path $_.FullName | Out-File -FilePath $outputFile -Encoding UTF8 -Append
Remove-Item -Path $_.FullName
}
}
try {
# 定义job数组
$jobs = @()
# 组装job
1..$threadCount | ForEach-Object {
$startRow = ($_ - 1) * $chunkSize + 1
$endRow = [math]::Min($_ * $chunkSize, $rows)
# 临时csv文件
$tempFile = "$outputFile.$_.part"
$jobs += Start-Job -ScriptBlock $scriptblock -ArgumentList $startRow, $endRow, $tempFile
}
# 统计生成csv文件所消耗的时间
$exec_time = Measure-Command {
Write-Host "临时csv文件开始生成..."
# 执行job，等待并收集所有执行结果
$jobs | ForEach-Object { Wait-Job -Job $_; Receive-Job -Job $_; Remove-Job -Job $_ }
# 合并所有并发生成的csv临时文件,组装成最终的总csv文件
Write-Host "临时csv文件生成完毕,开启合并..."
Merge-CSV -outputFile $outputFile -IsReadAllDataToMemory $False
}
Write-Host "csv文件生成完毕,共消耗$($exec_time.TotalSeconds)秒: $outputFile" -ForegroundColor Red
} catch {
# 当异常发生时,清空桌面上的临时csv文件
if (Test-Path -Path "$outputFile.*.part") {
Remove-Item -Path "$outputFile.*.part" -Force
}
Write-Host "脚本运行时发生异常: $_" -ForegroundColor Red
Write-Host "详细信息: $($_.Exception.Message)" -ForegroundColor Yellow
Write-Host "堆栈跟踪: $($_.Exception.StackTrace)" -ForegroundColor Gray
}
Read-Host "按 Enter 键退出..."

复制代码

创建控制文件

control_file.ctl

复制代码

LOAD DATA
INFILE 'person_data.csv'
INTO TABLE PERSON_TABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(id, name, age, email, created_date "TO_DATE(:created_date, 'YYYY/MM/DD HH24:MI:SS')")

复制代码

使用

SQL*Loader

复制代码

执行加载

性能极高，适合大规模数据插入。
支持多线程和并行加载。

sqlldr db_user/oracle@SERVICE_XEPDB1_CLIENT control=control_file.ctl direct=true

复制代码

4.1 效果

投入500万条数据，耗时居然不到10秒！

到此这篇关于Oracle批量投入数据方法总结的文章就介绍到这了,更多相关Oracle投入数据内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源：https://www.jb51.net/database/33418903j.htm
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

本帖子中包含更多资源

您需要登录才可以下载或查看，没有账号？立即注册

Oracle批量投入数据方法总结

本帖子中包含更多资源

最新评论

新人须知

常见问题

维权申诉

关注微博

扫描微信

关于合作

通讯联系