ISSN: 23943122 (Online)
Volume 1, Issue 1, November 2014
SK International Journal of Multidisciplinary Research Hub
Research Article / Survey Paper / Case Study Published By: SK Publisher
Cursor Management in PL/SQL
Dr. Laxmi Joshi IT Department
Computer Science & Information Technology Majmaah University
Majmaah – Saudi Arabia
Abstract:
To proficiently process the data available in oracle database first it should be brought into memory. Cursors are memory buffers in PL/SQL, or they can be called temporary tables in the memory. They are mainly used to perform the operation on set of rows using PL/SQL.
The work area allocated by oracle to process set of rows retrieved by SQL is called CURSOR. The data stored in cursor is called an Active Data Set (Result Set).The row that is being processed is called current row. While processing cursor, data will be transferred to the client via network in client server environment.
Keywords: Cursor, Implicit, Explicit.
I. INTRODUCTION
The oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL’s operations and called cursor.
The data that is stored in the cursor is called the Active Data Set. Conceptually, the size of the cursor in memory is the size required to hold the number of rows in the Active Data Set. The actual size, however, is determined by the oracle engines built in memory management capabilities and the amount of RAM available.
The values retrieved from a table are held in a cursor opened in memory by the oracle engine. This data is then transferred to the client machine via the network.in order to hold this data; a cursor is opened at the client end. If the numbers of rows returned by the oracle engine is more than the area available in the cursor opened on the client, the cursor data and the retrieved data is swapped between the operating system swap area and RAM.
There are two types of cursors: 1) Implicit and 2) Explicit. Oracle implicitly declares a cursor to all the DDL and DML commands that return only one row. If you want to retrieve multiples rows then create explicitly cursor with the using queries.
II. TYPES OF CURSORS
Cursors are classified depending on the situations under which they are opened. If the oracle engine opened a cursor for its internal processing it is known as implicit 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 explicit cursor.
1. Implicit Cursor
PL/SQL declares an implicit cursor for all DML Commands and queries that return a single row. Implicit cursor is opened and managed by oracle engine internally. It includes the function of reserving an area in memory, populating this area and releasing the memory area when the processing is complete. The result data is then passed to the client through the network.
Volume 1, Issue 1, November 2014 pg. 11-16 Implicit cursors attributes can be used to access information about the status of last insert, update and delete or single row select statement. This can be done by preceding the implicit cursors attributes with the cursor name SQL. The value of the cursor’s attributes always refer to the SQL cursor, the attributes of the implicit cursor will have NULL values in their fields.
Implicit Cursor Processing in client server Environment
Fig. Client Server and Cursor Processing
Implicit Cursor Attributes 1) SQL%ISOPEN
It is used to check whether a cursor is opened or not. The oracle engine automatically opens and closes the SQL cursor after executing its associated select, insert, update or delete. Thus SQL%ISOPEN attribute of an implicit cursor cannot be referenced outside of SQL statement. As a result, SQL%iSOPEN always evaluates to False.
2) SQL%FOUND
The attribute %found contains the value NULL until a DML command is executed. From then on, it returns true when a DML command affects one or more rows, or SELECT INTO does not return any row.
Delete from Student where Stud_No = Student;
If SQL%FOUND then – Successful
Insert into new_student values(func,my_sname,…);
3) SQL%NOTFOUND
%NOTFOUND is the opposite of % Found, and return True when an insert, Update or delete command does not affect any row, or when SELECT INTO does not return any row.
4) SQL%ROWCOUNT
%ROWCOUNT returns the number of rows affected by the insert, update, delete or select into commands. It returns zero when the commands do not affect or return any row.
Volume 1, Issue 1, November 2014 pg. 11-16 Delete from Student where…
If SQL%ROWCOUNT >5 then
…..Commands….
END IF;
example Declare No number(4);
sname student.name%type;
sdob student.dob%type;
scity student.city%type;
begin
select name,dob,city into sname,sdob,scity from student where rollno=&no;
dbms_output.put_line(sname||’ ’||sdob||’ ’||scity);
end;
2. Explicit Cursor
When a set of records in a table have to be processed inside a PL/SQL block, an explicit cursor is used. This cursor will be declared and linked to a SQL query in declaration section of PL/SQL block and used within the executable section. A cursor so created and used is known as explicit cursor. The explicit cursor is declared, named and managed by programmer. The oracle engine does not automatically manage it.
Explicit Cursor Management
Declare the cursor in declaration section
Open the cursor using OPEN statement
Fetch rows from the cursor using FETCH statement
Close the cursor after the process is completed using CLOSE statement.a) Declaring a Cursor
A cursor is declared in declaration section using the keyword CURSOR. Along with it appropriate select statement is also specified to process a set of data.
Syntax Cursor <Cursor_Name> is <select statement>
Example Cursor k_stud is select * from student where city= ‘bhavnagar’;
Explanation Here, above statement will declared cursor k_stud.which contain data selected by query.
Volume 1, Issue 1, November 2014 pg. 11-16 b) Opening a Cursor
Before using a cursor, it is necessary to open the cursor. Opening a cursor executes the query and creates the active set that contains all rows, which meet the query search criteria. An open statement retrieves record from a database table and places the records in the cursor. A cursor is opened in the server memory.
Syntax Open cursor_name[<input parameters>]
Example Open k_stud;
Explanation Above statement open a cursor k_stud.The input parameter is optional clause which will be used in case of parameterized cursor only.
c) Fetching rows from a cursor
Once a cursor is OPEN, the Fetch statement retrieves the rows from the active set opened in the server into memory variable declared in the PL/SQL code block on the client one row at a time. The memory variables are opened on the client machine. Each time fetch is executed, the cursor pointer is advanced to the next row in the Active Data Set.
Syntax FETCH cursor_name into Variable_1,variable2,……;
Example FETCH k_stud into srollno, sname,sdob,scity;
Explanation Above statement will fetch current row data from cursor and copy data value into specified memory variables.
d) Closing Cursor
Once all data are processed successfully, you can close the cursor using close statement. The close statement will release the memory occupied by the cursor and its data set both on the client and server.
Syntax Close cursor_name
Example Close k_stud;
Explanation Above statement will close the cursor k_stud.
Volume 1, Issue 1, November 2014 pg. 11-16 Explicit Cursor Attributes
To manipulate the rows you need to use some attributes of the explicit cursor. These attributes return a value referring to the execution of a multiline query.
1. %ISOPEN
%ISOPEN is TRUE when the cursor or variable is opened. Otherwise, it returns FALSE.
2. %FOUND
After the opening and before the first fetch, the value of %FOUND is NULL .From then on,it returns TRUE when a DML command affects one or more rows, or SELECT INTO when it does not return any row.
3. %NOTFOUND
%NOTFOUND returns TRUE when the last row of the cursor is processed and no other row is available .this is the same as finding the end of the file. Before the first FETCH command, %NOTFOUND returns NULL.
4. %ROWCOUNT
%rowcount returns the total number of rows returned by the FETCH command. Each time a FETCH command is executed, %ROWCOUNT is increased ny 1.when the cursor is opened, %ROWCOUNT is zeroed.
Example Declare
Cursor k_stud is select rollno,name,city from student;
m_stud k_stud%rowtype;
begin open k_stud;
loop
fetch k_stud into m_stud;
exit when k_stud%notfound;
dbms_output.put_line(m_stud.rollno|| ‘ ‘ ||m_stud.name || ‘ ‘ ||m_stud.city);
III. CONCLUSION
Cursor management concepts are supported by the PL/SQL. In PL/SQL provided two types of cursor. Implicit cursor is use for only one row that means this is disadvantage of implicit cursor. This disadvantage overcome by the explicit cursor that works for multiple rows but explicit cursor is not managed by automatically instead of implicit cursor is managed by oracle. In both the compared that the explicit cursor is strong then implicit cursor that’s reason is that explicit cursor uses the queries that return multiple rows.
Volume 1, Issue 1, November 2014 pg. 11-16 References
Books:
1. The programming Language of ORACLE -4th Edition, Ivan Bayross, BPB Publication.
2. Learn Oracle 8i,Jose a. Ramalho, BPB Publication.
3. Information Technology,Compiled by prof.Sonal Mehta, Aagam Publishers Pvt. Ltd. Ahmedabad.
4. Database Concepts SQL & PL/SQL Programming, 1st edition, Authors – Mr. K. U. Gundigara, Ms. V. H. Mehta.