在Oracle数据库中,有多种不同的方式可以将数据批量插入到表中。以下是几种常见的方法及其代码示例:
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3),
(value4, value5, value6),
(value7, value8, value9);
示例代码:
import cx_Oracle
# 创建连接
connection = cx_Oracle.connect('username/password@localhost:1521/service_name')
# 创建游标
cursor = connection.cursor()
# 定义插入语句
insert_sql = """
INSERT INTO employee (employee_id, employee_name, salary)
VALUES (:1, :2, :3)
"""
# 定义数据列表
data = [
(1, 'John Doe', 5000),
(2, 'Jane Smith', 6000),
(3, 'Mike Johnson', 5500)
]
# 批量插入数据
cursor.executemany(insert_sql, data)
# 提交事务
connection.commit()
# 关闭游标和连接
cursor.close()
connection.close()
INSERT ALL
INTO table_name (column1, column2, column3) VALUES (value1, value2, value3)
INTO table_name (column1, column2, column3) VALUES (value4, value5, value6)
INTO table_name (column1, column2, column3) VALUES (value7, value8, value9)
SELECT 1 FROM DUAL;
示例代码:
import cx_Oracle
# 创建连接
connection = cx_Oracle.connect('username/password@localhost:1521/service_name')
# 创建游标
cursor = connection.cursor()
# 定义插入语句
insert_sql = """
INSERT ALL
INTO employee (employee_id, employee_name, salary) VALUES (:1, :2, :3)
INTO employee (employee_id, employee_name, salary) VALUES (:4, :5, :6)
INTO employee (employee_id, employee_name, salary) VALUES (:7, :8, :9)
SELECT 1 FROM DUAL
"""
# 定义数据列表
data = [1, 'John Doe', 5000, 2, 'Jane Smith', 6000, 3, 'Mike Johnson', 5500]
# 批量插入数据
cursor.execute(insert_sql, data)
# 提交事务
connection.commit()
# 关闭游标和连接
cursor.close()
connection.close()
这里是一个使用SQL*Loader的示例:
a. 创建一个控制文件(例如,control.ctl),指定要加载的数据文件和目标表:
LOAD DATA
INFILE 'data.txt'
INTO TABLE table_name
FIELDS TERMINATED BY ',' (column1, column2, column3)
b. 创建一个数据文件(例如,data.txt),包含要插入的数据:
1,John Doe,5000
2,Jane Smith,6000
3,Mike Johnson,5500
c. 运行SQL*Loader来加载数据:
sqlldr username/password@localhost:1521/service_name control=control.ctl
以上是几种将数据批量插入到Oracle数据库中的常见方法和示例代码。根据具体的需求和使用场景,选择适合的方法来实现数据的批量插入。