PLSQL Cursors by dffhrtcv3

VIEWS: 0 PAGES: 31

									PL/SQL
Cursors
 Session - II
     Attributes
%TYPE
%ROWTYPE
% Found
% NotFound
% RowCount
% IsOPen
        %TYPE
useful when declaring variables
that    refers  to    Database
            Columns

Name   Varchar(15);
Name   Emp.Ename%TYPE;
     %ROWTYPE
Provides a    record Type that
 represents a Row in a Table.
   ENAME   DESIGNATION   SAL
   SMITH   CLERK         6000
   JONES   SALESMAN      4566
   ADAMS   PRSIDENT      4567
   KING    MANAGER       4500
Name Varchar(15);
Desig  Varchar(15);
Salary Number(8,2);



Emp_Rec     Emp%ROWTYPE;
Declare
EmRec Emp%Rowtype;
Begin
Select * INTO EmRec 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

IMPLICIT
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
Declare the Cursor
Open the Cursor
Fetch the Cursor
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


CURSOR C is Select Ename,deptno
         from emp ;                   C

                  OPEN CURSOR


                                  ENAME   DEPTNO
                                  SMITH     20
           OPEN C;
                                  JONES     30
                                  ADAMS     40
         FETCHING CURSOR

                           ENAME   DEPTNO
                           SMITH     20
                           JONES     30
                           ADAMS     40

FETCH C INTO ENAM,DEPTN;


          CLOSING CURSOR


        CLOSE C;
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

Every  Explicit cursor has four
 attributes.
Lets       you access usefull
 information about the execution
 of a multirow query.
%NOTFOUND
%FOUND
%ROWCOUNT
%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;
 Is
  the
             %FOUND
        Logical Opposite of
 %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
Use cursor attributes to access
 the SQL% cursors context
 area.
%NOTFOUND , %FOUND
%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 c1 (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 c1(dep) loop
Dbms_output.put_line(emp_rec.ename)
;
 End loop;
END;

								
To top