Oracle数据库中取数据如何自动生成INSERT语句

Oracle INSERT 语句

方法1

我估计有点 SQL 基础的人都会写 INSERT 语句。下面是 SQL 标准写法。

INSERT INTO employees (employee_id, name) VALUES (1, ‘Zhangsan’);
INSERT INTO employees VALUES (1, ‘Shangbo’);

方法2

其实, Oracle 还支持下面的写法,作用和上面的语句完全相同。

INSERT INTO (SELECT employee_id, name FROM employees) VALUES (2, ‘Lisi’);

方法3

此外,同其他数据库一样,Oracle 也支持下面这种写法。

INSERT INTO employees
SELECT 3, ‘Wangwu’ FROM DUAL;

方法4

下面这种写法可以实现列转行,如我们有下面的表存储原始数据,原始数据可能从文件中来。

create table sales_input_table (
prod_id number(9,0),
amt_mon number(9,6),
amt_tue number(9,6),
amt_wed number(9,6),
amt_thu number(9,6),
amt_fri number(9,6)
);
insert into sales_input_table values (1, 100.0, 200.0, 300.0, 400.0, 500.0);

下面我们通过一个 SQL 把上面的数据插入到下面的表中实现列转行。

CREATE TABLE sales (
prod_id number(9,0),
time_id date,
amount number(9,0)
);
INSERT ALL
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE, amt_mon)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 1, amt_tue)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 2, amt_wed)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 3, amt_thu)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 4, amt_fri)
SELECT prod_id, amt_mon, amt_tue, amt_wed, amt_thu, amt_fri FROM sales_input_table;

方法5

下面这种写法可以帮我们一次性把一个表中的数据倒入到多个表中,否则我们必须写多条 SQL 实现同样的功能。

INSERT ALL
WHEN order_total <= 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total <= 200000 THEN
INTO medium_orders
WHEN order_total = 500000 THEN
INTO special_orders
WHEN order_total > 200000 THEN
INTO large_orders
ELSE
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;

注意,当 order_total 大于 200000 时,orders 会被插入到 large_orders 和 special_orders 中。这可能不是你想要的结果,如果你只想让 orders 插入到 special_orders 表中,你只需要把 ALL 替换成 FIRST, 如下。

INSERT FIRST
WHEN order_total <= 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total <= 200000 THEN
INTO medium_orders
WHEN order_total = 500000 THEN
INTO special_orders
WHEN order_total > 200000 THEN
INTO large_orders
ELSE
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;

从Oracle数据库中读取数据,自动生成INSERT语句

创建表

— Create table
create table TB_ACCIDENT_TYPE
(
ID NUMBER(20) not null,
NAME VARCHAR2(50),
PATH VARCHAR2(20),
PARENTPATH VARCHAR2(20),
URL VARCHAR2(20),
TYPE VARCHAR2(2),
DESCR VARCHAR2(50)
)

显示表中的数据

select ‘INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘ || ”” || ID ||”” || ‘,’
|| ”” || NAME || ”” || ‘,’
|| ”” || PATH || ”” || ‘,’
|| ”” || PARENTPATH || ”” || ‘,’
|| ”” || URL || ”” || ‘,’
|| ”” || TYPE || ”” || ‘,’
|| ”” || DESCR || ”” || ‘);’
From tb_accident_type order by ID

显示结果

INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘1’,’事故类型关联 ‘,’1′,’0′,”,’0’,”);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘2′,’危险源类型关联’,’2′,’0′,”,”,”);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘3′,’危险品类型关联’,’3′,’0′,”,”,”);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘4′,’生产企业类型关联’,’4′,’0′,”,”,”);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘5′,’区域关联’,’5′,’0′,”,’0′,”);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘568′,’物体打击’,’1.1′,’1′,”,’1′,’物体打击’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘569′,’车辆伤害’,’1.2′,’1′,”,’1′,’车辆伤害’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘570′,’机器伤害’,’1.3′,’1′,”,’1′,’机器伤害’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘571′,’起重伤害’,’1.4′,’1′,”,’1′,’起重伤害’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘572′,’触电’,’1.5′,’1′,”,’1′,’触电’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘573′,’淹溺’,’1.6′,’1′,”,’1′,’淹溺’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘574′,’灼烫’,’1.7′,’1′,”,’1′,’灼烫’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘575′,’火灾’,’1.8′,’1′,”,’1′,’火灾’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘576′,’高处坠落’,’1.9′,’1′,”,’1′,’高处坠落’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘577′,’坍塌’,’1.10′,’1′,”,’1′,’坍塌’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘578′,’冒顶片帮’,’1.11′,’1′,”,’1′,’冒顶片帮’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘580′,’透水’,’1.12′,’1′,”,’1′,’透水’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘581′,’波炮’,’1.13′,’1′,”,’1′,’波炮’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘582′,’火药爆炸’,’1.14′,’1′,”,’1′,’火药爆炸’)INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘583′,’瓦斯爆炸’,’1.15′,’1′,”,’1′,’瓦斯爆炸’);

总结

Oracle INSERT 语句

方法1

我估计有点 SQL 基础的人都会写 INSERT 语句。下面是 SQL 标准写法。

INSERT INTO employees (employee_id, name) VALUES (1, ‘Zhangsan’);
INSERT INTO employees VALUES (1, ‘Shangbo’);

方法2

其实, Oracle 还支持下面的写法,作用和上面的语句完全相同。

INSERT INTO (SELECT employee_id, name FROM employees) VALUES (2, ‘Lisi’);

方法3

此外,同其他数据库一样,Oracle 也支持下面这种写法。

INSERT INTO employees
SELECT 3, ‘Wangwu’ FROM DUAL;

方法4

下面这种写法可以实现列转行,如我们有下面的表存储原始数据,原始数据可能从文件中来。

create table sales_input_table (
prod_id number(9,0),
amt_mon number(9,6),
amt_tue number(9,6),
amt_wed number(9,6),
amt_thu number(9,6),
amt_fri number(9,6)
);
insert into sales_input_table values (1, 100.0, 200.0, 300.0, 400.0, 500.0);

下面我们通过一个 SQL 把上面的数据插入到下面的表中实现列转行。

CREATE TABLE sales (
prod_id number(9,0),
time_id date,
amount number(9,0)
);
INSERT ALL
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE, amt_mon)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 1, amt_tue)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 2, amt_wed)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 3, amt_thu)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 4, amt_fri)
SELECT prod_id, amt_mon, amt_tue, amt_wed, amt_thu, amt_fri FROM sales_input_table;

方法5

下面这种写法可以帮我们一次性把一个表中的数据倒入到多个表中,否则我们必须写多条 SQL 实现同样的功能。

INSERT ALL
WHEN order_total <= 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total <= 200000 THEN
INTO medium_orders
WHEN order_total = 500000 THEN
INTO special_orders
WHEN order_total > 200000 THEN
INTO large_orders
ELSE
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;

注意,当 order_total 大于 200000 时,orders 会被插入到 large_orders 和 special_orders 中。这可能不是你想要的结果,如果你只想让 orders 插入到 special_orders 表中,你只需要把 ALL 替换成 FIRST, 如下。

INSERT FIRST
WHEN order_total <= 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total <= 200000 THEN
INTO medium_orders
WHEN order_total = 500000 THEN
INTO special_orders
WHEN order_total > 200000 THEN
INTO large_orders
ELSE
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;

从Oracle数据库中读取数据,自动生成INSERT语句

创建表

— Create table
create table TB_ACCIDENT_TYPE
(
ID NUMBER(20) not null,
NAME VARCHAR2(50),
PATH VARCHAR2(20),
PARENTPATH VARCHAR2(20),
URL VARCHAR2(20),
TYPE VARCHAR2(2),
DESCR VARCHAR2(50)
)

显示表中的数据

select ‘INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘ || ”” || ID ||”” || ‘,’
|| ”” || NAME || ”” || ‘,’
|| ”” || PATH || ”” || ‘,’
|| ”” || PARENTPATH || ”” || ‘,’
|| ”” || URL || ”” || ‘,’
|| ”” || TYPE || ”” || ‘,’
|| ”” || DESCR || ”” || ‘);’
From tb_accident_type order by ID

显示结果

INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘1’,’事故类型关联 ‘,’1′,’0′,”,’0’,”);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘2′,’危险源类型关联’,’2′,’0′,”,”,”);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘3′,’危险品类型关联’,’3′,’0′,”,”,”);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘4′,’生产企业类型关联’,’4′,’0′,”,”,”);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘5′,’区域关联’,’5′,’0′,”,’0′,”);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘568′,’物体打击’,’1.1′,’1′,”,’1′,’物体打击’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘569′,’车辆伤害’,’1.2′,’1′,”,’1′,’车辆伤害’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘570′,’机器伤害’,’1.3′,’1′,”,’1′,’机器伤害’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘571′,’起重伤害’,’1.4′,’1′,”,’1′,’起重伤害’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘572′,’触电’,’1.5′,’1′,”,’1′,’触电’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘573′,’淹溺’,’1.6′,’1′,”,’1′,’淹溺’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘574′,’灼烫’,’1.7′,’1′,”,’1′,’灼烫’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘575′,’火灾’,’1.8′,’1′,”,’1′,’火灾’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘576′,’高处坠落’,’1.9′,’1′,”,’1′,’高处坠落’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘577′,’坍塌’,’1.10′,’1′,”,’1′,’坍塌’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘578′,’冒顶片帮’,’1.11′,’1′,”,’1′,’冒顶片帮’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘580′,’透水’,’1.12′,’1′,”,’1′,’透水’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘581′,’波炮’,’1.13′,’1′,”,’1′,’波炮’);INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘582′,’火药爆炸’,’1.14′,’1′,”,’1′,’火药爆炸’)INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR)
VALUES(‘583′,’瓦斯爆炸’,’1.15′,’1′,”,’1′,’瓦斯爆炸’);

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对的支持。如果你想了解更多相关内容请查看下面相关链接


数据运维技术 » Oracle数据库中取数据如何自动生成INSERT语句