Oracle has two major different types of cursors. One is implicit and the other one is explicit.
Implicit cursors can be generated every time you do a SELECT statement in PL/SQL. The
general format goes something like this:
SELECT selectfields INTO declared_variables FROM table_list WHERE search_criteria;
The only catch is that the search criteria must return one and only one result. If it
returns zero, or more than one, an error is generated.
For example, lets say we wanted to get the name and price of some specific product
(identified by PRODUCT_ID):
SELECT DESCRIPTION,PRICE INTO NAME, AMOUNT
FROM PRODUCT WHERE PRODUCT_ID = 4;
DBMS_OUTPUT.PUT_LINE(’PRICE OF ’ || NAME || ’ IS ’ || AMOUNT);
Which faithfully displays out:
PRICE OF keyboard IS 19.95
Assuming the “keyboard” is in the database and has PRODUCT ID = 4 (and has that
Note that we used the table’s types, which brings up another issue: Now is a pretty good
time to illustrate the ROWTYPE type. Let’s rewrite the above using that.
SELECT * INTO P FROM PRODUCT WHERE PRODUCT_ID = 4;
DBMS_OUTPUT.PUT_LINE(’PRICE OF ’ || P.DESCRIPTION || ’ IS ’ || P.PRICE);
Notice that the code got a lot smaller since we don’t have to worry about defining every
single variable for retrieval purposes. We retrieve a whole row of data at a time.
Explicit Cursors are cursors that you have to explicitly declare, and which give you a lot
more flexibility than the implicit ones.
To declare an explicit cursor, you have to do it in the DECLARE section. The format looks
CURSOR cursorname IS SELECT_statement;
Where SELECT_statement is any select statement (except a more exotic one which contains a UNION or MINUS.
Opening an Explicit Cursor
In order to use an explicit cursor, you must open it. You do that with a simple:
(obviously you have to do that inside the code section, between BEGIN and END).
Fetching Data into an Explicit Cursor
Besides opening the cursor, we also have to grab the results of the SELECT statement one by
one. We do that with a FETCH. For example:
FETCH cursorname INTO recordvariables;
We shall do some examples when we learn our cursor loops, so hang on…
Closing a Cursor
Closing a cursor is just as easy as opening it. We just say:
Cursors will be closed automatically once your code exits, but it’s still a good idea to
close them explicitly.