Description: Create a PSP called Get_Student.psp. This PSP is an intermediate PSP to be created solely for the purpose of handling the programmatic decision as to whether to invoke Student_Personal_Info.psp or Student_List.psp.
1. When Search_Student passes the student last_name and first_name parameter information to Get_Student, create a cursor that uses the parameters as its filter (WHERE clause).
2. To handle NULLS don't use too many IF constructs. Instead, make use of Oracle's NVL function.
3. When looping through the cursor, you might try using a record counter to help you accomplish your decision branching logic.
4. If you have more than one matching result in your cursor, invoke Student_List. If you have just one matching record, invoke Student_Personal_Info. Otherwise, display a JavaScript alert to your user informing her that no records match her search criteria. (This will especially come in handy if anyone has entered something along the lines of !@#$% .)
5. VERY IMPORTANT: If you create the ALERT, be sure to recall Search_Student so that your user may try again.
<%@ page language="PL/SQL" %>
<%@ plsql procedure="get_student" %>
<%@ plsql parameter="p_first_name" default="null" %>
<%@ plsql parameter="p_last_name" default="null" %>
<%
------------------------------------------------------
-- FILENAME: get_student.psp
-- FILEDATE: 02.02.2002
-- CREATED BY: Melanie Caffrey
-- DESCRIPTION: Get Student
-- URL : http://local_host/pls/any/get_student
------------------------------------------------------
%>
<%! CURSOR get_student IS
SELECT student_id
FROM student
WHERE NVL(UPPER(first_name), 'QQ') LIKE
NVL(UPPER('%'||p_first_name||'%'),
'QQ')
AND UPPER(last_name) LIKE
NVL(UPPER('%'||p_last_name||'%'), UPPER(last_name));
v_counter INTEGER := 0;
v_student_id student.student_id%TYPE := 0;
%>
<HTML>
<HEAD>
<TITLE>Get Student</TITLE>
</HEAD>
<BODY BGCOLOR="#99CCCC">
<% FOR rec IN get_student
LOOP
v_counter := v_counter + 1;
v_student_id := rec.student_id;
END LOOP;
IF v_counter > 1
THEN
student_list(p_first_name, p_last_name);
ELSIF v_counter = 1
THEN
student_personal_info(v_student_id);
ELSE
%>
<SCRIPT language="JavaScript"> alert("Sorry. No Records Match Your Search Criteria.");
</SCRIPT>
<% search_student;
END IF;
%>
</BODY>
</HTML>