SET SERVEROUTPUT ON;/
 --ÎÒÊËÞ×ÈÒÜ SERVEROUTPUT
DECLARE
    TYPE performance_type IS RECORD
        (person_code      PERSON.person_code%TYPE,
         person_name      PERSON.last_name%TYPE,
         current_sales    NUMBER(8,2),
         perform_percent  NUMBER(8,1),
         status           VARCHAR2(30)
        );

    one_perform performance_type;

    CURSOR person_cur IS
        SELECT *
        FROM   PERSON;

    /* This procedure computes the performance and current total sales by
       one salesperson. The information for the salesperson is passed in
       as a record named a_person. If there are no sales for the day by the
       person then current_sales is set to zero. If the person has no
       history, for example, the person just joined today, then the
       perform_percent is set to zero.
     */
    PROCEDURE current_performance
        (a_person PERSON%ROWTYPE,
         a_perform OUT performance_type)
    IS
        CURSOR history_cur (person VARCHAR2) IS
            SELECT   AVG(tab2.product_price * tab1.quantity) avg_order
            FROM     PURCHASE_ARCHIVE tab1,
                     PRODUCT tab2
            WHERE    tab1.product_name = tab2.product_name
            GROUP BY tab1.salesperson
            HAVING   tab1.salesperson = person;

        hist_rec history_cur%ROWTYPE;
        current_avg_sales NUMBER(8,2) := 0;

    BEGIN
        a_perform.person_code := a_person.person_code;
        a_perform.person_name := a_person.last_name;
        a_perform.status := NULL;

        BEGIN
            SELECT   SUM(tbl2.product_price * tbl1.quantity),
                     AVG(tbl2.product_price * tbl1.quantity)
            INTO     a_perform.current_sales,
                     current_avg_sales
            FROM     PURCHASE tbl1,
                     PRODUCT tbl2
            WHERE    tbl1.product_name = tbl2.product_name
            GROUP BY tbl1.salesperson
            HAVING   tbl1.salesperson = a_person.person_code;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                a_perform.status := 'Current purchases exception';
                a_perform.current_sales := 0;
        END;

        OPEN history_cur (a_person.person_code);
        FETCH history_cur INTO hist_rec;
        IF (history_cur%NOTFOUND)
        THEN
            a_perform.perform_percent := 0;
            IF (a_perform.status IS NULL)
            THEN
                a_perform.status := 'Erroneous or no history';
            END IF;
        ELSE
            a_perform.perform_percent :=
                 100 * (current_avg_sales - hist_rec.avg_order)/
                                                  hist_rec.avg_order;
            a_perform.status := 'All fine';
        END IF;
        CLOSE history_cur;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            a_perform.status := 'Exceptions found';
    END current_performance;

BEGIN
    FOR person_rec IN person_cur
    LOOP
        current_performance(person_rec, one_perform);

        DBMS_OUTPUT.put_line(one_perform.person_code ||
                             ' ' ||
                             one_perform.person_name ||
                             ' ' ||
                             one_perform.current_sales ||
                             ' ' ||
                             one_perform.perform_percent ||
                             ' ' ||
                             one_perform.status);
    END LOOP;
END;