Oracle中打印99乘法表的13种方法

--实现1:
select r1 || '*' || r1 || '=' || r1 * r1 A,
       decode(r2, '', '', r2 || '*' || r1 || '=' || r2 * r1) b,
       decode(r3, '', '', r3 || '*' || r1 || '=' || r3 * r1) C,
       decode(r4, '', '', r4 || '*' || r1 || '=' || r4 * r1) D,
       decode(r5, '', '', r5 || '*' || r1 || '=' || r5 * r1) E,
       decode(r6, '', '', r6 || '*' || r1 || '=' || r6 * r1) F,
       decode(r7, '', '', r7 || '*' || r1 || '=' || r7 * r1) G,
       decode(r8, '', '', r8 || '*' || r1 || '=' || r8 * r1) H,
       decode(r9, '', '', r9 || '*' || r1 || '=' || r9 * r1) I
  from (select level r1,
               lag(level, 1) over(order by level) r2,
               lag(level, 2) over(order by level) r3,
               lag(level, 3) over(order by level) r4,
               lag(level, 4) over(order by level) r5,
               lag(level, 5) over(order by level) r6,
               lag(level, 6) over(order by level) r7,
               lag(level, 7) over(order by level) r8,
               lag(level, 8) over(order by level) r9
        from   dual
        connect by level < 10
        );
        
--实现2:        
select rn, ltrim(max(sys_connect_by_path(product, ',')), ',') product
  from (select rn, product, min(product) over(partition by rn) product_min,
               row_number() over(order by rn, product)) + (dense_rank() over(order by rn) numId
          from (select b.rn, a.rn || '*' || b.rn || '=' || a.rn * b.rn product 
                  from (select rownum rn from all_objects where rownum <= 9) a, 
                       (select rownum rn from all_objects where rownum <= 9) b
                  where a.rn <= b.rn
                  order by b.rn, product
                )
       ) 
start with product = product_min
connect by numId - 1 = prior numId
group by rn order by product;

--实现3:
select ltrim(sys_connect_by_path
              (rownum || '*' || lv || '=' || rpad(rownum * lv, 2),'  ')
            )
  from (select level lv from dual connect by level < 10)
where lv = 1
connect by lv + 1 = prior lv;

--实现4:
select reverse(ltrim((sys_connect_by_path(reverse(rownum || 'x' || lv || '=' ||
                                                  lpad(rownum * lv, 2, '0')),
                                          '   ')))) "乘法口诀"
  from (select level lv from dual connect by level < 10)
 where lv = 1
connect by prior lv = lv + 1;

--实现5:
with x as
(select level n from dual connect by level < 10)
select 
  max(decode(a, 1, cnt)) as a,
  max(decode(a, 2, cnt)) as b,
  max(decode(a, 3, cnt)) as c,
  max(decode(a, 4, cnt)) as d,
  max(decode(a, 5, cnt)) as e,
  max(decode(a, 6, cnt)) as f,
  max(decode(a, 7, cnt)) as g,
  max(decode(a, 8, cnt)) as h,
  max(decode(a, 9, cnt)) as i
from
  (
  select c0.n a, c1.n b, c0.n || '*' ||c1.n || '=' || c0.n*c1.n cnt
  from x c0, x c1
  where c0.n <= c1.n
  )
group by b;

--实现6:
select ltrim(sys_connect_by_path
   (rownum - rn1+1||'*'||rownum || '=' || rpad(rownum * (rownum - rn1+1), 2) ,'   ')) 
   from
   (select rownum rn1 from dual connect by rownum <=9)
   where rn1 = 1
   connect by rn1+1 = prior rn1; 
   
--实现7:
select max(decode(rowrn, 1, vresult, null)) A,
       max(decode(rowrn, 2, vresult, null)) B,
       max(decode(rowrn, 3, vresult, null)) C,
       max(decode(rowrn, 4, vresult, null)) D,
       max(decode(rowrn, 5, vresult, null)) E,
       max(decode(rowrn, 6, vresult, null)) F,
       max(decode(rowrn, 7, vresult, null)) G,
       max(decode(rowrn, 8, vresult, null)) H,
       max(decode(rowrn, 9, vresult, null)) J
  from (select rn,
               row_number() over(partition by rn order by vresult) rowrn,
               vresult
          from (select b.rn rn,
                       a.rn || '*' || b.rn || ' = ' || a.rn * b.rn vresult
                  from (select rownum rn from dual connect by rownum <= 9) a,
                       (select rownum rn from dual connect by rownum <= 9) b
                 where a.rn <= b.rn))
group by rn; 

--方法8:
select a.rn, substr(max( sys_connect_by_path(
       case when a.rn*b.rn >= 10 then substr(translate(b.rn
      ||'*'
      ||a.rn
      ||'='
      ||a.rn*b.rn,'1234567890*=','一二三四五六七八九十'),1,
      case when mod(a.rn*b.rn,10) = 0 or a.rn*b.rn > 20 then 3 else 2 end)
      ||'十'
      || translate(mod(a.rn*b.rn,10),'1234567890','一二三四五六七八九')
    else translate(b.rn
      ||'*'
      ||a.rn
      ||'='
      ||a.rn*b.rn,'123456789=*','一二三四五六七八九得')
  end ,',')),2) 口诀
from (select rownum rn from all_objects where rownum <= 9) a,
     (select rownum rn from all_objects where rownum <= 9) b
where a.rn >= b.rn
connect by prior a.rn = a.rn
    and prior b.rn = b.rn-1
  start with b.rn  = 1
group by a.rn
order by 1;

--方法10:
declare
    v_result    varchar2(200);
begin
    for i in 1..9 loop
        select wmsys.wm_concat(rownum||'*'||i||'='||rownum*i) into v_result from dual connect by rownum<=i;
        dbms_output.put_line(v_result);
    end loop;
end;

--方法11:
declare 
    i int;
    j int;
begin 
    i:=1;
    j:=1;
    while i < 10
        loop
        while j <= i
        loop
           dbms_output.put(j||'*'||i||'='); 
           if length(i*j) = 1 and j!=1 then 
               dbms_output.put(' '); 
           end if;
           dbms_output.put(i*j||' ');
           j:=j+1;
        end loop;
        j:=1;
        i:=i+1;
        dbms_output.put_line(' '); 
    end loop; 
end; 
/ 

--方法12:
declare 
begin 
    for i in 1..9 loop 
        for j in 1 .. i loop 
            dbms_output.put(j||'*'||i||'='); 
            if length(i*j) = 1 and j!=1 then 
            dbms_output.put(' '); 
            end if; 
            dbms_output.put(i*j); 
            dbms_output.put(' '); 
        end loop; 
        dbms_output.put_line(' '); 
    end loop; 
end; 

select decode(r1,null,null,r1 || '*' || rownum ||'='|| r1* rownum) a,
       decode(r2,null,null,r2 || '*' || rownum ||'='|| r2* rownum) b,
       decode(r3,null,null,r3 || '*' || rownum ||'='|| r3* rownum) c,
       decode(r4,null,null,r4 || '*' || rownum ||'='|| r4* rownum) d,
       decode(r5,null,null,r5 || '*' || rownum ||'='|| r5* rownum) e,
       decode(r6,null,null,r6 || '*' || rownum ||'='|| r6* rownum) f,
       decode(r7,null,null,r7 || '*' || rownum ||'='|| r7* rownum) g,
       decode(r8,null,null,r8 || '*' || rownum ||'='|| r8* rownum) h,
       decode(r9,null,null,r9 || '*' || rownum ||'='|| r9* rownum) i
  from (
        
        select 1 r1,
                decode(sign(level - 2), -1, null, 2) r2,
                decode(sign(level - 3), -1, null, 3) r3,
                decode(sign(level - 4), -1, null, 4) r4,
                decode(sign(level - 5), -1, null, 5) r5,
                decode(sign(level - 6), -1, null, 6) r6,
                decode(sign(level - 7), -1, null, 7) r7,
                decode(sign(level - 8), -1, null, 8) r8,
                decode(sign(level - 9), -1, null, 9) r9
          from dual
        connect by level < 10
        )


--方法13:
select  max(case when a < 1 then '' else '1*'+cast(a as varchar)+'='+cast(a*1 as varchar)  end) as [1], 
        max(case when a < 2 then '' else '2*'+cast(a as varchar)+'='+cast(a*2 as varchar)  end) as [2], 
        max(case when a < 3 then '' else '3*'+cast(a as varchar)+'='+cast(a*3 as varchar)  end) as [3], 
        max(case when a < 4 then '' else '4*'+cast(a as varchar)+'='+cast(a*4 as varchar)  end) as [4], 
        max(case when a < 5 then '' else '5*'+cast(a as varchar)+'='+cast(a*5 as varchar)  end) as [5], 
        max(case when a < 6 then '' else '6*'+cast(a as varchar)+'='+cast(a*6 as varchar)  end) as [6], 
        max(case when a < 7 then '' else '7*'+cast(a as varchar)+'='+cast(a*7 as varchar)  end) as [7], 
        max(case when a < 8 then '' else '8*'+cast(a as varchar)+'='+cast(a*8 as varchar)  end) as [8], 
        max(case when a < 9 then '' else '9*'+cast(a as varchar)+'='+cast(a*9 as varchar)  end) as [9]
  from (select rownum as a from dual connect by rownum <= 9)

 

转载于:https://www.cnblogs.com/huangbiquan/p/8232959.html