PLSQL Cursors Session II Attributes TYPE ROWTYPE Found

PL/SQL Cursors Session - II

Attributes %TYPE %ROWTYPE % Found % Not. Found % Row. Count % Is. OPen

%TYPE useful when declaring variables that refers to Database Columns Name Varchar(15); Emp. Ename%TYPE;

%ROWTYPE Provides a record Type that represents a Row in a Table. ENAME SMITH JONES ADAMS KING DESIGNATION CLERK SALESMAN PRSIDENT MANAGER SAL 6000 4566 4567 4500

Name Varchar(15); Desig Varchar(15); Salary Number(8, 2); Emp_Rec Emp%ROWTYPE;

Declare Em. Rec Emp%Rowtype; Begin Select * INTO Em. Rec from emp where empno=7369; Dbms_Output. put_line( emrec. ename||emrec. Sal); End;

CURSORS It is a Temporary Table created in the SGA of the Server.

Two Types of CURSORS l. IMPLICIT l. EXPLICIT

IMPLICIT Cursor PL/SQL Implicitly declares a cursor for all SQL data Manipulation Statements, Including Queries that return only One Row

EXPLICIT Cursor Declares a cursor that process more than one row individually.

Using Explicit Cursors l. Declare the Cursor l. Open the Cursor l. Fetch the Cursor l. Close the Cursor

Declaring a Explicit Cursor While Declaring we have to name it and Associate with a Query. Declare CURSOR emp_cursor IS SELECT ename, deptno from emp where sal>2000; Begin

Opening a Explicit Cursor Opening the Cursor executes the SELECT statement and Identifies the active set. OPEN emp_curs;

Fetching a Explicit Cursor Fetch statement retrieves each row in the active set, one at a time. FETCH emp_cur INTO emp_name;

Closing a Explicit Cursor Close Statement disables the Cursor. CLOSE emp_curs;

DEFINE CURSOR C is Select Ename, deptno from emp ; C OPEN CURSOR OPEN C; ENAME SMITH JONES ADAMS DEPTNO 20 30 40

FETCHING CURSOR ENAME SMITH JONES ADAMS FETCH C INTO ENAM, DEPTN; CLOSING CURSOR CLOSE C; DEPTNO 20 30 40

Declare enam varchar(15); depno number(3); CURSOR C Is Select ename, deptno from emp where job=‘SALESMAN’; Begin OPEN C; LOOP FETCH C into enam, depno ; exit when c%notfound; dbms_output. put_line(enam||depno); END LOOP; Close C; End;

Explicit Cursor Attributes l. Every Explicit cursor has four attributes. l. Lets you access usefull information about the execution of a multirow query.

l%NOTFOUND l%ROWCOUNT l%ISOPEN

%NOTFOUND Evaluates to TRUE if the last FETCH failed because no more rows were available. Loop Fetch emp_cur INTO enam, depno; EXIT WHEN emp_cur %NOTFOUND; End loop;

l Is %FOUND Logical Opposite of the %NOTFOUND Loop Fetch emp_cur INTO enam, detpn; IF emp_cur%FOUND Then Dbms_output. Put_line(‘Record Found’); else exit; End if; End loop;

%ROWCOUNT Returns the number of rows Fetched

n: =&number; open emp_cur; loop Fetch emp_cur INTO enam, deptn; IF emp_cur%ROWCOUNT>n Then Dbms_output. Put_line('more than '||n||' records'); exit; End if; exit when emp_cur%notfound; End loop; Close emp_cur;

%ISOPEN Checks Cursor is Opened Loop Fetch emp_cur INTO enam, detpn; IF emp_cur%ISOPEN Then Dbms_output. Put_line(‘Opened’); End if; End loop;

Implicit Cursor Attributes l. Use cursor attributes to access the SQL% cursors context area. l%NOTFOUND , %FOUND l%ROWCOUNT

%NOTFOUND Evaluates True if any INSERT, UPDATE or DELETE affected no Rows. Update emp set ename=‘RAM’ where empno=3445; IF SQL%NOTFOUND then

Cursor For Loops It Implicitly OPENS a Cursor, FETCH each row returned by the query associated with Cursor and CLOSE the Cursor. Advantages : - Lesser Coding

declare cursor lst is select * from emp; begin for I in lst loop dbms_output. put_line(I. ename|| I. job); end loop; end;

Dynamic Cursors

DECLARE CURSOR c 1 (dnum number) IS Select * from emp where deptno = dnum; dep emp. deptno%type; BEGIN Select deptno into dep from emp where ename = 'SMITH'; FOR emp_rec IN c 1(dep) loop Dbms_output. put_line(emp_rec. ename); End loop; END;