STUDENT MARK ANALYSIS USING CURSOR



TABLE CREATION:

SQL>CREATE TABLE  STU(STUNO NUMBER(10),STUNAME  VARCHAR(15),STUSTATUS VARCHAR(10));

INSERTION:

SQL>INSERT INTO STU VALUES(&STUNO,’&STUNAME’,’&STUSTATUS’);
ENTER VALUE FOR STUNO:5035
ENTER VALUE FOR STUNAME:JEBASTIN
ENTER VALUE FOR STUSTATUS: FIRST
Old 1: INSERT INTO STU VALUES(&STUNO,’&STUNAME’,’&STUSTATUS’);
New 1: INSERT INTO STU VALUES(5035,’JEBASTIN’,’FIRST’)

1 row created.

SQL>/
ENTER VALUE FOR STUNO:5019
ENTER VALUE FOR STUNAME:DAVID
ENTER VALUE FOR STUSTATUS: DISTINCT
Old 2: INSERT INTO STU VALUES(&STUNO,’&STUNAME’,’&STUSTATUS’);
New 2: INSERT INTO STU VALUES(5019,’DAVID,’DISTINCT’)

1 row created

SQL>/
ENTER VALUE FOR STUNO:5024
ENTER VALUE FOR STUNAME:GANESH
ENTER VALUE FOR STUSTATUS: FIRST
Old 3: INSERT INTO STU VALUES(&STUNO,’&STUNAME’,’&STUSTATUS’);
New 3: INSERT INTO STU VALUES(5024,’ GANESH,’FIRST’)

1 row created

CODING:

DECLARE
STUNUMBER STU.STUNO% TYPE;
STUNAM STU.STUNAME% TYPE;
STUSTAT STU.STUSTATUS% TYPE;
CURSOR C IS SELECT * FROM  cursor;
BEGIN
OPEN C;
FETCH C INTO STUNUMBER,STUNAM,STUSTAT;
IF C% FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘DATA FOUND INSIDE CURSOR’);
ELSE
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
END IF;
CLOSE C;
IF C% IS OPEN THEN
DBMS_OUTPUT.PUT_LINE('CURSOR OPEN');
ELSE
DBMS_OUT.PUT_LINE('CURSOR CLOSED');
END IF;
OPEN C;
DBMS_OUTPUT.PUT_LINE('NO OF ROWS ARE:'||C% ROW COUNT);
LOOP
FETCH C INTO STUNUMBER,STUNAM,STUSTAT;
EXIT WHEN C% NOT FOUND;
DBMS_OUTPUT.PUT_LINE(STUNUMBER||''||STUNAME||''||STUSTAT);
END LOOP;
DBMS_OUTPUT.PUT_LINE('NO OF ROWS ARE;'||C%ROW COUNT);
END;

OUTPUT:

SQL>@cursor.sql
33 /
DATA FOUND INSIDE CURSOR
CURSOR OPEN
CURSOR CLOSED
NUMBER OF ROWS ARE:0

5035   JEBASTIN              FIRST                
5019   DAVID                   DISTINCT
5024   GANESH                FIRST

NUMBER OF ROWS ARE:3

PL/SQL procedure successfully completed.

Previous
Next Post »

Still not found what you are looking for? Try again here.