/*  File: simple_pkg.sql  */
CREATE OR REPLACE PACKAGE simple_pkg AS

    TYPE person_rec_type IS RECORD              -- person-info record
        (person_id       NUMBER(6),
         last_name       VARCHAR2(15),
         first_name      VARCHAR2(15),
         title           VARCHAR2(8),
         user_name       VARCHAR2(8),
         address         VARCHAR2(12),
         url             VARCHAR2(20));

    PROCEDURE get_person_by_name(name IN VARCHAR2);

    PROCEDURE display_person_head(ctitle IN VARCHAR2);

    PROCEDURE display_person_rec(person_rec IN person_rec_type);

    PROCEDURE display_person_end;

END simple_pkg;
/

CREATE OR REPLACE PACKAGE BODY simple_pkg AS

    PROCEDURE get_person_by_name(name IN VARCHAR2)
    IS
        CURSOR person_cur(name IN VARCHAR2)
        IS
            SELECT *
            FROM person_info_table
            WHERE name IS NULL OR
            last_name LIKE ('%' || LOWER(name) || '%') OR
            first_name LIKE ('%' || LOWER(name) || '%');

            person_rec      person_rec_type;

    BEGIN

        display_person_head(name);

        IF NOT person_cur%ISOPEN THEN           -- open the cursor if
            OPEN person_cur(name);              -- not open yet
        END IF;

        -- Fetch rows into PL/SQL table

        LOOP
            FETCH person_cur INTO person_rec;
            EXIT WHEN person_cur%NOTFOUND;
            display_person_rec(person_rec);
        END LOOP;

        CLOSE person_cur;

        display_person_end;

    END get_person_by_name;

    PROCEDURE display_person_head(ctitle IN VARCHAR2)
    IS
    BEGIN
        v11.htp.title('Query Results by Searching "' || ctitle || '"');
        v11.htp.p('<H1>Query Results by Searching "' || ctitle || '"</H1>');
        v11.htp.p('<BR>');
        v11.htp.p('<TABLE BORDER ALIGN="CENTER">');
        v11.htp.p('<TR>');
        v11.htp.p('<TD>Last Name</TD>');
        v11.htp.p('<TD>First Name</TD>');
        v11.htp.p('<TD>Title</TD>');
        v11.htp.p('<TD>User Name</TD>');
        v11.htp.p('<TD>Address</TD>');
        v11.htp.p('<TD>URL</TD>');
        v11.htp.p('</TR>');
    END display_person_head;

    PROCEDURE display_person_rec(person_rec IN person_rec_type)
    IS
        url_string      VARCHAR2(120);
    BEGIN
        v11.htp.p('<TR>');
        v11.htp.p('<TD>' || INITCAP(person_rec.last_name) || '</TD>');
        v11.htp.p('<TD>' || INITCAP(person_rec.first_name) || '</TD>');
        v11.htp.p('<TD>' || INITCAP(person_rec.title) || '</TD>');
        v11.htp.p('<TD>' || person_rec.user_name || '</TD>');
        v11.htp.p('<TD>' || UPPER(person_rec.address) || '</TD>');
        url_string := '<a HREF=http://www.npac.syr.edu/users/' ||
                     person_rec.user_name ||
                     '/homepage/ TARGET=ows_display>';
        v11.htp.p('<TD>' || url_string || v11.htf.italic(person_rec.first_name) ||
                '</a>' || '</TD>');
        v11.htp.p('</TR>');
    END display_person_rec;

    PROCEDURE display_person_end
    IS
    BEGIN
        v11.htp.p('</TABLE>');
    END display_person_end;

END simple_pkg;
/