PLSQL Cursors

PLSQL Cursors

• 1.
  PL/SQL Cursors By Praveen & Naresh
• 2.
  What is Cursors? SQL: > SELECT * FROM employee; Oracle RDBMS : Assigns a private work area for that stmt.
• 3.
  contd.. PL/SQL cursoris a mechanism by which “ you can NAME that work area and manipulate the information within it”
• 4.
  Declare/Open/Fetch/Close CURSOR emp_cur IS SELECT * FROM employee ; Open emp_cur ; Fetch emp_cur INTO emp_rec; CLOSE emp_cur ;
• 5.
  Types of cursorsTwo Types: 1) Implicit 2) Explicit
• 6.
  Cursor Attributes Attribute:%FOUND %ISOPEN %NOTFOUND %ROWCOUNT
• 7.
  Implicit Cursors DMLStatements INSERT, UPDATE, DELETE SELECT statement with INTO clause
• 8.
  Single Row ImplicitCursors DECLARE e_id employee.empid%TYPE; e_name employee.empname%TYPE; BEGIN SELECT empid, empname INTO e_id, e_name FROM employee where empid=23; dbms_output.put_line( SQL%ROWCOUNT ); END; /
• 9.
  Multiple Row ImplicitCursors(1) BEGIN UPDATE employee SET emp_sal= emp_sal + 5000 where emp_deptid=1; IF SQL%FOUND THEN dbms_output.put_line( ' Updated: '|| SQL%ROWCOUNT ); ELSE dbms_output.put_line( 'Nothing Updated' ); END; /
• 10.
  Multiple Row ImplicitCursors(2) BEGIN FOR e_cursor IN ( SELECT e_name FROM employee ) LOOP dbms_output.put_line( e_cursor.e_name ); END LOOP; END; /
• 11.
  Cursor FOR LoopScope : Inside FOR Loop Cursor Index : - a pointer to Query work area. - Query work area is a memory region ( context area ) Note: SQL%ROWCOUNT attribute returns NULL .
• 12.
  Explicit Cursors Defineinside declaration block Static or dynamic SELECT stmt. Open/Fetch/Close
• 13.
  Syntax OPEN cursor_name [ (param1 , param2 ,....) ] FETCH cursor_name INTO (variable1,variable2,....)‏ FETCH cursor_name INTO record_variable; CLOSE cursor_name ;
• 14.
  Static Explicit CursorsSQL SELECT – behavior doesn't change
• 15.
  Static Cursor DECLAREeid employee.empid%TYPE; ename employee.empname%TYPE; CURSOR cur IS SELECT empid, empname FROM employee ; BEGIN OPEN cur ; LOOP FETCH cur INTO eid , ename ; EXIT WHEN cur%NOTFOUND ; dbms_output.put_line( 'NAME' || cur . ename ); END LOOP; CLOSE cur ; END; /
• 16.
  Eg- Cursor ForLoop DECLARE CURSOR C IS SELECT empid AS eid , empname AS ename FROM employee ; BEGIN FOR i IN C LOOP dbms_output.put_line( 'NAME' || i.ename ); END LOOP ; END; /
• 17.
  Static Cursor( Record)‏DECLARE TYPE emp_record IS RECORD ( eid NUMBER, ename VARCHAR2(30) ); employee EMP_RECORD ; CURSOR cur IS SELECT empid, empname FROM employee ; BEGIN OPEN cur ; LOOP FETCH cur INTO employee ; EXIT WHEN cur%NOTFOUND ; dbms_output.put_line( 'NAME' || employee . ename ); END LOOP; CLOSE cur ; END; /
• 18.
  Static Cursor( Record+FORLoop)‏ DECLARE TYPE emp_record IS RECORD ( eid NUMBER, ename VARCHAR2(30) ); explicit_employee EMP_RECORD ; CURSOR cur IS SELECT empid, empname FROM employee ; BEGIN FOR i IN cur LOOP explicit_employee := i; dbms_output.put_line( 'NAME' || explicit_employee . ename ); END LOOP; END; / Note: No Data Found , No error is raised
• 19.
  Static Cursor( Norows)‏ DECLARE TYPE emp_record IS RECORD ( eid NUMBER, ename VARCHAR2(30) ); employee EMP_RECORD ; CURSOR cur IS SELECT empid, empname FROM employee where empid = -1 ; BEGIN OPEN cur ; LOOP FETCH cur INTO employee ; IF cur%NOTFOUND THEN IF cur%ROWCOUNT = 0 THEN dbms_output.put_line( 'No Data Found' ); END IF; EXIT; ELSE dbms_output.put_line( 'NAME' || employee . ename ); END IF; END LOOP; CLOSE cur ; END; /
• 20.
  Thanks