数据库编程做九九乘法表

SQL写99乘法表

1：

select r1 || '*' || 1 || '=' || r1 * 1 A,

decode(r2, '', '', r2 || '*' || 2 || '=' || r2 * 2) b,

decode(r3, '', '', r3 || '*' || 3 || '=' || r3 * 3) C,

decode(r4, '', '', r4 || '*' || 4 || '=' || r4 * 4) D,

decode(r5, '', '', r5 || '*' || 5 || '=' || r5 * 5) E,

decode(r6, '', '', r6 || '*' || 6 || '=' || r6 * 6) F,

decode(r7, '', '', r7 || '*' || 7 || '=' || r7 * 7) G,

decode(r8, '', '', r8 || '*' || 8 || '=' || r8 * 8) H,

decode(r9, '', '', r9 || '*' || 9 || '=' || r9 * 9) I

from (select level r1,

lag(level+1, 1) over(order by level) r2,

lag(level+2, 2) over(order by level) r3,

lag(level+3, 3) over(order by level) r4,

lag(level+4, 4) over(order by level) r5,

lag(level+5, 5) over(order by level) r6,

lag(level+6, 6) over(order by level) r7,

lag(level+7, 7) over(order by level) r8,

lag(level+8, 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

对于这种既要控制行，也要控制列的显示，采用双层循环是比较理想的，外层循环控制多少行，内存循环控制列的显示。介于性能考虑，列的循环次数不能超过行的数量。

比如第一行，打印1列

    第二行，打印2列

    第三行，打印3列

    依次类推。

列显示为 行位置*列位置的乘积

代码如下：

declare

  v_no int:=9

begin

  for i in 1..(v_no+1)   --控制行（9行）

    loop

      dbms_output.put_line('')  --换行

      for j in 1..i           --控制列

      loop

       dbms_output.put(j||'*'||i||'='||j*i||' ') --最终显示的结果

    end loop

    end loop

end