RDBMS - Cursor implicit

RDBMS - Cursor implicit

Cursors:
->a cursor is a pointer / a reference to some memory location in Oracle. ???
->two types of cursors:
->implicit cursors (predefined cursors)
->explicit cursors (user defined cursors)

Point:
->whenever we execute a DML statement, the number of rows affected gets generated at the db.

Implicit Cursors:
--------------
->auto created by Oracle
->Types:
1. %FOUND
->returns TRUE if a DML statement has affected 1 or more rows
->for all other cases, retuns FALSE
2. %NOTFOUND
->returns FALSE if a DML statement has affected 1 or more rows
->for all other cases, retuns TRUE
3. %ISOPEN
4. %ROWCOUNT
->returns the number of rows affected by a DML statement
->returns the number of rows returned by a SELECT INTO statement
-----------------------------------------------------------------------
Note: we must use the implicit cursors by prefixing the SQL attribute
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
------------------------------------------------------------------

eg1: create a PL/SQL code block to update the stream of a rollno entered by the user.

SET SERVEROUTPUT ON;
SET ECHO ON;
DECLARE
/*variable declaration*/
rl number(4);
str varchar2(3);
ra number(2);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter rollno. to update: ');
rl:=&rl;
DBMS_OUTPUT.PUT_LINE('Enter updated stream: ');
str:=&str;
/*Query*/
UPDATE stutab
SET stream=str
WHERE rollno=rl;
/*check if the DML statement has been executed or not*/
IF SQL%FOUND = TRUE THEN
/*rows have been affected by the DML query*/
/*get the no. of rows affected*/
ra := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('Record Updated. No. of rows affected: ' || ra);
ELSIF SQL%NOTFOUND = TRUE THEN
/*rows have not been affected by the DML query*/
DBMS_OUTPUT.PUT_LINE('Record Updation failed as rollno '||rl|| ' is not found.');
END IF;
END;
/

===================================================================

eg2: CREATE A PL/SQL CODE BLOCK TO GET THE NAME OF A ROLLNO ENTERED BY THE USER

SET SERVEROUTPUT ON;
SET ECHO ON;
DECLARE
/*variable declaration*/
rl number(4);
nm varchar2(25);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter rollno. to search: ');
rl:=&rl;
/*SELECT Query*/
SELECT name INTO nm
FROM STUTAB
WHERE ROLLNO=rl;
DBMS_OUTPUT.PUT_LINE('Name of Rollno '||rl||' is '||nm||' ROW COUNT: '||SQL%ROWCOUNT);
EXCEPTION
WHEN NO_DATA_FOUND THEN
/*display an error message*/
DBMS_OUTPUT.PUT_LINE('Rollno '||rl|| ' is not found.');
END;
/
====================================================