54004.sql

Oracle9i和10g的PL/SQL教程

-- -------------------------------------------------
-- Filename: 54004.sql
--
-- Script file for PL/SQL 101 for Oracle 10g
-- Chapter 04
-- 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);

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 4-1 
SELECT   product_nmae
FROM     plsql101_produtc
WHERE    quantity_on_hand >= 100
         AND
         last_stock_date IS NOT NULL
ORDER BY product_name;


-- Figure 4-2
SELECT product_nmae FROM plsql101_product;
change/nmae/name
/


-- Figure 4-3
SELECT   product_nmae
FROM     plsql101_produtc
WHERE    quantity_on_hand >= 100
         AND
         last_stock_date IS NOT NULL
ORDER BY product_name;

1
c/ma/am
2
c/tc/ct
/


-- Figure 4-4
SELECT * FROM plsql101_product;

UPDATE plsql101_product
SET    product_name = 'Large Widget'
WHERE  product_name = 'Small Widget';

-- The following is from text instructions,
-- and it reverses the update done above.
UPDATE plsql101_product
SET    product_name = 'Small Widget'
WHERE  product_name = 'Large Widget';


-- Figure 4-5
SELECT * FROM plsql101_product;


-- Figure 4-6
-- No code relevant


-- Figure 4-7
-- No code relevant


-- Figure 4-8
-- No code relevant


-- Figure 4-9
INSERT INTO plsql101_product VALUES (
     'Extra Huge Mega Phoobar +', 
     9.95, 
     1234, 
     '15-JAN-07')
;

SELECT * FROM plsql101_product;

COLUMN product_price FORMAT 9999.99

SELECT * FROM plsql101_product;

-- The following commands are in the text
-- but not shown in a screen shot...
-- they will affect subsequent screen shots however.
COLUMN quantity_on_hand FORMAT 99,999

SELECT * FROM plsql101_product;

COLUMN product_price FORMAT $99.99

SELECT * FROM plsql101_product;


-- Figure 4-10
SELECT * FROM plsql101_product;

COLUMN product_name FORMAT A10 WORD_WRAP

SELECT * FROM plsql101_product;


-- Figure 4-11
-- Create script file
EDIT c:\plsql101_test.sql

-- Contents of script file
SELECT * FROM plsql101_product;

COLUMN product_name FORMAT A10 WORD_WRAP HEADING 'Name' JUSTIFY CENTER
COLUMN product_price FORMAT $99.99 HEADING 'Price' JUSTIFY RIGHT
COLUMN quantity_on_hand FORMAT 99,999 HEADING 'On|Hand' JUSTIFY RIGHT
COLUMN last_stock_date HEADING 'Last|Stock|Date' JUSTIFY RIGHT

SELECT * FROM plsql101_product;


-- Figure 4-12
	CREATE TABLE plsql101_temp (
     first_name VARCHAR2(15),
     last_name  VARCHAR2(25)
     )
;

INSERT INTO plsql101_temp VALUES ('Joe', 'Smith');
INSERT INTO plsql101_temp VALUES ('Jane', 'Miller');

SELECT * FROM plsql101_temp;

DROP TABLE plsql101_temp;

-- Command to run script file
@c:\plsql101_test


-- Further script files used in chapter, but without screenshots
-- plsql101_test2.sql
SET VERIFY OFF

SELECT product_name, quantity, purchase_date
FROM   plsql101_purchase
WHERE  quantity >= &minimum_quantity_sold
;

SET VERIFY ON

-- plsql101_test3.sql
SET VERIFY OFF

SELECT product_name, quantity, purchase_date
FROM   plsql101_purchase
WHERE  purchase_date = '&date_you_want_to_select'
;

SET VERIFY ON

-- plsql101_test4.sql 
SET VERIFY OFF
SET ECHO OFF

ACCEPT v_earliest_date PROMPT 'Earliest date you would like to see? (dd-mmm-yy): '
ACCEPT v_latest_date PROMPT 'Thank you. Latest date you would like to see? (dd-mmm-yy): '
SELECT product_name, quantity, purchase_date
FROM   plsql101_purchase
WHERE  purchase_date BETWEEN '&v_earliest_date' AND '&v_latest_date'
ORDER BY product_name, quantity
;

SET VERIFY ON
SET ECHO ON