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:
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);
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.
EmoticonEmoticon