Chapter Eighteen Cursors

1. Chapter EighteenCursors Objective: Introduction to cursors Use of cursors in a record variable Implicit & explicit cursors Cursors & loops Cursors with parameters

2. Cursors DEF: A cursor is a pointer to the context area. Context area: Memory location containing info needed to complete the PL/SQL processing Chapter 18: Cursors

3. Cursors (Example) DECLARE V_ID Student.id%TYPE; V_Name Student.name%TYPE; V_Major Student.major%TYPE=‘COSC’; CURSOR C_StudentIS SELECT id, name FROM Student WHERE major=V_Major; BEGIN OPEN C_Student; LOOP FETCH C_Student INTO V_ID, V_Name; EXIT WHEN C_Student%NOTFOUND; END LOOP; CLOSE C_Student; END; Chapter 18: Cursors

4. Cursors (Example 2) CURSOR My_cursor IS SELECT a.Name, b.Name, c.Name FROM faculty a, student b, staff c WHERE a.ID = b.ID AND b.ID = c.ID; Chapter 18: Cursors

5. Cursors • Every SQL statement is executed by the Oracle Server has a cursor associated with it. • Explicit cursors • Implicit cursors Chapter 18: Cursors

6. Explicit Cursors • Keep track of the current row that is processing • Process row by row • Programmer can manually control the cursor • All in an active set Chapter 18: Cursors

7. Controlling Explicit Cursors: • Create a named SQL area; Declare the cursor • Identify the active set • Load the current row into variables • Test for existing rows • If found goto step 3 • If not found goto step 5 • Release the active set Chapter 18: Cursors

8. Explicit Cursor Control retrieval of information: OPEN FETCH How Many Rows Close Chapter 18: Cursors

9. Cursors (Example) DECLARE V_ID Student.id%TYPE; V_Name Student.name%TYPE; V_Major Student.major%TYPE=‘COSC’; CURSOR C_StudentIS SELECT id, name FROM Student WHERE major=V_Major; BEGIN OPEN C_Student; LOOP FETCH C_Student INTO V_ID, V_Name; EXIT WHEN C_Student%NOTFOUND; END LOOP; CLOSE C_Student; END; Chapter 18: Cursors

10. Example CREATE OR REPLACE FUNCTION myFac(Name_In IN student.Name%TYPE) RETURN NUMBER AS CURSOR a IS SELECT Major FROM student WHERE Name = UPPER(Name_In); a_rec a%ROWTYPE; Ret_val NUMBER; BEGIN OPEN a FETCH a INTO a_rec; If a%FOUND THEN IF a_rec.Major = ‘COSC’ THEN Ret_val = 10; ELSEIF a_Rec.major = ‘MATH’ THEN Ret_val = 5; ENDIF ENDIF; CLOSE a; RETURN Ret_Val; END; Chapter 18: Cursors

11. Controlling Explicit Cursors: • DECLARE • OPEN • FETCH • Test for empty rows • No: goto 3 • Yes: goto 5 • CLOSE Chapter 18: Cursors

12. Declaring a Cursor Syntax: CURSOR C_Name [([Parameter [, parameter])] IS Select_Statement [RETURN ret] [For UPDATE [of [COLUMN List]]; Chapter 18: Cursors

13. Example CURSOR C_Faculty IS SELECT * FROM Faculty WHERE Major=UPPER(‘COSC’); ----------------------------- CURSOR C_Dept IS SELECT ID, No FROM Dept WHERE Name=UPPER(‘COSC’); ----------------------------- Chapter 18: Cursors

14. Example CURSOR c_student (student_ID IN NUMBER) IS SELECT Name FROM student WHERE student_ID = student.ID; ----------------------------- CURSOR staff_cur RETURN staff%ROWTYPE IS SELECT * FROM staff WHERE dept = UPPER(‘COSC’); Chapter 18: Cursors

15. Opening the Cursor Syntax OPEN C_Name; OPEN is an executable statement that performs: • Dynamically allocates memory for a context area • Parses the SELECT statement • Binds the input variables: (obtain their memory address) • Identifies the active set • Positions the pointer just before the 1st row in the active set Chapter 18: Cursors

16. Example DECLARE CURSOR C_Faculty IS SELECT * FROM F aculty WHERE Major=‘COSC’; CURSOR C_Dept IS SELECT ID, No FROM Dept WHERE Name=‘COSC’; V_F_Name Faculty.Name%TYPE; V_F_Salary Faculty.Salary%TYPE; V_D_Rec Dept%ROWTYPE; BEGIN OPEN C_Faculty; OPEN C_Dept; Chapter 18: Cursors

17. Fetching the Data Syntax: FETCH C_Name INTO [record_name| V1, V2, …]; • Retrieve the current row values into output variables • Match each variable to the columns • Test to see if the cursor points to a row. FETCH statement performs: • Advance the pointer to the next row in the active set • Reads the data from the current row into the output variables • Exit the loop, if the pointer points to the end of the active set Chapter 18: Cursors

18. Example DECLARE CURSOR C_Faculty IS SELECT * FROM Faculty WHERE Major=UPPER(‘COSC’); CURSOR C_Dept IS SELECT ID, No FROM Dept WHERE Name=‘COSC’; V_F_Name Faculty.Name%TYPE; V_F_Salary Faculty.Salary%TYPE; V_D_Rec Dept%ROWTYPE; BEGIN OPEN C_Faculty; OPEN C_Dept; Chapter 18: Cursors

19. Example FOR I IN 1..5 LOOP FETCH C_Faculty INTO V_F_Name, V_F_Salary …. END LOOP; LOOP FETCH C_Dept INTO V_D_Rec EXIT WHEN C_Dept%NOTFOUND END LOOP; Chapter 18: Cursors

20. Closing the Cursor Syntax CLOSE C_Name; • Close the cursor after the process is completed • Reopen the cursor, if it is needed • OPEN_CURSORS = 50 Chapter 18: Cursors

21. Cursor Attributes • %FOUND Boolean Type My_CUR%FOUND; • %NOTFOUND Boolean Type My_CUR%NOTFOUND; • %ISOPEN Boolean Type My_CUR%ISOPEN; • %ROWCOUNT Number Type My_CUR%ROWCOUNT; • %BULK_ROWCOUNT My_CUR% BULK_ROWCOUNT; Chapter 18: Cursors

22. Cursor Exception • INVALID_CURSOR • TO)_MANY_ROWS • %BULK_EXCEPTION Chapter 18: Cursors

23. Example Name Salary Mary 40,000 Mark 38,000 DECLARE CURSOR C_Faculty IS SELECT * FROM Faculty; V_F_Rec C_Faculty%ROWTYPE; BEGIN • OPEN C_Faculty • FETCH C_Faculty INTO V_F_Rec; • FETCH C_Faculty INTO V_F_Rec; • FETCH C_Faculty INTO V_F_Rec; • FETCH C_Faculty INTO V_F_Rec; • CLOSE C_Faculty; END; Chapter 18: Cursors

24. %FOUND & %NOTFOUND Chapter 18: Cursors

25. %ISOPEN & %ROWCOUNT Chapter 18: Cursors

26. Cursors with Parameters Syntax CURSOR C_Name [ (P1, P2, …) ] IS Select_Statement; Where P1,P2: P [IN] datatype [:=exp] • Pass parameter values to a cursor when the cursor is opened & the query is executed • Open an explicit cursor several times with a different active set each time. Chapter 18: Cursors

27. Cursors with Parameters • Example: DECLARE CURSOR C_Student (V_ID NUMBER, V_Major VARCHAR2) IS SELECT ID, Major FROM Student WHERE Major = V_Major AND ID = V_ID; BEGIN OPEN C_Student (1111, ‘COSC’); Chapter 18: Cursors

28. Cursors with Parameters DECLARE V_VAR_ID Student.ID%TYPE; V_VAR_Major Student.Major%TYPE:=‘COSC’; CURSOR C_Student (V_ID NUMBER, V_Major VARCHAR2) IS SELECT …. OPEN C_Student (1111, V_VAR_Major); OPEN C_Student (2222, ‘VART’); Chapter 18: Cursors

29. Cursors with Parameters CURSOR a (Name_IN VARCHAR2) IS SELECT Major FROM Student WHERE Name = UPPER(Name_IN); a_rec a%ROWTYPE; ret_val NUMBER; BEGIN OPEN a (‘Major’); FETCH a INTO a_Rec; IF a%ROWTYPE THEN IF a_Rec,Major = ‘COSC’ THEN Ret_val = 10; ELSEIf a_Rec.Major = ‘MATH’ THEN Ret_val = 5; ENDIF ENDIF; CLOSE a; END; Chapter 18: Cursors

30. Open Cursor with Parameters OPEN a (:My_pack.Name); OPEN a (‘John’); OPEN a (‘Mark’); Chapter 18: Cursors

31. FOR UPDATE Syntax SELECT ….. FROM ….. FOR UPDATE [OF Col_Ref] [NOWAIT] • Lock the record before update Chapter 18: Cursors

32. Example DECLARE CURSOR C_Student IS SELECT * FROM Student FOR UPDATE OF id, name; Chapter 18: Cursors

33. Example DECLARE CURSOR C_Student IS SELECT Major, id FROM Student WHERE Major = ‘COSC’ FOR UPDATE NOWAIT; Chapter 18: Cursors

34. NO_DATA_FOUND VS. %NOTFOUND SELECT …. INTO ….. FROM ….. WHERE ….. • NO_DATA_FOUND :Exception • %NOTFOUND :Flag Chapter 18: Cursors

35. BULK COLLECT • BC can retrieve multiple rows of data • Reduce Number of context switches between the PL/SQL & SQL • Reduce the overhead of data retrieval Chapter 18: Cursors

36. BULK COLLECT DECLARE TYPE n IS TABLE OF Student.ID%TYPE; TYPE m IS TABLE OF Student.Major%TYPE; a n; b m; CURSOR Temp IS SELECT ID, Major FROM Student WHERE Dept=‘COSC’; Chapter 18: Cursors

37. BULK COLLECT BEGIN Open Temp; FETCH Temp BULK COLLECTIONINTOa, b; CLOSE Temp; END; Chapter 18: Cursors

38. BULK COLLECT DECLARE TYPE T_Number IS TABLE OF Temp_Table.ID%TYPE; TYPE T_String IS TABLE OF Temp_Table.Major%TYPE; V_Num T_Number:=T_Number(1); V_String T_String := T_String(1); CURSOR Temp IS SELECT Major FROM Temp_Table WHERE ID > 1111 ORDER BY ID; Chapter 18: Cursors

39. BULK COLLECT BEGIN V_Number.EXTEND(1000); V_String.EXTEND(1000); -- Load data into Temp_Table SELECT ID, Major FROM Temp_Table BULK COLLECTIONINTOV_Num, V_String ORDER BY ID; END; Chapter 18: Cursors

40. Implicit Cursor INSERT DELETE UPDATE SELECT INTO DECLARE a faculty%ROWTYPE; BEGIN SELECT * INTO a FROM faculty WHERE ID = 1111; END; Chapter 18: Cursors

41. Error Handling with Implicit Cursor • NO_DATA_FOUND • TOO_MANY_ROWS Chapter 18: Cursors

42. Implicit Cursor Attributes • SQL%FOUND • SQL%NOTFOUND • SQL%ROWCOUNT • SQL%ISOPEN --Always Return False Chapter 18: Cursors

43. Implicit Cursor BEGIN UPDATE classroom SET NoOfSeats= 250 WHERE roomId=111; IF SQL%NOTFOUND THEN INSERT INTO classroom (roomId, NoOfSeats) VALUES( 111, 250); END IF; Chapter 18: Cursors

44. Implicit Cursor BEGIN UPDATE classroom SET NoOfSeats= 250 WHERE roomId=111; IF SQL%ROWCOUNT= 0 THEN INSERT INTO classroom (roomId, NoOfSeats) VALUES( 111, 250); ENDIF; Chapter 18: Cursors

45. Cursor Variable • CURSOR • CURSOR Variable Chapter 18: Cursors

46. Cursor Variable TYPE ref_Type IS REF CURSOR [RETURN TYPE]; Example: DECLARE TYPE stud_type REF CURSOR RETURN student%ROWTYPE; stud_cv stud_type; -- Declar Cursor Variable TYPE fac_type REF CURSOR RETURN faculty%ROWTYPE; fac_cv fac_type; TYPE List IS TABLE of student%TYPE; a List; Chapter 18: Cursors

47. Controlling Cursor Variables OPEN-FOR, FETCH, CLOSE Example: OPEN stud_cv FOR SELECT name, ID FROM Student; FETCH stud_cv INTO a; Chapter 18: Cursors

48. EXAMPLE DECLARE TYPE Company_Type IS REF CURSOR RETURN Company%ROWTYPE; company_cv Company_Type; company_Rec Company%ROWTYPE; BEING OPEN company_cv FOR SELECT * FROM Company; FETCH company_cv INTO company_Rec; CLOSE company_cv; END; Chapter 18: Cursors