Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

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;

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.

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: