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.