. SQL . SQL Cursor Oracle

SQL Cursor Oracle

SQL Cursor Processing with Oracle PL/SQL

Q: How can I retrieve data one row at a time with Oracle PL/SQL?

To retrieve data with SQL one row at a time you need to use cursor processing.
Not all relational databases support this, but many do. Here I show this in Oracle with PL/SQL, which is Procedural Language SQL.
Cursor processing is done in several steps:
1. Define the rows you want to retrieve. This is called declaring the cursor.
2. Open the cursor. This activates the cursor and loads the data. Note that declaring the cursor doesn't load data, opening the cursor does.
3. Fetch the data into variables.
4. Close the cursor.

Step 1 - declare variables and the cursor

   
 DECLARE                                    
   v_film_title            film_table.film_title%TYPE;
   v_american_pie_count    BINARY_INTEGER:=0;
   
 CURSOR c_american_pie IS
   SELECT Film_Title
     FROM Film_Table
    WHERE Film_Title like "American Pie%";

Note: Here we declare this cursor to include just the film title column, but one or more rows in one or more tables can be included.

Step 2 - open the cursor

   
 Open c_american_pie;

Note: Not much seems to happen in step 2. Quite a bit really does happen behind the scenes, as the cursor c_american_pie is loaded with data in step 2.

Step 3 - fetch the data into a PL/SQL variable

   
  LOOP    
      FETCH c_american_pie INTO v_film_title; 
      EXIT WHEN c_american_pie%NOT_FOUND;   
      --goes to END LOOP when cursor is empty--
         --Here we just add one to the counter,--
         --but we could do something with v_film_title--
         v_american_pie_count := v_american_pie_count + 1;
   END LOOP;

Variable v_film_title will equal "American Pie"

Step 4 - close the cursor

   
 CLOSE c_american_pie;

Comments Comments are left by visitors to FluffyCat.com, are not endorsed by FluffyCat.com, and may or may not be accurate.

Comment by Harsha Rate this Comment

Nice work!

to add your own comment