How do you fetch rows from an open cursor into local variables?
RoseHrs
Posted by RoseHrs
Last Updated: July 04, 2024
cursor fetch
To fetch rows from an open cursor into local variables in SQL, you typically use the FETCH statement in conjunction with the cursor you have declared. The exact syntax can vary slightly depending on the database system you are using (such as Oracle, SQL Server, PostgreSQL, etc.), but the general concept is similar. Below are examples of how this is done in some common databases.
Example in PL/SQL (Oracle)
1. Declare a cursor: 
DECLARE
       CURSOR my_cursor IS
           SELECT column1, column2 FROM my_table;
       var_column1 my_table.column1%TYPE;
       var_column2 my_table.column2%TYPE;
   BEGIN
       OPEN my_cursor;

       LOOP
           FETCH my_cursor INTO var_column1, var_column2;
           EXIT WHEN my_cursor%NOTFOUND;
           
           -- You can process your data here
           DBMS_OUTPUT.PUT_LINE('Column1: ' || var_column1 || ' Column2: ' || var_column2);
       END LOOP;

       CLOSE my_cursor;
   END;
Example in T-SQL (SQL Server)
1. Declare a cursor: 
DECLARE @var_column1 INT,
           @var_column2 VARCHAR(100);
   DECLARE my_cursor CURSOR FOR
       SELECT column1, column2 FROM my_table;

   OPEN my_cursor;

   FETCH NEXT FROM my_cursor INTO @var_column1, @var_column2;

   WHILE @@FETCH_STATUS = 0
   BEGIN
       -- Process data here
       PRINT 'Column1: ' + CAST(@var_column1 AS VARCHAR(10)) + ' Column2: ' + @var_column2;

       FETCH NEXT FROM my_cursor INTO @var_column1, @var_column2;
   END;

   CLOSE my_cursor;
   DEALLOCATE my_cursor;
Example in PL/pgSQL (PostgreSQL)
1. Declare a cursor: 
DO $$
   DECLARE
       my_cursor CURSOR FOR SELECT column1, column2 FROM my_table;
       var_column1 INTEGER;
       var_column2 TEXT;
   BEGIN
       OPEN my_cursor;

       LOOP
           FETCH my_cursor INTO var_column1, var_column2;
           EXIT WHEN NOT FOUND;

           -- You can process your data here
           RAISE NOTICE 'Column1: %, Column2: %', var_column1, var_column2;
       END LOOP;

       CLOSE my_cursor;
   END $$;
Key Points
- Declare the cursor: You define the SQL query you want to execute. - Open the cursor: This makes the cursor active and allows it to fetch data. - Fetch rows: Use the FETCH command to retrieve the data into local variables. - Check for completion: Use a condition to check if there is no more data to fetch (like NOT FOUND in PostgreSQL or %NOTFOUND in PL/SQL). - Close the cursor: Always ensure to close the cursor after you are done to free up resources. Ensure you modify the data types and names according to your actual table and schema.
Default Image