Cursors in PL/SQL


Oracle has two major different types of cursors. One is implicit and the other one is explicit.

Implicit Cursor
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):
DECLARE
NAME PRODUCT.DESCRIPTION%TYPE;
AMOUNT PRODUCT.PRICE%TYPE;
BEGIN
SELECT DESCRIPTION,PRICE INTO NAME, AMOUNT
FROM PRODUCT WHERE PRODUCT_ID = 4;
DBMS_OUTPUT.PUT_LINE(’PRICE OF ’ || NAME || ’ IS ’ || AMOUNT);
END;
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
price).
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.
DECLARE
P PRODUCT%ROWTYPE;
BEGIN
SELECT * INTO P FROM PRODUCT WHERE PRODUCT_ID = 4;
DBMS_OUTPUT.PUT_LINE(’PRICE OF ’ || P.DESCRIPTION || ’ IS ’ || P.PRICE);
END;
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 Cursor
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
something like:
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:
OPEN cursorname;
(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:
CLOSE cursorname;
Cursors will be closed automatically once your code exits, but it’s still a good idea to
close them explicitly.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s