Oracle学习笔记

-- 20130321

今天下午在一些群上面看到这样的一个题目，然后，一些积极的群友就充分的发挥了自己的才能。想出了下面的一些方法，后面那几种用到CONNECT BY 的其实都差不多，但是其中关于CASE WHEN 的用法就让我学习了一下。之前基本上都是在查询列表中使用,很少会在WHERE条件中使用。

-- 4个连续自然数的积为360

1 .存储过程.这种方法被那些技术高点的群友看不起,感觉有点小题大作,纷纷要求用一条SQL语句来写.
set serveroutput on
declare
 j number;
 i NUMBER ;
begin
  j:=0;
  for i in 1..10  loop
   j:=j+1;
   EXIT WHEN  j*(j+1)*(j+2)*(j+3)=360 ;


  end loop;
  dbms_output.put_line(TO_CHAR(j)||','||TO_CHAR(j+1)||','||TO_CHAR(j+2)||','||TO_CHAR(j+3));
end;
/

2. 分析函数.然后题目的作者就发了以下的语句,这个语句只是半成品.
-- 选择result=360的那个。
select rn, rn *
       lead(rn,1) over(order by rn) *
       lead(rn,2) over(order by rn) * 
       lead(rn,3) over(order by rn) result from (
select rownum rn from dual connect by level < 20
) 


-- 3. 使用了CONNECT BY加CASE WHEN,使用子查询来实现.
select x, x + 1, x + 2, x + 3
  from (select case when (x * (x + 1) * (x + 2) * (x + 3)) = 360 
                    then x
                    else null
                     end x
          from (select rownum x from dual connect by rownum < 360))
 where x is not null
 
--  3.1 在上面方法的基础上将CASE WHEN用在WHERE 子句了.说是效率会提高.
select x, x + 1, x + 2, x + 3
  from (select rownum x from dual connect by rownum < 360)
 where case  when (x * (x + 1) * (x + 2) * (x + 3)) = 360 
             then  x
             else null
              end 
           is not null


-- 4. 最简单的一种方法了.
select x, x + 1, x + 2, x + 3
  from (select rownum x from dual connect by rownum < 360)
 where (x * (x + 1) * (x + 2) * (x + 3)) = 360      

-- ============================================== 我是邪恶的分隔线 ================================================= 

-- WITH AS用法：WITH 子句会计算一次结果集，给它一个名字，然后允许我们在后面的查询中去引用它（可能很多次）。若后面的查询中多次使用到一个子查询，可以用WITH子                                 句来来替换。这样不仅可以减少语句的行数， 而且，对性能的提高也有帮助。

-- The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, 

-- and allow us to reference it (maybe multiple times), later in the query.

-- 在INSERT INTO子句中使用WITH AS子句.

-- 准备，
DROP TABLE TAB1 ;
CREATE TABLE TAB1 NOLOGGING AS
SELECT * FROM emp a 
NATURAL JOIN dept b
 WHERE 1 = 2 ;
 
-- 使用
INSERT INTO tab1 
WITH T AS
(
SELECT * 
  FROM emp  a NATURAL JOIN 
       dept b
)
SELECT * FROM t ;
-- 注意，WITH AS语句要先INSERT INTO里面，否则会报缺失关键字SELECT错误