Similar to the above, only this time the program will receive student's first name only (instead of ID) Then program finds full name and address and prints it on the screen (work with STUDENT table here) For both programs please implement NO_DATA_FOUND and TOO_MANY_ROWS exceptions. (please refer to chapter 8 in PL/SQL by Example book) For number 2 program, display a message "Possibly you have two or more students with the first name ...." in TOO_MANY_ROWS exception block. 1* select table_name from user_tables SQL> desc student Name Null? Type ----------------------------------------- -------- ---------------------------- STUDENT_ID NOT NULL NUMBER(8) SALUTATION VARCHAR2(5) FIRST_NAME VARCHAR2(25) LAST_NAME NOT NULL VARCHAR2(25) STREET_ADDRESS VARCHAR2(50) ZIP NOT NULL VARCHAR2(5) PHONE VARCHAR2(15) EMPLOYER VARCHAR2(50) REGISTRATION_DATE NOT NULL DATE CREATED_BY NOT NULL VARCHAR2(30) CREATED_DATE NOT NULL DATE MODIFIED_BY NOT NULL VARCHAR2(30) MODIFIED_DATE NOT NULL DATE
Where shouId I put the while loop in these programs thanks?
Develop a two small PL/SQL programs.
1. Enter instructor ID from the keyboard. The
2. Similar to the above, only this time the program will receive student's first name only (instead of ID)
Then program finds full name and address and prints it on the screen (work with STUDENT table here)
For both programs please implement NO_DATA_FOUND and TOO_MANY_ROWS exceptions. (please refer to chapter 8 in PL/SQL by Example book)
For number 2 program, display a message "Possibly you have two or more students with the first name ...." in TOO_MANY_ROWS exception block.
1* select table_name from user_tables
SQL> desc student
Name Null? Type
----------------------------------------- -------- ----------------------------
STUDENT_ID NOT NULL NUMBER(8)
SALUTATION VARCHAR2(5)
FIRST_NAME VARCHAR2(25)
LAST_NAME NOT NULL VARCHAR2(25)
STREET_ADDRESS VARCHAR2(50)
ZIP NOT NULL VARCHAR2(5)
PHONE VARCHAR2(15)
EMPLOYER VARCHAR2(50)
REGISTRATION_DATE NOT NULL DATE
CREATED_BY NOT NULL VARCHAR2(30)
CREATED_DATE NOT NULL DATE
MODIFIED_BY NOT NULL VARCHAR2(30)
MODIFIED_DATE NOT NULL DATE
SQL> desc instructor
Name Null? Type
----------------------------------------- -------- ----------------------------
INSTRUCTOR_ID NOT NULL NUMBER(8)
SALUTATION VARCHAR2(5)
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
STREET_ADDRESS VARCHAR2(50)
ZIP VARCHAR2(5)
PHONE VARCHAR2(15)
CREATED_BY NOT NULL VARCHAR2(30)
CREATED_DATE NOT NULL DATE
MODIFIED_BY NOT NULL VARCHAR2(30)
MODIFIED_DATE NOT NULL DATE
DECLARE
v_student_id NUMBER := &sv_student_id;
v_enrolled VARCHAR2(3) := 'NO';
BEGIN
DBMS_OUTPUT.PUT_LINE ('Check if the student is enrolled');
SELECT 'YES'
INTO v_enrolled
FROM enrollment
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('The student is enrolled into one course');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE ('The student is not enrolled');
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.PUT_LINE ('The student is enrolled in multiple courses');
End;
This example contains two exceptions in a single exception-handling section. The first exception, NO_DATA_FOUND, will be raised if there are no records in the ENROLLMENT table for a particular student. The second exception, TOO_MANY_ROWS, will be raised if a particular student is enrolled in more than one course.Consider what happens if you run this example for three different values of student ID: 102, 103, and 319. In the first run, when the student ID is 102, the example produces the following output: Check if the student is enrolledThe student is enrolled in multiple courses
DECLARE
v_instructor_id NUMBER := &sv_instructor_id;
v_instructor_name VARCHAR2(50);
BEGIN
SELECT first_name||' '||last_name
INTO v_instructor_name
FROM instructor
WHERE instructor_id = v_instructor_id;
DBMS_OUTPUT.PUT_LINE ('Instructor name is '||v_instructor_name);
WHEN OTHERS
THEN
BMS_OUTPUT.PUT_LINE ('An error has occurred');
end;
When a value of 100 is provided at run time for the variable v_instructor_id, this example produces the following output: An error has occurred
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
