Oracle选择插入数据

作者：--    发布时间：2019-11-20

在本教程中，您将学习如何使用oracle insert into select语句将数据从select语句的结果中插入到表中。

oracle insert into select语句概述

有时候，想要将从其它表中选择数据并将其插入到另一个表中。要做到这一点，可使用oracle insert into select语句，如下所示：

insert into target_table (col1, col2, col3)
select col1,
       col2,
       col3
from source_table
where condition;

oracle insert into select语句要求源表和目标表匹配数据类型。

oracle insert into select示例

1. 插入所有销售数据示例

下面了演示如何使用insert into select语句，首先创建一个名为sales的表。

create table sales (
    customer_id number,
    product_id number,
    order_date date not null,
    total number(9,2) default 0 not null,
    primary key(customer_id,
                product_id,
                order_date)
);

以下语句将orders和order_items表中的销售摘要插入到sales表中，参考以下实现语句 -

insert into  sales(customer_id, product_id, order_date, total)
select customer_id,
       product_id,
       order_date,
       sum(quantity * unit_price) amount
from orders
inner join order_items using(order_id)
where status = 'shipped'
group by customer_id,
         product_id,
         order_date;

以下语句从sales表中检索数据以验证插入结果：

select *
from sales
order by order_date desc,
         total desc;

执行上面查询语句，得到以下结果 -

2. 插入部分销售数据示例

假设只想将2017年的销售摘要数据复制到新表中。 为此，首先创建一个名为sales_2017的新表，如下所示：

create table sales_2017 
as select
    *
from
    sales
where
    1 = 0;

where子句中的条件是确保sales表中的数据不会被复制到sales_2017表中。

其次，使用oracle insert into select和where子句将2017年的销售数据复制到sales_2017表中：

insert into  sales_2017
    select customer_id,
           product_id,
           order_date,
           sum(quantity * unit_price) amount
    from orders
    inner join order_items using(order_id)
    where status = 'shipped' and extract(year from order_date) = 2017        
    group by customer_id,
             product_id,
             order_date;

在此示例中，没有在insert into子句中指定列列表，因为select语句的结果具有与sales_2017表的列对应的值。 另外，在select语句的where子句中添加了更多的条件，以在2017年仅检索销售数据。

以下查询选择sales_2017表中的所有数据：

select *
from sales_2017
order by order_date desc,
         total desc;

执行上面查询语句，得到以下结果 -

3. 插入部分数据和文字值示例

假设，想要发送电子邮件给所有客户告知新产品上市。 要做到这一点，可以将客户数据复制到单独的表并跟踪电子邮件发送状态。

首先，创建一个名为customer_lists的新表，如下所示：

-- oracle 12c写法
create table customer_lists(
    list_id number generated by default as identity,
    first_name varchar2(255) not null,
    last_name varchar2(255) not null,
    email varchar2(255) not null,
    sent number(1) not null,
    sent_date date,
    primary key(list_id)
);

-- oracle 11g写法
drop sequence customer_lists_seq;
create sequence customer_lists_seq
 increment by 1
 start with 1
 maxvalue 9999999999
 nocache;

create table customer_lists(
    list_id number,
    first_name varchar2(255) not null,
    last_name varchar2(255) not null,
    email varchar2(255) not null,
    sent number(1) not null,
    sent_date date,
    primary key(list_id)
);

其次，将contacts表中的数据复制到customer_lists表中：

-- oracle 12c写法
insert into
        customer_lists(
            first_name,
            last_name,
            email,
            sent
        ) select
            first_name,
            last_name,
            email,
            0
        from
            contacts;

-- oracle 11g写法
insert into
        customer_lists(
            list_id,
            first_name,
            last_name,
            email,
            sent
        ) select
            customer_lists_seq.nextval,
            first_name,
            last_name,
            email,
            0
        from
            contacts;

在这个例子中，除了从contacts表中检索数据之外，我们还使用文字值：0 作为sent列的初始值。

以下查询从customer_lists表中检索数据：

请注意，这个例子只是为了演示，可以将default 0添加到sent列的定义中。

在本教程中，您已经学习了如何使用oracle insert into select语句将查询结果向其它表中插入数据。