Oracle Web Application Programming for PL/SQL Developers

Solutions Specification for update_student

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.

Sample Code

<%@ 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;
%>

Select a Sample Application Code Unit

  1. main_frame (15.2)
  2. top_menu (13.3) / (15.2)
  3. splash (13.1)
  4. student_main_frame (6.3)
  5. students_left_nav (6.3)
  6. search_student (12.4)
  7. get_student (12.4)
  8. student_list (12.4)
  9. student_personal_info (12.4)
  10. student_zipcode (18.5)
  11. update_student (18.5)
  12. 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)
  13. classes_main_frame (6.3)
  14. classes_left_nav (6, Building App)
  15. classes_list (6, Building App)
  16. classes_location (13.4)
  17. classes_location_update (13.4)
  18. set_cookie (15.2)
  19. get_cookie (15.2)
  20. cookie_exists (15.2)
  21. visitor_name (15.2)
  22. process_visitor_name (15.2)
  23. student_list_update (not in book)

Main Solutions Page