rem 
rem $Header: gpapkg.sql,v 1.4 1994/04/22 05:42:02 cracicot Exp $ 
rem 
Rem  Copyright (c) 1993 by Oracle Corporation 
Rem    NAME
Rem      gpapkg.sql - <one-line expansion of the name>
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem    RETURNS
Rem 
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    MODIFIED   (MM/DD/YY)
Rem     tssmith    02/18/94 -  New and improved
Rem     tssmith    09/01/93 -  Fix a small bug 
Rem     tssmith    08/11/93 -  Creation 

--  Create the specification for a package
--  that contains the GET_GPA stored procedure.
--  Use the WITH INTERFACE PROCEDURE clause so that
--  the package procedure can be called from a 3GL.

--  Note that the procedure parameters have PL/SQL
--  datatypes, but in the WITH INTERFACE PROCEDURE clause
--  SQL datatypes must be used, and they must be
--  constrained if required (for example, CHARACTER(15)).
--  The WITH INTERFACE PROCEDURE clause allows you to
--  specify error-handling parameters, such as SQLSTATE,
--  as well as indicator parameters.  These are filled
--  in as the procedure executes.

--  The calling host 3GL application calls the procedure
--  named in the WITH INTERFACE PROCEDURE clause.  This
--  would usually be given the same name as the procedure
--  in the body.  Here it is given a different name, to 
--  demonstrate that (1) you can do this, and (2) it is
--  the WITH INTERFACE PROCEDURE clause name that gets
--  generated in the stub as the procedure to call.

--  C programmers should note that this package will create
--  the package and procedure names in uppercase. So the
--  module compiler will generate stubs that have the names
--  in uppercase, which means that you must call them using
--  upper case in your host program. If you prefer lowercase,
--  simply change the package and procedure names to be
--  quoted lowercase, for example:
--
--    CREATE OR REPLACE PACKAGE "gpa_pkg" AS ...



CREATE OR REPLACE PACKAGE GPA_PKG AS

    PROCEDURE GET_GPA(student_id        IN     NUMBER,
                      student_last_name IN OUT CHARACTER,
                      gpa               OUT    NUMBER)
              WITH INTERFACE PROCEDURE GET_GPA_IF
                     (student_id        INTEGER,
                      student_last_name CHARACTER(15)
                                          INDICATOR sname_ind,
                      sname_ind         SMALLINT,
                      gpa               REAL,
                      sqlstate          CHARACTER(5),
                      sqlcode           INTEGER);
END;
/


--  Create the package body.  There is no need for
--  a WITH INTERFACE PROCEDURE clause in the body.
--  The GET_GPA procedure computes the cumulative GPA
--  over all courses that the student has taken, and returns
--  the computed value.  If the student has received no
--  grades yet, a null is returned (through the indicator
--  parameter).


CREATE OR REPLACE PACKAGE BODY GPA_PKG AS

    PROCEDURE GET_GPA(student_id        IN     NUMBER,
                      student_last_name IN OUT CHARACTER,
                      gpa               OUT    NUMBER) IS

-- The cursor selects all the classes that
-- the student has enrolled in.

    CURSOR get_enroll_curs(sid IN NUMBER) IS
        SELECT enrollment.grade
        FROM   enrollment 
        WHERE  enrollment.student_id = sid
          AND  enrollment.grade IS NOT NULL;
 
-- Declare local variables.
-- gpa_temp needed because gpa is an OUT parameter
    n        NUMBER   := 0;
    grade    NUMBER;
    gpa_temp NUMBER   := 0;


    BEGIN
        gpa := 0.0;

-- Get the last name;
-- if not found, the no_data_found
-- predefined exception is raised.
        SELECT last_name
            INTO student_last_name
            FROM students
            WHERE id = student_id;

-- Otherwise, open the cursor and FETCH.
        open get_enroll_curs(student_id);
        loop
            FETCH get_enroll_curs INTO grade;
            exit when get_enroll_curs%notfound;
            gpa_temp := gpa_temp + grade;
            n := n + 1;
        end loop;

        close get_enroll_curs;

        if n > 0 then
            gpa := gpa_temp / n;
        end if;

    exception

-- The SQLCODE parameter in the WITH INTERFACE
-- parameter list will not be set to +100 because
-- the exception is handled here, but the indicator
-- variable will be set to -1 because of the null
-- assignment.
        when no_data_found then
            student_last_name := null;
    end GET_GPA;

END;
/






