Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    45

    Unanswered: processing Cursor in a array loop

    Can some one tell me the best way to do this. I have to process each element of an array through the cursor.

    Example :

    for i=1...10
    {
    delcare cursor c is
    select emp from emptable where empid = i;
    open c;
    LOOP
    fetch c into vEmp;
    exit when c%NOTFOUND;
    dbms_output.put_line(vEmp);
    END LOOP;

    CLOSE c;
    }

    question is do I need to declare and close cursor each time for i = 1 to 10 or basically opening and closing cursor 10times here ? what is best way to handle this?

    thanks in advance.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Why execute the query 10 times when you can instead execute the query once and put the range 1 to 10 into the sql i.e replace the empid=i with empid between 1 and 10.

    This is a very common problem in plsql where people either use loops to execute the query multiple times for a range of values or use multiple nested loops executing different sql when you can combine the different sql statements into one.

    Alan

  3. #3
    Join Date
    Jul 2004
    Posts
    45
    well, actually I was looking to process through each element (diff values) in an array, like select emp from emptable where empid = emp[i]; There is no range in this case but cursor should go through all the entries in the array.

Posting Permissions

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