54009.sql

Oracle9i和10g的PL/SQL教程

CREATE TABLE plsql101_timetab (
     c1     NUMBER NOT NULL,
     c2     VARCHAR2(30) NULL,
     c3     DATE NULL
     )
;
CREATE OR REPLACE PROCEDURE test_time IS
     maxloops NUMBER := 5000;
     loopcount NUMBER(6,0) := 0;
     starttime CHAR(5) ;
     endtime CHAR(5) ;
     /* Note that since the start and end times are defined in terms
     of the number of seconds since midnight, this routine will not 
     work if the run time crosses over midnight.
     */
     runtime NUMBER;
     processrate NUMBER(20,10);
BEGIN
     starttime := TO_CHAR(SYSDATE,'SSSSS');
     LOOP
          loopcount := loopcount +1;
          INSERT INTO plsql101_timetab (C1, C2,C3)
          VALUES (loopcount, 'TEST ENTRY', SYSDATE);
          COMMIT;
          IF loopcount >= maxloops THEN 
               EXIT;
          END IF;
     END LOOP;
     COMMIT;
     endtime := TO_CHAR(SYSDATE,'SSSSS');
     runtime := TO_NUMBER(endtime)-TO_NUMBER(starttime);
     dbms_output.put_line(runtime || ' seconds' ); 
     processrate := maxloops / runtime; 
     INSERT INTO plsql101_timetab (C1, C2, C3) VALUES
          (loopcount+1,
          TO_CHAR(processrate, '9999999999')||' records per second',
          SYSDATE
          );
END test_time;
/
EXECUTE test_time;
SELECT * FROM plsql101_timetab
WHERE  c1 > 5000;



-- Figure 9-7
TRUNCATE TABLE plsql101_timetab; 
COMMIT;
SET SERVEROUTPUT ON
BEGIN
    FOR trial_count IN 1..10
    LOOP
        test_time;
        COMMIT;
    END LOOP;
END;
/ 
SELECT   *
FROM     plsql101_timetab
WHERE    c1 > 5000
ORDER BY c3;



-- Figure 9-8
TIMING START;
EXECUTE test_time;
COMMIT;
TIMING STOP;



-- Figure 9-9
CREATE OR REPLACE PACKAGE plsql101_pack IS
    DATE_LOADED DATE;
    /* 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.
     */
    TYPE pkg_perform_type IS RECORD
        (person_code      plsql101_person.person_code%TYPE,
         person_name      char(12),
         current_sales    NUMBER(8,2),
         perform_percent  NUMBER(8,1),
         status           char(30)
        );
    CURSOR PKG_PER_CUR RETURN plsql101_person%ROWTYPE;
    /* Compute discounts on orders.
       Input order amount. 
       Returns discount amount (zero for wrong inputs).
     */
    FUNCTION pkg_comp_discounts (order_amt NUMBER) RETURN NUMBER;

    /* This procedure computes the performance and current total sales 
       BY a salesperson. The information for the salesperson is passed
       in as a record a_person. If there are no sales for the day BY 
       the person the 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 pkg_compute_perform
        (a_person plsql101_person%ROWTYPE,
         a_perform OUT pkg_perform_type);
END plsql101_pack;
/

CREATE OR REPLACE PACKAGE BODY plsql101_pack IS
    small_order_amt NUMBER(8,2) := 400;
    large_order_amt NUMBER(8,2) := 1000;
    small_disct NUMBER(4,2)     := 1;
    large_disct NUMBER(4,2)     := 5;

    CURSOR PKG_PER_CUR
    RETURN plsql101_person%ROWTYPE
    IS
        SELECT *
        FROM plsql101_person;

    FUNCTION pkg_comp_discounts (order_amt NUMBER)
    RETURN NUMBER IS
    BEGIN
        IF (order_amt < large_order_amt
             AND
             order_amt >= small_order_amt)
        THEN
                RETURN (order_amt * small_disct / 100);
        ELSIF (order_amt >= large_order_amt)
        THEN
                RETURN (order_amt * large_disct / 100);
        ELSE
                RETURN(0);
        END IF;
    END pkg_comp_discounts;

    PROCEDURE pkg_compute_perform
        (a_person plsql101_person%ROWTYPE,
         a_perform OUT pkg_perform_type)
    IS
        hist_ord_avg NUMBER(8,2) := 0;
        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 purchses exception';
                a_perform.current_sales := 0;
        END;

        BEGIN 
        SELECT   AVG(tab2.product_price * tab1.quantity) avg_order
        INTO     hist_ord_avg
        FROM     plsql101_purchase_archive tab1,
                 plsql101_product tab2
        WHERE    tab1.product_name = tab2.product_name
        GROUP BY tab1.salesperson
        HAVING   tab1.salesperson = a_person.person_code;

            a_perform.perform_percent :=
               100 * (current_avg_sales - hist_ord_avg)/hist_ord_avg;
            a_perform.status := 'All fine';

        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                a_perform.perform_percent := 0;
                IF (a_perform.status IS NULL)
                THEN
                    a_perform.status := 'Erroneous or no history';
                END IF;
        END;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            a_perform.status := 'Exceptions found';
    END pkg_compute_perform;        

BEGIN
    /* The date the package was first loaded */
    DATE_LOADED := SYSDATE;
END plsql101_pack;    
/

DECLARE
   one_perform plsql101_pack.pkg_perform_type;
   cursale char(8);
   disct   char(8);
   perf    char(8);
BEGIN
    dbms_output.put_line('Code' ||
                         ' ' ||
                         'Last Name' ||
                         ' ' ||
                         'Total Sales' ||
                         ' ' ||
                         'Discounts' ||
                         ' ' ||
                         'Performance%' ||
                         ' ' ||
                         'Errors?');
    FOR person_rec IN plsql101_pack.PKG_PER_CUR
    LOOP
        plsql101_pack.pkg_compute_perform(person_rec, one_perform);
        cursale := TO_CHAR(one_perform.current_sales);
        disct   := TO_CHAR(plsql101_pack.pkg_comp_discounts
                            (one_perform.current_sales));
        perf    := TO_CHAR(one_perform.perform_percent);

        dbms_output.put_line(one_perform.person_code ||
                             '  ' ||
                             one_perform.person_name ||
                             '  ' ||
                             cursale ||
                             '  ' ||
                             disct ||
                             '  ' ||
                             perf ||
                             '  ' ||
                             one_perform.status);
    END LOOP;
    
    dbms_output.put_line('Pkg load date seconds ' ||
                         TO_CHAR(plsql101_pack.DATE_LOADED, 'SSSSS'));

    dbms_output.put_line('System date seconds ' ||
                         TO_CHAR(SYSDATE, 'SSSSS'));

END;
/



-- Figure 9-10
-- Add a column to the purchase table. The values will be null.
ALTER TABLE plsql101_purchase
    ADD ORDER_NUMBER NUMBER(10);

-- Create the audit table.
CREATE TABLE plsql101_audit
    (ORDER_NUMBER     NUMBER(10),
     person_code      VARCHAR2(3),
     user_name        CHAR(30),
     user_machine     CHAR(20),
     change_in_quant  NUMBER(5),
     transaction_time DATE,
     FOREIGN KEY (person_code) REFERENCES plsql101_person);

-- Sequence for order numbers
CREATE SEQUENCE order_num_seq;

CREATE OR REPLACE TRIGGER audit_trigger
BEFORE INSERT OR UPDATE ON plsql101_purchase
FOR EACH ROW
DECLARE
    no_name_change EXCEPTION;
    quant_change NUMBER(5) := 0;
BEGIN
    /* Do not allow any changes to product_name for orders.
       Raise exception and reset the values back to original.
     */
    IF (UPDATING
        AND
        (:NEW.product_name <> :OLD.product_name))
    THEN
        RAISE no_name_change;
    END IF;

    /* Create an order number for old non-numbered orders as well
       as new orders that do not have any order number.
     */
    IF (((UPDATING)
         AND
         (:OLD.ORDER_NUMBER IS NULL))
         OR
         ((INSERTING)
          AND
          (:NEW.ORDER_NUMBER IS NULL)))
    THEN
        SELECT order_num_seq.NEXTVAL
        INTO   :NEW.ORDER_NUMBER
        FROM   dual;
    END IF;

    /* Finally, populate the audit table with user name, user's
       computer or terminal name, the change he or she made to
       the quantity, and the time of the change. If inserting, then
       change to quantity is same as new quantity.
     */

    IF (UPDATING)
    THEN
        quant_change := :NEW.quantity - :OLD.quantity;
    ELSE
        quant_change := :NEW.quantity;
    END IF;

    INSERT INTO plsql101_audit
    VALUES (:NEW.ORDER_NUMBER,
            :NEW.salesperson,
            USER,
            USERENV('TERMINAL'),
            quant_change,
            SYSDATE);

     EXCEPTION
         WHEN no_name_change
         THEN
             dbms_output.put_line('Change of product name not allowed');
             dbms_output.put_line('Aborting and resetting to old values');
             :NEW.product_name := :OLD.product_name;
             :NEW.salesperson  := :OLD.salesperson;
             :NEW.ORDER_NUMBER := :OLD.ORDER_NUMBER;
             :NEW.quantity     := :OLD.quantity;
END audit_trigger;
/

col user_name format a30
col user_machine format a20
set pages 9999

SELECT * FROM plsql101_purchase;
SELECT * FROM plsql101_audit;
INSERT INTO plsql101_purchase
     VALUES ('Round Snaphoo', 'LN', '15-NOV-05', 2, NULL);
SELECT * FROM plsql101_purchase WHERE salesperson = 'LN';
SELECT * FROM plsql101_audit;
UPDATE plsql101_purchase SET salesperson = 'LB'
     WHERE salesperson = 'CA' AND quantity = 1;
SELECT * FROM plsql101_purchase WHERE salesperson = 'CA';
SELECT * FROM plsql101_audit;
UPDATE plsql101_purchase SET quantity = 20 WHERE salesperson = 'BB';
SELECT * FROM plsql101_purchase WHERE salesperson = 'BB';
SELECT * FROM plsql101_audit;
UPDATE plsql101_purchase SET product_name = 'Round Snaphoo'
     WHERE salesperson = 'BB';
SELECT * FROM plsql101_purchase WHERE salesperson = 'BB';
SELECT * FROM plsql101_audit;