Answer to Question #268395 in Databases | SQL | Oracle | MS Access for tumi

Question

Question 4                                                                                                                                                         [20]

a.      Write a PL/SQL block to retrieve registration information and status for a particular student. The student identification number is supplied at run time by the user. The program should display the relevant information, refer to the sample output given below.

Enter value for studentno: 94001111

Petoors WHY (DoB: 03-Dec-1975) a Male student enrol for INTERNAL AUDITING 1

approved by Morgan, I on date unknown

b.      Make provision to handle any kind of unknown ORACLE error using a ORACLE predefined error handler by displaying appropriate message”ORACLE/Unknown error! – call support stuff”

c.      Using a sample data, 99000278. Display information as in question 5a above. Otherwise, make provision for “invalid or data does not exist error” using ORACLE pre-defined error message “ student 99000278 May not be a registered student”

Answer 1

DECLARE
stu_id student.studid%type:= :stu_id ;
stu_name  student.name%type; 
stu_gender student.gender%type; 
stu_course  student.course%type; 
begin
stu_id := :stu_id;
SELECT name, gender, course INTO stu_name, stu_gender, stu_course FROM student WHERE studid = stu_id;
dbms_output.put_line('Student Name is: '||stu_name||' Gender is: '||stu_gender||'Course is: '||stu_course);
dbms_output.put_line('Petoors WHY (DoB: 03-Dec-1975) a Male student enrol for INTERNAL AUDITING 1 approved by Morgan, I on date unknown');
end;