Nolem的部落格

需求:

生成 動態參數 的數列, 需求每10個數字為一筆資料 的呈現方式...

以下範例是pl/sql進階的的一些技巧...

技巧:

1.pipeline

2.native sql

3.native sql out

一般資料:

Column_Value
   1
   2
   3
   4
   5
   7
   8
   9
  10
  11
   .
   .
   .
  99
 100

需要的效果：

  1    2    3    4    5    6    7    8    9   10
 11   12   ..   ..   ..   ..    ..   ..   ..  20
 21   ..   ..   ..   ..   ..    ..   ..   ..  30
 ..   ..   ..   ..   ..   ..    ..   ..   ..  ..
 91   92   93   94   95   96    97   98   99 100  
============================================

範例:

--建立record 10 個數值欄位record type object type

CREATE OR REPLACE
TYPE MY_REC AS OBJECT
(
  COL1 NUMBER,  COL2 NUMBER,  COL3 NUMBER,
  COL4 NUMBER,  COL5 NUMBER,  COL6 NUMBER,
  COL7 NUMBER,  COL8 NUMBER,  COL9 NUMBER,
  COL0 NUMBER
)/

--建立record 集合 object type
CREATE OR REPLACE TYPE ARRAY AS TABLE OF MY_REC /

--建立pipelined function

create or replace function gen_consec_num(m in number default 1,n in number default 1000) 
return array 
PIPELINED 
as 
 cnt number:=0; 

--初始化record
 rec1 my_rec :=my_rec(null,null,null,null,null,null,null,null,null,null);
 recempty my_rec :=my_rec(null,null,null,null,null,null,null,null,null,null);
 vsql varchar2(2000):='declare rec my_rec:=my_rec(null,null,null,null,null,null,null,null,null,null); begin ';
begin 
  cnt :=0;
   for i in m .. n loop
     cnt := cnt+1;

    --組合出動態欄位資訊PL/SQL
    vsql := vsql || 'rec.col' || to_char(mod(i,10)) || ' :=' || to_char(i) || '; ' ;
    if (cnt >=10) or (i = n) then
       vsql := vsql || ' :1 :=rec; end ;' ;
      execute immediate vsql using in out rec1 ;
      --pipe out

       pipe row(rec1);           

      rec1 := recempty; 
       vsql :='declare rec my_rec:=my_rec(null,null,null,null,null,null,null,null,null,null); begin ';
      cnt := 0; 
    end if;
   end loop; 
   return; 
end gen_consec_num; 

=========================================

測試與結果

=================================
select  
  * from table(gen_consec_num(1,100)); 
=====================================
col1 col2 col3 col4 col5 col6 col7 col8 col9 col0 
1 2 3 4 5 6 7 8 9 10 
11 12 13 14 15 16 17 18 19 20 
21 22 23 24 25 26 27 28 29 30 
31 32 33 34 35 36 37 38 39 40 
41 42 43 44 45 46 47 48 49 50 
51 52 53 54 55 56 57 58 59 60 
61 62 63 64 65 66 67 68 69 70 
71 72 73 74 75 76 77 78 79 80 
81 82 83 84 85 86 87 88 89 90 
91 92 93 94 95 96 97 98 99 100