ORACLE WITH AS 用法
原文传送门：http://blog.csdn.net/wh62592855/archive/2009/11/06/4776631.aspx
记得以前在论坛里看到inthirties用到过WITH AS这个字眼，当时没特别在意。今天在一个帖子里又看到有人用这个，所以就去网上搜了搜相关内容，自己小试了一把，写下来，方便以后忘了的话学习。
===================================================================================
先举个例子吧：
有两张表，分别为A、B，求得一个字段的值先在表A中寻找，如果A表中存在数据，则输出A表的值；如果A表中不存在，则在B表中寻找，若B表中有相应记录，则输出B表的值；如果B表中也不存在，则输出"no records”字符串。
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 re
相关文档：
嘿嘿，这还是上章知识了，只是单一写出来比较好一点，因为上面的是较基础的，不怎么涉及操作，而这里是通过操作出来的，呵呵。
首先，打开数据库服务，然后通过控制台打开sqlplus：
然后将数据库设置为只读：alter database read only；但是呢，这样是不� ......
如何远程判断Oracle数据库的安装平台
select * from v$version;
查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES ......
Oracle Workflows Access Protection Concepts
As a workflow developer, you can use access protection to allow or discourage “customers” of your workflows from modifying your “seed data” workflow definitions.
作为工作流开发者，你可以利用访问控制保护权限来允许或阻止工作流的使� ......
declare
v_isexist number(3,0):=0;
begin
select count(1) into v_isexist from sys.all_objects where owner = 'TEST' and object_type = 'TABLE' and object_name = 'TAB1';
if v_isexist>0 ......
select r.rollid as rollid from zh1_rool r where r.date_p >=to_date('2009-11-26 23:59:59','yyyy-mm-dd hh24:mi:ss') and rollid not in
(select t.lot_number as rollid
from inv.mtl_onhand_quantities_detail t
left join mtl_system_items_b mi
on t.inventory_item_id = mi.inventory_ ......
