{"id":2021,"date":"2025-09-20T20:57:01","date_gmt":"2025-09-21T00:57:01","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=2021"},"modified":"2025-09-18T21:00:33","modified_gmt":"2025-09-19T01:00:33","slug":"understanding-oracle-cursors-in-pl-sql","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/understanding-oracle-cursors-in-pl-sql\/","title":{"rendered":"Understanding Oracle Cursors in PL\/SQL"},"content":{"rendered":"\n

In Oracle PL\/SQL, cursors<\/strong> are pointers that allow you to work with the result set of a SQL query. They act as handles for fetching rows returned by a SELECT statement. Cursors are essential when you need to process query results row by row, perform complex logic, or reuse queries dynamically.<\/p>\n\n\n\n

Implicit Cursors<\/strong><\/h2>\n\n\n\n

Oracle automatically creates an implicit cursor<\/strong> every time you run a DML<\/strong> statement (INSERT<\/code>, UPDATE<\/code>, DELETE<\/code>) or a SELECT \u2026 INTO<\/code> query. You don\u2019t need to declare or open it manually\u2014Oracle does all the work behind the scenes.<\/p>\n\n\n\n

Example: Implicit Cursor with SELECT INTO<\/strong><\/h3>\n\n\n\n
DECLARE\n    v_first_name employees.first_name%TYPE;\nBEGIN\n    SELECT first_name INTO v_first_name\n    FROM employees\n    WHERE employee_id = 100;\n\n    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_first_name);\n\n    -- Access implicit cursor attributes\n    DBMS_OUTPUT.PUT_LINE('Rows Processed: ' || SQL%ROWCOUNT);\n    IF SQL%FOUND THEN\n        DBMS_OUTPUT.PUT_LINE('Record found.');\n    END IF;\nEND;\n<\/code><\/pre>\n\n\n\n
Key Points:<\/strong><\/p>\n\n\n\n
    \n
  • SQL%FOUND<\/code>, SQL%NOTFOUND<\/code>, and SQL%ROWCOUNT<\/code> automatically refer to the last executed SQL statement<\/strong>.<\/li>\n\n\n\n
  • Best for single-row queries or simple DML operations<\/strong>.<\/li>\n<\/ul>\n\n\n\n
    Explicit Cursors<\/strong><\/h2>\n\n\n\n
    An explicit cursor<\/strong> is one you declare, open, fetch from, and close manually. Use these when:<\/p>\n\n\n\n
      \n
    • You expect multiple rows<\/strong>.<\/li>\n\n\n\n
    • You need more control over fetching and processing data.<\/li>\n<\/ul>\n\n\n\n
      Steps for Using Explicit Cursors<\/strong><\/h3>\n\n\n\n
        \n
      1. Declare<\/strong> the cursor with a SELECT<\/code> statement.<\/li>\n\n\n\n
      2. Open<\/strong> the cursor.<\/li>\n\n\n\n
      3. Fetch<\/strong> rows into variables.<\/li>\n\n\n\n
      4. Close<\/strong> the cursor.<\/li>\n<\/ol>\n\n\n\n
        Example: Explicit Cursor<\/strong><\/h3>\n\n\n\n
        DECLARE\n    CURSOR emp_cursor IS\n        SELECT employee_id, first_name, salary\n        FROM employees\n        WHERE department_id = 60;\n    v_id    employees.employee_id%TYPE;\n    v_name  employees.first_name%TYPE;\n    v_sal   employees.salary%TYPE;\nBEGIN\n    OPEN emp_cursor;\n    LOOP\n        FETCH emp_cursor INTO v_id, v_name, v_sal;\n        EXIT WHEN emp_cursor%NOTFOUND;\n        DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name || ' - ' || v_sal);\n    END LOOP;\n    CLOSE emp_cursor;\nEND;\n<\/code><\/pre>\n\n\n\n
        Cursor FOR Loop<\/strong><\/h2>\n\n\n\n
        The Cursor FOR loop<\/strong> simplifies explicit cursor handling by automatically opening, fetching, and closing the cursor. It reduces boilerplate code.<\/p>\n\n\n\n
        Example: Cursor FOR Loop<\/strong><\/h3>\n\n\n\n
        BEGIN\n    FOR rec IN (SELECT employee_id, first_name, salary\n                FROM employees\n                WHERE department_id = 90) LOOP\n        DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' ||\n                             rec.first_name  || ' - ' ||\n                             rec.salary);\n    END LOOP;\nEND;\n<\/code><\/pre>\n\n\n\n
        Advantages:<\/strong><\/p>\n\n\n\n
          \n
        • No need for explicit OPEN<\/code>, FETCH<\/code>, or CLOSE<\/code>.<\/li>\n\n\n\n
        • Automatically uses an implicit cursor for the loop.<\/li>\n<\/ul>\n\n\n\n
          Cursor Attributes<\/strong><\/h2>\n\n\n\n
          Oracle provides special attributes to check the cursor\u2019s status or the result of the last fetch:<\/p>\n\n\n\n
          Attribute<\/th>Description<\/th><\/tr><\/thead>
          %FOUND<\/code><\/td>TRUE<\/code> if the last fetch returned a row, otherwise FALSE<\/code>.<\/td><\/tr>
          %NOTFOUND<\/code><\/td>TRUE<\/code> if the last fetch returned no<\/strong> row.<\/td><\/tr>
          %ROWCOUNT<\/code><\/td>Returns the number of rows fetched or affected so far.<\/td><\/tr>
          %ISOPEN<\/code><\/td>TRUE<\/code> if the cursor is open.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
          Example Using Attributes<\/strong><\/h3>\n\n\n\n
          DECLARE\n    CURSOR c IS SELECT employee_id FROM employees WHERE ROWNUM <= 5;\n    v_id employees.employee_id%TYPE;\nBEGIN\n    OPEN c;\n    LOOP\n        FETCH c INTO v_id;\n        EXIT WHEN c%NOTFOUND;\n        DBMS_OUTPUT.PUT_LINE('Fetched ID: ' || v_id);\n    END LOOP;\n\n    DBMS_OUTPUT.PUT_LINE('Rows Fetched: ' || c%ROWCOUNT);\n\n    IF c%ISOPEN THEN\n        CLOSE c;\n    END IF;\nEND;\n<\/code><\/pre>\n\n\n\n
          Parameterized Cursors<\/strong><\/h2>\n\n\n\n
          Parameterized cursors<\/strong> accept values at runtime, making them reusable for different queries. They\u2019re useful when the WHERE clause changes depending on input.<\/p>\n\n\n\n
          Example: Parameterized Cursor<\/strong><\/h3>\n\n\n\n
          DECLARE\n    CURSOR emp_by_dept(p_dept_id NUMBER) IS\n        SELECT employee_id, first_name, salary\n        FROM employees\n        WHERE department_id = p_dept_id;\nBEGIN\n    FOR rec IN emp_by_dept(50) LOOP\n        DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' || rec.first_name);\n    END LOOP;\n\n    FOR rec IN emp_by_dept(90) LOOP\n        DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' || rec.first_name);\n    END LOOP;\nEND;\n<\/code><\/pre>\n\n\n\n
          Key Point:<\/strong>
          The cursor behaves like a function that takes parameters.<\/p>\n\n\n\n
          REF CURSOR (Dynamic Cursors)<\/strong><\/h2>\n\n\n\n
          A REF CURSOR<\/strong> (or cursor variable<\/strong>) is a pointer to a result set that can be opened dynamically at runtime. It\u2019s widely used for dynamic SQL<\/strong>, procedures returning datasets<\/strong>, or passing results between programs<\/strong>.<\/p>\n\n\n\n
          Example: REF CURSOR<\/strong><\/h3>\n\n\n\n
          DECLARE\n    TYPE ref_cursor_type IS REF CURSOR;\n    v_cursor ref_cursor_type;\n    v_id     employees.employee_id%TYPE;\n    v_name   employees.first_name%TYPE;\nBEGIN\n    OPEN v_cursor FOR\n        SELECT employee_id, first_name\n        FROM employees\n        WHERE salary > 10000;\n\n    LOOP\n        FETCH v_cursor INTO v_id, v_name;\n        EXIT WHEN v_cursor%NOTFOUND;\n        DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);\n    END LOOP;\n\n    CLOSE v_cursor;\nEND;\n<\/code><\/pre>\n\n\n\n
          Real-World Use:<\/strong><\/p>\n\n\n\n
            \n
          • Returning a result set from a stored procedure<\/strong> to a calling application: CREATE OR REPLACE PROCEDURE get_high_salary(p_salary IN NUMBER, p_out OUT SYS_REFCURSOR) AS BEGIN OPEN p_out FOR SELECT employee_id, first_name, salary FROM employees WHERE salary > p_salary; END;<\/code><\/li>\n<\/ul>\n\n\n\n
            Best Practices for Using Cursors<\/strong><\/h2>\n\n\n\n
              \n
            1. Use implicit cursors<\/strong> for single-row queries or simple DML.<\/li>\n\n\n\n
            2. Prefer Cursor FOR loops<\/strong> when possible\u2014they are cleaner and less error-prone.<\/li>\n\n\n\n
            3. Always close explicit and REF cursors<\/strong> to free memory.<\/li>\n\n\n\n
            4. Use parameterized cursors<\/strong> to avoid repetitive code for similar queries.<\/li>\n\n\n\n
            5. Use REF CURSOR<\/strong> for flexible, dynamic operations and client-facing procedures.<\/li>\n\n\n\n
            6. Avoid unnecessary cursors<\/strong>\u2014bulk operations or set-based SQL are usually faster.<\/li>\n<\/ol>\n\n\n\n
              Summary Table<\/strong><\/h2>\n\n\n\n
              Cursor Type<\/th>When to Use<\/th>Example Use Case<\/th><\/tr><\/thead>
              Implicit<\/strong><\/td>Simple queries or DML<\/td>Logging rows updated in an UPDATE statement<\/td><\/tr>
              Explicit<\/strong><\/td>Multi-row result sets requiring manual control<\/td>Processing payroll records row by row<\/td><\/tr>
              Cursor FOR Loop<\/strong><\/td>Multi-row sets without manual open\/fetch\/close overhead<\/td>Generating department-wise salary reports<\/td><\/tr>
              Attributes<\/strong><\/td>To check cursor state or progress<\/td>Counting rows fetched so far<\/td><\/tr>
              Parameterized<\/strong><\/td>Reusable cursors with variable input<\/td>Retrieving employees by department<\/td><\/tr>
              REF CURSOR<\/strong><\/td>Dynamic SQL or returning results to applications<\/td>Building a reporting API in PL\/SQL<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
              Final Thoughts<\/strong><\/h3>\n\n\n\n
              Cursors are fundamental to PL\/SQL programming. They give you row-by-row control when set-based operations aren\u2019t enough. Start with implicit cursors<\/strong> for simplicity, move to explicit or FOR loop cursors<\/strong> for multi-row queries, and adopt REF CURSORs<\/strong> for advanced, dynamic requirements. Using cursor attributes and parameters properly ensures flexible, efficient, and maintainable PL\/SQL code.<\/p>\n","protected":false},"excerpt":{"rendered":"
              In Oracle PL\/SQL, cursors are pointers that allow you to work with the result set of a SQL query. They act as handles for fetching rows returned by a SELECT statement. Cursors are essential when you need to process query results row by row, perform complex logic, or reuse queries dynamically. Implicit Cursors Oracle automatically […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,515],"tags":[],"class_list":["post-2021","post","type-post","status-publish","format-standard","hentry","category-database","category-oracle"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/2021","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/comments?post=2021"}],"version-history":[{"count":2,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/2021\/revisions"}],"predecessor-version":[{"id":2024,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/2021\/revisions\/2024"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=2021"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=2021"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=2021"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}