set serveroutput on
set echo on
--
--
-- joseph speigle
-- lab 11
-- july 22, 2002
-- PL half of the PL/SQL class
--
-- problem
-- one
-- write a function that receives a studen id as input
-- locate that student and produce the requested display
--
--
CREATE OR REPLACE function find_student
(s_iid student.student_id%TYPE)
return varchar2
IS
v_student varchar2(100);
BEGIN
SELECT student_id||' '|| last_name||' '||first_name||' '||phone INTO v_student FROM student WHERE student_id = s_iid;
RETURN v_student;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN('no such student');
WHEN OTHERS THEN return('error');
END find_student;
.
/
--
--
-- run that function
declare
v_description varchar2(200);
begin
v_description := find_student(112);
DBMS_OUTPUT.PUT_LINE(v_description);
v_description := find_student(11200);
DBMS_OUTPUT.PUT_LINE(v_description);

end;

/
-- problem
-- two
-- ...
CREATE OR REPLACE FUNCTION what_grade
(gtc grade.grade_type_code%TYPE)
return varchar2
IS
v_description grade_type.description%TYPE;
BEGIN

        SELECT description INTO v_description FROM grade_type WHERE grade_type_code = 'gtc';
        return v_description;

EXCEPTION
WHEN NO_DATA_FOUND

        THEN
        return('there wasnt a description for that grade_type');
WHEN OTHERS
        THEN
        return('error');

END what_grade;
.
/
--
--
-- call with good and bad data
DECLARE
v_description varchar2(200);
BEGIN
v_description := what_grade('HI');
DBMS_OUTPUT.PUT_LINE("The assignment is: " || v_description);
v_description := what_grade('LO');
DBMS_OUTPUT.PUT_LINE("The assignment is: " || v_description);
END;
.
/