[PL/SQL] Cursor

binh12A3

6 min readSep 23, 2021

😎😎

What is cursor ?

Cursor is a pointer to a memory location (private SQL area) that stores informations about the SELECT or DML statement.

And there are 2 types of cursor :

Implicit cursor : is a session cursor that is constructed and managed by PL/SQL. PL/SQL opens an implicit cursor for every time you run a SELECT or DML statement. You can not control an implicit cursor, but you can get information from its attributes.
Explicit cursor : is a session cursor that you construct and manage. You must declare and define an explicit cursor, giving it a name and associating it with a query.

What are the cursor attributes ?

NOTE

Implicit cursor has SQL%ISOPEN always return FALSE, since the cursor is managed (opened and closed) automatically by oracle engine.

How to find cursor information ?

There is a dynamic view called “v$open_cursor” which hold informatiom about all session cursors.

The maximum number of cursors could be opened in the session is defined in “v$parameter”. If you try to open more than this number (i.e : 300), you will get the error “ORA-01000: maximum open cursors exceeded”.

1. Named Cursor

2. Parameterized Cursor

For example, when you want the cursor returns particular data for department 10, and department 20. Normally, you need to declare 2 particular cursors for each. Or you could create 1 cursor for all by declaring a variable.

Intead of using variable as above, you could create cursor with declared parameters instead

3. For Cursor

If you only use the cursor once in your code, and you don’t have plan to use this cursor elsewhere. Then it’s better to use For Cursor since it takes care open, loop, fetch, close operations automatically.

Note

You can use Named/Parameterized cursor as a part of For Cursor (re-used ability). For example in other places you can reuse the same explicit cursor by its name with FOR LOOP instead of open(), fetch(), close()

4. Ref Cursor, strongly typed and weakly typed

One of the advantage of Ref Cursor is we can use a single cursor to open completely different set of queries.

The key advantage of Ref Cursor is we can open a cursor with a dynamic query string

By default, the Ref Cursor is weakly typed. The difference between the strongly typed and weakly typed is, in the strongly typed we’ll define a type of return value. So if the cursor returns a different type, then it’ll throw an error “PLS-00382: expression is of wrong type”.

Instead of define cursor type via “IS REF CURSOR”, you could use directly a pre-defined type “sys_refcursor”. Note that this “sys_refcursor” is pre-defined as weakly typed only.

“SYS_REFCURSOR” can be used as a returned type of function in order to return a collection type.

Additional notes

1. What is FOR UPDATE clause ?

The moment you open a cursor, it’ll load data into memory location. Then if someone delete data in another session. Since the data was already loaded into memory location, so it still stays there, and you’re still able to fetch them.
But if you have an UPDATE in your cursor loop, it’ll return 0 row, since data are deleted. To ensure the data consistency, we can lock rows when declare a cursor by using “FOR UPDATE”.

2. What is WHERE CURRENT OF Clause ?

For example, the cursor just fetch records which has deptno =10. But in the cursor loop, we update all the records. So to ensure the data consistency as above, we can use “WHERE CURRENT OF” to enforce only rows in the context of cursor will be updated.

3. When to use cursor, when to use collection ?

In the collection we can access the value by using index. While in the cursor you just can access value from top to bottom and you can only access it one time. If you want to access it for the second time, you need to re-open the cursor.
For collection, when we use it from the 3rd party (i.e : python), it’ll load entire data from plsql collection into python collection object, so you could close the connection after you made a call to the procedure.

While for refcursor, every fetch of plsql code will load data into python variable. Which mean until we complete the fetch operation, we have to hold the database connection. Everytime we do a loop in python code, it’ll fetch data from the cursor via the database connection.