Oracle Web Application Programming for PL/SQL Developers
Solutions Specification for student_personal_info
Description: Create a PSP called Student_Personal_Info.psp. This procedure should accept a student ID value as an IN Parameter.
1. If the student ID value passed in is not null, then the form fields of the page painted by Student_Personal_Info should be filled in with the values of the result of the search for a student that matches the student ID input parameter. This allows your user to update information for a single student.
2. If the student ID value passed in is null, then the page painted by Student_Personal_Info should be the same, with the exception that the form fields should be empty. This allows your user to insert information for a new student. Ensure that you include a default value of "null" for your student ID input parameter.
Sample Screen
Here is an example of the page painted by Student_Personal_Info when a valid student ID value has been passed in. The personal information for this student is retrieved into the form fields so that it may be edited by the user.
Here is an example of the page painted by Student_Personal_Info when no student ID value has been passed in. In this case, the default parameter value of null is used, and the form fields for this page are empty, ready to accept the input of personal information for a new student.
Sample Code
<%@ page language="PL/SQL" %>
<%@ plsql procedure="student_personal_info" %>
<%@ plsql parameter="p_student_id" type="number" default="null" %>
<%! v_student_id student.student_id%TYPE;
v_salutation student.salutation%TYPE;
v_first_name student.first_name%TYPE;
v_last_name student.last_name%TYPE;
v_street_address student.street_address%TYPE;
v_city zipcode.city%TYPE;
v_state zipcode.state%TYPE;
v_zip student.zip%TYPE;
v_phone student.phone%TYPE;
v_employer student.employer%TYPE;
v_registration_date student.registration_date%TYPE;
v_count INTEGER := 0;
%>
<%
------------------------------------------------------
-- FILENAME: student_personal_info.psp
-- FILEDATE: 7.15.2001
-- CREATED BY: Melanie Caffrey
-- DESCRIPTION: Student Personal Info
-- MODIFIED:
-- This PSP is specified as using the PL/SQL language
-- in the page directive.
-- The name, student_personal_info, is provided in the
-- procedure directive.
-- This procedure takes one input parameter, (PSPs
-- do not currently provide the ability for creating
-- output parameters), p_student_id.
-- The default value for this input parameter is null.
-- And, if it isn't obvious by now, you see that one
-- can create comments inside PSP scriptlet tags.
-- Below, several variables are declared between PSP
-- declarative tags to hold information about the
-- particular student your user is editing (updating)
-- or entering (inserting).
------------------------------------------------------
%>
<%!
-- One can also create comments inside of PSP
-- declarative tags.
-- The below SELECT statement obtains information
-- about the student whose student ID value matches
-- that of the input parameter.
-- The information for that particular student is
-- used to populate the variables declared above.
%>
<%
SELECT COUNT(*)
INTO v_count
FROM student
WHERE student_id = p_student_id;
IF v_count > 0
THEN
SELECT student_id,
salutation,
first_name,
last_name,
street_address,
city,
state,
s.zip,
phone,
employer,
registration_date
INTO v_student_id,
v_salutation,
v_first_name,
v_last_name,
v_street_address,
v_city,
v_state,
v_zip,
v_phone,
v_employer,
v_registration_date
FROM student s, zipcode z
WHERE student_id = p_student_id
AND s.zip = z.zip;
ELSE
SELECT MAX(student_id) + 1
INTO v_student_id
FROM student;
END IF;
%>
<%
-- Note the use of the COUNT function used to check
-- how many students exist in the STUDENT table with
-- the student ID value entered in the p_student_id
-- parameter.
-- The v_count variable is initialized to a value of
-- zero (0) when it is declared.
-- Since the COUNT function always returns some
-- value, even if it is zero (0), it provides a way
-- to sidestep the formidable NO DATA FOUND error
-- message when preparing to perform a SELECT
-- statement.
-- IF the COUNT function returns a value of zero,
-- there is no need to perform the subsequent SELECT
-- statement from the STUDENT table, since doing so,
-- if the value of v_count is zero, will surely
-- result in a NO DATA FOUND message.
-- Therefore, it is better to check first, then
-- provide an alternative piece of logic in your
-- code if no such student exists.
-- If no such student exists, then this example
-- assumes that the input parameter has been left
-- with its default value of null, and therefore,
-- the user wishes to create (insert) a new user.
-- The ELSE portion of the above conditional
-- statement readies the code for such a situation
-- by obtaining a new student ID value from the
-- database using the MAX function.
%>
<HTML>
<HEAD>
<TITLE>Student Personal Info</TITLE>
</HEAD>
<BODY BGCOLOR="#99CCCC">
<%
-- Display the student ID, first name and last name
-- values for the entered student, if available, as
-- a heading. Otherwise, display a default heading.
%>
<% IF v_count > 0
THEN
%>
<H2>Personal Information For Student <%= v_student_id %> - <%= v_first_name %> <%= v_last_name %></H2>
<% ELSE %>
<H2>Personal Information For New Student</H2>
<% END IF; %>
<%
-- Paint the form using the values from the variables
-- populated with the SELECT statement, if available.
%>
<FORM NAME="student_personal_form" ACTION="update_student" METHOD="post">
<INPUT TYPE="hidden" NAME="p_student_id" VALUE="<%= v_student_id %>">
<TABLE>
<TR>
<TD>Salutation:</TD>
<TD><INPUT TYPE="text" NAME="p_salutation" VALUE="<%= v_salutation %>"></TD>
</TR>
<TR>
<TD>First Name:</TD>
<TD><INPUT TYPE="text" NAME="p_first_name" VALUE="<%= v_first_name %>"></TD>
</TR>
<TR>
<TD>Last Name:</TD>
<TD><INPUT TYPE="text" NAME="p_last_name" VALUE="<%= v_last_name %>"></TD>
</TR>
<TR>
<TD>Street Address:</TD>
<TD><INPUT TYPE="text" NAME="p_street_address" VALUE="<%= v_street_address %>"></TD>
</TR>
<% IF v_count > 0
THEN
%>
<TR>
<%
-- If your user is editing an existing student's
-- personal information, then display city and state
-- information for that student, and ensure that the
-- button to change zipcode information reads
-- "Change Zipcode." Otherwise, generically display
-- the words "City, State and Zipcode", and create
-- the button to change zipcode information with the
-- displayed value, "Select City, State and
-- Zipcode."
%>
<TD><%= v_city %>, <%= v_state %> </TD>
<TD><INPUT TYPE="text" NAME="p_zip" VALUE="<%= v_zip%>"></TD>
<TD><INPUT TYPE="button" VALUE="Change Zipcode" onClick="javascript:void(window.open('student_zipcode?p_student_id=<%= v_student_id %>', 'zipwindow', 'width=740, height=550, scrollbars=yes, resizable=yes'));"></TD>
</TR>
<% ELSE %>
<TR>
<TD>City, State and Zipcode</TD>
<TD><INPUT TYPE="text" NAME="p_zip" VALUE="<%= v_zip %>"></TD>
<TD><INPUT TYPE="button" VALUE="Select City, State and Zipcode" onClick="javascript:void(window.open('student_zipcode?p_student_id=<%= v_student_id %>', 'zipwindow', 'width=740, height=550, scrollbars=yes, resizable=yes'));"></TD>
</TR>
<% END IF; %>
<TR>
<TD>Phone:</TD>
<TD><INPUT TYPE="text" NAME="p_phone" VALUE="<%= v_phone %>"></TD>
</TR>
<TR>
<TD>Employer:</TD>
<TD><INPUT TYPE="text" NAME="p_employer" VALUE="<%= v_employer %>"></TD>
</TR>
<TR>
<TD>Registration Date:</TD>
<TD><INPUT TYPE="text" NAME="p_registration_date" VALUE="<%= v_registration_date %>"></TD>
</TR>
<TR>
<TD></TD>
<TD ALIGN="left"><INPUT TYPE="submit" VALUE="Submit"> <INPUT TYPE="reset" VALUE="Reset">
</TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>
Select a Sample Application Code Unit
- main_frame (15.2)
- top_menu (13.3) / (15.2)
- splash (13.1)
- student_main_frame (6.3)
- students_left_nav (6.3)
- search_student (12.4)
- get_student (12.4)
- student_list (12.4)
- student_personal_info (12.4)
- student_zipcode (18.5)
- update_student (18.5)
- instruct (Package Spec and Body)
- grade_calc (9.2)
- v_font (9.4)
- c_instruct_list (9.4)
- inst_tbl_type (9.4)
- instruct_table (9.4)
- [PL/SQL block to load instruct_table] (9.4)
- instruct_personal_info (11.3)
- instructor_list_info (11.3)
- showzip (11.3)
- instructors_frame (11.1)
- instructors_left_nav (11.1)
- update_instructor (11.2)
- instructor_list_class (11.3)
- instruct_classes (11.3)
- student_list (11.2)
- classes_main_frame (6.3)
- classes_left_nav (6, Building App)
- classes_list (6, Building App)
- classes_location (13.4)
- classes_location_update (13.4)
- set_cookie (15.2)
- get_cookie (15.2)
- cookie_exists (15.2)
- visitor_name (15.2)
- process_visitor_name (15.2)
- student_list_update (not in book)