Results 1 to 4 of 4

Thread: Cursor Variable

  1. #1
    Join Date
    Oct 2004
    Posts
    9

    Unanswered: Cursor Variable

    I need to get one record set of specific orders and then from that record set I need to query a second set collecting specific dated from another table for the orders in the first query then I need to update another table with the information.


    Here is the query I have sofar.
    Please correct me ad im REALLY rusty, its been over 4 years sence ive worked with pl/sql

    DECLARE
    CURSOR C1 IS
    SELECT ORDER_NO
    FROM SHIP.SHIP_PACKAGE
    WHERE IS_SHIPPED=1;
    O_ID SHIP.SHIP_PACKAGE.ORDER_NO%TYPE;
    C1_REC C1%ROWTYPE;

    CURSOR C2 IS
    SELECT EDITED_ON
    FROM A.ORDER_UPDATE
    WHERE ORDER_NO=C1_O_ID AND OSTATUS_OLD='R' AND OSTATUS_NEW='W';
    E_DATE A.ORDER_UPDATE.EDIT_ON
    BEGIN

    FOR C1_REC IN C1 LOOP
    UPDATE A.SHIPLOG SET SHIP_DATE= C2_E_DATE, PRINT_DATE = C2_E_DATE WHERE ORDER_NO=C1_o_id)
    END LOOP;
    END

    Thanks

    jepierce the neophite

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    combine the two cursors together
    PHP Code:
    DECLARE

    CURSOR C2 IS
        select a
    .edited_ona.order_no
            from a
    .order_update aship.ship_package b
            where   a
    .order_no    b.order_no and 
                    
    b.is_shipped  1          and
                    
    a.ostatus_old 'R'        and 
                    
    a.ostatus_new 'W';

    BEGIN

        
    FOR vC2 IN C2 LOOP
        
            UPDATE A
    .SHIPLOG 
              SET SHIP_DATE  
    vC2.edited_on
                  
    PRINT_DATE vC2.edited_on 
            WHERE ORDER_NO 
    vC2.order_no;
        
        
    END LOOP;
    END
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Better if you can do it in one sql statement!
    Code:
    update
      ( select a.edited_on edited_on, c.ship_date ship_date, c.print_date print_date
          from a.order_update a, ship.ship_package b, a.shiplog c
         where a.order_no = b.order_no
           and b.order_no = c.order_no
           and b.is_shipped = 1
           and a.ostatus_old = 'R'
           and a.ostatus_new  = 'W' )
       set ship_date  = edited_on,
           print_date = edited_on
    or..
    Code:
    update a.shiplog s
       set ( ship_date, print_date ) = ( select a.edited_on, a.edited_on
                                           from a.order_update a, ship.ship_package b
                                          where a.order_no = b.order_no
                                            and b.is_shipped = 1
                                            and a.ostatus_old = 'R'
                                            and a.ostatus_new = 'W'
                                            and a.order_no = s.order_no )
     where exists ( select null
                      from a.order_update a, ship.ship_package b
                     where a.order_no = b.order_no
                       and b.is_shipped = 1 
                       and a.ostatus_old = 'R'
                       and a.ostatus_new = 'W'
                       and a.order_no = s.order_no )

  4. #4
    Join Date
    Oct 2004
    Posts
    9
    LOL thats what I started thinking about

    Thanks for the help

    Final query

    _____________________________

    DECLARE
    CURSOR C1 IS
    SELECT S.ORDER_NO, O.EDITED_ON
    FROM SHIP.SHIP_PACKAGE S, A.ORDER_UPDATE O
    WHERE
    S.IS_SHIPPED=1 and O.ORDER_NO = S.ORDER_NO AND
    O.OSTATUS_OLD='R' AND
    O.OSTATUS_NEW='W';
    C1_REC C1%ROWTYPE;
    V_CNT PLS_INTEGER :=0;
    BEGIN
    FOR C1_REC IN C1 LOOP
    UPDATE A.SHIPLOG SET SHIP_DATE= C1_REC.EDITED_ON , PRINT_DATE = C1_REC.EDITED_ON
    WHERE ORDER_NO= C1_REC.ORDER_NO;
    V_CNT := V_CNT + 1;
    IF V_CNT >= 100 THEN
    V_CNT := 0
    COMMIT;
    END IF;
    END LOOP;
    COMMIT;
    END;
    /
    _________________

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •