如何从Oracle数据库中读取数据自动生成INSERT语句-创新互联
小编给大家分享一下如何从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语句”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注创新互联行业资讯频道！
文章标题：如何从Oracle数据库中读取数据自动生成INSERT语句-创新互联
文章地址：http://6mz.cn/article/jjjog.html