CODEWITHSUNDEEP
for-loopplsqlcursorrdbms
Arka Mitra
Arka Mitra

Reputation: 21

For loop not printing last record in a cursor

So,I am working with a cursor,the cursor was initially operating with the initial for loop,now i needed to perform operation if the no of records in the cursor is more than one,so i fetched the no of records first and stored in a variable and used an if-condition based on that.Now the problem is when I run the whole process,the procedure does its job,but only for the first record in the cursor and entirely skips the second record.Please suggest or help me identify the mistake. Adding code snippet.

for m in get_m_p(a,b)--main cursor

loop fetch get_m_p into c_m;

exit when g_m_p%notfound;

end loop;

tempcount := g_m_p%ROWCOUNT:

statements---

if(tempcount>1) then

statements----

end if;

end loop;

for the two records the main curosr is returning in first line,operations are only done for the first one,and the second record is being skipped entirely.

Upvotes: 1

Views: 1844

Answers (3)

alvalongo
alvalongo

Reputation: 571

As an alternative you can cache every row and process after.
Example using sample schema "HR" on Oracle 11g Express Edition:

DECLARE
  CURSOR get_m_p
  IS
    SELECT *
    FROM hr.employees
    WHERE department_id = 60
    order by employee_id;
  --
  rcEmp_last  get_m_p%rowtype;
  l_cnt       NUMBER;
BEGIN
  FOR rcM IN get_m_p LOOP
      l_cnt := get_m_p%rowcount;
      Dbms_Output.Put_Line('l_cnt='||l_cnt);
      if l_cnt=1 then
         rcEmp_last:=rcM;
      Else
         Dbms_Output.Put_Line('Process='||to_char(l_cnt-1));
         Dbms_Output.Put_Line('rcEmp_last.employee_id='||rcEmp_last.employee_id);
         --
         rcEmp_last:=rcM;
      END IF;
  End loop;
  --
  Dbms_Output.Put_Line('Exited FOR-LOOP');
  Dbms_Output.Put_Line('l_cnt='||l_cnt);
  --
  if l_cnt>1 then
     Dbms_Output.Put_Line('rcEmp_last.employee_id='||rcEmp_last.employee_id);
  End if;
END;

Output:

Text

PL/SQL block, executed in 1 ms
l_cnt=1
l_cnt=2
Process=1
rcEmp_last.employee_id=103
l_cnt=3
Process=2
rcEmp_last.employee_id=104
l_cnt=4
Process=3
rcEmp_last.employee_id=105
l_cnt=5
Process=4
rcEmp_last.employee_id=106
Exited FOR-LOOP
l_cnt=5
rcEmp_last.employee_id=107
Total execution time 35 ms

Upvotes: 0

Piyali Das
Piyali Das

Reputation: 76

Cursor:

Oracle creates memory area to process SQL statement which is called context area and the cursor is pointer to the context area. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

There are two type of cursor

1. Implicit cursor
2. Explicit cursor

Implicit Cursors :

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed. Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example. Use the SQL%ROWCOUNT attribute to determine the number of rows affected

DECLARE  
   no_of_records number(2); 
   BEGIN 
       select * from records; 
   IF sql%notfound THEN 
      dbms_output.put_line('no records present'); 
   ELSIF sql%found THEN 
      no_of_records := sql%rowcount;
      IF no_of_records > 1 THEN
          dbms_output.put_line('no of records ' || no_of_records); 
      END IF
   END IF;  
END;

Explicit Cursors :

Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

Please see below example:

DECLARE 
   r_id records.id%type; 
   
   CURSOR c_records is 
      SELECT id FROM records; 
BEGIN 
   OPEN c_records; 
   LOOP 
   FETCH c_records into r_id; 
      EXIT WHEN c_records%notfound; 
      dbms_output.put_line('Record id ' || r_id ); 
   END LOOP; 
   CLOSE c_records; 
END;

Reference : https://www.tutorialspoint.com/plsql/plsql_cursors.htm

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143053

This is a superfluous line:

fetch get_m_p into c_m;

You don't explicitly fetch within a cursor FOR loop, it is implicitly done in each loop iteration. Remove that line.

How to get number of rows returned by a cursor? Lucky you, it seems that you don't care whether how many rows - exactly - it returns. All you want to know is whether it returned more than 1 row. So, count them, literally; exit the loop if counter exceeds 1.

SQL> DECLARE
  2     CURSOR get_m_p IS
  3        SELECT *
  4          FROM emp
  5         WHERE deptno = 10;
  6
  7     l_cnt  NUMBER := 0;
  8  BEGIN
  9     FOR m IN get_m_p
 10     LOOP
 11        l_cnt := l_cnt + 1;
 12        EXIT WHEN l_cnt > 1;
 13     END LOOP;
 14
 15     DBMS_OUTPUT.put_line ('Cursor returned at least ' || l_cnt || ' row(s)');
 16
 17     IF l_cnt > 1
 18     THEN
 19        NULL;
 20     -- the rest of statements go here
 21     END IF;
 22  END;
 23  /
Cursor returned at least 2 row(s)

PL/SQL procedure successfully completed.

SQL>

As there's no way to know how many rows will cursor return, unfortunately, you'll have to check that first, and then decide what to do with the result.

DECLARE
   CURSOR get_m_p IS
      SELECT *
        FROM emp
       WHERE deptno = 10;

   l_cnt  NUMBER := 0;
BEGIN
   SELECT COUNT (*)
     INTO l_cnt
     FROM (-- this is cursor's SELECT statement
           SELECT *
             FROM emp
            WHERE deptno = 10);

   FOR m IN get_m_p
   LOOP
      -- some statements here

      IF l_cnt > 1
      THEN
         NULL;
         -- statements to be executed if cursor return more than 1 row
      END IF;
   END LOOP;
END;
/

Upvotes: 1

Related Questions