1. 定义一个索引变量

TYPE indexType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

2. 创建一个临时表来保存插入数据

CREATE GLOBAL TEMPORARY TABLE temp_table (
    column_1 NUMBER,
    column_2 VARCHAR2(100),
    column_3 DATE
)
ON COMMIT DELETE ROWS;

3. 将数据从外部文件插入到临时表中

DECLARE
    file_handle UTL_FILE.FILE_TYPE;
    line_text VARCHAR2(4000);
    index_var indexType;
    TYPE dataType IS TABLE OF temp_table%ROWTYPE;
    bulk_data dataType;
    bulk_limit CONSTANT INTEGER := 500;
    bulk_count INTEGER := 0;
BEGIN
    file_handle := UTL_FILE.FOPEN('DIRECTORY', 'FILENAME', 'r');
    LOOP
        UTL_FILE.GET_LINE(file_handle, line_text);
        index_var(index_var.COUNT+1) := bulk_count+1;
        bulk_data.extend;
        bulk_data(bulk_data.LAST).column_1 := TO_NUMBER(SUBSTR(line_text,1,10));
        bulk_data(bulk_data.LAST).column_2 := SUBSTR(line_text,11,100);
        bulk_data(bulk_data.LAST).column_3 := TO_DATE(SUBSTR(line_text,111,8), 'YYYYMMDD');
        bulk_count := bulk_count + 1;
        IF bulk_count = bulk_limit THEN
            FOR i IN 1..index_var.COUNT LOOP
                bulk_data(index_var(i)).column_1 := index_var(i);
            END LOOP; 
            FORALL i IN 1..bulk_data.COUNT
                INSERT INTO temp_table VALUES bulk_data(i);
            bulk_data.delete;
            index_var.delete;
            bulk_count := 0;
        END IF;
    END LOOP;
    UTL_FILE.FCLOSE(file_handle);
END;

4. 将插入的数据从临时表中复制到目标表中，并建立索引

INSERT INTO target_table (column_1, column_2, column_3)
SELECT column_1, column_2, column_3
FROM temp_table;
CREATE INDEX index_name ON target