ORACLE WITH AS 用法.doc

ORACLE WITH AS 用法 分类： Oracle 2009-12-02 13:51 950人阅读 评论(0) 收藏 举报 原文传送门：/warchive/2009/11/06/4776631.aspx ? 记得以前在论坛里看到inthirties用到过WITH AS这个字眼，当时没特别在意。今天在一个帖子里又看到有人用这个，所以就去网上搜了搜相关内容，自己小试了一把，写下来，方便以后忘了的话学习。 =================================================================================== 先举个例子吧： 有两张表，分别为A、B，求得一个字段的值先在表A中寻找，如果A表中存在数据，则输出A表的值；如果A表中不存在，则在B表中寻找，若B表中有相应记录，则输出B表的值；如果B表中也不存在，则输出no records”字符串。 view plaincopy to clipboardprint? with?? sql1?as?(select?to_char(a)?s_name?from?test_tempa),?? sql2?as?(select?to_char(b)?s_name?from?test_tempb?where?not?exists?(select?s_name?from?sql1?where?rownum=1))?? select?*?from?sql1?? union?all?? select?*?from?sql2?? union?all?? select?no?records?from?dual?? ???????where?not?exists?(select?s_name?from?sql1?where?rownum=1)?? ???????and?not?exists?(select?s_name?from?sql2?where?rownum=1);?? 再举个简单的例子 with a as (select * from test) select * from a; 其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它 这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了 下面是有哪些信誉好的足球投注网站到的英文文档资料 About Oracle WITH clause Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times. The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”: ?? ? The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.?? ? Formally, the “WITH clause” is called subquery factoring?? ? The SQL “WITH clause” is used when a subquery is executed multiple times?? ? Also useful for recursive queries (SQL-99, but not Oracle SQL) To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query. We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause”