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

http://topic.csdn.net/u/20080719/09/42593213-94fb-40ca-b4dc-5991e2a5615d.html

解答:

用存储过程写一个，没有考虑单个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