Scribd
Upload
79 views2 pages

Oracle Cursor Loop Examples

This document discusses different ways to iterate through a cursor in PL/SQL, including using an implicit cursor, a simple loop, a while loop, and a for loop. It provides code examples for each approach. The implicit cursor updates all salaries in a table by 1000. The simple loop fetches each row from a cursor and outputs field values. The while loop and for loop examples both iterate through a cursor and output field values, demonstrating alternative syntaxes for iterating through a cursor in PL/SQL.

Uploaded by

Praneeth Ch
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
79 views2 pages

Oracle Cursor Loop Examples

This document discusses different ways to iterate through a cursor in PL/SQL, including using an implicit cursor, a simple loop, a while loop, and a for loop. It provides code examples for each approach. The implicit cursor updates all salaries in a table by 1000. The simple loop fetches each row from a cursor and outputs field values. The while loop and for loop examples both iterate through a cursor and output field values, demonstrating alternative syntaxes for iterating through a cursor in PL/SQL.

Uploaded by

Praneeth Ch
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Implicit cursor:

DECLARE var_rows number(5);


BEGIN
UPDATE employee
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are
updated');
END IF;
END;
Cursor with a Simple Loop:
1> DECLARE
2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
4> emp_rec emp_cur%rowtype;
5> BEGIN
6> IF NOT sales_cur%ISOPEN THEN
7> OPEN sales_cur;
8> END IF;
9> LOOP
10> FETCH emp_cur INTO emp_rec;
11> EXIT WHEN emp_cur%NOTFOUND;
12> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
13> || ' ' ||emp_cur.salary);
14> END LOOP;
15> END;
16> /


Lets modify the above program to use while loop.
1> DECLARE
2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
4> emp_rec emp_cur%rowtype;
5> BEGIN
6> IF NOT sales_cur%ISOPEN THEN
7> OPEN sales_cur;
8> END IF;
9> FETCH sales_cur INTO sales_rec;
10> WHILE sales_cur%FOUND THEN
11> LOOP
12> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
13> || ' ' ||emp_cur.salary);
15> FETCH sales_cur INTO sales_rec;
16> END LOOP;
17> END;
18> /











General Syntax for using FOR LOOP:
FOR record_name IN cusror_name
LOOP
process the row...
END LOOP;
Lets use the above example to learn how to use for loops in cursors.
1> DECLARE
2> CURSOR emp_cur IS
3> SELECT first_name, last_name, salary FROM emp_tbl;
4> emp_rec emp_cur%rowtype;
5> BEGIN
6> FOR emp_rec in sales_cur
7> LOOP
8> dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
9> || ' ' ||emp_cur.salary);
10> END LOOP;
11>END;
12> /

You might also like