54008.sql

Oracle9i和10g的PL/SQL教程

-- -------------------------------------------------
-- Filename: 54008.sql
--
-- Script file for PL/SQL 101 for Oracle 10g
-- Chapter 08
-- 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;
DROP TABLE plsql101_old_item;
DROP TABLE plsql101_purchase_archive;

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 8-1
set serveroutput on

DECLARE
        Num_a NUMBER := 6;
        Num_b NUMBER;
BEGIN
        Num_b := 0;
        Num_a := Num_a / Num_b;
        Num_b := 7;
        dbms_output.put_line(' Value of Num_b ' || Num_b);
EXCEPTION
        WHEN ZERO_DIVIDE
THEN
              dbms_output.put_line('Trying to divide by zero');
              dbms_output.put_line(' Value of Num_a ' || Num_a);
              dbms_output.put_line(' Value of Num_b ' || Num_b);
END;
/



-- Figure 8-2
set serveroutput on
CREATE PROCEDURE my_first_proc IS
        greetings VARCHAR2(20);
BEGIN
        greetings := 'Hello World';
        dbms_output.put_line(greetings);
END my_first_proc;
/

EXECUTE my_first_proc;

BEGIN
        my_first_proc;
END;
/



-- Figure 8-3
CREATE  PROCEDURE hike_prices (old_price NUMBER,
                               percent_hike NUMBER := 5,
                               new_price OUT NUMBER)
IS
BEGIN
        new_price := old_price + old_price * percent_hike / 100;
END hike_prices;
/

set serveroutput on
DECLARE
        price_to_hike NUMBER(6,2) := 20;
        hiked_price NUMBER(6,2) := 0;
BEGIN
        dbms_output.put_line('Price before hike ' || price_to_hike);
        dbms_output.put_line('hiked_price before hike ' || hiked_price);
        hike_prices (old_price => price_to_hike,
                     new_price => hiked_price);
        dbms_output.put_line('price_to_hike after hike ' || price_to_hike);
        dbms_output.put_line('hiked_price after hike ' || hiked_price);
END;
/



-- Figure 8-4
set serveroutput on
DECLARE
product_quant        NUMBER;
BEGIN
      SELECT  quantity_on_hand 
      INTO    product_quant
      FROM    plsql101_product
      WHERE   product_name = 'Small Widget';
dbms_output.put_line ('Small Widget ' || product_quant);
END;
/



-- Figure 8-5
CREATE FUNCTION compute_discounts (order_amt NUMBER)
RETURN NUMBER IS
        small_order_amt NUMBER := 400;
        large_order_amt NUMBER := 1000;
        small_disct NUMBER := 1;
        large_disct NUMBER := 5;
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 compute_discounts;
/

set serveroutput on
DECLARE
        tiny NUMBER := 20;
        med NUMBER := 600;
        big NUMBER := 4550;
        wrong NUMBER := -35;
BEGIN
        dbms_output.put_line (' Order     AND      Discount ');
        dbms_output.put_line (tiny || ' ' || compute_discounts(tiny));
        dbms_output.put_line (med || ' ' || compute_discounts (med));
        dbms_output.put_line (big || ' ' || compute_discounts (big));
        dbms_output.put_line (wrong || ' ' || compute_discounts (wrong));
END;
/



-- Figure 8-6
set serveroutput on
DECLARE
        just_a_num NUMBER := 1;
BEGIN
        <<just_a_loop>>
        LOOP
                dbms_output.put_line(just_a_num);
        EXIT just_a_loop
        WHEN (just_a_num >= 10);
                just_a_num := just_a_num + 1;
        END LOOP;
END;
/



-- Figure 8-7
set serveroutput on
DECLARE
     just_a_num NUMBER := 1;
BEGIN
     WHILE (just_a_num <= 10) LOOP
          dbms_output.put_line(just_a_num);
          just_a_num := just_a_num + 1;
     END LOOP;
END;
/



-- Figure 8-8
set serveroutput on
BEGIN
        FOR just_a_num IN 1..10
        LOOP
                dbms_output.put_line(just_a_num);
        END LOOP;
END;
/



-- Figure 8-9
SELECT product_name, product_price
FROM   plsql101_product;

DECLARE
     CURSOR product_cur IS
     SELECT * FROM plsql101_product
     FOR UPDATE OF product_price;
BEGIN
     FOR product_rec IN product_cur
     LOOP
          UPDATE plsql101_product
          SET    product_price = (product_rec.product_price * 0.97)
          WHERE  CURRENT OF product_cur;
     END LOOP;
END;
/

SELECT product_name, product_price
FROM   plsql101_product;



-- Figure 8-10
-- This procedure computes the commissions for salespersons. 
-- It prints out the salesperson's code, his or her total sales, 
-- and corresponding commission.
-- No inputs. No errors are reported and no exceptions are raised.
/* Logic: A cursor to create a join between PLSQL101_PRODUCT and 
PLSQL101_PURCHASE on PRODUCT_NAME column is done. 
The result is ordered by salesperson. 
Outer loop starts with a new salesperson and inner loop 
processes all rows for one salesperson. 
*/
CREATE OR REPLACE PROCEDURE do_commissions IS
     commission_rate NUMBER  := 2   ;
     total_sale      NUMBER  := 0   ;
     current_person  CHAR(3) := ' ' ;
     next_person     CHAR(3)        ;
     quantity_sold   NUMBER  := 0   ;
     item_price      NUMBER  := 0   ;
     CURSOR sales_cur IS
          SELECT purc.salesperson,
                 purc.quantity,
                 prod.product_price
          FROM   plsql101_purchase purc,
                 plsql101_product  prod
          WHERE  purc.product_name = prod.product_name
          ORDER BY salesperson;
BEGIN
     OPEN sales_cur;
     LOOP
          FETCH sales_cur INTO
                next_person, quantity_sold, item_price;
          WHILE (next_person = current_person
                 AND
                 sales_cur%FOUND)
          LOOP
               total_sale := 
                     total_sale + (quantity_sold * item_price);
                FETCH sales_cur INTO 
                     next_person, quantity_sold, item_price;
          END LOOP;
          IF (sales_cur%FOUND)
          THEN
               IF (current_person != next_person)
               THEN
                    IF (current_person != ' ' )
                    THEN
                         dbms_output.put_line
                            (current_person ||
                             ' ' ||
                             total_sale ||
                             ' ' ||
                             total_sale * commission_rate / 100);
                    END IF;
                    total_sale := quantity_sold * item_price;
                    current_person := next_person;
               END IF;
          ELSE IF (current_person != ' ')
          THEN
                 dbms_output.put_line(current_person ||
                               ' ' ||
                               total_sale ||
                               ' ' ||
                               total_sale * commission_rate / 100);
               END IF;
          END IF;
          EXIT WHEN sales_cur%NOTFOUND;
          END LOOP;
          CLOSE sales_cur;
     END do_commissions;
/

SELECT purc.salesperson,
       purc.quantity,
       prod.product_price
FROM   plsql101_purchase purc,
       plsql101_product  prod
WHERE  purc.product_name = prod.product_name
ORDER BY salesperson;

set serveroutput on
EXECUTE do_commissions;



-- Figure 8-11
set serveroutput on
DECLARE
     Num_a NUMBER := 6;
     Num_b NUMBER;
BEGIN
     Num_b := 0;
     Num_a := Num_a / Num_b;
     Num_b := 7;
     dbms_output.put_line(' Value of Num_b ' || Num_b);
EXCEPTION
     WHEN ZERO_DIVIDE THEN
          DECLARE
               err_num NUMBER        := SQLCODE;
               err_msg VARCHAR2(512) := SQLERRM;
          BEGIN
               dbms_output.put_line('ORA Error Number '  || err_num );
               dbms_output.put_line('ORA Error message ' || err_msg);
               dbms_output.put_line(' Value of Num_a is '   || Num_a);
               dbms_output.put_line(' Value of Num_b is '   || Num_b);
          END;
END;
/



-- Figure 8-12
set serveroutput on
DECLARE
     quantity1 NUMBER := -2;
     quantity2 NUMBER := 3;
     total NUMBER := 0;
     quantity_must_positive EXCEPTION;
     FUNCTION find_cost (quant NUMBER) RETURN NUMBER IS
     BEGIN
          IF (quant > 0) 
          THEN
               RETURN(quant * 20);
          ELSE
               RAISE quantity_must_positive;
          END IF;
     END find_cost;
BEGIN
     total := find_cost (quantity2);
     total := total + find_cost(quantity1);
EXCEPTION
     WHEN quantity_must_positive
     THEN
          dbms_output.put_line('Total until now: ' || total);
          dbms_output.put_line('Tried to use negative quantity ');
END;
/