Oracle批量投入数据方法总结

2025-03-21 11:53 数据库作者： fengyehongWorld

零. 待投入数据的表结构

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

Oracle批量投入数据方法总结

一. 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 脚本介绍

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

BEGIN
    FOR i IN 1..5000000 LOOP
        INSERT INTO PERSON_TABLE (id, name, age, email, created_date)
      android  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分钟。

Oracle批量投入数据方法总结

三. PL/SQL FORALL 批量操作

3.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秒。

Oracle批量投入数据方法总结

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

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

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

# javascript文件名称
$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
		# 删除所有分段文件
		$partFileandroids | 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/ohttp://www.devze.comracle@SERVICE_XEPDB1_CLIENT control=control_file.ctl direct=true

4.1 效果

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

Oracle批量投入数据方法总结

到此这篇关于oracle批量投入数据方法总结的文章就介绍到这了,更多相关Oracle投入数据内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！

继续阅读：Oracle批量投入数据的方法 Oracle投入数据

Oracle批量投入数据方法总结

目录

零. 待投入数据的表结构

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

1.1 普通的方式投入数据

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

二. PL/SQL 循环投入数据

2.1 脚本介绍

2.2 效果

三. PL/SQL FORALL 批量操作

3.1 脚本介绍

3.2 效果

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

4.1 效果

更多精彩内容

精彩评论

最新数据库

SQL BETWEEN 的常见用法小结

MySql match against工具详细用法

MySQL索引的优化之LIKE模糊查询功能实现

Redis缓存雪崩的物种解决方案

Redis遍历海量数据的实现示例

数据库排行榜

Hadoop Key Management Server (KMS)配置及测试

spark报错ERROR ObjectStore: Version information found in metastore differs 2.1.0 from expected schema version 1.2.0. Schema verififcation is disabled hive.metastore.schema.verification so setting version.

解决Navicat远程连接MySQL出现 10060 unknow error的方法

redis-cluster集群调优之cluster-require-full-coverage参数

Navicat连接Oracle数据库的详细步骤与注意事项