54005.sql

Oracle9i和10g的PL/SQL教程

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


-- Startup text at beginning of chapter
DROP TABLE plsql101_product;
CREATE TABLE plsql101_product ( 
     product_name     VARCHAR2(25), 
     product_price    NUMBER(4,2),
     quantity_on_hand NUMBER(5,0),
     last_stock_date  DATE
     )
;

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');

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

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 Snaphoo', 'CA', '16-JUL-06',5);


-- Figure 5-1 
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'SH', sysdate,10);

INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'SH', sysdate-14,15);
INSERT INTO plsql101_purchase VALUES 
     ('Round Snaphoo', 'SH', sysdate-7,25);
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 'SH', sysdate+7,10);

-- Figure 5-2
-- Data cleanup from the screenshot before
DELETE FROM plsql101_purchase
WHERE  SALESPERSON = 'SH';

-- Start of actual screenshot code
SELECT product_name, product_price
FROM   plsql101_product;

SELECT product_name, ROUND(product_price, 0)
FROM   plsql101_product;


-- Figure 5-3
SELECT UPPER(product_name) FROM plsql101_product;
SELECT LOWER(product_name) FROM plsql101_product;
SELECT INITCAP(product_name) FROM plsql101_product;


-- Figure 5-4
UPDATE plsql101_product
SET    product_name = 'chrome phoobar'
WHERE  product_name = 'Chrome Phoobar';

SELECT * FROM plsql101_product
WHERE  UPPER(product_name) LIKE '%PHOOBAR%';


-- Figure 5-5
SELECT INITCAP(product_name), 
       product_price, 
       quantity_on_hand, 
       last_stock_date
FROM   plsql101_product
WHERE  UPPER(product_name) LIKE '%PHOOBAR%';


-- Figure 5-6
-- Data cleanup from prior screenshot
UPDATE plsql101_product
SET    product_name = 'Chrome Phoobar'
WHERE  product_name = 'chrome phoobar';

-- Preparation for upcoming screenshot
DROP TABLE plsql101_old_item;

CREATE TABLE plsql101_old_item (
     item_id   CHAR(20),
     item_desc CHAR(25)
     )
;

INSERT INTO plsql101_old_item VALUES
     ('LA-101', 'Can, Small');
INSERT INTO plsql101_old_item VALUES
     ('LA-102', 'Can, Large');
INSERT INTO plsql101_old_item VALUES
     ('LA-103', 'Bottle, Small');
INSERT INTO plsql101_old_item VALUES
     ('LA-104', 'Bottle, Large');
INSERT INTO plsql101_old_item VALUES
     ('NY-101', 'Box, Small');
INSERT INTO plsql101_old_item VALUES
     ('NY-102', 'Box, Large');
INSERT INTO plsql101_old_item VALUES
     ('NY-103', 'Shipping Carton, Small');
INSERT INTO plsql101_old_item VALUES
     ('NY-104', 'Shipping Carton, Large');

-- Code for this screenshot
SELECT SUBSTR(item_id, 1, 2) MFGR_LOCATION,
       SUBSTR(item_id, 4, 3) ITEM_NUMBER,
       item_desc
FROM   plsql101_old_item
;


-- Figure 5-7
SELECT item_desc, 
       INSTR(item_desc, 
             ',', 
             1
             ) 
FROM   plsql101_old_item;


-- Figure 5-8
SELECT item_desc,
       SUBSTR(item_desc, 
              1, 
              INSTR(item_desc, 
                    ',', 
                    1
                    ) -1
              )
FROM   plsql101_old_item;


-- Figure 5-9
SELECT item_desc,
       SUBSTR(item_desc, 
              1, 
              INSTR(item_desc, 
                    ',', 
                    1
                    ) -1
              ) CATEGORY,
       SUBSTR(item_desc, 
              INSTR(item_desc, 
                    ',', 
                    1
                    ) +2,
              99
              ) ITEM_SIZE
FROM   plsql101_old_item;


-- Figure 5-10
SELECT 'Item  ' || 
       item_id ||
       ' is described as a ' ||
       item_desc ||
       '.'  "Item Description Sentence"
FROM   plsql101_old_item;


-- Figure 5-11
SELECT 'Item  ' || 
       RTRIM(item_id) ||
       ' is described as a ' ||
       RTRIM(item_desc) ||
       '.'  "Item Description Sentence"
FROM   plsql101_old_item;


-- Figure 5-12
-- The following code would be a good basis for a
-- quiz question asking the student to modify the code
-- to solve the space-padding problem of CHAR columns.
-- This code is NOT SHOWN IN THE BOOK.
SELECT 'The Item ID for a ' ||
       substr(ITEM_DESC,
              instr(ITEM_DESC,
                    ',',
                    1
                    ) +2,
              99
              ) ||
       ' ' ||
       substr(ITEM_DESC,
              1,
              instr(ITEM_DESC,
                    ',',
                    1
                    ) -1
              ) ||        
       ' is: ' ||
       ITEM_ID ||
       '.' "Item ID sentence"
from   PLSQL101_OLD_ITEM;

-- Corrected version:
-- This code is NOT SHOWN IN THE BOOK.
SELECT 'The Item ID for a ' ||
       rtrim(substr(ITEM_DESC,
                    instr(ITEM_DESC,
                          ',',
                          1
                          ) +2,
                    99
                    )
             ) ||
       ' ' ||
       substr(ITEM_DESC,
              1,
              instr(ITEM_DESC,
                    ',',
                    1
                    ) -1
              ) ||        
       ' is: ' ||
       rtrim(ITEM_ID) ||
       '.' "Item ID sentence"
from   PLSQL101_OLD_ITEM;


-- Figure 5-13
INSERT INTO plsql101_product VALUES 
     ('Square Zinculator', 45, 1, SYSDATE);

SELECT * FROM plsql101_product;

SELECT * FROM plsql101_product
WHERE  last_stock_date = '26-MAR-04';

SELECT * FROM plsql101_product
WHERE  TRUNC(last_stock_date) = '26-MAR-04';

-- 
DELETE FROM plsql101_product 
WHERE  product_name = 'Square Zinculator';

INSERT INTO plsql101_product VALUES 
     ('Square Zinculator', 45, 1, trunc(sysdate));

SELECT * FROM plsql101_product
WHERE  last_stock_date = '26-MAR-04';


-- Figure 5-13
SELECT ADD_MONTHS('28-NOV-05', 1) FROM DUAL;
SELECT ADD_MONTHS('29-NOV-05', 1) FROM DUAL;
SELECT ADD_MONTHS('30-NOV-05', 1) FROM DUAL;
SELECT ADD_MONTHS('31-DEC-05', -1) FROM DUAL;


-- Figure 5-14
-- Create new table first
DROP TABLE plsql101_person;

CREATE TABLE plsql101_person (
     person_code VARCHAR2(3),
     first_name  VARCHAR2(15),
     last_name   VARCHAR2(20),
     hire_date   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');

-- Now run the SELECT statement
SELECT first_name, 
       last_name,
       hire_date,
       LAST_DAY(hire_date)+1 INSURANCE_START_DATE
FROM   plsql101_person;


-- Figure 5-15
SELECT product_name,
       quantity_on_hand,
       last_stock_date,
       LAST_DAY(ADD_MONTHS(last_stock_date, 3))+1 RESTOCK_DATE
FROM   plsql101_product
WHERE  quantity_on_hand <= 100
ORDER BY product_name;


-- Figure 5-16
SELECT product_name, 
       TO_CHAR(product_price, '$9,999.00') "Price",
       quantity_on_hand,
       last_stock_date
FROM   plsql101_product;


-- Figure 5-17
SELECT product_name, 
       TO_CHAR(product_price, '$9,999.00') "Price",
       TO_CHAR(quantity_on_hand, '999,999') "On Hand",
       '  '|| TO_CHAR(last_stock_date, 'MON DD, YYYY') "Last Stocked"
FROM   plsql101_product;


-- Figure 5-18
SELECT product_name, 
       product_price,
       quantity_on_hand,
       TO_CHAR(last_stock_date, 'MM-DD-YYYY HH24:MI') "Last Stocked"
FROM   plsql101_product;

UPDATE plsql101_product
SET    last_stock_date = TO_DATE('December 31, 2005, 11:30 P.M.',
                                 'Month dd, YYYY, HH:MI P.M.')
WHERE  product_name LIKE '%Zinc%';

SELECT product_name, 
       product_price,
       quantity_on_hand,
       TO_CHAR(last_stock_date, 'MM-DD-YYYY HH24:MI') "Last Stocked"
FROM   plsql101_product;


-- Figure 5-19
SELECT DECODE(SUBSTR(item_id, 1, 2),
              'LA', 'Western',
              'NY', 'Eastern',
              '* Unknown *'
              )             "Region",
       SUBSTR(item_id, 4,3) "Item ID",
       item_desc
FROM   plsql101_old_item;



-- Figure 5-20
SELECT product_name,
       last_stock_date
FROM   plsql101_product;

SELECT product_name,
       NVL(last_stock_date, '01-JAN-2001') "Last Stocked"
FROM   plsql101_product;


-- Figure 5-21
SELECT product_name,
       NVL(last_stock_date, 'N/A') "Last Stocked"
FROM   plsql101_product;

SELECT product_name,
       NVL(TO_CHAR(last_stock_date), 'N/A') "Last Stocked"
FROM   plsql101_product;


-- Figure 5-22
SELECT * FROM plsql101_product;
-- This line will be ignored.  Oracle will not try to run it.
SELECT * FROM plsql101_purchase;


-- Figure 5-23
/*
This script is designed to show how multiple-line commenting works.
It is used in the PL/SQL 101 book by Oracle Press.
*/

SELECT * FROM plsql101_product; 

SELECT * FROM plsql101_purchase;


-- Figure 5-24
SELECT COUNT(1) FROM plsql101_product;

SELECT COUNT(product_name) FROM plsql101_product;

SELECT COUNT(last_stock_date) FROM plsql101_product;

SELECT COUNT(last_stock_date) / COUNT(product_name) "Populated Records"
FROM plsql101_product;


-- Figure 5-25
SELECT * FROM plsql101_purchase;

SELECT product_name, SUM(quantity) 
FROM   plsql101_purchase
GROUP BY product_name;


-- Figure 5-26
SELECT SUBSTR(product_name, 1, 15) "Product", 
       SUM(quantity) "Total Sold",
       AVG(quantity) "Average",
       COUNT(quantity) "Transactions",
       MIN(quantity) "Fewest",
       MAX(quantity) "Most"
FROM   plsql101_purchase
GROUP BY product_name;
desc