CREATE OR REPLACE PACKAGE wow_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));

    TYPE phone_rec_type IS RECORD               -- phone-list record
       (person_id       NUMBER(6),
        phone_no        NUMBER(10),
        phone_type      VARCHAR2(10));

    TYPE mix_rec_type IS 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),
        phone_no        NUMBER(10),
        phone_type      VARCHAR2(10));

    PROCEDURE get_person_phone_by_name(name IN VARCHAR2);

    PROCEDURE display_mix_rec(t IN NUMBER, mix_rec IN mix_rec_type);

    PROCEDURE display_mix_head(ctitle IN VARCHAR2);

    PROCEDURE display_error_msg(cname IN VARCHAR2);

    PROCEDURE display_found_msg (t IN NUMBER);
    
    PROCEDURE display_mix_end;
END wow_pkg;
/
CREATE OR REPLACE PACKAGE BODY wow_pkg AS




    PROCEDURE get_person_phone_by_name(name IN VARCHAR2)
    IS
        CURSOR mix_cur(name IN VARCHAR2)
        IS
            SELECT a.person_id, last_name, first_name, title,
                   user_name, address, url,
                   phone_no, phone_type
            FROM person_info_table a, phone_list_table b
            WHERE (name IS NULL OR 
                   last_name LIKE (LOWER(name) || '%')) AND
                  (a.person_id = b.person_id);

        mix_rec   mix_rec_type;
        tempname VARCHAR2(15) :='';
        item_found NUMBER(5) := 0;

    BEGIN

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

        -- Fetch rows into PL/SQL table
        LOOP
            FETCH mix_cur INTO mix_rec;
            EXIT WHEN mix_cur%NOTFOUND;
            IF (item_found = 0) THEN
               display_mix_head(name);
            END IF;
            IF  (tempname = mix_rec.first_name) THEN 
                 display_mix_rec(0,mix_rec);       
            ELSE 
                 display_mix_rec(1,mix_rec);       
                 tempname := mix_rec.first_name;
            END IF;
           item_found := item_found + 1;
        END LOOP;

        CLOSE mix_cur;

        IF (item_found = 0) THEN
           display_error_msg(name);
        ELSE
           display_found_msg(item_found);
           display_mix_end;
        END IF;

    END get_person_phone_by_name;


    PROCEDURE display_mix_rec(t IN NUMBER, mix_rec IN mix_rec_type)
    IS
        url_string      VARCHAR2(120);
    BEGIN
        htp.p('<TR>');
        IF (t = 1)  THEN

           htp.p('<TD>' || INITCAP(mix_rec.last_name) || '</TD>');
           htp.p('<TD>' || INITCAP(mix_rec.first_name) || '</TD>');
           htp.p('<TD>' || INITCAP(mix_rec.title) || '</TD>');
           htp.p('<TD>' || mix_rec.user_name || '</TD>');
           htp.p('<TD>' || UPPER(mix_rec.address) || '</TD>');
           url_string := '<a HREF=http://www.npac.syr.edu/users/' || 
                       mix_rec.user_name ||
                       '/homepage/ TARGET=wow_display>';
           htp.p('<TD>' || url_string || htf.italic(mix_rec.first_name) ||
                   '</a>' || '</TD>');
        ELSE
           htp.p('<TD>  </TD> <TD>  </TD> <TD>  </TD> <TD>  </TD> <TD>  </TD> <TD>  </TD>');
        END IF;      
        htp.p('<TD>' || mix_rec.phone_no || '</TD>');
        htp.p('<TD>' || INITCAP(mix_rec.phone_type) || '</TD>');
        htp.p('</TR>');
    END display_mix_rec;

    PROCEDURE display_mix_head(ctitle IN VARCHAR2)
    IS
	background VARCHAR2(120) := 'http://kayak.npac.syr.edu:3768' || 
 			'/cps616spring96-docs/balsoy/hw7/tan_paper.gif';
    BEGIN
        htp.p('<title>Query Results by Searching "' || ctitle || '"</title>');
	htp.p('<body background=' || background || '>');
        htp.p('<TABLE ALIGN="CENTER" WIDTH=100%>');
	htp.p('<CAPTION>Query Results by Searching "' || ctitle || '"</CAPTION>');
        htp.p('<TR>');
        htp.p('<TH>Last Name</TH>');
        htp.p('<TH>First Name</TH>');
        htp.p('<TH>Title</TH>');
        htp.p('<TH>User Name</TH>');
        htp.p('<TH>Address</TH>');
        htp.p('<TH>URL</TH>');
        htp.p('<TH>Phone NO</TH>');
        htp.p('<TH>Phone Type</TH>');
        htp.p('</TR>');
    END display_mix_head;



    PROCEDURE display_error_msg(cname IN VARCHAR2)
    IS
        background VARCHAR2(120) := 'http://kayak.npac.syr.edu:3768' ||
                        '/cps616spring96-docs/balsoy/hw7/tan_paper.gif';

    BEGIN
        htp.title('Query Results by Searching "' || cname || '"');
        htp.p('<body background=' || background || '>');
	htp.p('<hr width=50%>');
        htp.p('<h1><center>No record is found.</center></h1>');
	htp.p('<hr width=50%>');

    END display_err_msg;



    PROCEDURE display_found_msg (t IN NUMBER)
    IS
    BEGIN
        htp.p('<CAPTION align=bottom>' || t || ' record(s) found!</CAPTION>');
    END display_found_msg;


    PROCEDURE display_mix_end
    IS
    BEGIN
        htp.p('</TABLE><HR>');
    END display_mix_end;

END wow_pkg;
/