-- -------------------------------------------------
-- Filename: 54009.sql
--
-- Script file for PL/SQL 101 for Oracle 10g
-- Chapter 09
-- Created by Christopher Allen
-- Copyright Oracle Press (c) 2004
-- -------------------------------------------------


-- Startup text at beginning of chapter
DROP TABLE plsql101_purchase;
DROP TABLE plsql101_product;
DROP TABLE plsql101_person CASCADE CONSTRAINTS;
DROP TABLE plsql101_old_item;
DROP TABLE plsql101_purchase_archive;
DROP TABLE plsql101_audit;

CREATE TABLE plsql101_person (
     person_code VARCHAR2(3) PRIMARY KEY,
     first_name  VARCHAR2(15),
     last_name   VARCHAR2(20),
     hire_date   DATE
     )
;

CREATE INDEX plsql101_person_name_index 
ON plsql101_person(last_name, first_name);

ALTER TABLE plsql101_person 
ADD CONSTRAINT plsql101_person_unique UNIQUE (
     first_name,
     last_name,
     hire_date
     )
;

INSERT INTO plsql101_person VALUES
     ('CA', 'Charlene', 'Atlas', '01-FEB-05');
INSERT INTO plsql101_person VALUES
     ('GA', 'Gary', 'Anderson', '15-FEB-05');
INSERT INTO plsql101_person VALUES
     ('BB', 'Bobby', 'Barkenhagen', '28-FEB-05');
INSERT INTO plsql101_person VALUES
     ('LB', 'Laren', 'Baxter', '01-MAR-05');
INSERT INTO plsql101_person VALUES 
     ('LN', 'Linda', 'Norton', '01-JUN-06');

CREATE TABLE plsql101_product (
     product_name     VARCHAR2(25) PRIMARY KEY,
     product_price    NUMBER(4,2),
     quantity_on_hand NUMBER(5,0),
     last_stock_date  DATE
     )
;

ALTER TABLE plsql101_product ADD CONSTRAINT positive_quantity CHECK(
     quantity_on_hand IS NOT NULL
     AND
     quantity_on_hand >=0
     )
;

INSERT INTO plsql101_product VALUES
     ('Small Widget', 99, 1, '15-JAN-06');
INSERT INTO plsql101_product VALUES
     ('Medium Wodget', 75, 1000, '15-JAN-05');
INSERT INTO plsql101_product VALUES
     ('Chrome Phoobar', 50, 100, '15-JAN-06');
INSERT INTO plsql101_product VALUES
     ('Round Chrome Snaphoo', 25, 10000, null);
INSERT INTO plsql101_product VALUES
     ('Extra Huge Mega Phoobar +',9.95,1234,'15-JAN-07');
INSERT INTO plsql101_product VALUES ('Square Zinculator', 
     45, 1, TO_DATE('December 31, 2005, 11:30 P.M.',
                       'Month dd, YYYY, HH:MI P.M.')
     )
;
INSERT INTO plsql101_product VALUES (
     'Anodized Framifier', 49, 5, NULL);
INSERT INTO plsql101_product VALUES (
     'Red Snaphoo', 1.95, 10, '31-DEC-04');
INSERT INTO plsql101_product VALUES (
     'Blue Snaphoo', 1.95, 10, '30-DEC-04')
;

CREATE TABLE plsql101_purchase ( 
     product_name  VARCHAR2(25), 
     salesperson   VARCHAR2(3),
     purchase_date DATE, 
     quantity      NUMBER(4,2)
     )
;

ALTER TABLE plsql101_purchase
ADD PRIMARY KEY (product_name,
                 salesperson,
                 purchase_date
                 )
;

ALTER TABLE plsql101_purchase 
ADD CONSTRAINT reasonable_date CHECK(
     purchase_date IS NOT NULL
     AND
     TO_CHAR(purchase_date, 'YYYY-MM-DD') >= '2003-06-30'
     )
;

ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product FOREIGN KEY 
     (product_name) REFERENCES plsql101_product;

ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_person FOREIGN KEY 
     (salesperson) REFERENCES plsql101_person;

CREATE INDEX plsql101_purchase_product
ON plsql101_purchase(product_name);

CREATE INDEX plsql101_purchase_salesperson
ON plsql101_purchase(salesperson);

INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'CA', '14-JUL-06', 1);
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'BB', '14-JUL-06', 75);
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 'GA', '14-JUL-06', 2);
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'GA', '15-JUL-06', 8);
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'LB', '15-JUL-06', 20);
INSERT INTO plsql101_purchase VALUES 
     ('Round Chrome Snaphoo', 'CA', '16-JUL-06', 5);
INSERT INTO plsql101_purchase VALUES (
     'Small Widget', 'CA', '17-JUL-06', 1)
;

UPDATE plsql101_product
SET    product_price = product_price * .9
WHERE  product_name NOT IN (
       SELECT DISTINCT product_name
       FROM   plsql101_purchase
       )
;

CREATE TABLE plsql101_purchase_archive ( 
     product_name  VARCHAR2(25), 
     salesperson   VARCHAR2(3),
     purchase_date DATE, 
     quantity      NUMBER(4,2)
     )
;

INSERT INTO plsql101_purchase_archive VALUES 
     ('Round Snaphoo', 'BB', '21-JUN-04', 10);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Large Harflinger', 'GA', '22-JUN-04', 50);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Medium Wodget', 'LB', '23-JUN-04', 20);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Small Widget', 'ZZ', '24-JUN-05', 80);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Chrome Phoobar', 'CA', '25-JUN-05', 2);
INSERT INTO plsql101_purchase_archive VALUES 
     ('Small Widget', 'JT', '26-JUN-05', 50);

-- ======================================================================

-- Figure 9-1
/* Performance is current average per order amount as a percentage of the  historical average per order sale amount for a salesperson. Status returns the status of errors if any.
*/
SET SERVEROUTPUT ON
DECLARE
    TYPE performance_type IS RECORD
        (person_code      plsql101_person.person_code%TYPE,
         person_name      plsql101_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   plsql101_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 plsql101_person%ROWTYPE,
         a_perform OUT performance_type)
    IS
        CURSOR history_cur (person varchar2) IS
            SELECT   AVG(tab2.product_price * tab1.quantity) avg_order
            FROM     plsql101_purchase_archive tab1,
                     plsql101_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     plsql101_purchase tbl1,
                     plsql101_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;
/



-- Figure 9-2
DECLARE
    quant NUMBER := 20;
BEGIN
    INSERT INTO plsql101_purchase
    VALUES ('Medium Wodget',
            'LN',
            '18-AUG-05',
            quant);
    IF (SQL%NOTFOUND)
    THEN
        dbms_output.put_line('Insert error?!');
    END IF;
END;
/
SELECT * FROM plsql101_purchase;



-- Figure 9-3
INSERT INTO plsql101_product
VALUES ('Large Harflinger',
        21,
        100,
        '29-AUG-04');

INSERT INTO plsql101_product
VALUES ('Round Snaphoo',
        12,
        144,
        '21-JUL-04');
SELECT * FROM plsql101_product;



-- Figure 9-4
CREATE OR REPLACE PROCEDURE update_prod (
     prod_rec plsql101_product%ROWTYPE
     ) IS
BEGIN
     UPDATE plsql101_product
     SET    last_stock_date = prod_rec.last_stock_date,
            quantity_on_hand = quantity_on_hand 
                               + 
                               prod_rec.quantity_on_hand
     WHERE product_name = prod_rec.product_name;
END update_prod;
/
DECLARE
     a plsql101_product%ROWTYPE;
BEGIN
     a.product_name := 'Small Widget';
     a.product_price := 87;
     a.quantity_on_hand := 31;
     a.last_stock_date := TO_DATE('23-NOV-04');
     update_prod(a);
END;
/
SELECT * FROM plsql101_product;



-- Figure 9-5
set serveroutput on

BEGIN
  DELETE FROM plsql101_product
  WHERE       product_name = 'junk';
  IF (SQL%NOTFOUND)
  THEN
      dbms_output.put_line('No such product');
  END IF;
END;
/



-- Figure 9-6
DROP TABLE plsql101_timetab CASCADE CONSTRAINTS;