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

    TYPE person_rec_type IS RECORD              -- person-info record
        (name            VARCHAR2(24),
         age             NUMBER(6),
         occup           VARCHAR2(20));

    PROCEDURE insert_record(string IN VARCHAR2,
                            years  IN NUMBER,
                            occ    IN VARCHAR2);

    PROCEDURE delete_record(string IN VARCHAR2);

    PROCEDURE get_person_by_name(string IN VARCHAR2);

    PROCEDURE get_person_by_age(years IN NUMBER);

    PROCEDURE get_person_by_occup(occ IN VARCHAR2);

    PROCEDURE display_person_head(ctitle IN VARCHAR2);

    PROCEDURE display_person_rec(person_rec IN person_rec_type);

    PROCEDURE display_person_end;

END demo_pkg;
/

CREATE OR REPLACE PACKAGE BODY demo_pkg AS

    PROCEDURE insert_record(string IN VARCHAR2,
                            years  IN NUMBER,
                            occ    IN VARCHAR2)
    IS
    BEGIN
        INSERT INTO persontable VALUES(LOWER(string), years, occ);
        commit;

        get_person_by_name(string);
    END insert_record;

    PROCEDURE delete_record(string IN VARCHAR2)
    IS
    BEGIN
        DELETE FROM persontable WHERE name = string;
        commit;

        htp.p('<H1>' || INITCAP(string) || ' is deleted!');
    END delete_record;
    
    PROCEDURE get_person_by_name(string IN VARCHAR2)
    IS
        CURSOR person_cur(string_ IN VARCHAR2)
        IS
            SELECT *
            FROM persontable
            WHERE string_ IS NULL OR name LIKE LOWER(string_);

            person_rec      person_rec_type;

    BEGIN

        display_person_head('Name - ' || string);

        IF NOT person_cur%ISOPEN THEN           -- open the cursor if
            OPEN person_cur(string);            -- 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 get_person_by_age(years IN NUMBER)
    IS
        CURSOR person_cur(years_ IN NUMBER)
        IS
            SELECT *
            FROM persontable
            WHERE years_ IS NULL OR age = years_;

            person_rec      person_rec_type;

    BEGIN

        display_person_head('Age - ' || years);

        IF NOT person_cur%ISOPEN THEN           -- open the cursor if
            OPEN person_cur(years);             -- 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_age;

    PROCEDURE get_person_by_occup(occ IN VARCHAR2)
    IS
        CURSOR person_cur(occ_ IN VARCHAR2)
        IS
            SELECT *
            FROM persontable
            WHERE occ_ IS NULL OR occup LIKE LOWER(occ_);

            person_rec      person_rec_type;

    BEGIN

        display_person_head('Occupation - ' || occ);

        IF NOT person_cur%ISOPEN THEN           -- open the cursor if
            OPEN person_cur(occ);               -- 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_occup;

    PROCEDURE display_person_head(ctitle IN VARCHAR2)
    IS
    BEGIN
        htp.p('<H1>Query Results by Searching "' || ctitle || '"</H1>');
        htp.p('<BR>');
        htp.p('<TABLE BORDER>');
        htp.p('<TR>');
        htp.p('<TD><b>Name</b></TD>');
        htp.p('<TD><b>Age</b></TD>');
        htp.p('<TD><b>Occupation</b></TD>');
        htp.p('</TR>');
    END display_person_head;

    PROCEDURE display_person_rec(person_rec IN person_rec_type)
    IS
        url_string      VARCHAR2(120);
    BEGIN
        htp.p('<TR>');
        htp.p('<TD>' || INITCAP(person_rec.name) || '</TD>');
        htp.p('<TD>' || person_rec.age || '</TD>');
        htp.p('<TD>' || INITCAP(person_rec.occup) || '</TD>');
        htp.p('</TR>');
    END display_person_rec;

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

END demo_pkg;
/