products-service

In Oracle, cursors are pointers to the result set of a query. They allow row-by-row processing of query results. Oracle supports the following types of cursors:

1. Implicit Cursor

Automatically created by Oracle when a DML (INSERT, UPDATE, DELETE, SELECT INTO) statement is executed.
No need for explicit declaration.

Example:

BEGIN
   UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
   DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');
END;

Use Case: Simple DML operations where individual row processing is not required.

2. Explicit Cursor

Declared explicitly to handle query result sets one row at a time.
Provides better control over fetching and processing.

Example:

DECLARE
   CURSOR emp_cursor IS SELECT employee_id, first_name FROM employees WHERE department_id = 10;
   v_id employees.employee_id%TYPE;
   v_name employees.first_name%TYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO v_id, v_name;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
   END LOOP;
   CLOSE emp_cursor;
END;

Use Case: When you need to process each row of a query individually.

3. Cursor FOR Loop

Simplifies explicit cursor by automatically handling OPEN, FETCH, and CLOSE.

Example:

BEGIN
   FOR emp_record IN (SELECT employee_id, first_name FROM employees WHERE department_id = 10)
   LOOP
      DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || ' - ' || emp_record.first_name);
   END LOOP;
END;

Use Case: Preferred for readability and simplicity when no complex fetch logic is needed.

4. Parameterized Cursor

Accepts parameters to make cursor queries more dynamic.

Example:

DECLARE
   CURSOR emp_cursor(p_dept_id NUMBER) IS
      SELECT employee_id, first_name FROM employees WHERE department_id = p_dept_id;
BEGIN
   FOR emp_record IN emp_cursor(10)
   LOOP
      DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || ' - ' || emp_record.first_name);
   END LOOP;
END;

Use Case: When you need to reuse the same cursor logic for different inputs.

5. REF Cursor (Cursor Variable)

A pointer to a query result set. Can be passed between procedures/functions.
Strongly typed or Weakly typed.

Example:

DECLARE
   TYPE ref_cursor_type IS REF CURSOR;
   emp_ref_cursor ref_cursor_type;
   v_id employees.employee_id%TYPE;
   v_name employees.first_name%TYPE;
BEGIN
   OPEN emp_ref_cursor FOR SELECT employee_id, first_name FROM employees WHERE department_id = 10;
   LOOP
      FETCH emp_ref_cursor INTO v_id, v_name;
      EXIT WHEN emp_ref_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
   END LOOP;
   CLOSE emp_ref_cursor;
END;

Use Case: Useful in dynamic SQL, and for returning query results from stored procedures/functions.

Would you like a diagram to visualize the types and flow of cursor usage?

Oracle’s implementation of cursors extends the ANSI SQL standards by providing more flexibility and procedural capabilities through PL/SQL. Here’s a breakdown of how Oracle cursors differ from ANSI SQL:

1. PL/SQL-Specific Enhancements

Feature	Oracle Cursor	ANSI SQL
Explicit Cursors	Fully supported in PL/SQL with `DECLARE`, `OPEN`, `FETCH`, `CLOSE`.	Not part of standard SQL; only found in procedural extensions like T-SQL or PL/pgSQL.
Cursor FOR Loop	Oracle simplifies iteration with implicit open/fetch/close using `FOR IN`.	Not defined in ANSI SQL. Requires manual handling.
Parameterized Cursors	Oracle supports passing parameters to cursors.	Not in ANSI SQL.
REF Cursors (Cursor Variables)	Oracle supports both strongly and weakly typed cursor variables (REF CURSOR).	ANSI SQL has no such concept.
%ROWTYPE and %TYPE Attributes	Oracle allows dynamic variable typing using table column/row definitions.	Not part of ANSI SQL.

2. Implicit Cursor Handling

Oracle implicitly creates a cursor (SQL) for every DML or SELECT INTO operation.
You can reference attributes like:
- SQL%ROWCOUNT
- SQL%FOUND
- SQL%NOTFOUND

ANSI SQL doesn’t define a concept of implicit cursors or expose cursor metadata via variables.

3. Cursor Attributes

Oracle provides cursor attributes:
- %ISOPEN, %FOUND, %NOTFOUND, %ROWCOUNT
ANSI SQL has no concept of these attributes — procedural logic must handle states explicitly.

Oracle allows cursor sharing with REF CURSOR, and passing them between procedures.
ANSI SQL lacks built-in support for passing cursors as parameters or variables between code blocks.

Summary

While ANSI SQL defines a basic CURSOR construct (mainly in embedded SQL for C/C++ like SQL/CLI or SQL/PSM), Oracle goes far beyond with its PL/SQL procedural capabilities, making it a more powerful and developer-friendly cursor system.

Would you like a comparison with other databases like PostgreSQL or SQL Server for cursors?

Here’s a comparison of cursors in Oracle, PostgreSQL, and SQL Server against ANSI SQL, highlighting the differences in implementation, syntax, and capabilities:

1. Cursor Basics

Feature	Oracle	PostgreSQL	SQL Server (T-SQL)	ANSI SQL
Basic Cursor Support	Yes (PL/SQL)	Yes (PL/pgSQL)	Yes (T-SQL)	Yes (Embedded SQL)
Explicit Cursor Syntax	`OPEN`, `FETCH`, `CLOSE`	Same	Same	Yes
Implicit Cursors	Yes (`SQL%FOUND`, etc.)	Yes (in loops)	No	No
Cursor Attributes	Yes (`%FOUND`, `%ROWCOUNT`)	Limited (`FOUND`, `NOTFOUND`)	No built-in cursor attributes	No
Cursor FOR Loops	Yes (simplified syntax)	Yes	No	No

2. Advanced Features

Feature	Oracle	PostgreSQL	SQL Server	ANSI SQL
Parameterized Cursors	Yes	No (use `EXECUTE`)	No (use dynamic SQL)	No
Cursor Variables (REF Cursor)	Yes	Yes (cursor variables supported)	No (uses `sp_cursor`)	No
Dynamic Cursors	Via `REF CURSOR`	Via `EXECUTE` with cursors	Yes (`FAST_FORWARD`, `STATIC`, etc.)	No
Cursor as Procedure Param	Yes (IN/OUT parameters)	Yes	Partially (complex)	No
FETCH Orientation	Basic	Yes (forward/backward, scroll)	Yes	Yes (in CLI)

3. Cursor Use Cases & Examples

Oracle (PL/SQL)

DECLARE
  CURSOR emp_cursor IS SELECT emp_name FROM employees;
  name employees.emp_name%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO name;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(name);
  END LOOP;
  CLOSE emp_cursor;
END;

PostgreSQL (PL/pgSQL)

DO $$
DECLARE
  emp_cursor CURSOR FOR SELECT emp_name FROM employees;
  name TEXT;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO name;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE '%', name;
  END LOOP;
  CLOSE emp_cursor;
END $$;

SQL Server (T-SQL)

DECLARE emp_cursor CURSOR FOR
SELECT emp_name FROM employees;

DECLARE @name NVARCHAR(100);

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @name;
  FETCH NEXT FROM emp_cursor INTO @name;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Conclusion

Feature	Oracle	PostgreSQL	SQL Server	ANSI SQL
Best procedural support	Yes	Good	Good	Basic
Cursor variables	Yes	Yes	Limited	No
Rich cursor attributes	Yes	Partial	No	No
Suitable for heavy logic	Yes	Yes	Yes	No

Let me know if you want performance comparisons or cursor alternatives like array fetching, window functions, or set-based approaches in these databases.

In Oracle PL/SQL, when working with cursors, attributes can be implicit or explicit, depending on how the cursor is defined and used.

Let’s break both down with examples 👇

🔸 Implicit Cursor Attributes

Oracle automatically creates an implicit cursor whenever you execute a DML statement (INSERT, UPDATE, DELETE, or SELECT INTO) without explicitly defining a cursor.

✅ Common Implicit Cursor Attributes

Attribute	Description
`SQL%FOUND`	Returns `TRUE` if at least one row was affected.
`SQL%NOTFOUND`	Returns `TRUE` if no rows were affected.
`SQL%ROWCOUNT`	Returns the number of rows affected by the DML statement.
`SQL%ISOPEN`	Always returns `FALSE` for implicit cursors (they close automatically).

🔍 Example

BEGIN
    DELETE FROM employees WHERE department_id = 10;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows deleted.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No rows deleted.');
    END IF;
END;

🔹 Explicit Cursor Attributes

When you explicitly declare a cursor, Oracle gives you similar attributes, but they are prefixed by the cursor name.

✅ Common Explicit Cursor Attributes

Attribute	Description
`cursor_name%FOUND`	Returns `TRUE` if the last fetch returned a row.
`cursor_name%NOTFOUND`	Returns `TRUE` if the last fetch did not return a row.
`cursor_name%ROWCOUNT`	Number of rows fetched so far.
`cursor_name%ISOPEN`	Returns `TRUE` if the cursor is currently open.

🔍 Example

DECLARE
    CURSOR emp_cur IS SELECT employee_id, last_name FROM employees;
    v_id employees.employee_id%TYPE;
    v_name employees.last_name%TYPE;
BEGIN
    OPEN emp_cur;
    
    LOOP
        FETCH emp_cur INTO v_id, v_name;
        EXIT WHEN emp_cur%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_id || ', Name: ' || v_name);
    END LOOP;

    CLOSE emp_cur;
    
    DBMS_OUTPUT.PUT_LINE('Total rows processed: ' || emp_cur%ROWCOUNT);
END;

✅ Summary Table

Attribute Type	Attribute	Scope	Use Case Example
Implicit	`SQL%FOUND`	After DML or SELECT INTO	`IF SQL%FOUND THEN...`
Implicit	`SQL%ROWCOUNT`	After DML	`SQL%ROWCOUNT`
Explicit	`c%ISOPEN`	During cursor operations	`IF c%ISOPEN THEN...`
Explicit	`c%NOTFOUND`	After `FETCH`	`EXIT WHEN c%NOTFOUND`

Let me know if you’d like a demo of cursor FOR loops or parameterized cursors too!

products-service

1. Implicit Cursor

Example:

2. Explicit Cursor

Example:

3. Cursor FOR Loop

Example:

4. Parameterized Cursor

Example:

5. REF Cursor (Cursor Variable)

Example:

1. PL/SQL-Specific Enhancements

2. Implicit Cursor Handling

3. Cursor Attributes

4. Cursor Sharing and Scope

Summary

1. Cursor Basics

2. Advanced Features

3. Cursor Use Cases & Examples

Oracle (PL/SQL)

PostgreSQL (PL/pgSQL)

SQL Server (T-SQL)

Conclusion

🔸 Implicit Cursor Attributes

✅ Common Implicit Cursor Attributes

🔍 Example

🔹 Explicit Cursor Attributes

✅ Common Explicit Cursor Attributes

🔍 Example

✅ Summary Table