sql 练习（3）

　　1.打印九九乘法表

with nums as (select rownum num from dual connect by level<10)
 select max(decode(A,1,exp)) A,
 max(decode(A,2,exp))B,
  max(decode(A,3,exp))C,
   max(decode(A,4,exp))D,
    max(decode(A,5,exp))E,
     max(decode(A,6,exp))F,
      max(decode(A,7,exp))G,
       max(decode(A,8,exp))H,
        max(decode(A,9,exp))I
 from
 ( select a.num a,b.num b,a.num||'*'||b.num ||'='||a.num*b.num exp from nums a,nums b where a.num<=b.num ) group by b order by a;


1*1=1                                
1*2=2    2*2=4                            
1*3=3    2*3=6    3*3=9                        
1*4=4    2*4=8    3*4=12    4*4=16                    
1*5=5    2*5=10    3*5=15    4*5=20    5*5=25                
1*6=6    2*6=12    3*6=18    4*6=24    5*6=30    6*6=36            
1*7=7    2*7=14    3*7=21    4*7=28    5*7=35    6*7=42    7*7=49        
1*8=8    2*8=16    3*8=24    4*8=32    5*8=40    6*8=48    7*8=56    8*8=64    
1*9=9    2*9=18    3*9=27    4*9=36    5*9=45    6*9=54    7*9=63    8*9=72    9*9=81

 使用自链接构造乘法表的所有内容，然后通过行列转换显示为列输出

行列转换原理：将当前的表的内容复制多列，每一列只取部分内容

　　2.计算给定范围内的质数

with temp as
 (select rownum num from dual connect by level<10000)
 select tt1 from
 (select t1.num tt1,t2.num tt2 ,mod(t1.num,t2.num) mm from temp t1,temp t2 where   t2.num>1 and t2.num<=(case when t1.num <100 then t1.num-1
  else 100 end))
  group by tt1  having min(mm)!=0 order by tt1;

 

 

上面给出的是优化后的sql，之前写的是t2.num<=sqrt(t1.num)由于使用大量函数计算导致性能很差，用时2分钟左右，改成t2.num<=100后用时20s

然后继续优化，发现使用plsql只需要0.1s

CREATE OR REPLACE PROCEDURE ZHISHU_PROC 
(m number)
AS 
sta date:=sysdate;
tt number:=sqrt(m);
tt1 number;
flag boolean;
BEGIN
  for i in 3..m loop
  flag:=false;
  tt1:=(case when i<tt then i-1
  else tt end);
    for j in 2..tt1 loop
    if mod(i,j)=0 then
    flag:=true;
    end if;
    end loop;
    if not flag then
    dbms_output.put_line(i);
    end if;
  end loop;
  dbms_output.put_line('hello');
  dbms_output.put_line(to_timestamp(sysdate)-to_timestamp(sta));
END ZHISHU_PROC;