Oracle - Use FETCH Statement in a Loop in Oracle

Use FETCH Statement in a Loop in Oracle

Q

How To Use FETCH Statement in a Loop in Oracle?

✍: FYIcenter.com

A

If 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 FYI_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

⇒Working with Cursors in Oracle PL/SQL

⇒⇒Oracle Database Tutorials

2018-04-07, 633👍, 0💬

Retrieve Data from an Explicit Cursor in Oracle
How To Retrieve Data from an Explicit Cursor in Oracle? If you have a cursor opened ready to use, you can use the FETCH ... INTO statement to retrieve data from the cursor into variables. FETCH statement will: Retrieve all the fields from the row pointed by the current cursor pointer and assign them... 2018-07-22, 658👍, 0💬

Retrieve Data from a Cursor to a RECORD in Oracle
How To Retrieve Data from a Cursor to a RECORD in Oracle? If you have a cursor opened ready to use, you can also use the FETCH statement to retrieve data from the cursor into a RECORD variable as shown in the tutorial exercise below: CREATE OR REPLACE PROCEDURE FYI_CENTER AS CURSOR t_list IS SELECT ... 2018-07-22, 650👍, 0💬

Open Multiple Cursors at the Same Time in Oracle
Can Multiple Cursors Being Opened at the Same Time in Oracle? Yes, multiple cursors can be opened at the same time. See the following example: CREATE OR REPLACE PROCEDURE FYI_CENTER AS CURSOR emp_cur IS SELECT * FROM employees; emp_rec employees%ROWTYPE; CURSOR dpt_cur IS SELECT * FROM departments; ... 2018-04-07, 635👍, 0💬

Use FETCH Statement in a Loop in Oracle
How To Use FETCH Statement in a Loop in Oracle? If 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 sc... 2018-04-07, 634👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.