oracle儲存過程!解決網友問題

有表T_1(index_1, crop_id, corp_name, start_time, end_time),
需要生成T_2(index_2, corp_id, corp_name, year_time, month_time).
其中index_1和index2由SEQUENCE產生
資料如下：
T_1
index_1, corp_id, corp_name, start_time, end_time
101 1 microsoft 2007-5 2007-7
102 2 ibm 2008-1 2008-2

T_2
index_2, corp_id corp_name year_time month_time
8 1 microsoft 2007 5
9 1 microsoft 2007 6
10 1 microsoft 2007 7
11 2 ibm 2008 1
12 2 ibm 2008 2

解答:

用儲存過程寫一個，沒有考慮單個corp_name跨年的情況!

SQL code

--建立T_1
create table T_1
(
       corp_id  int
       ,corp_name varchar(10)
       ,start_time date
       ,end_time   date
)

--建立T_2
create table T_2
(
       corp_id  int
       ,corp_name varchar(10)
       ,year_time    DATE
       ,month_time   date
)



--插入資料
insert  into T_1 values('1','microsoft',to_date('2007-05','yyyy-mm'),to_date('2007-07','yyyy-mm'))
insert  into T_1 values('2','ibm',to_date('2008-01','yyyy-mm'),to_date('2008-02','yyyy-mm'))


--建立儲存過程
create or replace procedure pro_test_A is
       i INT;
       j INT;
       A01  INT;
       A02  varchar2(10);
       A03  Date;
       A04  Date;
       A05  INT;
       A06  INT;
       tmpA INT;
       tmpB INT;
       CURSOR cur_q IS SELECT *  FROM t_1;
BEGIN  
      OPEN cur_q; 
      LOOP  
      FETCH cur_q INTO A01,A02,A03,A04;
      EXIT   WHEN   cur_q%NOTFOUND;
         tmpA :=MONTH(A03);
         tmpB :=MONTH(A04);
         A06 :=YEAR(A03);
         A05 := tmpB-tmpA;
         j :=tmpA-1;
         FOR i in 0..A05 Loop
              j :=j+1;
              INSERT INTO T_2 values(A01,A02,A06,j);
              COMMIT;
         END Loop;
      END LOOP; 
      CLOSE Cur_q;
END; 

______________________
SQL: exec pro_test_a
______________________
RESULT:
1    2    ibm            2008    1
2    2    ibm            2008    2
3    1    microsoft    2007    5
4    1    microsoft    2007    6
5    1    microsoft    2007    7

[@more@]