PL/SQL - Cursors: Your Gateway to Data Manipulation

Hello there, future database wizards! Today, we're going to embark on an exciting journey into the world of PL/SQL cursors. Don't worry if you're new to programming – I'll be your friendly guide, and we'll explore this topic step by step. By the end of this tutorial, you'll be wielding cursors like a pro!

What Are Cursors?

Before we dive in, let's understand what cursors are. Imagine you're at a buffet (I know, I'm making you hungry already!). You have a plate (your program) and you're looking at all the delicious dishes (your database tables). A cursor is like your hand – it helps you pick up specific items from the buffet and put them on your plate. In database terms, a cursor allows you to retrieve and manipulate data from your database, one row at a time.

Now, let's explore the two types of cursors in PL/SQL:

Implicit Cursors

Implicit cursors are like the automatic functions on your smartphone camera – they work behind the scenes without you having to do much. Oracle creates and manages these cursors for you when you execute SQL statements.

Let's look at an example:

BEGIN
   UPDATE employees SET salary = salary * 1.1 WHERE department_id = 20;

   IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employees received a 10% raise!');
   ELSE
      DBMS_OUTPUT.PUT_LINE('No employees in department 20 found.');
   END IF;
END;
/

In this code:

1. We're updating the salary of employees in department 20.
2. SQL%FOUND is an attribute of the implicit cursor that returns TRUE if the UPDATE affected any rows.
3. We use this to print an appropriate message.

Other useful implicit cursor attributes include:

• SQL%ROWCOUNT: Number of rows affected
• SQL%ISOPEN: Always returns FALSE for implicit cursors
• SQL%NOTFOUND: Opposite of SQL%FOUND

Explicit Cursors

Explicit cursors are like manual mode on your camera – you have more control, but you need to manage them yourself. They're perfect when you need to process rows one at a time or when you want more control over the data retrieval process.

Let's break down the lifecycle of an explicit cursor:

Declaring the Cursor

First, we need to tell our PL/SQL block what data we want to work with. It's like deciding what you want to eat at the buffet before you start filling your plate.

DECLARE
   CURSOR c_emp IS 
      SELECT employee_id, first_name, last_name 
      FROM employees 
      WHERE department_id = 30;

Here, we've declared a cursor named c_emp that will fetch employee details from department 30.

Opening the Cursor

Now that we've decided what we want, it's time to approach the buffet (or in our case, the database).

OPEN c_emp;

This line tells Oracle to execute the SELECT statement and prepare the result set.

Fetching the Cursor

This is where we actually start putting food on our plate – or in programming terms, retrieving data row by row.

DECLARE
   v_emp_id employees.employee_id%TYPE;
   v_first_name employees.first_name%TYPE;
   v_last_name employees.last_name%TYPE;
BEGIN
   OPEN c_emp;
   LOOP
      FETCH c_emp INTO v_emp_id, v_first_name, v_last_name;
      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_emp_id || ' - ' || v_first_name || ' ' || v_last_name);
   END LOOP;
   CLOSE c_emp;
END;
/

In this example:

1. We declare variables to hold our fetched data.
2. We open the cursor.
3. We use a LOOP to fetch rows one by one.
4. We exit the loop when there are no more rows (c_emp%NOTFOUND).
5. We print each employee's details.

Closing the Cursor

Just like you'd return your plate after finishing at a buffet, we need to close our cursor when we're done.

CLOSE c_emp;

This releases the resources associated with the cursor.

Cursor FOR Loops: A Shortcut

Now, I have a little secret for you. There's a way to make working with cursors even easier – it's called a Cursor FOR Loop. It's like having a helpful friend at the buffet who fills your plate for you!

BEGIN
   FOR emp_rec IN (SELECT employee_id, first_name, last_name 
                   FROM employees 
                   WHERE department_id = 30) LOOP
      DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ' - ' || 
                           emp_rec.first_name || ' ' || 
                           emp_rec.last_name);
   END LOOP;
END;
/

This magical loop does all the work for you:

1. It declares a record variable (emp_rec) to hold each row.
2. It opens the cursor, fetches each row, and closes the cursor automatically.
3. You just need to focus on what to do with each row inside the loop.

Cursor Methods: Your Handy Toolkit

Let's summarize the methods we can use with cursors in a neat table:

Method	Description
DECLARE	Defines the cursor and its SELECT statement
OPEN	Executes the SELECT statement and prepares the result set
FETCH	Retrieves the next row from the result set
CLOSE	Closes the cursor and releases resources

Remember, with great power comes great responsibility. Always close your cursors when you're done with them to free up resources!

And there you have it, my dear students! You've just taken your first steps into the world of PL/SQL cursors. Practice with these concepts, play around with different scenarios, and soon you'll be navigating through your databases with the grace and precision of a seasoned programmer.

Remember, in programming, as in life, the key is to keep learning and experimenting. So go forth, write some code, make some mistakes, and most importantly, have fun! Who knows? The next great database application might just be at your fingertips. Happy coding!

Credits: Image by storyset