1 / 57

Chapter Sixteen Cursors

Chapter Sixteen Cursors. Objective: Introduction to cursors Use of cursors in a database record Implicit & explicit cursors Cursors & loops Cursors with parameters. Cursors. DEF: A cursor is a pointer to the context area .

obullington
Download Presentation

Chapter Sixteen Cursors

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter SixteenCursors Objective: Introduction to cursors Use of cursors in a database record 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 COSC 641 Chapter 16: PL/SQL Cursors

  3. Cursors (Example 1) 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; COSC 641 Chapter 16: PL/SQL 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; COSC 641 Chapter 16: PL/SQL Cursors

  5. Cursors • Every SQL statement executed by the Oracle Server has an individual cursor associated with it. • Explicit cursors • Implicit cursors COSC 641 Chapter 16: PL/SQL Cursors

  6. Explicit Cursors (from example 1) • Keep track of the current row that is processing • Process row by row • Programmer can manually control the cursor • All in an active set COSC 641 Chapter 16: PL/SQL Cursors

  7. Controlling Explicit Cursors: • Create a named SQL area; (DECLARE) • Identify the active set (OPEN) • Load the current row into variables (FETCH) • Test for existing rows • If found goto step 3 • If not found goto step 5 • Release the active set (CLOSE) COSC 641 Chapter 16: PL/SQL Cursors

  8. Explicit Cursor OPEN FETCH How Many Rows CLOSE COSC 641 Chapter 16: PL/SQL Cursors

  9. Cursors (Example 1) 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; COSC 641 Chapter 16: PL/SQL 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; ELSIF a_Rec.major = ‘MATH’ THEN Ret_val = 5; END IF; END IF; CLOSE a; RETURN Ret_Val; END; / COSC 641 Chapter 16: PL/SQL Cursors

  11. Declaring a Cursor Syntax: CURSOR C_Name [([Parameter [, parameter])] IS Select_Statement [RETURN ret] [For UPDATE [of [COLUMN List]]; COSC 641 Chapter 16: PL/SQL Cursors

  12. Example CURSOR C_Faculty IS SELECT * FROM Faculty WHERE Major=UPPER(‘COSC’); ----------------------------- CURSORC_Dept IS SELECT ID, No FROM Dept WHERE Name=UPPER(‘COSC’); ----------------------------- COSC 641 Chapter 16: PL/SQL Cursors

  13. Opening the Cursor Syntax: OPEN C_Name; OPEN is an executable statement that: • Dynamically allocates memory for a context area • Parses the SELECT statement • Binds the input variables: (by obtaining their memory address) • Identifies the active set • Positions the pointer just before the 1st row in the active set COSC 641 Chapter 16: PL/SQL Cursors

  14. Example DECLARE CURSOR C_Faculty IS SELECT * FROM Faculty 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; …. COSC 641 Chapter 16: PL/SQL Cursors

  15. 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 • Exits the loop, if the pointer points to the end of the active set COSC 641 Chapter 16: PL/SQL Cursors

  16. 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; 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; END; / COSC 641 Chapter 16: PL/SQL Cursors

  17. 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; COSC 641 Chapter 16: PL/SQL Cursors

  18. Illegal Cursor DECLARE CURSOR C_Student IS SELECT id, name FROM Student WHERE major=V_Major; V_Major Student.major%TYPE=‘COSC’; COSC 641 Chapter 16: PL/SQL Cursors

  19. ROWNUM • Return a number indicating the order in which a row was selected from a table DECLARE CURSOR C1 IS SELECT ID, Name FROM Faculty WHERE salary > 40000 AND ROWNUM <=100; --100 rows CURSOR C2 IS SELECT * FROM (SELECT ID, Name FROM faculty_temp WHERE salary> 40000 ORDER BY salary DESC) WHERE ROWNUM <10; --first 9 sorted rows BEGIN ……… UPDATE faculty_temp SET ID = ROWNUM; --each row gets a different number END; / COSC 641 Chapter 16: PL/SQL Cursors

  20. Explicit Cursor Attributes • %FOUND Boolean Type C_Dept%FOUND; • %NOTFOUND Boolean Type C_Dept%NOTFOUND; • %ISOPEN Boolean Type C_Dept%ISOPEN; • %ROWCOUNT Number Type C_Dept%ROWCOUNT; • %BULK_ROWCOUNT C_Dept% BULK_ROWCOUNT; COSC 641 Chapter 16: PL/SQL Cursors

  21. %ISOPEN • You can fetch rows only when it is open • Use %ISOPEN to check IF NOT C_faculty%ISOPEN THEN OPEN C_faculty; END IF; LOOP FETCH C_faculty into V_ID, V_Name; … COSC 641 Chapter 16: PL/SQL Cursors

  22. Explicit Cursor Attributes • Use %ROWCOUNT cursor attribute to retrieve an exact number of rows • Use %NOTFOUND cursor attribute to determine when to exit the loop SET SERVEROUTPUT ON; BEGIN …. OPEN C_faculty; LOOP FETCH C_faculty INTO V_ID, V_Name; DBMS_OUTPUT.PUT_LINE (‘faculty’ || V_Name || ’has ID = ‘ || V_ID); EXIT WHEN C_faculty %ROWCOUNT >5 OR C_faculty %NOTFOUND; END LOOP; CLOSE C_faculty; END; / COSC 641 Chapter 16: PL/SQL Cursors

  23. Cursor Exception • INVALID_CURSOR • TOO_MANY_ROWS • %BULK_EXCEPTION COSC 641 Chapter 16: PL/SQL Cursors

  24. Example DECLARE CURSOR C_Faculty IS SELECT * FROM Faculty; V_F_Rec C_Faculty%ROWTYPE; BEGIN A OPEN C_Faculty; B FETCH C_Faculty INTO V_F_Rec; C FETCH C_Faculty INTO V_F_Rec; D CLOSE C_Faculty; E F END; Name Salary Mary 40,000 Mark 38,000 COSC 641 Chapter 16: PL/SQL Cursors

  25. %FOUND & %NOTFOUND COSC 641 Chapter 16: PL/SQL Cursors

  26. %ISOPEN & %ROWCOUNT COSC 641 Chapter 16: PL/SQL Cursors

  27. NO_DATA_FOUND • SELECT INTO…. • %NOTFOUND SQL%NOTFOUND • Cursor COSC 641 Chapter 16: PL/SQL Cursors

  28. Practice: Create a PL/SQL block to find the top n salaries • Use a substitution parameter so user can input n. • Use loop to get names and salaries of the top n faculty from faculty table. • Store the names and salaries in a temporary table. • Test for n=0 , and n > number of faculty • Assume no two faculty members have the same salary. • Empty the table after each test. COSC 641 Chapter 16: PL/SQL Cursors

  29. Practice 2: • Assume faculty members may have the same salaries. In this case list all of them; • Mary 10000 • Mark 5000 • Sandy 5000 • John 2000 • If n=2; list Mary, Mark, and Sandy • If n=3; list Mary, Mark, Sandy, and John COSC 641 Chapter 16: PL/SQL Cursors

  30. Cursors with Parameters Syntax: CURSOR C_Name [ (P1, P2, …) ] IS Select_Statement; Where P1,P2: P [IN] datatype [[:= | DEFAULT ] 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. COSC 641 Chapter 16: PL/SQL Cursors

  31. Example of Cursors with Parameters 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’); …. CLOSE C_Student; OPEN C_Student(2222, ‘MATH’); …. CLOSE C_Student; END; / COSC 641 Chapter 16: PL/SQL Cursors

  32. 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); CLOSE C_Student; OPEN C_Student (2222, ‘VART’); COSC 641 Chapter 16: PL/SQL Cursors

  33. 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 (‘Sandy’); FETCH a INTO a_Rec; IF a%FOUND THEN IF a_Rec.Major = ‘COSC’ THEN Ret_val = 10; ELSIF a_Rec.Major = ‘MATH’ THEN Ret_val = 5; END IF; END IF; CLOSE a; END; COSC 641 Chapter 16: PL/SQL Cursors

  34. Open Cursor with Parameters OPEN a (:My_pack.Name); OPEN a (‘John’); OPEN a (‘Mark’); COSC 641 Chapter 16: PL/SQL Cursors

  35. Cursor With Parameter: CURSOR C_staff RETURN staff%ROWTYPE IS SELECT * FROM staff WHERE dept=UPPER(‘Cosc’); COSC 641 Chapter 16: PL/SQL Cursors

  36. Cursor with Default Value DECLARE CURSOR C1(low NUMBER DEFAULT 0, high NUMBER DEFAULT 99) IS SELECT … COSC 641 Chapter 16: PL/SQL Cursors

  37. FOR UPDATE Clause Syntax: SELECT ….. FROM ….. FOR UPDATE [OF Col_Ref] [NOWAIT] - Lock the record before update or delete COSC 641 Chapter 16: PL/SQL Cursors

  38. Example DECLARE CURSOR C_Student IS SELECT Name, ID, GPA FROM Student WHERE Dept=‘COSC’ FOR UPDATE OF id, name; COSC 641 Chapter 16: PL/SQL Cursors

  39. WHERE CURRENT OF clause Syntax: WHERE CURRENT OF cursor • Use Cursors to update or delete the current row • Include the FOR UPDATE clause in the cursor query to Lock the rows first • Use WHERE CURRENT OF clause to reference the current row from an explicit cursor. COSC 641 Chapter 16: PL/SQL Cursors

  40. Example DECLARE CURSOR C_Student IS SELECT Major, id FROM Student WHERE Major = ‘COSC’ FOR UPDATENOWAIT; BEGIN FOR I IN C_Student LOOP UPDATE student SET ID=student.Id * 100 WHERE CURRENT OF C_Student; END LOOP; COMMIT; END; / COSC 641 Chapter 16: PL/SQL Cursors

  41. Cursor with subqueries DECLARE CURSOR C1 is SELECT id, Name FROM faculty WHERE salary > ( SELECT AVG(Salary) FROM Faculty ); CURSOR C2 is SELECT * FROM (SELECT Name,Salary FROM Faculty ORDER BY Salary DESC,Name) WHERE ROWNUM<21; Continued… COSC 641 Chapter 16: PL/SQL Cursors

  42. Cursor FOR loops: Syntax: FOR record_name IN cursor_name LOOP statement1; statement2; …. END LOOP; COSC 641 Chapter 16: PL/SQL Cursors

  43. Cursor FOR loop: BEGIN FOR I IN C1 LOOP DBMS_OUTPUT.PUT_LINE(‘Above average Salary’ || I.Name); END LOOP; FOR I IN C2 LOOP DBMS_OUTPUT.PUT_LINE(‘Twenty Top paid Faculty’ || I.NAME || I.SALARY); END LOOP; END; / COSC 641 Chapter 16: PL/SQL Cursors

  44. NO_DATA_FOUND VS. %NOTFOUND SELECT …. INTO ….. FROM ….. WHERE ….. • NO_DATA_FOUND :Exception • %NOTFOUND :Flag COSC 641 Chapter 16: PL/SQL Cursors

  45. Implicit Cursor INSERT DELETE UPDATE SELECT INTO COSC 641 Chapter 16: PL/SQL Cursors

  46. Implicit Cursor DECLARE a faculty%ROWTYPE; BEGIN SELECT * INTO a FROM faculty WHERE ID = 1111; END; COSC 641 Chapter 16: PL/SQL Cursors

  47. Error Handling with Implicit Cursor • NO_DATA_FOUND • TOO_MANY_ROWS COSC 641 Chapter 16: PL/SQL Cursors

  48. Implicit Cursor Attributes • SQL%FOUND • SQL%NOTFOUND • SQL%ROWCOUNT • SQL%ISOPEN --Always Return False COSC 641 Chapter 16: PL/SQL Cursors

  49. 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; COSC 641 Chapter 16: PL/SQL Cursors

  50. UPDATE, DELETE, INSERT SET SERVEROUTPUT ON; BEGIN UPDATE Faculty SET Salary = Salary * 0.05 WHERE Dept = ‘COSC’; DBMS_OUTPUT.PUT_LINE(‘Update’ || SQL%ROWCOUNT); END; / COSC 641 Chapter 16: PL/SQL Cursors

More Related