Here’re the two types of cursors in PL/SQL. We have lots of options in PL/SQL for executing SQL, and all of them occur as some type of cursor.
- Two types of cusors possible in PL/SQL; those are static and dynamic.
- SQL is static if the content of the SQL statement is determined at compile time. A SQL statement is dynamic if it is constructed at runtime and then executed.
- Dynamic SQL is made possible in PL/SQL only through the use of the DBMS_SQL built-in package. All other forms of SQL executed inside a PL/SQL program represent static SQL.
- Even within the category of static SQL, we have further differentiation. Those are implicit and explicit
Static cursor objects
These are the really static cursors of PL/SQL. The SQL is determined at compile-time, and the cursor always refers to one SQL statement, which is known at compile time.
We can declare a variable, which references a cursor object in the database. Our variable may refer to different SQL statements at different times (but that SQL is defined at compile-time, not run time).
The cursor variable is one of the newest enhancements to PL/SQL. Cursor variables act as references to cursor objects.
A cursor variable can change its value as our program executes. The variable can refer to different cursor objects (queries) at different times. We can also pass a cursor variable as a parameter to a procedure or function.
The static cursor itself comes in two types: implicit and explicit.
PL/SQL declares and manages an implicit cursor every time we execute a SQL DML statement, such as an INSERT or a SELECT that returns a single row.
We can define our own explicit cursors in our code. We must use an explicit cursor when we need to retrieve more than one row of data at a time through a SELECT statement.
We can then use the cursor to fetch these rows one at a time. The set of rows returned by the query associated with an explicit cursor is called the active set or result set of the cursor.
The row to which the explicit cursor points is called the current row of the result set.
Life Cycle of Cursors
The first step in processing an SQL statement is to parse it to make sure it is valid and to determine the execution plan (using either the rule-based or cost-based optimizer).
When we bind, we associate values from the program (host variables) with placeholders inside the SQL statement. For static SQL, the SQL engine itself performs these binds.
When we use dynamic SQL, we explicitly request the binding of variable values.
When we open a cursor, the bind variables are used to determine the result set for the SQL statement. The pointer to the active or current row is set to the first row.
Sometimes we will not explicitly open a cursor; instead, the PL/SQL engine will perform this operation for us (as with implicit cursors).
If you are performing a query, the FETCH command retrieves the next row from the cursor’s result set. Each time you fetch, PL/SQL moves the pointer forward in the result set.
When working with explicit cursors, remember that if there are no more rows to retrieve, then FETCH does nothing (it does not raise an error).
The CLOSE statement closes the cursor and releases all memory used by the cursor. Once closed, the cursor no longer has a result set.
Sometimes you will not explicitly close a cursor; instead, the PL/SQL engine will perform this operation for you (as with implicit cursors).
Static: implicit cursor
Here, we are updating all the salaries of the employee table. This is the implicit cursor.
UPDATE employee SET salary = salary * 1.1;
Static: explict cursor
When you declare a cursor explicitly, you can say it as an explicit cursor.
DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; the_name employees.last_name%TYPE; the_salary employees.salary%TYPE; BEGIN IF c1%ISOPEN = FALSE THEN -- cursor was not already open OPEN c1; END IF; FETCH c1 INTO the_name, the_salary; CLOSE c1; END; /