oracle insert into with select

最新推荐文章于 2024-02-20 10:50:51 发布

原创最新推荐文章于 2024-02-20 10:50:51 发布 · 345 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #C #C++ #C#

数据库应用专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个SQL脚本实例，展示了如何创建表、插入数据、使用序列生成唯一标识符以及执行查询来获取最大值等操作。通过具体代码说明了基本的数据库管理任务。


set feedback off;
set pagesize 0;

create table foo (a number, b varchar(10), c varchar(10));

insert into foo values ( 15, 'abc','def' );
insert into foo values (998, 'max','min' );
insert into foo values (  7, 'bla','bla' );
insert into foo values (632, 'now','then');

insert into foo 
  (a,b,c)
  (select                   AA,
                            BB,
                            CC 
  from
    (select max(a) +1       AA      from foo), 
    (select 'new'           BB,
            'old'           CC      from dual));

select * from foo where a = (select max(a) from foo);

drop table foo;

[size=medium][color=red]--注意，当需要序列值时，不能放在子查询中[/color] [/size]


INSERT INTO T_A    
    (id,   
     goodsA_id,   
     goodsB_id,   
     goodsC_id,   
     num,   
     money,   
     dateA_id,   
     dateB_id,   
     create_date)   
    (SELECT test1_SEQUENCE.NEXTVAL,   
                    goodsA_id,   
                    goodsB_id,   
                    goodsC_id,   
                    num,   
                    money,   
                    dateA_id,   
                    dateB_id,   
                    SYSDATE   
     FROM   (SELECT goodsA_id,   
                                    goodsB_id,   
                                    goodsC_id,   
                                    num,   
                                    33 money   
                     FROM   goods_info),   
                    (SELECT id     dateA_id,   
                                    period dateB_id   
                     FROM   date_x   
                     WHERE  flagA = 'Y'))