Datatid 1999 #6/Datatid_1999-06.iso/internet/Tango352Promo/P.SQL/PTKPKG.1/UNIVDEMO.SQL

Text File | 1996-06-06 | 6.3 KB | 172 lines

SET BINARYNULL = 255#

-------------------------------------------------------------------------
-- The following Scalable SQL statements create tables, procedures, and
-- triggers that demonstate how these constructs can be used to check the
-- maximum enrollment limit of a college class before allowing a student
-- to enroll.
-------------------------------------------------------------------------

-- Create the table for the General Studies class.  In this simplified
-- example, this table just consists of the identification numbers of
-- the students that are enrolled in the class.
--
CREATE TABLE GeneralStudies
  (StudentId INTEGER(4))#

-- Create the table that defines the maximum enrollment allowed for each
-- of the college classes.  The ClassId column is an identification
-- number associated with each of the college classes.  (In our example,
-- the General Studies class will have an identification number of 1.)
--
CREATE TABLE ClassMax
  (ClassId INTEGER(4), MaxSize INTEGER(4))#

-- Create the table that defines the current number of students enrolled
-- in each of the college classes.  The ClassId column is an
-- identification number associated with each of the college classes.
-- (In our example, the General Studies class will have an identification
-- number of 1.)
--
CREATE TABLE ClassActual
  (ClassId INTEGER(4), ActualSize INTEGER(4))#

-- Create the external procedure that will be called to generate a
-- rejection letter or an acceptance letter for a student who is
-- attempting to enroll in a college class.  This external procedure
-- declaration must have the same name and parameter types as the
-- Letter() procedure defined in the UNIVDEMO.SBL script.
--
CREATE PROCEDURE Letter(IN StudentId  INTEGER(4),
                        IN ClassName  CHAR(60),
                        IN Confirm    CHAR(1));
  EXTERNAL#

-- Create the stored procedure that checks the current enrollment of a
-- college class against the maximum allowed enrollment.  If the class
-- is already at its maximum enrollment, a FailEnrollment condition is
-- signaled and a SBL script is invoked to generate a rejection letter.
-- If the class is not at its maximum enrollment, an AcceptEnrollment
-- condition is signaled and a SBL script is invoked to generate an
-- acceptance letter.
--
-- This procedure will be invoked by a trigger defined for insert
-- operations on the General Studies table so that class enrollment can
-- be checked to determine whether the insert operation should succeed.
--
CREATE PROCEDURE CheckEnrollment(IN student_id INTEGER(4),
                                 IN class_id   INTEGER(4),
                                 IN class_name CHAR(30));
  BEGIN
    DECLARE NumEnrolled INTEGER(4);
    DECLARE MaxEnrollment INTEGER(4);

    DECLARE FailEnrollment CONDITION
      FOR SQLSTATE '09000';

    -- Get the number of students currently enrolled for this class.
    --
    SET NumEnrolled = (SELECT ActualSize
                         FROM ClassActual WHERE ClassId = class_id);

    -- Get the maximum number of students allowed for this class.
    --
    SET MaxEnrollment = (SELECT MaxSize
                           FROM ClassMax WHERE ClassId = class_id);

    -- Check whether the current enrollment is less than the maximum
    -- allowed enrollment.
    --
    IF (NumEnrolled >= MaxEnrollment) THEN
      -- Call the SBL script Letter() to generate a rejection letter.
      --
      Call Letter(student_id, class_name, 'N');
      SIGNAL FailEnrollment;

    ELSE
      -- Call the SBL script Letter() to generate an acceptance letter.
      -- Also increment the number of students enrolled in the class.
      --
      Call Letter(student_id, class_name, 'Y');
      UPDATE ClassActual
        SET ActualSize = (ActualSize + 1)
        WHERE ClassId = class_id;
    END IF;
  END#

-- Create a 'Before Insert' trigger on the GeneralStudies table.  This
-- trigger invokes the CheckEnrollment stored procedure to ensure that
-- this course is not already at its maximum enrollment.  In this
-- example, the GeneralStudies class has a class identification number
-- of 1.
--
CREATE TRIGGER CheckGeneralStudiesLimit
  BEFORE INSERT
  ON GeneralStudies
  REFERENCING NEW AS N
  FOR EACH ROW
  BEGIN
    DECLARE ClassName CHAR(30) = 'General Studies';
    DECLARE Id INTEGER(4) = 1;

    CALL CheckEnrollment(N.StudentId, Id, ClassName);
  END#


------------------------------------------------------------------------
-- The following Scalable SQL statements initialize the ClassMax and
-- ClassActual tables and then insert a series of values into the
-- GeneralStudies table to enroll a group of students in the General
-- Studies class.  These enrollments will succeed and acceptance letters
-- will be generated by the Letter() procedure in UNIVDEMO.SBL script as
-- long as the maximum enrollment has not been reached.  When the
-- maximum enrollment is reached, however, subsequent enrollments will
-- fail and rejection letters will be generated by Letter().
------------------------------------------------------------------------

-- Initialize the ClassMax and ClassActual tables for this example.  The
-- class maximum for the General Studies class (with class identification
-- number 1) is set to 3 and the actual enrollment is initialized to 0.
--
INSERT INTO ClassMax
  VALUES(1, 3)#

INSERT INTO ClassActual
  VALUES(1, 0)#

-- Attempt to enroll 5 students in the General Studies class.  The first
-- 3 enrollments for Anthony Happy (student id 777777777), August Hartig
-- (student id 888888888), and John Jackson (student id 123456789) should
-- succeed, but the last 2 enrollments for Jane Doe (student id
-- 234567890) and John Smith (student id 345678901) should fail.
--
INSERT INTO GeneralStudies
  VALUES(777777777)#

INSERT INTO GeneralStudies
  VALUES(888888888)#

INSERT INTO GeneralStudies
  VALUES(123456789)#

INSERT INTO GeneralStudies
  VALUES(234567890)#

INSERT INTO GeneralStudies
  VALUES(345678901)#

-- Clean up the database by dropping the tables, procedures, and trigger
-- defined above.
--
DROP TRIGGER CheckGeneralStudiesLimit#

DROP PROCEDURE CheckEnrollment#

DROP PROCEDURE Letter#

DROP TABLE ClassActual#

DROP TABLE ClassMax#

DROP TABLE GeneralStudies#