How To Use FETCH Statement in a Loop?
Submitted by: AdministratorIf you have a cursor opened ready to use, you can also use the FETCH statement in a loop to retrieve data from the cursor more efficiently. But you need to remember to use an EXIT statement break the loop when the cursor pointer reaches the end. The script below gives you a good example:
CREATE OR REPLACE PROCEDURE ggl_CENTER AS
CURSOR emp_cur IS SELECT * FROM employees
WHERE manager_id = 101;
emp_rec employees%ROWTYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name = ' ||
emp_rec.first_name || ' ' || emp_rec.last_name);
END LOOP;
CLOSE emp_cur;
END;
/
Name = Nancy Greenberg
Name = Jennifer Whalen
Name = Susan Mavris
Name = Hermann Baer
Name = Shelley Higgins
Submitted by: Administrator
CREATE OR REPLACE PROCEDURE ggl_CENTER AS
CURSOR emp_cur IS SELECT * FROM employees
WHERE manager_id = 101;
emp_rec employees%ROWTYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name = ' ||
emp_rec.first_name || ' ' || emp_rec.last_name);
END LOOP;
CLOSE emp_cur;
END;
/
Name = Nancy Greenberg
Name = Jennifer Whalen
Name = Susan Mavris
Name = Hermann Baer
Name = Shelley Higgins
Submitted by: Administrator
Read Online Oracle Database Job Interview Questions And Answers
Top Oracle Database Questions
☺ | How To Write Date and Time Interval Literals in Oracle? |
☺ | What Happens to Indexes If You Drop a Table? |
☺ | What Is Input Buffer in SQL*Plus? |
☺ | What Happens If You Lost a Data File? |
☺ | How To Recover a Dropped Table in Oracle? |
Top DB Oracle Categories
☺ | Oracle PL-SQL Interview Questions. |
☺ | Oracle DBA Interview Questions. |
☺ | Oracle D2K Interview Questions. |
☺ | OCI Interview Questions. |
☺ | Oracle RMAN Interview Questions. |