PLSQL Cursors by uzd19483

VIEWS: 30 PAGES: 14

									          PL/SQL Cursors




                        Cursors
n   Cursors allow embedded SQL statements
    n Result is a set (table) in a temporary work area


n   Cursor name permits iterative manipulation of rows

n   Two varieties of cursors
    n Implicit
    n Explicit
        More detailed to write, Permit more advanced manipulations




                                                                     1
                  Example
               A SIMPLE CURSOR




         A SIMPLE CURSOR
Declare
     cursor c1 is
     select * from emp;

BEGIN
for emp_rec in c1 loop
     dbms_output.put_line(emp_rec.ename);
end loop;
END;




                                            2
SQL> create or replace procedure sumsalary IS

    cursor c1 is
    select * from emp;                                   Declaration

    salsum integer;

BEGIN
 salsum := 0;
 for emp_rec in c1 loop
 salsum := salsum + emp_rec.sal;
 end loop;
 dbms_output.put_line('Salary sum: ' || salsum);
END;
                                    SQL> execute sumsalary;
Procedure created.                  Salary sum: 29025
                                    PL/SQL procedure successfully completed.




          Explicit Cursor Operations
n   DECLARE
     n Introduces cursor object, bound to SQL statement


n   OPEN
     n Constructs temporary table


n   FETCH
     n Accesses next row


n   TESTS
     n %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN


n   CLOSE
     n Releases temporary table and associated working data




                                                                               3
                   DECLARE
Introduces cursor object, bound to SQL
              statement
CURSOR EMP_CUR IS
 select empno, ename, sal from emp;




                      OPEN
        Constructs temporary table

                  BEGIN
                  open emp_cur;




                                         4
                           FETCH
               accesses next row
      LOOP
       fetch emp_cur into v_empno, v_ename, v_sal;




                            TESTS
       %FOUND, %NOTFOUND,
       %ROWCOUNT, %ISOPEN
LOOP
 fetch emp_cur into v_empno, v_ename, v_sal;
  EXIT when emp_cur%NOTFOUND;
  IF (v_sal > 1000) then
  DBMS_OUTPUT.put_line(v_empno || ' ' || v_ename || ' ' || v_sal);
  ELSE
  DBMS_OUTPUT.put_line(v_ename || ' sal is less then 1000');
  END IF;
END LOOP;




                                                                     5
              CLOSE
Releases temporary table and
  associated working data
           close emp_cur;




 Writing complete
  Explicit Cursor
            A complete example

              Just Remember




 DECLARE   OPEN    FETCH      TESTS   CLOSE




                                              6
                 Example Problem
We will retrieve all records from EMP table and display them one
 by one by using CURSOR. With some data manipulation.




CREATE OR REPLACE PROCEDURE MY_TEST IS
v_empno emp.empno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;                                     DECLARE
CURSOR EMP_CUR IS
  select empno, ename, sal from emp;
BEGIN
open emp_cur;                                            OPEN
LOOP
   fetch emp_cur into v_empno, v_ename, v_sal;           FETCH
    EXIT when emp_cur%NOTFOUND;                          TESTS
    IF (v_sal > 1000) then
    DBMS_OUTPUT.put_line(v_empno || ' ' || v_ename || ' ' || v_sal);
    ELSE
    DBMS_OUTPUT.put_line(v_ename || ' sal is less then 1000');
    END IF;
END LOOP;
close emp_cur;                                            CLOSE
   DBMS_OUTPUT.put_line('Execution Complete');
END;




                                                                       7
                      Example-2




DECLARE
 a emp.empno%TYPE;
 b emp.ename%TYPE;

CURSOR T1Cursor IS                  /* Cursor declaration: */

   SELECT empno, ename FROM emp;
BEGIN
OPEN T1Cursor;
 LOOP
  /* Retrieve each row of the result of the above query into variables: */
   FETCH T1Cursor INTO a, b;

     /* If there are no more rows to fetch, exit the loop: */
   EXIT WHEN T1Cursor%NOTFOUND;
   DBMS_output.put_line(a || ' , ' || b);
 END LOOP; /* Free cursor used by the query. */
 CLOSE T1Cursor;
END;




                                                                             8
                        Example-3
                         Using %ROWTYPE




Declare
CURSOR EMP_CUR IS             Defining ROWTYPE variable
  select * from emp;
var_cur EMP%ROWTYPE;
BEGIN
open emp_cur;
                              FETCH
LOOP
  fetch emp_cur into var_cur;
   EXIT when emp_cur%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(var_cur.empno || ' ' || var_cur.ename ||' ' ||
                            var_cur.job || ' ' ||var_cur.mgr || ' ' ||
                            var_cur.hiredate|| ' ' ||var_cur.sal || ' ' ||
                            var_cur.deptno);
END LOOP;
close emp_cur;
    DBMS_OUTPUT.put_line('Execution Complete');
END;
/




                                                                             9
                        Example 4
                        Fetching Limited rows




Declare
CURSOR EMP_CUR IS                                 s
  select * from emp;                           row
                                           ted
var_cur EMP%ROWTYPE;
                                      glimi
BEGIN                             chin
open emp_cur;                  Fet
LOOP
  fetch emp_cur into var_cur;
   EXIT when emp_cur%ROWCOUNT = 5;
     DBMS_OUTPUT.PUT_LINE(var_cur.empno || ' ' || var_cur.ename ||' ' ||
                            var_cur.job || ' ' ||var_cur.mgr || ' ' ||
                            var_cur.hiredate|| ' ' ||var_cur.sal || ' ' ||
                            var_cur.deptno);
END LOOP;
close emp_cur;
    DBMS_OUTPUT.put_line('Execution Complete');
END;
/




                                                                             10
                    Practice


n   Write a cursor that will display last five row
    from emp table and also all rows that contain
    salary between 1000 to 2000.




       Creating Functions




                                                     11
                       Function Syntax
  CREATE [OR REPLACE] FUNCTION <FUNCTION NAME>

  (argument 1                 [MODE 1]           datatype-1,
   argument 2                 [MODE 2]           datatype-2)

  RETURN Number

  IS

  PLSQL BLOCK;




                    A Simple Function
CREATE OR REPLACE FUNCTION TEST
 (v_id in number)
 return number
IS
a number;
BEGIN
 a := v_id +100;
return a;
end test;

Execute a function
                                         Here :Z is a SQL variable   SQL> print z;
SQL> variable z number;
SQL> execute :z := test(10);
                                                                      Z
PL/SQL procedure successfully completed.                             ---------
                                                                      110




                                                                                     12
      Creating Function
                        2nd Example




CREATE OR REPLACE FUNCTION GET_SAL
       (v_id in emp.empno%type)
       return number
IS
       v_salary emp.sal%type := 0 ;
BEGIN
       select sal into v_salary
       from emp
       where empno = v_id;
return (v_salary);
end get_sal;
                                      SQL> print a;
  SQL> variable a number;
  SQL> Execute :a := get_sal(7934);          A
  SQL> Print a;                       ---------
                                          1300




                                                      13
      Thanks

Next:- PLSQL Packages




                        14

								
To top