PL/SQL cursor question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: PL/SQL cursor question

  1. #1
    Join Date
    May 2002
    Posts
    35
    Hi, there

    I have a question regarding PL/SQL programming. Reference the following table:
    Part No. Case No.
    p1 | c1
    p2 | c1
    p3 | c2
    p3 | c2
    I need to find whether there is duplication of part No. under the same case No. In the above case, p3 c2 are duplicated. Now I have a program fetching each record from a cursor in a loop. In the loop I want to write this checking. Is there optimized way to write this?

  2. #2
    Join Date
    May 2002
    Posts
    35
    Anyone has any idea? Please help. Thanks!

  3. #3
    Join Date
    Feb 2001
    Posts
    180
    Hi shenru,
    Here are 2 options.
    Test them first, I just wrote it without:

    declare
    cursor c_check
    is
    select part_no, case_no, count(0) part_cnt
    from the_table
    group by part_no, case_no
    having count(0) > 1
    ;
    begin
    for vc_check in c_check
    loop
    delete from the_table
    where part_no = vc_check.part_no
    and case_no = vc_check.case_no
    and rownum < vc_check.part_cnt
    ;
    end loop;
    end;
    /

    Or without a cursor:

    delete from the_table t1
    where (part_no, case_no) in
    ( select part_no, case_no
    from the_table t2
    where t1.part_no = t2.part_no
    and t1.case_no = t2.case_no
    and t1.rowid > t2.rowid
    )
    ;
    Regards
    Ben de Boer

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    There are more ways ...
    1. You can put the actual values in a variable and test them for the equality in the next iteration
    2. You can write another query that tells you number of occurences for every Part No + Case No and use it for the cursor
    3. As 2. and to join it with the original query

  5. #5
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Thank you very much!
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  6. #6
    Join Date
    May 2002
    Posts
    35
    bensr and ales, thank you very much!

Posting Permissions

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


Click Here to Expand Forum to Full Width