Cursor

In most of the case bulk inserts and bulk update works fine but if we need to process the data record by record then we need to use cursors.

 

Oracle

 

            DECLARE V_COL1 VARCHAR2 (100); V_COL2 NUMBER;

            --declare the cursor

CURSOR MYCURSOR IS

--replace this select script with your query

SELECT COL1, COL2

FROM (

                        SELECT CAST ('OUTPUT SAM1' AS VARCHAR (100)) AS COL1, CAST (1 AS NUMBER) AS COL2 FROM DUAL

                        UNION ALL

                        SELECT CAST ('OUTPUT SAM2' AS VARCHAR (100)) AS COL1, CAST (2 AS NUMBER) AS COL2 FROM DUAL

            ) SAM_TEMP;

--we can open cursor only in begin and end blocks

BEGIN

OPEN MYCURSOR;

--loop through the data until not found

LOOP

FETCH MYCURSOR INTO V_COL1, V_COL2;

EXIT WHEN MYCURSOR%NOTFOUND;

--write the actual operations here

DBMS_OUTPUT.PUT_LINE (V_COL1);

END LOOP;

--close the cursor

CLOSE MYCURSOR;

END;

 

Another way to loop through cursor is using for loop

 

            DECLARE V_COL1 VARCHAR2 (100); V_COL2 NUMBER;

--declare the cursor

CURSOR MYCURSOR IS

--replace this select script with your query

SELECT COL1, COL2

FROM (

                        SELECT CAST ('OUTPUT SAM1' AS VARCHAR (100)) AS COL1, CAST (1 AS NUMBER) AS COL2 FROM DUAL

                        UNION ALL

                        SELECT CAST ('OUTPUT SAM2' AS VARCHAR (100)) AS COL1, CAST (2 AS NUMBER) AS COL2 FROM DUAL

            ) SAM_TEMP;

--we can open cursor only in begin and end blocks

BEGIN

--loop through the data for all the records

FOR X IN MYCURSOR LOOP

V_COL1:=X.COL1;

DBMS_OUTPUT.PUT_LINE (V_COL1);

END LOOP;

CLOSE MYCURSOR;

END;

 

SQL Server

 

DECLARE @V_COL1 VARCHAR (100), @V_COL2 INT

--declare the cursor

DECLARE MYCURSOR CURSOR FOR

--replace this select script with your query

SELECT *

FROM (

                        SELECT CAST ('OUTPUT SAM1' AS VARCHAR (100)) AS COL1, CAST (1 AS INT) AS COL2

                        UNION ALL

                        SELECT CAST ('OUTPUT SAM2' AS VARCHAR (100)) AS COL1, CAST (2 AS INT) AS COL2

            ) SAM_TEMP

--open cursor

OPEN MYCURSOR

--fetch the first record

FETCH NEXT FROM MYCURSOR INTO @V_COL1, @V_COL2

            --write a while loop to loop until no data found

WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT @V_COL1+CAST (@V_COL2 AS VARCHAR (10))

FETCH NEXT FROM MYCURSOR INTO @V_COL1, @V_COL2

END

CLOSE MYCURSOR

DEALLOCATE MYCURSOR

 

If you are looking to access reference cursor then click here

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.