Chapter 27: An Introduction to PL/SQL The Exception Handling section of a PL/SQL block is optional—none of the PL/SQL blocks shown previously in this chapter included an Exception Handling section. However, the examples shown in this chapter have been based on a very small set of known input values with very limited processing performed. In the following listing, the simple loop for calculating the area of a circle is shown, with two modifications (shown in bold). A new variable named some_variable is declared in the Declarations section, and a calculation to determine the variable’s value is created in the Executable Commands. | Chapter 27 An Introduction to PL SQL 507 The Exception Handling section of a PL SQL block is optional none of the PL SQL blocks shown previously in this chapter included an Exception Handling section. However the examples shown in this chapter have been based on a very small set of known input values with very limited processing performed. In the following listing the simple loop for calculating the area of a circle is shown with two modifications shown in bold . A new variable named some_variable is declared in the Declarations section and a calculation to determine the variable s value is created in the Executable Commands section. t declare pi constant NUMBER 9 7 radius INTEGER 5 area NUMBER 14 2 some_variable NUMBER 14 2 begin radius 3 loop some_variable 1 radius-4 area pi power radius 2 insert into AREAS values radius area radius radius 1 exit when area 100 end loop end Because the calculation for some_variable involves division you may encounter a situation in which the calculation attempts to divide by zero an error condition. The first time through the loop the Radius variable with an initial value of 3 is processed and a record is inserted into the AREAS table. The second time through the loop the Radius variable has a value of 4 and the calculation for some_variable encounters an error t declare ERROR at line 1 ORA-01476 divisor is equal to zero ORA-06512 at line 9 Since an error was encountered the first row inserted into AREAS is rolled back and the PL SQL block terminates. You can modify the processing of the error condition by adding an Exception Handling section to the PL SQL block as shown in the following listing. I declare pi constant NUMBER 9 7 radius INTEGER 5 area NUMBER 14 2 some_variable NUMBER 14 2 begin radius 3 508 Part III PL SQL loop some_variable 1 radius-4 area pi power radius 2 insert into AREAS values radius area radius radius 1 exit when area 100 end loop exception when ZERO_DIVIDE then insert into AREAS values 0
