Write a PL/SQL block that retrieves entire COURSE table into a cursor. Then, ask user to input a courseId to search. If course exists then print its information, but if course does not exist throw a user-defined exception and handle it with a message ‘Course does not exist’ in the COURSE table when you execute the block with a course ID such as CIS555. (user-defined exception problem).

What will be an ideal response?


```
SQL> DECLARE
2 no_course EXCEPTION;
3 CURSOR course_cur IS
4 SELECT * FROM course;
5 crs_id course.CourseId%TYPE := '&CourseId';
6 found BOOLEAN := FALSE;
7 course_rec course%ROWTYPE;
8 BEGIN
9 OPEN course_cur;
10 FETCH course_cur INTO course_rec;
11 WHILE course_cur%FOUND LOOP
12 IF course_rec.CourseId = crs_id THEN
13 found := TRUE;
14 DBMS_OUTPUT.PUT_LINE
15 ('Course Id: ' || course_rec.CourseId);
16 DBMS_OUTPUT.PUT_LINE
17 ('Title ' || course_rec.Title);
18 DBMS_OUTPUT.PUT_LINE
19 ('Credits: ' || course_rec.Credits);
20 DBMS_OUTPUT.PUT_LINE
21 ('PreReq: ' || course_rec.PreReq);
22 END IF;
23 FETCH course_cur INTO course_rec;
24 END LOOP;
25 IF found = false THEN
26 RAISE no_course;
27 END IF;
28 CLOSE course_cur;
29 EXCEPTION
30 WHEN no_course THEN
31 DBMS_OUTPUT.PUT_LINE('No such course: ' || crs_id);
32 END;
33 /
Enter value for courseid: EN100
Course Id: EN100
Title Basic English
Credits: 0
PreReq:

PL/SQL procedure successfully completed.

SQL> /
Enter value for courseid: CIS555
No such course: CIS555

PL/SQL procedure successfully completed.
```

Computer Science & Information Technology

You might also like to view...

In a multiple-document interface (MDI), a JDesktopPane is the parent window and what class is the child window?

a. JChild. b. JInternalFrame. c. JChildWindow. d. JFrame.

Computer Science & Information Technology

When defining keys, the combination of two fields is called a natural primary key

Indicate whether the statement is true or false

Computer Science & Information Technology

A(n) ____________________ lets you record information about people.

Fill in the blank(s) with the appropriate word(s).

Computer Science & Information Technology

RAM memory is a type of nonvolatile storage that holds the critical startup instructions.

Answer the following statement true (T) or false (F)

Computer Science & Information Technology