Cursors
Armed with the knowledge from the previous examples, let’s look at Example 1-3, where you can begin to see more of the power of PL/SQL. Just to keep it interesting, I’ve reverted again to uppercase. Try not to be intimidated by any content you haven’t seen before.
Example 1-3. More powerful PL/SQL
DECLARE
CURSOR
c1
is
SELECT
ename
,
empno
,
sal
FROM
emp
ORDER
BY
sal
DESC
;
— start with highest paid employee
my_ename
VARCHAR2
(
10
)
;
my_empno
NUMBER
(
4
)
;
my_sal
NUMBER
(
7
,
2
)
;
BEGIN
OPEN
c1
;
FOR
i
IN
1
.
.
5
LOOP
FETCH
c1
INTO
my_ename
,
my_empno
,
my_sal
;
EXIT
WHEN
c1
%
NOTFOUND
;
/*
in case the number requested
*/
/*
is more than the total
*/
/*
number of employees
*/
INSERT
INTO
temp
VALUES
(
my_sal
,
my_empno
,
my_ename
)
;
COMMIT
;
END
LOOP
;
CLOSE
c1
;
END
;
Let’s break down Example 1-3 into its component parts. Again, remember we’re just practicing reading the code and trying to imagine what it’s doing:
- A CURSOR is used: this is a PL/SQL construct for collecting blocks of table data.
2. Some new variables are declared: my_ename, my_empno, and my_sal.
3. The new variables and their data types are added, e.g., my_ename VARCHAR2(10).
4. There’s an OPEN on the CURSOR variable c1.
5. There’s some sort of FETCH from the CURSOR into the declared variables.
6. There’s an INSERT of the fetched data into a table called temp.
7. There’s a COMMIT of the data and a finish to the LOOP.
8. There’s a close on the CURSOR c1.
These descriptions should help you make sense of the PL/SQL in Example 1-3. It turns out that a CURSOR is simply a kind of work area composed of one or more rows returned by a SQL query. A CURSOR is a PL/SQL programming construct that points to the result of a query. Oracle supports two types of CURSORs: implicit and explicit. Let’s have a quick look at these two important constructs.