 |
|
EnterpriseDB: Cursors
Oracle Tips by
Burleson
|
The
cursor is not the little blinky thing on your screen. Nor is it
someone who talks dirty.
A cursor
is a pointer to a memory area where your executing query is waiting.
First, you create your pointer by declaring a cursor query, you
initialize that area (by OPENing the cursor), you populate the area
(by FETCHing data) and when you're finished, you CLOSE the cursor to
free up the memory.
The
syntax for declaring a cursor is:
CURSOR <cursor name>[(<parameter list>)] IS
<select statement>;
The syntax for cursor processing is:
OPEN <cursor name>[(<parameters>)];
FETCH <cursor name> INTO <variable list|record>;
CLOSE <cursor name>;
Let's
take a look at a very simple cursor that retrieves a single row,
displays it on the screen and then exits:
DECLARE
v_ename emp.ename%TYPE; -- Step 1
CURSOR c_get_name IS -- Step 2
SELECT ename
FROM emp
LIMIT 5;
BEGIN
OPEN c_get_name;
-- Step 3
FETCH c_get_name INTO v_ename; -- Step 4
DBMS_OUTPUT.PUT_LINE( 'Ename: ' || v_ename ); -- Step 5
CLOSE c_get_name; -- Step 6
END;
INFO: Ename: SMITH
Your
results may be a little different than mine depending on the name
returned. Without an order by, there is no guarantee that any
particular ename will be returned.
Step 1
declares a regular variable that will be used to hold the data
returned by the cursor.
In step
2, I declared a cursor query. A cursor query is a normal select
statement preceded by the CURSOR keyword. The cursor is given a name
like a TABLE or RECORD type, although it is not really creating a
type. It is just naming your memory pointer. In my case here, I
decided to limit the number of rows to 5.
In step
3, I opened the cursor (initialized it). In step 4, I fetched data
into my variable and in step 6, I closed the cursor. Step 5 displayed
the data to the screen.
I can
modify this block to return more than a single record. There are two
ways to loop through a cursor. I will do the more complicated one
first and then show you how to do it the easy way:
DECLARE
v_ename emp.ename%TYPE; -- Step 1
CURSOR c_get_name IS -- Step 2
SELECT ename
FROM emp
LIMIT 5;
BEGIN
OPEN
c_get_name; -- Step 3
LOOP
FETCH c_get_name INTO v_ename; -- Step 4
EXIT WHEN v_ename IS NULL;
DBMS_OUTPUT.PUT_LINE( 'Ename: ' || v_ename ); -- Step 5
END LOOP;
CLOSE c_get_name; -- Step 6
END;
INFO: Ename: SMITH
INFO: Ename: ALLEN
INFO: Ename: WARD
INFO: Ename: JONES
INFO: Ename: MARTIN
The code
is exactly the same. I wrapped the fetch and output in a loop. I
exit the loop when v_ename is null. v_ename will only be null when
there are no records to retrieve.
As
useful as this cursor is, there is an easier way to achieve the same
results:
DECLARE
CURSOR c_get_name IS -- Step 2
SELECT ename
FROM emp
LIMIT 5;
BEGIN
FOR
cursor_variable IN c_get_name LOOP
DBMS_OUTPUT.PUT_LINE( 'Ename: ' || cursor_variable.ename ); --
Step 5
END LOOP;
END;
I won't
display the output from this code because it exactly matches the last
code output.
What did
I change? I was able to get rid of the variable, the open, the fetch
and the close. This is an example of a CURSOR FOR LOOP. The cursor
for loop opens the query and loops through it. On each loop through,
it fetches a row and makes it available in a dynamically created row
variable. In this example, I called it cursor_variable. It can be
named anything you would like to name it.
When the
cursor for loop hits the last row, it automatically closes the cursor
for you. It really doesn't get much easier than that.
Cursors
have variables associated with them (called cursor attributes) that
give you status about that cursor. Implicit cursors (DML and SELECT
INTO) are referred to by the keyword SQL. Explicit cursors are
referred to by name. SPL has four cursor attributes: %ROWCOUNT,
%FOUND, %NOTFOUND, %ISOPEN.
* %ROWCOUNT
– The number of rows affected by a SQL DML statement or the number of
rows fetched from an explicit cursor
* %FOUND
– True if any row was affected by the statement
* %NOTFOUND
– True if no row was affected by the statement
* %ISOPEN
– True if the named cursor is already open
Here is
a small example:
DECLARE
v_ename emp.ename%TYPE; -- Step 1
CURSOR c_get_name IS -- Step 2
SELECT ename
FROM emp
LIMIT 5;
BEGIN
IF NOT
c_get_name%ISOPEN -- Step 2a
THEN
OPEN c_get_name; -- Step 3
FETCH c_get_name INTO v_ename; -- Step 4
DBMS_OUTPUT.PUT_LINE( 'Ename: ' || v_ename ||
', Row Count: ' || to_char( c_get_name%ROWCOUNT )); -- Step
5
CLOSE c_get_name; -- Step 6
END IF;
INSERT INTO emp (empno) -- Step 7
VALUES (999);
DBMS_OUTPUT.PUT_LINE( 'Insert rows: ' || to_char(SQL%ROWCOUNT) ); --
Step 8
UPDATE emp -- Step 9
SET ename = 'Jimmy'
WHERE empno = 999;
IF SQL%FOUND THEN -- Step 10
DBMS_OUTPUT.PUT_LINE( 'Updated rows: ' || to_char(SQL%ROWCOUNT)
);
ELSE
DBMS_OUTPUT.PUT_LINE( 'No Rows Updated.' );
END IF;
END;
INFO: Ename: SMITH, Row Count: 1
INFO: Inserting employee 999
CONTEXT: SQL statement "INSERT INTO emp ( empno ) VALUES ( 999 )"
INFO:
CONTEXT: SQL statement "INSERT INTO emp ( empno ) VALUES ( 999 )"
INFO: User enterprisedb added employee(s) on 2006-11-21
CONTEXT: SQL statement "INSERT INTO emp ( empno ) VALUES ( 999 )"
INFO: Insert rows: 1
INFO: Updating employee 999
CONTEXT: SQL statement "UPDATE emp SET ename = 'Jimmy' WHERE empno =
999"
INFO:
CONTEXT: SQL statement "UPDATE emp SET ename = 'Jimmy' WHERE empno =
999"
INFO:
CONTEXT: SQL statement "UPDATE emp SET ename = 'Jimmy' WHERE empno =
999"
INFO:
CONTEXT: SQL statement "UPDATE emp SET ename = 'Jimmy' WHERE empno =
999"
INFO: User enterprisedb updated employee(s) on 2006-11-21
CONTEXT: SQL statement "UPDATE emp SET ename = 'Jimmy' WHERE empno =
999"
INFO: Updated rows: 1
* If you
are wondering where all of the extra information in my output is
coming from, the sample schema has triggers that produce additional
output. You can disable or drop those triggers if you prefer not to
see that output. For the examples in the book, I think the additional
information is useful.
The new
and changed steps here are 2a, 5 and 7-10.
Step 2a
uses the %ISOPEN variable to see if the cursor is already open. If it
is not open, it executes the code.
Step 5
was changed to include a %rowcount.
Step 7
inserted a record (via implicit cursor) and step 8 checked the
rowcount using the implicit cursor syntax.
Step 9
updated a record and step 10 checked to see if a row was found using
the %FOUND attribute and the implicit cursor syntax. If a row was
found (which there was a row), it displayed the number of rows
updated. If no row was found, it displayed the text, "No Rows
Updated.".
Cursors
also allow parameter replacement. A cursor parameter is a variable
name and a data type specified in the cursor declaration. A cursor
may have 0, 1 or more parameters.
Parameters are bound to the query at runtime and allow cursors to be
re-used across calls for varying result sets. Parameters can be used
anywhere a variable is allowed in a query: where clause, select list,
group by, order by, having. You cannot replace a column or table name
with a parameter.
Below is
an example, like the one above, that sets the limit of the select.
DECLARE
CURSOR c_get_name( p_limit NUMBER ) IS -- Step 1
SELECT ename
FROM emp
LIMIT p_limit;
BEGIN
FOR cursor_variable IN c_get_name( 5 ) LOOP --
Step2
DBMS_OUTPUT.PUT_LINE( 'Ename: ' || cursor_variable.ename ); --
Step 3
END LOOP;
END;
In the
code above, Step 1 creates a cursor with a parameter. The parameter
will be used in the LIMIT clause. Notice that the parameter p_limit is
defined as a number and that the same parameter is used in the LIMIT
clause.
In Step
2, the cursor is called in a cursor for loop and the parameter is set
to 5. This could be set to any number and it would limit the number
of rows returned by the query. A parameter could have been used to
restrict by ename, deptno or any other valid where criteria.
The
next step in making your life easier (at least where querying data is
concerned) is the REF CURSOR.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
