Two Types of PLSQL Cursors: What are those?

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.

PLSQL Cursors

• 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.

Cursor variables

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

Parse

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).

Bind

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.

Open

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).

Execute

In the execute phase, the statement is run within the SQL engine.

Fetch

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).

Close

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;
/

Related Posts