Create the following script: Check to see whether there is a record in the STUDENT table for a given student ID. If there is no record for the given student ID, insert a record into the STUDENT table for the given student ID. Answer: Your answer should look similar to the following:
SET SERVEROUTPUT ON
DECLARE
v_student_id NUMBER := &sv_student_id;
v_first_name VARCHAR2(30) := '&sv_first_name';
v_last_name VARCHAR2(30) := '&sv_last_name';
v_zip CHAR(5) := '&sv_zip';
v_name VARCHAR2(50);
BEGIN
SELECT first_name||' '||last_name
INTO v_name
FROM student
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('Student '||v_name||' is a valid student');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('This student does not exist, and will be '||
'added to the STUDENT table');
INSERT INTO student
(student_id, first_name, last_name, zip, registration_date,
created_by, created_date, modified_by, modified_date)
VALUES
(v_student_id, v_first_name, v_last_name, v_zip, SYSDATE,
USER, SYSDATE, USER, SYSDATE);
COMMIT;
END;
This script accepts a value for student's ID from a user. For a given student ID, it determines student's name via the SELECT INTO statement, and displays it on the screen. If the value provided by the user is not a valid student ID, the control of execution is passed to the exception-handling section of the block where NO_DATA_FOUND exception is raised. As a result, the message 'This student does not exist…' is displayed on the screen, and a new record is inserted in the STUDENT table.
To test this script fully, consider running it for two values of student ID. Only one value should correspond to an existing student ID. It is important to note that valid zip code should be provided for both runs. Why do you think it is necessary?
When 319 is provided for the student ID (it is a valid student ID), this exercise produces the output shown below:
Enter value for sv_student_id: 319 old 2: v_student_id NUMBER := &sv_student_id; new 2: v_student_id NUMBER := 319; Enter value for sv_first_name: John old 3: v_first_name VARCHAR2(30) := '&sv_first_name'; new 3: v_first_name VARCHAR2(30) := 'John'; Enter value for sv_last_name: Smith old 4: v_last_name VARCHAR2(30) := '&sv_last_name'; new 4: v_last_name VARCHAR2(30) := 'Smith'; Enter value for sv_zip: 07421 old 5: v_zip CHAR(5) := '&sv_zip'; new 5: v_zip CHAR(5) := '07421'; Student George Eakheit is a valid student PLSQL procedure successfully completed.
Notice that the name displayed by the script does not correspond to the name entered at runtime. Why do you think it occurs?
When 555 is provided for the student ID (it is not a valid student ID), this exercise produces the output shown below:
Enter value for sv_student_id: 555 old 2: v_student_id NUMBER := &sv_student_id; new 2: v_student_id NUMBER := 555; Enter value for sv_first_name: John old 3: v_first_name VARCHAR2(30) := '&sv_first_name'; new 3: v_first_name VARCHAR2(30) := 'John'; Enter value for sv_last_name: Smith old 4: v_last_name VARCHAR2(30) := '&sv_last_name'; new 4: v_last_name VARCHAR2(30) := 'Smith'; Enter value for sv_zip: 07421 old 5: v_zip CHAR(5) := '&sv_zip'; new 5: v_zip CHAR(5) := '07421'; This student does not exist, and will be added to the STUDENT table PLSQL procedure successfully completed.
Next, you can select this new record from the STUDENT table as follows:
SELECT student_id, first_name, last_name
FROM student
WHERE student_id = 555;
STUDENT_ID FIRST_NAME LAST_NAME
---------- ------------------------- ----------------
555 John Smith
Create the following script: For a given instructor ID, check to see whether it is assigned to a valid instructor. Then check the number of sections that are taught by this instructor and display this information on the screen. Answer: Your answer should look similar to the following:
SET SERVEROUTPUT ON
DECLARE
v_instructor_id NUMBER := &sv_instructor_id;
v_name VARCHAR2(50);
v_total NUMBER;
BEGIN
SELECT first_name||' '||last_name
INTO v_name
FROM instructor
WHERE instructor_id = v_instructor_id;
-- check how many sections are taught by this instructor
SELECT COUNT(*)
INTO v_total
FROM section
WHERE instructor_id = v_instructor_id;
DBMS_OUTPUT.PUT_LINE ('Instructor, '||v_name||', teaches '||v_total||
' section(s)');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('This is not a valid instructor');
END;
This script accepts a value for instructor's ID from a user. For a given instructor ID, it determine instructor's name via the SELECT INTO statement. This SELECT INTO statement checks if the ID provided by the user is a valid instructor ID. If this value is not valid, the control of the execution is passed to the exception-handling section of the block where NO_DATA_FOUND exception is raised. As a result, the message 'This is not a valid instructor' is displayed on the screen. On the other hand, if the value provided by the user is a valid instructor ID, the second SELECT INTO statement calculates how many sections are taught by this instructor.
To test this script fully, consider running it for two values of instructor ID. When 105 is provided for the instructor ID (it is a valid instructor ID), this exercise produces the output shown below:
Enter value for sv_instructor_id: 105 old 2: v_instructor_id NUMBER := &sv_instructor_id; new 2: v_instructor_id NUMBER := 105; Instructor, Anita Morris, teaches 10 section(s) PLSQL procedure successfully completed.
When 123 is provided for the instructor ID (it is not a valid student ID), this exercise produces the output shown below:
Enter value for sv_instructor_id: 123 old 2: v_instructor_id NUMBER := &sv_instructor_id; new 2: v_instructor_id NUMBER := 123; This is not a valid instructor PLSQL procedure successfully completed.