A PL/SQL package is a group of related subprograms. These subprograms or packages can be called explicitly by users or applications. PL/SQL blocks can include various PL/SQL language constructs. These constructs include variables, constants, cursors, and exceptions.

Oracle Cursos explained

If you are coming from a programming background, then you must be familiar with terminologies like variables, constants, and exceptions but what is cursor?

A cursor is a handle or a name for an area in memory in which parsed statements and related information are kept. It is like a pointer that points to the result of the query being executed.
Basically, it retrieves data, one row at a time, from the result set, unlike the SQL commands which works on all rows in the result set. The data store in a cursor is called the Active Data Set.

PL/SQL has two types of cursors depending on the circumstance in which they were created.

  • Implicit cursors
  • Explicit cursors

Implicit Cursors

Oracle automatically creates implicit cursors when there is no explicit cursor for the statement. It also manages the whole execution of implicit cursors internally and reveals only the cursor’s information which makes it impossible for programmers to control it.

When you execute an SQL statement, such as Select into, Insert, Update or Delete, it creates an implicit cursor. For Insert statements, the cursor holds the data that needs to be inserted. For update and delete statements, the cursor points to the rows that would be affected.

In Oracle, the most recent implicit cursor is known as SQL cursor, which has attributes such as %found, %isopen, %notfound, and %rowcount. You can access the SQL cursor attribute using sql%attribute_name.

Let’s say you have an employee table with columns like id, name, join_date, gender, dept, and salary. If you perform an update operation on column salary to increase the salary of each an employee, sql%rowcount attribute will return the number of rows being affected.

DECLARE  
   total_rows number(10); 
BEGIN 
   UPDATE employees 
   SET salary = salary + 1000; 
   IF sql%notfound THEN 
      dbms_output.put_line('No Employee Being Selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' Employees Selected '); 
   END IF;  
END;

The Implicit cursor cannot handle large or empty data set which causes too_many_rows or no_data_found exception respectively.

Implicit Cursor Attributes

%FOUND – It will return true if an insert, update or delete statement affect one or more rows. Otherwise, it will return false.

%NOTFOUND – It is the logical opposite of %FOUND.

%ISOPEN – Oracle manages implicit cursor internally, that is why it will always return false for the Implicit cursor.

%ROWCOUNT – It returns the total number of rows being affected by an Insert, Update or Delete statement or returned by a Select Into statement.

Explicit Cursors

An explicit cursor is defined in the declaration statement of the PL/SQL. Usually, it is created on a select statement which returns more than one row. Declaring cursors explicitly gives more control over its execution cycle.

The general syntax of cursor execution cycle:

DECLARE variables;
 records;
 create a cursor;
 BEGIN 
OPEN cursor; 
FETCH cursor;
 process the records;
 CLOSE cursor; 
END;

Cursor Execution Cycle

Cursor Execution Cycle has four simple steps

  • Declare
  • Open
  • Fetch
  • Close

Let’s focus on each step in detail.

Declaring a cursor

Before using a cursor, first, you must declare it as follows:

CURSOR cursor_name IS query;

A real-world example would be something like this:

CURSOR cursor_employees IS select id, name, dept, salary from employees;

Opening a cursor

To start fetching rows from the cursor, you need to open it first.

OPEN cursor_name;

When you open a cursor, Oracle parses the query, binds variables and executes the SQL statement. Oracle also defines an execution plan, cursor parameters and any associates host variables required for the execution of the result set. By default, the cursor is set to the first row in the result set.

Fetching from a cursor

The fetch statement would extract the content and place it into variables. The syntax of the fetch statement is as follows:

FETCH cursor_name INTO variable_list;

A cursor will retrieve data row by row. To get all rows in a result set, you need to fetch each row till the last one.

Closing a cursor

After you are done with data retrieval, you need to close the cursor.

CLOSE cursor_name;

After you close the cursor, Oracle will release the memory allocated to the cursor. If you have declared a cursor in a function block, the cursor will automatically close at the termination of the block. However, you must explicitly close package-based cursors. If you close an un-opened cursor by mistake, Oracle will through an invalid_cursor exception.

Example

DECLARE 
   c_id employees.id%type; 
   c_name employees.name%type; 
   CURSOR c_employees is 
      SELECT id, name FROM employees; 
BEGIN 
   OPEN c_employees; 
   LOOP 
   FETCH c_ employees into c_id, c_name; 
      EXIT WHEN c_ employees %notfound; 
      dbms_output.put_line(c_id || ' ' || c_name); 
   END LOOP; 
   CLOSE c_ employees; 
END;

Related Articles

How to reverse an Array in C

Functional Programming in C++

Export data from SQL Server database to Text file in using C#

Last modified: February 26, 2019

Comments

Write a Reply or Comment

Your email address will not be published.