Description: Create a PSP called Update_Student.psp. This PSP should be a multi-purpose, reusable procedure in that it can be used to either add a new student or edit an existing student. It is the procedure named in the ACTION attribute of the form painted by the PSP, Student_Personal_Info.
<%@ page language="PL/SQL" %>
<%@ plsql procedure="update_student" %>
<%@ plsql parameter="p_student_id" type="number" default="null" %>
<%@ plsql parameter="p_salutation" default="null" %>
<%@ plsql parameter="p_first_name" default="null" %>
<%@ plsql parameter="p_last_name" default="null" %>
<%@ plsql parameter="p_street_address" default="null" %>
<%@ plsql parameter="p_phone" default="null" %>
<%@ plsql parameter="p_employer" default="null" %>
<%@ plsql parameter="p_registration_date" type="date" default="null" %>
<%@ plsql parameter="p_zip" default="null" %>
<%
---------------------------------------------------------
-- FILENAME: update_student.psp
-- FILEDATE: 02.02.2002
-- CREATED BY: Melanie Caffrey
-- DESCRIPTION: Update Student
-- URL: http://local_host/pls/any/update_student
---------------------------------------------------------
%>
<%! v_count INTEGER := 0;
%>
<% SELECT COUNT(*)
INTO v_count
FROM student
WHERE student_id = p_student_id;
%>
<%
-- If the value passed in for p_student_id matches one
-- already stored in the database, (i.e., the value of
-- v_count is greater than 0), then the information for
-- the student whose student ID value matches that of the
-- p_student_id value is updated.
%>
<%
IF v_count > 0
THEN
UPDATE student
SET salutation = p_salutation,
first_name = p_first_name,
last_name = p_last_name,
street_address = p_street_address,
phone = p_phone,
employer = p_employer,
registration_date = p_registration_date,
zip = p_zip
WHERE student_id = p_student_id;
COMMIT;
%>
<%
-- A JavaScript alert informs the user that, not only
-- does this student exist in the database, but that
-- this student's information has now been successfully
-- updated with the values passed in from the
-- student_personal_info procedure's form.
%>
<SCRIPT LANGUAGE="JavaScript">
alert("This student has been updated.");
</SCRIPT>
<%
-- Another call to student_personal_info then redisplays
-- the newly updated values for this student, just after
-- the update and the informational JavaScript alert, so
-- that the user may double-check her work immediately,
-- without having to re-query.
%>
<% student_personal_info(p_student_id);
%>
<%
-- Otherwise, if the student ID passed in does not match
-- that of any student in the database, then the student
-- information in the student_personal_info procedure's
-- form is for that of a new student. Therefore, the
-- DML action to be taken is an insert statement,
-- instead of an update statement.
%>
<%
ELSE
INSERT INTO student (student_id,
salutation,
first_name,
last_name,
street_address,
phone,
employer,
registration_date,
zip,
created_by,
created_date,
modified_by,
modified_date)
VALUES (p_student_id,
p_salutation,
p_first_name,
p_last_name,
p_street_address,
p_phone,
p_employer,
NVL(p_registration_date, SYSDATE),
p_zip,
USER,
SYSDATE,
USER,
SYSDATE);
COMMIT;
%>
<%
-- Notice that the message displayed in this JavaScript
-- alert, following a successful insert statement, is
-- slightly different from the one displayed in the
-- JavaScript alert that immediately follows a
-- successful update statement.
%>
<SCRIPT LANGUAGE="JavaScript">
alert("This new student has been added.");
</SCRIPT>
<%
-- Here again, you should always redisplay the form so
-- that the user may double-check her work.
%>
<% student_personal_info(p_student_id);
END IF;
%>