PL/SQL Tutorial: Chapter 5

In this chapter, we’ll learn about some pretty useful concepts in PL/SQL:

• What a cursor is
• The two types of cursors
• Inserting, updating, and deleting data in tables using PL/SQL
• Selecting data from a table into PL/SQL
• What an array is and how to use one

What Is a Cursor in PL/SQL?

A cursor is an area in memory that Oracle creates when an SQL statement is run. The cursor contains several pieces of information including the rows that the statement has returned (if it’s a SELECT statement), and attributes about the result set such as the number of rows impacted by the statement.

There are two types of cursors in Oracle SQL: implicit cursors and explicit cursors.

Implicit Cursors

An implicit cursor is a type of cursor automatically created by Oracle when an SQL statement is run. It’s called an implicit cursor because it’s created automatically – you don’t need to do anything for it to get created.

Whenever your run a DML statement (INSERT, UPDATE, DELETE, or SELECT), an implicit cursor is created:

• For INSERT statements, the cursor contains the data that is being inserted.
• For UPDATE or DELETE statements, the cursor identifies the rows that will be affected
• For SELECT statements, the rows that are retrieved can be stored in an object.

How can implicit cursors help us? For INSERT, UPDATE, and DELETE statements, we can see several attributes of the statements to understand if they have worked or not.

Let’s see an example of this.

Example – Using a Cursor with INSERT

Let’s see what can be done with an implicit cursor and an INSERT statement.

First, we’ll need to create a table:

CREATE TABLE person (
fname VARCHAR2(50)
);

If you run this in an SQL window, the table will be created.

Now let’s write some PL/SQL to insert data into this table.

BEGIN
  INSERT INTO person (fname)
  VALUES ('John');
  DBMS_OUTPUT.PUT_LINE('Total number of rows impacted: ' || sql%rowcount);
END;

This code includes the term sql%rowcount. The “sql” is the object name of the SQL statement that ran last. The “rowcount” is the attribute of this most recent SQL statement, and contains the number of rows impacted by the statement. The % sign indicates that the rowcount is an attribute of the sql object. So, sql%rowcount will contain the number of rows impacted by the most recent SQL statement.

If we run this code, we should see this output:

1 row(s) inserted.
Total number of rows impacted: 1

The output shows the number of impacted rows. We can do more with this value, such as storing it in a variable:

DECLARE
  l_total_rows NUMBER(10);
BEGIN
  INSERT INTO person (fname)
  VALUES ('John');
  l_total_rows := sql%rowcount;
  DBMS_OUTPUT.PUT_LINE('Total number of rows impacted: ' || l_total_rows);
END;

You can also use this information in an IF statement and run different code based on the value.

DECLARE
  l_total_rows NUMBER(10);
BEGIN
  INSERT INTO person (fname)
  VALUES ('John');
  l_total_rows := sql%rowcount;
  IF (l_total_rows > 0) THEN
    DBMS_OUTPUT.PUT_LINE('Rows inserted: ' || l_total_rows);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No rows inserted.');
  END IF;
END;

If you run a SELECT statement on your table, you should see the data in the table. Assuming the statement was only run once, this is what you’ll see:

Example – Using a Cursor with UPDATE

We can make use of implicit cursors in PL/SQL with UPDATE statements. For example, this code shows us the number of rows impacted by the UPDATE statement.

BEGIN
  UPDATE person
  SET fname='Susan'
  WHERE fname = 'John';
  DBMS_OUTPUT.PUT_LINE('Total number of rows impacted: ' || sql%rowcount);
END;

If we run this code, we’ll get this output:

1 row(s) updated.
Total number of rows impacted: 1

It says that 1 row is impacted by our update statement. The row is also updated: that UPDATE statement is executed. If we run a SELECT query on the table, we’ll see the new value:

Now, let’s run our earlier code and see what happens:

1 row(s) updated.
Total number of rows impacted: 0

It says 0 rows are impacted (even though the output also says “1 row(s) updated”). This is because the WHERE clause means the statement is looking for a row of “John”, but the value in the row is equal to “Susan”, so it is not updated.

Another way of checking for rows being updated is to use the %FOUND and %NOTFOUND attributes, rather than %ROWCOUNT.

The %FOUND attribute will return true if at least one row is impacted by an INSERT, UPDATE, or DELETE statement, otherwise, it will return false. The %NOTFOUND attribute is the opposite: it will return true if no rows are impacted and false if at least one row is impacted.

We can change our code to use the %FOUND attribute:

BEGIN
  UPDATE person
  SET fname='Susan'
  WHERE fname = 'John';
  IF (sql%found) THEN
    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || sql%rowcount);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Rows not updated.');
  END IF;
END;

This output is shown:

1 row(s) updated.
Rows not updated.

The output message says no rows are updated. This is because the WHERE clause doesn’t match any of the existing rows. We can change our UPDATE statement so rows are found:

BEGIN
  UPDATE person
  SET fname='Mark'
  WHERE fname = 'Susan';
  IF (sql%found) THEN
    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || sql%rowcount);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Rows not updated.');
  END IF;
END;

The output shows:

1 row(s) updated.
Rows updated: 1

Example – Using a Cursor with DELETE

The same concept of an implicit cursor applies to a DELETE statement. We can write some code that deletes a row and then displays an output depending on if a row was deleted or not.

BEGIN
  DELETE FROM person
  WHERE fname = 'Susan';
  IF (sql%found) THEN
    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || sql%rowcount);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Rows not updated.');
  END IF;
END;

This output shows that no rows were deleted.

1 row(s) deleted.
Rows not updated.

This is because no rows have an fname value of “Susan”. If we change it to “Mark” and run the statement again, the row will be deleted.

BEGIN
  DELETE FROM person
  WHERE fname = 'Mark';
  IF (sql%found) THEN
    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || sql%rowcount);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Rows not updated.');
  END IF;
END;

The output shows:

1 row(s) deleted.
Rows updated: 1.

Selecting Data with an Implicit Cursor

What about a SELECT query with an implicit cursor? That works a little differently.

We can use a SELECT query with an implicit cursor. However, we need to use a variable to store the returned value. We can use SELECT INTO for this.

First, we declare a variable to hold our data. Then, we select a column value from the table and store it in this variable. In this example, we’ll just select a single value, as we need to treat multiple rows differently.

Ensure your person table only has one row in it:

DELETE FROM person;
INSERT INTO person (fname) VALUES ('John');
SELECT * FROM person;

Now, let’s write some PL/SQL code to select this value into a variable.

DECLARE
  l_fname VARCHAR2(50);
BEGIN
  SELECT fname
  INTO l_fname
  FROM person;
  DBMS_OUTPUT.PUT_LINE('The name is ' || l_fname);
END;

First, we’ve declared a variable called l_fname. Then in the BEGIN section, we have selected the fname column from the person table into the l_fname variable. This variable now holds the value from that query. We then output it using the PUT_LINE function.

The output from this statement is:

Statement processed.
The name is John

We can see the output says “The name is John”.

What if there are two or more values in the table? How do we handle this? Our variable can only hold one value.

Here’s the code to insert another row into the table:

INSERT INTO person (fname) VALUES ('Susan');
SELECT * FROM person;

Now, let’s run our PL/SQL code to SELECT the value from this column into a variable.

DECLARE
  l_fname VARCHAR2(50);
BEGIN
  SELECT fname
  INTO l_fname
  FROM person;
  DBMS_OUTPUT.PUT_LINE('The name is ' || l_fname);
END;

This is our output. The error message we get is:

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4 ORA-06512: at "SYS.DBMS_SQL", line 1721

This error happens because we have two values returned from our query (John and Susan), but only one variable to store them.

How do we handle situations like this? We’ll learn about that later in this guide.

For now, let’s look at explicit cursors.

Explicit Cursors

In Oracle PL/SQL, an explicit cursor is one that is declared by the PL/SQL code. You have more control over it and how the data is handled.
Using an explicit cursor involves several steps:

1. Declaring the cursor as a variable in the DECLARE section.
2. Opening the cursor, which allocates memory for it.
3. Fetching the cursor, which means the SELECT statement is run and data is stored in the cursor.
4. Closing the cursor, which releases the allocated memory.

Example: Explicit Cursors in PL/SQL

To declare a cursor, you add a line into the DECLARE section of your code. It includes your SELECT statement.

DECLARE
  CURSOR c_person IS
  SELECT fname FROM person;
BEGIN
 
END;

As shown above, cursors are declared using this syntax:

CURSOR cursor_name IS select_statement;

Next, we need to open the cursor. This is done in the BEGIN block:

DECLARE
  CURSOR c_person IS
  SELECT fname FROM person;
BEGIN
  OPEN c_person;
END;

Now, we fetch the data from the cursor into a variable.

DECLARE
  l_fname VARCHAR2(50);
  CURSOR c_person IS
  SELECT fname FROM person;
BEGIN
  OPEN c_person;
  FETCH c_person INTO l_fname;
END;

We then have to close the cursor.

DECLARE
  l_fname VARCHAR2(50);
  CURSOR c_person IS
  SELECT fname FROM person;
BEGIN
  OPEN c_person;
  FETCH c_person INTO l_fname;
  CLOSE c_person;
END;

Let’s write the variable to the screen.

DECLARE
  l_fname VARCHAR2(50);
  CURSOR c_person IS
  SELECT fname FROM person;
BEGIN
  OPEN c_person;
  FETCH c_person INTO l_fname;
  DBMS_OUTPUT.PUT_LINE('Name is: ' || l_fname);
  CLOSE c_person;
END;

The output shown here is:

Statement processed.
Name is: John
It shows the name of John.

But there are two records in the table. How can we handle this with our cursor code?

Example: Explicit Cursors and Loops

If our SELECT query returns multiple rows from an explicit cursor, we need to use a loop to get all of the data.

We use the same loop as we learned earlier in this guide:

LOOP
  FETCH cursor_name INTO variables;
  EXIT WHEN cursor_name%notfound;
  your_code;
END LOOP;

What does this code do?

• It starts with a LOOP, as we’ve learned in an earlier section.
• We then have a FETCH statement, which will fetch the values of the columns of your SELECT statement from a single row into the variables you specify
• We then have EXIT WHEN cursor_name%notfound, which means the loop will exit once there are no more rows found in the cursor.
• The code you want to execute is then run
• The END LOOP statement will end the loop.

The important part about this code is that the FETCH statement will fetch the next row from the cursor. This means it fetches one row at a time.

Let’s see an example.

DECLARE
  l_fname VARCHAR2(50);
  CURSOR c_person IS
  SELECT fname FROM person;
BEGIN
  OPEN c_person;
  LOOP
    FETCH c_person INTO l_fname;
    EXIT WHEN c_person%notfound;
    DBMS_OUTPUT.PUT_LINE('Name is: ' || l_fname);
  END LOOP;
  CLOSE c_person;
END;

This is similar to the earlier code:

• We declare the l_fname variable, and then the cursor as the SELECT statement.
• We open the cursor
• We start the loop
• Inside the loop, we fetch the cursor into the l_fname variable
• We output the value of l_fname
• We loop until no more records are found
• We close the cursor and end the program

This might seem like a lot of code just to write out some values to the screen, and it is. It’s simpler to just do this as a SELECT statement.

But this is just to demonstrate the concepts. You can use any code in your PL/SQL program instead of writing the values to screen.

Here’s what is shown when that code is run:

Statement processed.
Name is: John
Name is: Susan

It shows two rows of output as there are two rows in the table.

So that’s how you can use explicit cursors with SELECT statements and use loops to fetch data from tables that have multiple rows.

Arrays: Multiple Values in a Single Variable

In our earlier examples, we have used a cursor and run a SELECT statement to fetch a value into a variable.

What if we want to store multiple values but don’t want to have to create separate variables for each of the values? We want to avoid doing this:

DECLARE
  l_fname_1 VARCHAR2(50);
  l_fname_2 VARCHAR2(50);
  l_fname_3 VARCHAR2(50);
..

That will end up with a lot of unnecessary code.

There is a better way: using arrays.

Arrays are a concept in programming where a single variable stores multiple values. Each of the values is accessed using an index number.

The concept works like this:

• Declare an array, such as array_fname.
• Populate the array, so each value in the array has an index number:
  • array_fname(1) = ‘John’,
  • array_fname(2) = ‘Susan’
  • array_fname(3) = ‘Mark’
• Access the values throughout your code.

So how can you create an array in PL/SQL? Arrays are created like this:

DECLARE
  TYPE fname_array_type IS VARRAY(10) OF VARCHAR2(50);
  fname_array fname_array_type;
BEGIN
  your_code;
END;

The code includes two lines to set up your array: creating a type, and creating the variable.

Example: Creating a VARRAY

In PL/SQL, to use an array, you need to declare the type of array first. This is done using the TYPE statement. In other programming languages, the array declaration and type are done on a single line, but in PL/SQL they are on separate lines.

This line is:

TYPE fname_array_type IS VARRAY(10) OF VARCHAR2(50);

It says that you are creating a TYPE, and the name of this type we have called fname_array_type. This is just a name we made up, but it’s clear it’s a type. I could have called it varchar_type or something else to indicate what it is.

We then specify IS VARRAY(10). This means the type is a VARRAY, which is the array type in PL/SQL. The 10 indicates the maximum number of elements in this array. This means any variable based on this type can hold up to 10 values in it.

Finally, we specify OF VARCHAR2(50) which is the type of data it holds. You can create types that hold NUMBER values or any other PL/SQL data type.

Once we have declared our type, we can then declare a variable of that type:

fname_array fname_array_type;

This code declares a new variable called fname_array with the data type of fname_array_type. We don’t need to specify anything else because that’s included in the TYPE declaration.

Now what? We populate the array.

DECLARE
  TYPE fname_array_type IS VARRAY(10) OF VARCHAR2(50);
  fname_array fname_array_type;
BEGIN
  fname_array := fname_array_type('Adam', 'Belinda', 'Charles', 'Debra');
END;

We’ve set the fname_array to contain four different names.

What can we do with these values now? We can use a FOR loop to loop through them and write them to the screen. We can do a lot more with these values, but for this example, we’ll just write them to the screen to keep it simple.

DECLARE
  TYPE fname_array_type IS VARRAY(10) OF VARCHAR2(50);
  fname_array fname_array_type;
BEGIN
  fname_array := fname_array_type('Adam', 'Belinda', 'Charles', 'Debra');
  FOR i IN 1 .. fname_array.count LOOP
    DBMS_OUTPUT.PUT_LINE('Name is: ' || fname_array(i));
  END LOOP;
END;

The code we have added is the FOR loop. As we learned in an earlier section, the FOR loop starts at a number and ends at another number. In this example, the variable is called “i”, and it starts at 1 and ends at something called fname_array.count.

This count is an attribute of the array, and it returns the number of records or elements in the array. It’s better to use this rather than just the number 4, because the array size can change (if you’re populating it from somewhere else).

Inside the loop, we show the value by using fname_array(i), which means we are referring to a single element inside the array. We are referring to the element with an index position of whatever “i” is equal to, which changes each time the loop is run.

The output should show separate lines that display each of the values in the array.

If we run the code, this is what we get:

Statement processed.
Name is: Adam
Name is: Belinda
Name is: Charles
Name is: Debra

It shows separate lines, one for each name in the array.

So that’s how you can use arrays in your PL/SQL code.

Conclusion

A cursor is an area in memory used to hold details of an SQL statement, such as the number of impacted rows and the values returned from a SELECT query.

Oracle offers two types of cursors: an implicit and an explicit cursor. Implicit cursors are created automatically without you doing anything.

Explicit cursors are specified by you but you have more control over them.

Arrays are types of variables that allow you to store many values in a single variable. They are declared as a TYPE first, and then the array variable is declared. They can be accessed using a number that represents their index, and are often done using loops.

Chapter Quiz

TYPE id_arraytype IS VARRAY(10) OF NUMBER(4);
id_array id_arraytype;

Time's up

< Back to Tutorial Index

Next Chapter >