{"id":613,"date":"2026-07-15T05:25:04","date_gmt":"2026-07-15T05:25:04","guid":{"rendered":"https:\/\/mitalgoswami.in\/?p=613"},"modified":"2026-07-23T05:57:37","modified_gmt":"2026-07-23T05:57:37","slug":"cursor-in-oracle-pl-sql","status":"publish","type":"post","link":"https:\/\/mitalgoswami.in\/?p=613","title":{"rendered":"Cursor in Oracle PL\/SQL"},"content":{"rendered":"\n

A Cursor<\/strong> is a pointer to the result set of a SQL query. It allows PL\/SQL to retrieve and process one row at a time<\/strong> from the query result.<\/p>\n\n\n\n

Cursors are mainly used when a query returns multiple rows<\/strong>.<\/p>\n\n\n\n

\n\n\n\n

Types of Cursors<\/h1>\n\n\n\n
    \n
  1. Implicit Cursor<\/strong><\/li>\n\n\n\n
  2. Explicit Cursor<\/strong><\/li>\n<\/ol>\n\n\n\n
    \n\n\n\n

    1. Implicit Cursor<\/h1>\n\n\n\n
      \n
    • Created automatically by Oracle.<\/li>\n\n\n\n
    • Used for SQL statements that return one row<\/strong> or affect rows (INSERT<\/code>, UPDATE<\/code>, DELETE<\/code>, SELECT INTO<\/code>).<\/li>\n<\/ul>\n\n\n\n

      Example<\/h3>\n\n\n\n
      BEGIN\n   UPDATE EMPLOYEE\n   SET SALARY = SALARY + 5000\n   WHERE EMP_ID = 101;\n\n   DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' Row Updated');\nEND;\n\/<\/code><\/pre>\n\n\n\n
      Output<\/strong><\/p>\n\n\n\n
      1 Row Updated<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      2. Explicit Cursor<\/h1>\n\n\n\n
        \n
      • Created by the programmer.<\/li>\n\n\n\n
      • Used when a query returns multiple rows<\/strong>.<\/li>\n\n\n\n
      • The programmer controls opening, fetching, and closing the cursor.<\/li>\n<\/ul>\n\n\n\n
        \n\n\n\n
        Steps to Use an Explicit Cursor<\/h1>\n\n\n\n
          \n
        1. Declare the cursor.<\/li>\n\n\n\n
        2. Open the cursor.<\/li>\n\n\n\n
        3. Fetch rows from the cursor.<\/li>\n\n\n\n
        4. Close the cursor.<\/li>\n<\/ol>\n\n\n\n
          Syntax<\/h3>\n\n\n\n
          DECLARE\n   CURSOR cursor_name IS\n      SELECT column_list\n      FROM table_name;\n\nBEGIN\n   OPEN cursor_name;\n\n   LOOP\n      FETCH cursor_name INTO variables;\n\n      EXIT WHEN cursor_name%NOTFOUND;\n\n      -- Process the row\n   END LOOP;\n\n   CLOSE cursor_name;\nEND;\n\/<\/code><\/pre>\n\n\n\n
          \n\n\n\n
          Example: Explicit Cursor<\/h1>\n\n\n\n
          Suppose the EMPLOYEE<\/code> table contains:<\/p>\n\n\n\n
          EMP_ID<\/th>EMP_NAME<\/th>SALARY<\/th><\/tr><\/thead>
          101<\/td>Amit<\/td>25000<\/td><\/tr>
          102<\/td>Rahul<\/td>30000<\/td><\/tr>
          103<\/td>Neha<\/td>28000<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
          DECLARE\n   CURSOR c_emp IS\n      SELECT EMP_ID, EMP_NAME, SALARY\n      FROM EMPLOYEE;\n\n   v_id EMPLOYEE.EMP_ID%TYPE;\n   v_name EMPLOYEE.EMP_NAME%TYPE;\n   v_salary EMPLOYEE.SALARY%TYPE;\n\nBEGIN\n   OPEN c_emp;\n\n   LOOP\n      FETCH c_emp INTO v_id, v_name, v_salary;\n\n      EXIT WHEN c_emp%NOTFOUND;\n\n      DBMS_OUTPUT.PUT_LINE(\n         v_id || ' ' || v_name || ' ' || v_salary\n      );\n   END LOOP;\n\n   CLOSE c_emp;\nEND;\n\/<\/code><\/pre>\n\n\n\n
          Output<\/strong><\/p>\n\n\n\n
          101 Amit 25000\n102 Rahul 30000\n103 Neha 28000<\/code><\/pre>\n\n\n\n
          \n\n\n\n
          Cursor FOR Loop (Simplest Method)<\/h1>\n\n\n\n
          Oracle automatically opens, fetches, and closes the cursor.<\/p>\n\n\n\n
          DECLARE\n   CURSOR c_emp IS\n      SELECT EMP_ID, EMP_NAME\n      FROM EMPLOYEE;\nBEGIN\n   FOR emp_rec IN c_emp LOOP\n      DBMS_OUTPUT.PUT_LINE(\n         emp_rec.EMP_ID || ' ' || emp_rec.EMP_NAME\n      );\n   END LOOP;\nEND;\n\/<\/code><\/pre>\n\n\n\n
          \n\n\n\n
          Cursor Attributes<\/h1>\n\n\n\n
          Attribute<\/th>Description<\/th><\/tr><\/thead>
          %FOUND<\/code><\/td>Returns TRUE<\/code> if the last fetch returned a row.<\/td><\/tr>
          %NOTFOUND<\/code><\/td>Returns TRUE<\/code> if no row was returned.<\/td><\/tr>
          %ROWCOUNT<\/code><\/td>Returns the number of rows fetched so far.<\/td><\/tr>
          %ISOPEN<\/code><\/td>Returns TRUE<\/code> if the cursor is open.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
          \n\n\n\n
          Example of Cursor Attributes<\/h1>\n\n\n\n
          DECLARE\n   CURSOR c_emp IS\n      SELECT * FROM EMPLOYEE;\n\n   emp_rec EMPLOYEE%ROWTYPE;\nBEGIN\n   OPEN c_emp;\n\n   LOOP\n      FETCH c_emp INTO emp_rec;\n\n      EXIT WHEN c_emp%NOTFOUND;\n\n      DBMS_OUTPUT.PUT_LINE(\n         'Rows fetched: ' || c_emp%ROWCOUNT\n      );\n   END LOOP;\n\n   CLOSE c_emp;\nEND;\n\/<\/code><\/pre>\n\n\n\n
          \n\n\n\n
          Advantages of Cursors<\/h1>\n\n\n\n
            \n
          • Processes records one row at a time.<\/li>\n\n\n\n
          • Handles multiple-row query results.<\/li>\n\n\n\n
          • Gives greater control over data retrieval.<\/li>\n\n\n\n
          • Makes row-by-row processing easy.<\/li>\n\n\n\n
          • Supports complex business logic.<\/li>\n<\/ul>\n\n\n\n
            \n\n\n\n
            Implicit vs Explicit Cursor<\/h1>\n\n\n\n
            Implicit Cursor<\/th>Explicit Cursor<\/th><\/tr><\/thead>
            Created automatically by Oracle<\/td>Created by the programmer<\/td><\/tr>
            Used for single-row queries and DML<\/td>Used for multi-row queries<\/td><\/tr>
            No need to open or close<\/td>Must be opened and closed manually (unless using a FOR<\/code> loop)<\/td><\/tr>
            Easier to use<\/td>More control over processing<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
            \n\n\n\n
            Interview\/Exam Questions<\/h1>\n\n\n\n
            Q1. What is a cursor in PL\/SQL?<\/strong>
            Answer:<\/strong> A cursor is a pointer to the result set of a SQL query that allows row-by-row processing.<\/p>\n\n\n\n
            Q2. What are the two types of cursors?<\/strong>
            Answer:<\/strong> Implicit Cursor and Explicit Cursor.<\/p>\n\n\n\n
            Q3. What are the four steps of an explicit cursor?<\/strong>
            Answer:<\/strong><\/p>\n\n\n\n
              \n
            1. Declare<\/li>\n\n\n\n
            2. Open<\/li>\n\n\n\n
            3. Fetch<\/li>\n\n\n\n
            4. Close<\/li>\n<\/ol>\n\n\n\n
              Q4. Which cursor attribute returns the number of rows fetched?<\/strong>
              Answer:<\/strong> %ROWCOUNT<\/code><\/p>\n\n\n\n
              Q5. Which cursor attribute checks whether a cursor is open?<\/strong>
              Answer:<\/strong> %ISOPEN<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"
              A Cursor is a pointer to the result set of a SQL query. It allows PL\/SQL to retrieve and process one row at a time from the query result. Cursors are mainly used when a query returns multiple rows. Types of Cursors 1. Implicit Cursor Example Output 2. Explicit Cursor Steps to Use an Explicit […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30],"tags":[37],"class_list":["post-613","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t