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:
1CREATE TABLE person (
2fname VARCHAR2(50)
3);
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.
1BEGIN
2 INSERT INTO person (fname)
3 VALUES ('John');
4 DBMS_OUTPUT.PUT_LINE('Total number of rows impacted: ' || sql%rowcount);
5END;
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:
11 row(s) inserted.
2Total 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:
1DECLARE
2 l_total_rows NUMBER(10);
3BEGIN
4 INSERT INTO person (fname)
5 VALUES ('John');
6 l_total_rows := sql%rowcount;
7 DBMS_OUTPUT.PUT_LINE('Total number of rows impacted: ' || l_total_rows);
8END;
You can also use this information in an IF statement and run different code based on the value.
1DECLARE
2 l_total_rows NUMBER(10);
3BEGIN
4 INSERT INTO person (fname)
5 VALUES ('John');
6 l_total_rows := sql%rowcount;
7 IF (l_total_rows > 0) THEN
8 DBMS_OUTPUT.PUT_LINE('Rows inserted: ' || l_total_rows);
9 ELSE
10 DBMS_OUTPUT.PUT_LINE('No rows inserted.');
11 END IF;
12END;
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:
| FNAME |
|---|
| John |
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.
1BEGIN
2 UPDATE person
3 SET fname='Susan'
4 WHERE fname = 'John';
5 DBMS_OUTPUT.PUT_LINE('Total number of rows impacted: ' || sql%rowcount);
6END;
If we run this code, we'll get this output:
11 row(s) updated.
2Total 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:
| FNAME |
|---|
| Susan |
Now, let's run our earlier code and see what happens:
11 row(s) updated.
2Total 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:
1BEGIN
2 UPDATE person
3 SET fname='Susan'
4 WHERE fname = 'John';
5 IF (sql%found) THEN
6 DBMS_OUTPUT.PUT_LINE('Rows updated: ' || sql%rowcount);
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('Rows not updated.');
9 END IF;
10END;
This output is shown:
11 row(s) updated.
2Rows 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:
1BEGIN
2 UPDATE person
3 SET fname='Mark'
4 WHERE fname = 'Susan';
5 IF (sql%found) THEN
6 DBMS_OUTPUT.PUT_LINE('Rows updated: ' || sql%rowcount);
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('Rows not updated.');
9 END IF;
10END;
The output shows:
11 row(s) updated.
2Rows 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.
1BEGIN
2 DELETE FROM person
3 WHERE fname = 'Susan';
4 IF (sql%found) THEN
5 DBMS_OUTPUT.PUT_LINE('Rows updated: ' || sql%rowcount);
6 ELSE
7 DBMS_OUTPUT.PUT_LINE('Rows not updated.');
8 END IF;
9END;
This output shows that no rows were deleted.
11 row(s) deleted.
2Rows 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.
1BEGIN
2 DELETE FROM person
3 WHERE fname = 'Mark';
4 IF (sql%found) THEN
5 DBMS_OUTPUT.PUT_LINE('Rows updated: ' || sql%rowcount);
6 ELSE
7 DBMS_OUTPUT.PUT_LINE('Rows not updated.');
8 END IF;
9END;
The output shows:
11 row(s) deleted.
2Rows 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:
1DELETE FROM person;
2INSERT INTO person (fname) VALUES ('John');
3SELECT * FROM person;
Now, let's write some PL/SQL code to select this value into a variable.
1DECLARE
2 l_fname VARCHAR2(50);
3BEGIN
4 SELECT fname
5 INTO l_fname
6 FROM person;
7 DBMS_OUTPUT.PUT_LINE('The name is ' || l_fname);
8END;
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:
1Statement processed.
2The 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:
1INSERT INTO person (fname) VALUES ('Susan');
2SELECT * FROM person;
| FNAME |
|---|
| John |
| Susan |
Now, let's run our PL/SQL code to SELECT the value from this column into a variable.
1DECLARE
2 l_fname VARCHAR2(50);
3BEGIN
4 SELECT fname
5 INTO l_fname
6 FROM person;
7 DBMS_OUTPUT.PUT_LINE('The name is ' || l_fname);
8END;
This is our output. The error message we get is:
1ORA-01422: exact fetch returns more than requested number of rows
2ORA-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:
- Declaring the cursor as a variable in the DECLARE section.
- Opening the cursor, which allocates memory for it.
- Fetching the cursor, which means the SELECT statement is run and data is stored in the cursor.
- 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.
1DECLARE
2 CURSOR c_person IS
3 SELECT fname FROM person;
4BEGIN
5
6END;
As shown above, cursors are declared using this syntax:
1CURSOR cursor_name IS select_statement;
Next, we need to open the cursor. This is done in the BEGIN block:
1DECLARE
2 CURSOR c_person IS
3 SELECT fname FROM person;
4BEGIN
5 OPEN c_person;
6END;
Now, we fetch the data from the cursor into a variable.
1DECLARE
2 l_fname VARCHAR2(50);
3 CURSOR c_person IS
4 SELECT fname FROM person;
5BEGIN
6 OPEN c_person;
7 FETCH c_person INTO l_fname;
8END;
We then have to close the cursor.
1DECLARE
2 l_fname VARCHAR2(50);
3 CURSOR c_person IS
4 SELECT fname FROM person;
5BEGIN
6 OPEN c_person;
7 FETCH c_person INTO l_fname;
8 CLOSE c_person;
9END;
Let's write the variable to the screen.
1DECLARE
2 l_fname VARCHAR2(50);
3 CURSOR c_person IS
4 SELECT fname FROM person;
5BEGIN
6 OPEN c_person;
7 FETCH c_person INTO l_fname;
8 DBMS_OUTPUT.PUT_LINE('Name is: ' || l_fname);
9 CLOSE c_person;
10END;
The output shown here is:
1Statement processed.
2Name is: John
3It 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:
1LOOP
2 FETCH cursor_name INTO variables;
3 EXIT WHEN cursor_name%notfound;
4 your_code;
5END 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.
1DECLARE
2 l_fname VARCHAR2(50);
3 CURSOR c_person IS
4 SELECT fname FROM person;
5BEGIN
6 OPEN c_person;
7 LOOP
8 FETCH c_person INTO l_fname;
9 EXIT WHEN c_person%notfound;
10 DBMS_OUTPUT.PUT_LINE('Name is: ' || l_fname);
11 END LOOP;
12 CLOSE c_person;
13END;
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:
1Statement processed.
2Name is: John
3Name 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:
1DECLARE
2 l_fname_1 VARCHAR2(50);
3 l_fname_2 VARCHAR2(50);
4 l_fname_3 VARCHAR2(50);
5..
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:
1DECLARE
2 TYPE fname_array_type IS VARRAY(10) OF VARCHAR2(50);
3 fname_array fname_array_type;
4BEGIN
5 your_code;
6END;
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:
1TYPE 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:
1fname_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.
1DECLARE
2 TYPE fname_array_type IS VARRAY(10) OF VARCHAR2(50);
3 fname_array fname_array_type;
4BEGIN
5 fname_array := fname_array_type('Adam', 'Belinda', 'Charles', 'Debra');
6END;
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.
1DECLARE
2 TYPE fname_array_type IS VARRAY(10) OF VARCHAR2(50);
3 fname_array fname_array_type;
4BEGIN
5 fname_array := fname_array_type('Adam', 'Belinda', 'Charles', 'Debra');
6 FOR i IN 1 .. fname_array.count LOOP
7 DBMS_OUTPUT.PUT_LINE('Name is: ' || fname_array(i));
8 END LOOP;
9END;
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:
1Statement processed.
2Name is: Adam
3Name is: Belinda
4Name is: Charles
5Name 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.