PL/SQL is an advanced programming language, and one of the many ways to retrieve data in PL/SQL is through the use of cursors.
There are several ways cursors can be used in PL/SQL, and in a five-part series we are going to look at the basics of PL/SQL cursors, and then four different ways to use cursors in PL/SQL.
Before we are going to get into the specifics of the four types (covered in this series) of PL/SQL cursors, we are going to speak a little bit about the basics.
Depending on the type of cursor you use, you can name the cursor after your liking. The Cursor operates as a pointer to a temporary work area in the SGA, and will help you out on performance if you choose to i.e. manipulate the data in the cursor – meaning that Oracle knows what the resultset looks like, so if you re-use it, it will not be re-executed. It is cached in the SGA.
If you would like to visualize a resultset in the SGA, try to think about it as a huge
dresser,
with one drawer per record in the resultset. Each time you move from
one record to the next in the resultset, a new drawer is opened, and the
previous is closed. The open drawer is called the active or current
record, and is kept track of by Oracle in the SGA shared memory area.
The resultset can be thought about as a virtual table, since a resultset contains columns, and records, just like a table would have. The difference is that the records in a resultset can have been put together by the result from many different tables, depending on what your SQL looks like.
NOTE: It is only possible to traverse FORWARDS in a SQL cursor in Oracle (and probably most SQL based databases). If you NEED to traverse backwards, an idea can i.e. be to upload your resultset to a PL/SQL collection, and then loop both ways.
In the next part we are going to look at EXPLICIT cursors. We are also going to discuss the different attributes associated with the EXPLICIT cursors.
There are several ways cursors can be used in PL/SQL, and in a five-part series we are going to look at the basics of PL/SQL cursors, and then four different ways to use cursors in PL/SQL.Before we are going to get into the specifics of the four types (covered in this series) of PL/SQL cursors, we are going to speak a little bit about the basics.
Series Outline
As mentioned, there will be five parts in this series. They are as follows:- PL/SQL Cursors Basics
- PL/SQL Explicit Cursors
- PL/SQL Implicit Cursors
- PL/SQL REF Cursors
- PL/SQL Dynamic SQL Cursors
Alrighty then, let’s get started with part 1.
What is a cursor?
When an SQL statement is executed from within PL/SQL, Oracle will automatically assign a specific SQL/work area in the SGA (System Global Area). This private work area contains information about the SQL that was executed, as well as the result set that was the result of the SQL execution.Depending on the type of cursor you use, you can name the cursor after your liking. The Cursor operates as a pointer to a temporary work area in the SGA, and will help you out on performance if you choose to i.e. manipulate the data in the cursor – meaning that Oracle knows what the resultset looks like, so if you re-use it, it will not be re-executed. It is cached in the SGA.
What is a resultset?
The data returned back from the execution of the SELECT statement in the cursor is called the “resultset“.If you would like to visualize a resultset in the SGA, try to think about it as a huge
dresser,
with one drawer per record in the resultset. Each time you move from
one record to the next in the resultset, a new drawer is opened, and the
previous is closed. The open drawer is called the active or current
record, and is kept track of by Oracle in the SGA shared memory area.The resultset can be thought about as a virtual table, since a resultset contains columns, and records, just like a table would have. The difference is that the records in a resultset can have been put together by the result from many different tables, depending on what your SQL looks like.
NOTE: It is only possible to traverse FORWARDS in a SQL cursor in Oracle (and probably most SQL based databases). If you NEED to traverse backwards, an idea can i.e. be to upload your resultset to a PL/SQL collection, and then loop both ways.
Cursor / query operations
When the SQL in a cursor is executed, a certain set of operations are executed. This is performed no matter what kind of cursor you are using. In some cases, like with EXPLICIT cursors, you yourself are executing these steps.- Parse: The SQL statement is validated, and the execution plan is determined according to if your database is set up with a cost-based, or rule-based optimization
- Bind:The fields in your SQL statement are associated with the variables that you have defined in your PL/SQL code.Depending on the type of cursor used, PL/SQL will automatically take care of the binding.
- Open: When a cursor is opened, the resultset of the SQL in your cursor is determined based on the bind variables. When the resultset has been determined, the “current row” is set to the first record in the result set.Depending on the type of cursor used, PL/SQL will automatically take open the cursor for you. I.e. when working with IMPLICIT cursors, or with DYNAMIC SQL Cursors.
- Execute: The SQL statement in the cursor is executed, and the the resultset will be established.
- Fetch: When performing a fetch, the “current row” pointer is set to the next record in the resultset, and this record will be fetched into your PL/SQL block .In most cases, a fetch will fetch the next record, but in the case of “bulk processing” or the use of BULK COLLECT, multiple records will be returned from the resultset at a time.
- Close: In most cased you will close the cursor yourself in your PL/SQL code to release the memory used by the cursor, but in other cases Oracle (or the PL/SQL Engine) will take care of this itself.
End of part 1
This concludes part # 1 in the “PL/SQL Cursors” series. By now we have a basic understanding of what a PL/SQL cursor is, how it is operated, and what is returned to our PL/SQL code through the resultset.In the next part we are going to look at EXPLICIT cursors. We are also going to discuss the different attributes associated with the EXPLICIT cursors.
No comments :
Post a Comment