Oracle INSERT 语句
方法1
我估计有点 SQL 基础的人都会写 INSERT 语句。下面是 SQL 标准写法。
1
2
|
INSERT INTO employees (employee_id, name ) VALUES (1, 'Zhangsan' );
INSERT INTO employees VALUES (1, 'Shangbo' );
|
方法2
其实, Oracle 还支持下面的写法,作用和上面的语句完全相同。
1
|
INSERT INTO ( SELECT employee_id, name FROM employees) VALUES (2, 'Lisi' );
|
方法3
此外,同其他数据库一样,Oracle 也支持下面这种写法。
1
2
|
INSERT INTO employees
SELECT 3, 'Wangwu' FROM DUAL;
|
方法4
下面这种写法可以实现列转行,如我们有下面的表存储原始数据,原始数据可能从文件中来。
1
2
3
4
5
6
7
8
9
|
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 把上面的数据插入到下面的表中实现列转行。
1
2
3
4
5
6
7
8
9
10
11
12
|
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 实现同样的功能。
1
2
3
4
5
6
7
8
9
10
11
12
|
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, 如下。
1
2
3
4
5
6
7
8
9
10
11
12
|
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语句
创建表
1
2
3
4
5
6
7
8
9
10
11
|
-- 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)
)
|
显示表中的数据
1
2
3
4
5
6
7
8
9
|
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
|
显示结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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' , '瓦斯爆炸' );
|
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对服务器之家的支持。如果你想了解更多相关内容请查看下面相关链接
原文链接:https://blog.csdn.net/linder0209/article/details/83193130