Skip to content

Blocks in PL/SQL

Cursors in PL/SQL

Last Updated : 17 May, 2024

A Cursor in PL/SQL is a pointer to a context area that stores the result set of a query.

PL/SQL Cursors

The cursor is used to retrieve data one row at a time from the results set, unlike other SQL commands that operate on all rows at once.

Cursors update table records in a singleton or row-by-row manner.

The Data that is stored in the Cursor is called the Active Data Set. Oracle DBMS has another predefined area in the main memory Set, within which the cursors are opened. Hence the size of the cursor is limited by the size of this pre-defined area.

cursor function

Cursor Actions

Key actions involved in working with cursors in PL/SQL are:

Declare Cursor: A cursor is declared by defining the SQL statement that returns a result set.
Open: A Cursor is opened and populated by executing the SQL statement defined by the cursor.
Fetch: When the cursor is opened, rows can be fetched from the cursor one by one or in a block to perform data manipulation.
Close: After data manipulation, close the cursor explicitly.
Deallocate: Finally, delete the cursor definition and release all the system resources associated with the cursor.

Types of Cursors in PL/SQL

Cursors are classified depending on the circumstances in which they are opened.

Implicit Cursor: If the Oracle engine opened a cursor for its internal processing it is known as an Implicit Cursor. It is created “automatically” for the user by Oracle when a query is executed and is simpler to code.
Explicit Cursor: A Cursor can also be opened for processing data through a PL/SQL block, on demand. Such a user-defined cursor is known as an Explicit Cursor.

Explicit cursor

An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

Syntax for creating cursor

CURSOR cursor_name IS select_statement;

Where,

cursor_name: A suitable name for the cursor.
select_statement: A select query which returns multiple rows

How to use Explicit Cursor?

There are four steps in using an Explicit Cursor.

DECLARE the cursor in the Declaration section.
OPEN the cursor in the Execution Section.
FETCH the data from the cursor into PL/SQL variables or records in the Execution Section.
CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

Syntax

General Syntax of using an explicit cursor in PL/SQL is:

DECLARE
  variables;
  records;
  CURSOR cursor_name IS select_statement;
BEGIN
  OPEN cursor_name;
  LOOP
    FETCH cursor_name INTO variables OR records;
    EXIT WHEN cursor_name%NOTFOUND;
    
    process the records;
  END LOOP;
  CLOSE cursor_name;
END;

Blocks in PL/SQL

A

akash sharan 2

Improve

Article Tags :

Similar Reads

What is Cursor in SQL ?

When working with SQL, most operations are performed on entire sets of data. But what if we need to process each row individually maybe to perform some custom logic or apply conditions row-by-row? Cursors come into play in such scenarios, providing a way to process each row individually. This articl

Blocks in PL/SQL

In PL/SQL, All statements are classified into units that is called Blocks. PL/SQL blocks can include variables, SQL statements, loops, constants, conditional statements and exception handling. Blocks can also build a function or a procedure or a package. The Declaration section: Code block start wit

PLSQL | COS Function

The PLSQL COS function is used to return the cosine of a numeric value. The COS function accepts one parameter which is the number whose cosine needs to be calculated. The COS function returns a value of the numeric data type. This function takes as an argument any numeric data type as well as any n

PLSQL | CHR Function

The string in PL/SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. The CHR Function in PLSQL is the opposite of the ASCII function and is used to return the character based on the

Decision Making in PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle's extension to SQL that allows for procedural programming within databases. It features various conditional statements to control the flow of execution based on specific conditions. In this article, We will learn about the various PL/S

PLSQL | COMPOSE Function

The string in PL/SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. The Compose Function in PLSQL is used to return a Unicode string. The unistring values that can be combined with

PLSQL | CONVERT Function

The string in PL/SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. The CONVERT function in PLSQL is used to convert a string from one character set to another. Generally, the dest

PL/SQL Introduction

PL/SQL (Procedural Language/Structured Query Language) is a block-structured language developed by Oracle that allows developers to combine the power of SQL with procedural programming constructs. The PL/SQL language enables efficient data manipulation and control-flow logic, all within the Oracle D

PL/SQL Functions

PL/SQL functions are reusable blocks of code that can be used to perform specific tasks. They are similar to procedures but must always return a value. A function in PL/SQL contains:Function Header: The function header includes the function name and an optional parameter list. It is the first part o

PLSQL | ASCIISTR Function

The string in PL/SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. The ASCIISTR Function in PLSQL is used for converting a string in any character set to an ASCII string using the