Lab 7.1 Exercise Answers


This section gives you some suggested answers to the questions in Lab 7.1, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

7.1.1 Answers

a)

What output was printed on the screen (for both runs)?

A1:

Answer: The first version of the output is produced when the value of v_num is equal to 4. Your output should look like the following:

  Enter value for sv_num: 4   old 2: v_num NUMBER := &sv_num;   new 2: v_num NUMBER := 4;   Square root of 4 is 2   PL/SQL procedure successfully completed.  

The second version of the output is produced when v_num is equal to - 4. Your output should look like the following:

  Enter value for sv_num: -4   old 2: v_num NUMBER := &sv_num;   new 2: v_num NUMBER := -4;   An error has occurred   PL/SQL procedure successfully completed.  
b)

Why do you think an error message was generated when the script was run a second time?

A2:

Answer: Error message "An error has occurred" was generated for the second run of example because a runtime error has occurred. The built-in function SQRT is unable to accept a negative number as its argument. Therefore, the exception VALUE_ERROR was raised, and the error message was displayed on the screen.

c)

Assume that you are not familiar with the exception VALUE_ERROR. How would you change this script to avoid this runtime error?

A3:

Answer: The new version of the program should look similar to the program below. All changes are shown in bold letters .

 -- ch07_1b.sql, version 2.0 SET SERVEROUTPUT ON; DECLARE v_num NUMBER := &sv_num; BEGIN  IF v_num >= 0 THEN  DBMS_OUTPUT.PUT_LINE ('Square root of 'v_num ' is 'SQRT(v_num));  ELSE   DBMS_OUTPUT.PUT_LINE ('A number cannot be negative');   END IF;  END; 

Notice that before you calculate the square root of a number, you can check to see if the number is greater than or equal to 0 with the help of the IF-THEN-ELSE statement. If the number is negative, the message "A number cannot be negative" is displayed on the screen. When the value of -4 is entered for the variable v_num , this script produces the following output:

 
  Enter value for sv_num: -4   old 2: v_num NUMBER := &sv_num;   new 2: v_num NUMBER := -4;   A number cannot be negative   PL/SQL procedure successfully completed.  


Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net