欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

从Oracle数据库中读取数据自动生成INSERT语句的方法

程序员文章站 2023-12-13 11:40:28
oracle insert 语句 方法1 我估计有点 sql 基础的人都会写 insert 语句。下面是 sql 标准写法。 insert into empl...

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','瓦斯爆炸');

总结

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

上一篇:

下一篇: