Lecture Notes Unit5 chapter19 Cursor in Pl/SQL

Description:
Welcome to the comprehensive guide on Relational Database Management System (RDBMS) concepts, tailored for final year B.Sc. Computer Science students affiliated with Alagappa University. This document covers fundamental principles and advanced topics in RDBMS, offering a structured approach to understanding databases in the context of modern computing. PDF content is prepared from the text book Learn Oracle 8I by JOSE A RAMALHO.

Key Topics Covered:

Main Topic : CURSOR
Sub-Topic :
Definition of a Cursor, creating a cursor, Declaring the cursor, Opening the cursor, Attributes of an explicit cursor, Attributes of an implicit cursor, Accessing the cursor rows, closing a cursor, steps for executing a cursor, Example program

Target Audience:

Final year B.Sc. Computer Science students at Alagappa University seeking a solid foundation in RDBMS principles for academic and practical applications.

URL for previous slides
Unit V
Chapter 18: https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter18-packages-pdf/270845026
Chapter 17: https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter17-stored-procedures-and-functions/270844763
Chapter 16 : https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter16-trigger-creation/270631080
Chapter 15 : https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter-15-pl-sql-programming/270383949

Unit IV

Chapter 14 Synonym : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter14_synonyms-pdf/270327685
Chapter 13 Users, Privileges : https://www.slideshare.net/slideshow/lecture-notes-unit4-chapter13-users-roles-and-privileges/270304806
Chapter 12 View : https://www.slideshare.net/slideshow/rdbms-lecture-notes-unit4-chapter12-view/270199683
Chapter 11 Sequence: https://www.slideshare.net/slideshow/sequnces-lecture_notes_unit4_chapter11_sequence/270134792
chapter 8,9 and 10 : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter_8_9_10_rdbms-for-the-students-affiliated-by-alagappa-university/270123800

About the Author:
Dr. S. Murugan is Associate Professor at Alagappa Government Arts College, Karaikudi. With 23 years of teaching experience in the field of Computer Science, Dr. S. Murugan has a passion for simplifying complex concepts in database management.

Disclaimer:
This document is intended for educational purposes only. The content presented here reflects the author’s understanding in the field of RDBMS as of 2024.

1. RDBMS -Unit V
Chapter 19
Cursor
Prepared By
Dr. S.Murugan, Associate Professor
Department of Computer Science,
AlagappaGovernment Arts College, Karaikudi.
(Affiliated by AlagappaUniversity)
Mailid: [email protected]
Reference Book:
LEARN ORACLE 8i, JOSE A RAMALHO

2. Cursor -Definition
➢Acursorisatemporaryworkareacreatedinsystem
memorywhenaSQLstatementisexecuted.
➢Byusingcursormechanism,wecanreturnmultiple
rowsfromthetableusingselectstatement.
➢ForstaticSQLcommands,therearetwotypesof
cursors:implicitandexplicit.
➢OracleimplicitlydeclaresacursortoalltheDDLand
DMLcommandsthatreturnonlyonerow.
➢Forqueries(select)thatreturnmultiplerows,you
havetoexplicitlycreateacursor.

3. Creating a Cursor
Creating a cursor involves four steps:
1.Declaring the cursor—the cursor receives a name and
is assigned a SELECT command.
2. Opening the cursor—the query is executed and the
number of rows to be returned is determined.
3. Fetching—the row that is found is sent to the program
in PL/SQL.
4. Closing the cursor—the Oracle resources allocated to
the cursor are freed.

4. Declaring the Cursor
The DECLARE command is used to declare a cursor.
Syntax:
DECLARE
CURSOR cursor_name[(parameter[, parameter]...)]
[RETURN return_type] IS SELECT COMMAND;
Arguments:
cursor_name
Name of the cursor to be created.
return_type
Represents a row or record in a table.

5. Declaring the Cursor
Example 1:
DECLARE
CURSOR prim01 IS SELECT empno, ename, job,
salFROM empWHERE deptno=30;
Example 2:
DECLARE
CURSOR seg02 RETURN dept%ROWTYPEIS
SELECT * FROM deptWHERE deptno= 20;

6. Opening a Cursor
➢Theopeningofthecursoristheoperationthat
performsthequeryandcreatestheresultset,whichis
thegroupofrowsthatmeetsthequerycondition.
➢AcursorisopenedwiththeOPENcommand:
➢OPENprim01;

7. Attributes of an Explicit Cursor
➢Whenacursororvariableisopened,therowsthat
satisfythequeryareidentifiedandformtheresultset.
Therowsoftheresultsetaredownloadedoneata
time.
➢%FOUNDreturnstruewhenDMLcommandsaffects
oneormultiplerows.
➢%ISOPEN returns TRUE when the cursor or variable
is opened. Otherwise, it returns FALSE.
➢%NOTFOUND returns TRUE when the last row of
the cursor is processedand no other row is available.
➢%ROWCOUNT returns the total number of rows
returned by the FETCH command. Each time a
FETCH command is executed, %ROWCOUNT is
increased by 1.

8. Attributes of an Implicit Cursor
Attributes of an Implicit Cursor
The attributes of an implicit cursor return information
about the execution of the INSERT, UPDATE, DELETE,
or SELECT INTO commands.
Example:
DELETE FROM empWHERE empno= emp;
IF SQL%FOUND THEN —successful
INSERT INTO new_empVALUES (func, my_ename,
...);

9. Accessing the Cursor Rows
➢AfterthecursorisopenedwiththeOPENcommand,
theselectedrowsareavailable.
➢PL/SQLusestheFETCHcommandtoreadthe
contentsofarow.
➢FETCHreadsthevalueofeachcolumnoftherowthat
isspecifiedbytheSELECTcommand,andassignsa
variable.Thiscanbeseenasanequivalenttothe
SELECTINTOcommand.
➢TheFETCHcommandrecoversonerowatatime
fromtheresultset.

10. Accessing the Cursor Rows -example
LOOP
FETCH display30 INTO t_ename, t_deptno, t_sal;
EXIT WHEN display30%notfound;
DBMS_OUTPUT.PUT_LINE(t_ENAME// ´´
//TO_CHAR(T_DEPTNO) //
´´/ / TO_CHAR (T_SAL)) ;
END LOOP;

11. Closing a Cursor
➢Whenacursorisnotinuse,itmustbeclosedso
Oraclecanfreetheresourcesthatwereallocatedtoit.
➢ThecommandresponsibleforthistaskisCLOSE:
Example:
➢CLOSEdisplay30

12. Steps for executing a Cursor
1.CreateanEmployeetable
createtableempl(namevarchar2(25),dnonumber(5),
salarynumber(7,2));
2.Insertfewrecordsandviewtherecord
3.TypetheProgramandexecutetheprogram
sql>editd:\agac\cursor1.sql
sql>@d:\agac\cursor1.sql
4.Executetheprocedure
SQL>execcursor1

13. Example Program
CREATE OR REPLACE PROCEDURE CUR1 IS
e_nameempl.name%type;
e_dnoempl.dno%type;
e_salaryempl.salary%type;
CURSOR dispempIS
SELECT NAME,DNO,SALARY FROM EMPL WHERE DNO=1002
ORDER BY NAME;
BEGIN
DBMS_OUTPUT.ENABLE;
/*open cursor*/
OPEN dispemp;
LOOP
FETCH dispempINTO e_name, e_dno, e_salary;
EXIT WHEN dispemp%notfound;
DBMS_OUTPUT.PUT_LINE( e_name|| '-'||TO_CHAR(e_dno) || '-'
||TO_CHAR (e_salary)) ;
END LOOP;
close dispemp;
end;
/

14. Example Program
1. A procedure called Cursor1, which displays the contents of
the Ename, Deptno, and Salary fields of the EMPL table.
2.Initially, three variables are created. They will contain the
contents of the fields. To do that, use the %type attribute
that creates it with the same data type as the field.
3. Then, the cursor is defined with the name Dispempand the
specified SELECT command.
4. Activate the output of data from the DBMS_OUTPUT
package, and then open the cursor.
5.A loop is executed until there are no more rows to be
processed.
6. The FETCHcommanddownloads the contents of the fields
to the variables.
7.Finally, the procedure displays the data: