Lab 8.1 Exercises


8.1.1 Use Simple Loops with EXIT Conditions

In this exercise, you will use the EXIT condition to terminate a simple loop, and a special variable, v_counter , which keeps count of the loop iterations. With each iteration of the loop, the value of v_counter will be incremented and displayed on the screen.

Create the following PL/SQL script:

 
 -- ch08_1a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    v_counter BINARY_INTEGER := 0; BEGIN    LOOP       -- increment loop counter by one       v_counter := v_counter + 1;       DBMS_OUTPUT.PUT_LINE ('v_counter = 'v_counter);       -- if EXIT condition yields TRUE exit the loop       IF v_counter = 5 THEN          EXIT;       END IF;    END LOOP;    -- control resumes here    DBMS_OUTPUT.PUT_LINE ('Done...'); END; 
graphics/trick_icon.gif

The statement

 
 v_counter := v_counter + 1 

is used often when working with a loop. Variable v_counter is a loop counter that tracks the number of times the statements in the body of the loop are executed. You will notice that for each iteration of the loop, its value is incremented by 1. However, it is very important to initialize the variable v_counter for successful termination of the loop. If v_counter is not initialized , its value is NULL. Then, the statement

 
 v_counter := v_counter + 1 

will never increment the value of v_counter by one, because NULL + 1 evaluates to NULL. As result, the EXIT condition will never yield TRUE, and the loop will become infinite.


Execute the script, and then answer the following questions.

a)

What output was printed on the screen?

b)

How many times was the loop executed?

c)

What is the EXIT condition for this loop?

d)

How many times will the value of the variable v_counter be displayed if the DBMS_OUTPUT.PUT_LINE statement is used after the END IF statement?

e)

Why does the number of times the loop counter value is displayed on the screen differ when the DBMS_OUTPUT.PUT_ LINE statement is placed after the END IF statement?

f)

Rewrite this script using the EXIT WHEN condition instead of the EXIT condition, so that it produces the same result.


8.1.2 Use Simple Loops with EXIT WHEN Conditions

In this exercise, you will use the EXIT WHEN condition to terminate the loop. You will add a number of sections for a given course number. Try to answer the questions before you run the script. Once you have answered the questions, run the script and check your answers.

Create the following PL/SQL script:

 
 -- ch08_2a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    v_course course.course_no%type := 430;    v_instructor_id instructor.instructor_id%type := 102;    v_sec_num section.section_no%type := 0; BEGIN    LOOP       -- increment section number by one       v_sec_num := v_sec_num + 1;       INSERT INTO section          (section_id, course_no, section_no,          instructor_id, created_date, created_by,          modified_date, modified_by)       VALUES          (section_id_seq.nextval, v_course, v_sec_num,          v_instructor_id, SYSDATE, USER, SYSDATE,          USER);       -- if number of sections added is four exit the loop       EXIT WHEN v_sec_num = 4;    END LOOP;    -- control resumes here    COMMIT; EXCEPTION    WHEN OTHERS THEN       DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END; 

Notice that the INSERT statement contains an Oracle built-in function called USER. At first glance, this function looks like a variable that has not been declared. This function returns the name of the current user. In other words, it will return the login name that you use when connecting to Oracle.

Try to answer the following questions first, and then execute the script:

a)

How many sections will be added for the specified course number?

b)

How many times will the loop be executed if the course number is not valid?

c)

How would you change this script to add 10 sections for the specified course number?

d)

How would you change the script to add only even-numbered sections (maximum section number is 10) for the specified course number?

e)

How many times will the loop be executed in this case?




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