PL/SQL Explicit Cursor with Examples


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.

There are four steps in using an Explicit Cursor.

1)       DECLARE the cursor in the declaration section.
2)       OPEN the cursor in the Execution Section.
3)       FETCH the data from cursor into PL/SQL variables or records in the Execution      Section.
4)       CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

These are the three steps in accessing the cursor.
1)   Open the cursor.
2)
  Fetch the records in the cursor one at a time.
3)
  Close the cursor. 



Example: Fetch the Records in the Cursor




Example: Using Simple Loop



Example: Using WHILE Loop



Example: Using FOR Loop




Deletion or Updation Using Cursor:

In all the previous examples I show how to retrieve data using cursors. Now the next example is relate to modify or delete rows in a table using cursors. In order to Update or Delete rows, the cursor must be defined with the FOR UPDATE clause. The Update or Delete statement must be declared with WHERE CURRENT OF



Comments

  1. You have made an awesome blog that contains a lot of information on Oracle and Pl/Sql. You do have even provided a complete set of articles containing all about inbuilt functions with their syntax and usage. I will keep visiting it more frequently to learn as much about Pl/Sql. Keep sharing more.

    ReplyDelete
  2. You have made an awesome blog that contains a lot of information on Oracle and Pl/Sql. You do have even provided a complete set of articles containing all about inbuilt functions with their syntax and usage. I will keep visiting it more frequently to learn as much about Pl/Sql. Keep sharing more.

    ReplyDelete
  3. You have made an awesome blog that contains a lot of information on Oracle and Pl/Sql. You do have even provided a complete set of articles containing all about inbuilt functions with their syntax and usage. I will keep visiting it more frequently to learn as much about Pl/Sql. Keep sharing more.
    sap erp financials

    ReplyDelete

Post a Comment

Popular posts from this blog

What is Oracle Integration Cloud Service - ICS?

How to Create Packages in Oracle Database using TOAD for Oracle

How to create a Simple Scheduler Job in Oracle Database using Toad