pl-sql
  1. pl-sql-cursor
PreviousNext

Cursor - (PL/SQL Cursor)

A cursor is a read-only pointer to a set of rows returned by a SQL statement. PL/SQL provides a way to retrieve and manipulate data using cursors.

Syntax

The basic syntax for defining a cursor in PL/SQL is as follows:

CURSOR cursor_name IS
   SELECT column_name(s)
   FROM table_name
   WHERE condition;

Here, cursor_name is the name of the cursor, column_name(s) are the name(s) of the column(s) to be selected, table_name is the name of the table to select the data from, and condition is an optional condition to filter the rows.

Example

In the following example, we define a cursor to retrieve data from the employees table:

DECLARE
   CURSOR c_emp IS
      SELECT employee_id, first_name, last_name
      FROM employees
      WHERE department_id = 50;
   emp_record c_emp%ROWTYPE;
BEGIN
   OPEN c_emp;
   LOOP
      FETCH c_emp INTO emp_record;
      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(emp_record.first_name || ' ' ||
                           emp_record.last_name);
   END LOOP;
   CLOSE c_emp;
END;

Output

The output of the above example will be a list of first and last names of the employees whose department_id is 50.

Explanation

In the above example, we define a cursor c_emp to select the employee_id, first_name, and last_name columns from the employees table where the department_id is 50. We then declare a record type emp_record with the same column names and data types as the columns selected in the cursor.

We use the OPEN keyword to open the cursor and the LOOP keyword to loop through the result set. We use the FETCH keyword to fetch the next row from the cursor and store it in the emp_record variable. We use the EXIT keyword to exit the loop when there are no more rows to fetch.

We use the CLOSE keyword to close the cursor when we are done with it.

Use

Cursors are useful for retrieving and manipulating data in PL/SQL. They allow us to iterate through the result set of a query and perform operations on each row. Cursors can be used in various scenarios like processing batches of data, updating data, and generating reports.

Important Points

  • A cursor is a read-only pointer to a set of rows returned by a SQL statement.
  • PL/SQL provides a way to retrieve and manipulate data using cursors.
  • Cursors are declared using the CURSOR keyword and must be opened before use.
  • Cursors can be used to iterate through the result set of a query and perform operations on each row.

Summary

In summary, a cursor is a powerful tool in PL/SQL that allows us to retrieve and manipulate data. Cursors provide a way to iterate through the result set of a query and perform operations on each row. Cursors can be used in various scenarios like processing batches of data, updating data, and generating reports. Cursors are an important feature that every PL/SQL developer should master.

FunctionException
Published on: