ch04 - P L / S Q L Oracle10 g Developer: PL/SQL Programming...

Info iconThis preview shows pages 1–12. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: P L / S Q L Oracle10 g Developer: PL/SQL Programming Chapter 4 Cursors and Exception Handling P L / S Q L Oracle10g Developer: PL/SQL Programming 2 Chapter Objectives After completing this lesson, you should be able to understand: Manipulating data with cursors Managing errors with exception handlers Addressing exception-handling issues, such as RAISE_APPLICATION_ERROR and propagation Documenting code with comments P L / S Q L Rebuild your database Download and run c4Dbcreate Oracle10g Developer: PL/SQL Programming 3 P L / S Q L Oracle10g Developer: PL/SQL Programming 4 Cursors A cursor represents a work area or section of memory in which an SQL statement is processed The memory section is also referred as context area Implicit cursor declared automatically for all DML and SELECT statements Explicit cursor declared and managed programmatically to handle a set of rows returned by a SELECT statement Cursor variable reference or pointer to a work area or cursor P L / S Q L Oracle10g Developer: PL/SQL Programming 5 Cursor Attributes Attribute Name Data type Description %ROWCOUNT Number Number of rows affected by the SQL statement %FOUND Boolean TRUE if at least one row is affected by the SQL statement, otherwise FALSE %NOTFOUND Boolean TRUE if no rows are affected by the SQL statement, otherwise FALSE Cursor attributes allow the results of an SELECT statement to be checked as to whether the statement affected any row and how many. P L / S Q L Oracle10g Developer: PL/SQL Programming 6 Implicit Cursor P L / S Q L Implicit Cursor Note: An update may affect zero or many rows, and none of these cases raise an Oracle error. The attribute references are preceded by SQL %. When referencing SQL attributes, the attribute information always reflects the information from the most recent SQL statement processed. Oracle10g Developer: PL/SQL Programming 7 P L / S Q L Implicit Cursor A SELECT statement embedded directly in the executable section of a PL/SQL block that returns no rows will result in a no data found error. A common programming mistake is to check the %NOTFOUND cursor attribute immediately following a SELECT statement to determine if rows were selected. Once an error is raised, the processing actually jumps to the last section of a block the EXCEPTION area. Oracle10g Developer: PL/SQL Programming 8 P L / S Q L Implicit Cursor DECLARE lv_tot_num bb_basket.total%TYPE; BEGIN SELECT total INTO lv_tot_num FROM bb_basket WHERE idbasket = 99; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE( Not Found); END IF; END; Oracle10g Developer: PL/SQL Programming 9 P L / S Q L Challenges Challenge 4-1 Oracle10g Developer: PL/SQL Programming 10 P L / S Q L Explicit Cursor Explicit cursors are cursors that are declared with the declaration containing a SELECT statement....
View Full Document

Page1 / 62

ch04 - P L / S Q L Oracle10 g Developer: PL/SQL Programming...

This preview shows document pages 1 - 12. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online