oracle with as delete,oracle with as 用法

oracle with as 用法

WITH

subquery_name

AS

(the aggregation SQL statement)

SELECT

(query naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”:

=================================================================================

下面自己小试一把，当然，一点都不复杂，很简单很简单的例子，呵呵。

view plaincopy to clipboardprint?

SQL> create table t2(id int);

Table created.

SQL> create table t3(id int);

Table created.

SQL> insert into t2 values(1);

1 row created.

SQL> insert into t2 values(2);

1 row created.

SQL> insert into t3 values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t2;

ID

----------

1

2

SQL> select * from t3;

ID

----------

3

SQL> with

2  sql1 as (select * from t2),

3  sql2 as (select * from t3)

4  select * from t2

5  union

6  select * from t3;

sql2 as (select * from t3)

*

ERROR at line 3:

ORA-32035: unreferenced query name defined in WITH clause

--从这里可以看到，你定义了sql1和sql2，就得用它们哦，不然会报错的。

SQL> with

2  sql1 as (select * from t2),

3  sql2 as (select * from t3)

4  select * from sql1

5  union

6  select * from sql2;

ID

----------

1

2

3

--下面加个WHERE条件试试

SQL> with

2  sql1 as (select * from t2),

3  sql2 as (select * from t3)

4  select * from sql1

5  union

6  select * from sql2

7  where id in(2,3);

ID

----------

1

2

3

--奇怪？为什么加了WHERE条件还是输出ID=1的记录了，继续往下看：

SQL> with

2  sql1 as (select * from t2),

3  sql2 as (select * from t3)

4  select * from sql1

5  where id=3

6  union

7  select * from sql2

8  where id=3;

ID

----------

3

--可以看到，每个条件是要针对每个SELECT语句的。

SQL> create table t2(id int);

Table created.

SQL> create table t3(id int);

Table created.

SQL> insert into t2 values(1);

1 row created.

SQL> insert into t2 values(2);

1 row created.

SQL> insert into t3 values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t2;

ID

----------

1

2