Tuesday, November 8, 2022

WHAT IS A CURSOR?

 There are two types of cursors:

1. Explicit: these cursors are declared directly as a sys_refcursor by the user and have a name.


2. Implicit: these cursors are created each time the user runs a select statement.

PROPERTIES

%FOUND

Returns true if the user executes an Insert, Update or Delete statement and affects one or more rows or a value could be stored in a variable using a Select Into statement. Otherwise, it returns False.

%NOTFOUND

This is precisely the opposite as %FOUND. It will return True if NO rows were affected or a value was not found in a select statement. Otherwise, it returns False.

%ISOPEN

This returns True if the cursor is still open. On implicit cursor, this is always False.

%ROWCOUNT

This returns the number of rows fetched from the cursor. However, if the cursor has yet to be opened, this will return an invalid cursor error. 

IMPLICIT CURSORS

We can run these using select or update statements. 

Let's say we want to increase the price of some products by $25.00 in the table products. To do this, we will have to do the following,


EXPLICIT CURSOR

These need to be declared. If they are declared in the procedure signature, it will look like the following,


Then, we need to fill out these cursors so we can send the data somewhere where it's needed.


There can also be cursors that are declared inside a procedure or function. 




Another explanation,

In Oracle PL/SQL, a cursor is a database object that enables you to retrieve data from a result set, one row at a time. Cursors are particularly useful when dealing with complex queries that return multiple rows of data. There are two types of cursors: implicit cursors and explicit cursors. I'll explain how to use explicit cursors, which provide more control over the result set.

Here's a step-by-step guide on how to use explicit cursors in Oracle PL/SQL:

  1. Declare the Cursor:

    Declare an explicit cursor using the CURSOR keyword. You define the SQL query that retrieves the data you want.

    sql
    DECLARE
    CURSOR cursor_name IS
        SELECT column1, column2
        FROM your_table
        WHERE your_conditions;


  3. Open the Cursor:

    After declaring the cursor, you must open it to start fetching rows.

    sql
    OPEN cursor_name;


  5. Fetch Data:

    Use a loop to fetch data from the cursor one row at a time. The FETCH statement retrieves the next row into specified variables.

    sql
    LOOP
    FETCH cursor_name INTO variable1, variable2;
    EXIT WHEN cursor_name%NOTFOUND; -- Exit the loop when no more rows are left
    
    -- Process the fetched data
END LOOP;


  7. Close the Cursor:

    After fetching all the data, close the cursor to release resources.

    sql
    CLOSE cursor_name;

Putting it all together, here's an example that retrieves employee names and salaries from an employees table:

sql
DECLARE 
CURSOR emp_cursor IS 
    SELECT first_name, salary 
    FROM employees 
    WHERE department_id = 30; 
 emp_first_name employees.first_name%TYPE; 
 emp_salary employees.salary%TYPE; 
BEGIN 
    OPEN emp_cursor; 
     LOOP
        FETCH emp_cursor INTO emp_first_name, emp_salary; 
     EXIT WHEN emp_cursor%NOTFOUND; 
        -- Process the fetched data 
         DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_first_name || ', Salary: ' ||             emp_salary); 
    END LOOP; 
CLOSE emp_cursor; 
END;

Remember that explicit cursors give you more control over fetching and processing data, but they also require more code compared to implicit cursors (used in SQL statements like SELECT INTO), which are automatically managed by the database engine.


Hope you have a great day!! Until next time!






on