ORACLE: Looping through data using CURSORS

Below the syntax for creating the cursors in Oracle. This example also demostrates creating of procedures in Oracle


CREATE OR REPLACE PROCEDURE MYSCHEMA.GET_EMAILS AS

 EMAIL_ID VARCHAR2(100);
 
 CURSOR CUR IS SELECT EMAIL_ADDRESS FROM MYSCHEMA.USERS; /* DEFINE CURSOR */
 
BEGIN
 
 OPEN CUR; /* OPEN THE CURSOR */
 
 LOOP /* LOOP */
 
 FETCH CUR INTO EMAIL_ID; /* READ THE CURSOR VALUE */
 
 EXIT WHEN CUR%NOTFOUND; /* EXIT WHEN DATA NOT FOUND */
 
 DBMS_OUTPUT.PUT_LINE(EMAIL_ID); /* PRINT THE VALUE / PROCESS ACCORDING TO YOUR NEED */
 
 
 END LOOP; /* END OF LOOP */
 
 CLOSE CUR; /* FINALLY RELASE/CLOSE THE CURSOR */
END;
/

Happy Kooding.. Hope this helps!

Advertisements