Looking for a Tutor Near You?

Post Learning Requirement »
x

Choose Country Code

x

Direction

x

Ask a Question

x

x
x
For better view click here
x
Hire a Tutor
DISCOVER

ORACLE PLSQL - Cursors

Loading...

Published in: Oracle Training
1,308 Views

This document discuss about the cursors in PL/SQL.

Debarun S / Kolkata

10 years of teaching experience

Qualification: B.Tech

Teaches: Computer, Mathematics, Science, Chemistry, Computer Science, Physics, School Level Computer, Defence Exams, IBPS, Insurance Exams, SSC Exams

Contact this Tutor
  1. In this chapter, we will discuss the cursors in PL/SQL. Oracle creates a memory area, known as the context area, for processing an SQL statement, which contains all the information needed for processing the statement; for example, the number of rows processed, etc. A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set. You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors Implicit cursors Explicit cursors Implicit Cursors Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it. Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected. In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, and designed for use with the FORALL statement. The following table provides the description of the most used attributes S.N0 %FOUND Attribute & Description 1 2 3 Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE. % NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE. % ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
  2. % ROWCOUNT 4 Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. Any SQL cursor attribute will be accessed as sql as shown below in the example. Example We will be using the CUSTOMERS table we had created and used in the previous chapters. Select 1 2 3 4 5 6 * from customers; NAME Ramesh Khilan kaushik Cha itali Hardik Komai I AGE 32 25 23 25 27 22 ADDRESS Ahmedabad Delhi Kota Mumb a i Bhopal MP SALARY 2000.00 1500.00 2000.00 6500.00 8500.00 4500.00 The following program will update the table and increase the salary of each customer by 500 and use the SQL %ROWCOUNT attribute to determine the number of rows affected DECLARE total rows number (2) BEGIN UPDATE cus tome rs SET salary salary + 500; IF sql%notfound THEN dbms output. put line( 'no customers selected' ) ELSIE sq] 00 found THEN total rows sql%rowcount; dbms output . put line( total rows END IF; END; customers selected When the above code is executed at the SQL prompt, it produces the following result 6 customers selected P L/ SQL procedure successfully completed. If you check the records in customers table, you will find that the rows have been updated Select * from customers;
  3. 1 2 3 4 5 6 NAME Ramesh Khilan kaushik Cha itali Hardik Komai AGE 32 25 23 25 27 22 ADDRESS Ahmedabad Delhi Kota Mumb a i Bhopal MP SALARY 2500.00 2000.00 2500.00 7000.00 9000.00 5000.00 Explicit Cursors Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. The syntax for creating an explicit cursor is CURSOR cursor name IS select statement; Working with an explicit cursor includes the following steps Declaring the cursor for initializing the memory Opening the cursor for allocating the memory Fetching the cursor for retrieving the data Closing the cursor to release the allocated memory Declaring the Cursor Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example CURSOR c customers IS SELECT i d, name, address FROM customers ; Opening the Cursor Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open the above defined cursor as follows OPEN c customers; Fetching the Cursor
  4. Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above-opened cursor as follows FETCH c customers INTO c i d, c name, Closing the Cursor c addr; Closing the cursor means releasing the allocated memory. For example, we will close the above- opened cursor as follows CLOSE c customers; Example Following is a complete example to illustrate the concepts of explicit cursors &minua; DECLARE c id customers . i d % type; c name customerS . No . ame%type, c addr customers . address%type, CURSOR c customers is SELECT i d, name, address FROM customers ; BEGIN OPEN c customers; LOOP FETCH c customers into c i d, c name, EXIT WHEN c customers%notfound; dbms output. put line (c id I I END LOOP; CLOSE c customers; END; c addr ; c name c addr) When the above code is executed at the SQL prompt, it produces the following result 1 2 3 4 5 6 Ramesh Ahmedabad Khilan Delhi kaushik Kota Cha itali Mumbai Hardik Bhopal Komai MP P L/ SQL procedure successfully completed.

Need a Tutor or Coaching Class?

Post an enquiry and get instant responses from qualified and experienced tutors.

Post Requirement

Related Study Notes

Upload Study Notes

If you have your own Study Notes which you think can benefit others, please upload on LearnPick. For each approved Study Note you will get 100 Credit Points and 100 Activity Score which will increase your profile visibility.

Upload Now
Home > Study Notes > ORACLE PLSQL - Cursors