8.1.1 Use Simple Loops with EXIT ConditionsIn 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;
Execute the script, and then answer the following questions.
8.1.2 Use Simple Loops with EXIT WHEN ConditionsIn 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:
|