insert.sql

介绍Oracle PL SQL编程

/*
 * Insert.sql
 * Chapter 4, Oracle10g PL/SQL Programming
 * by Ron Hardman, Mike McLaughlin, Scott Urman
 *
 * This script demonstrates how INSERTs work with PL/SQL
 */

exec clean_schema.trigs
exec clean_schema.procs
exec clean_schema.tables
exec clean_schema.ind

set feedback on escape ~ 

CREATE TABLE logging (
  username VARCHAR2(30),
  datetime TIMESTAMP
);

CREATE TABLE authors (
  id         NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name  VARCHAR2(50)
);

CREATE TABLE books (
  isbn      CHAR(10) PRIMARY KEY,
  category  VARCHAR2(20),
  title     VARCHAR2(100),
  num_pages NUMBER,
  price     NUMBER,
  copyright NUMBER(4),
  author1   NUMBER CONSTRAINT books_author1
            REFERENCES authors(id),
  author2   NUMBER CONSTRAINT books_author2
            REFERENCES authors(id),
  author3   NUMBER CONSTRAINT books_author3
            REFERENCES authors(id)
);

CREATE TABLE inventory (
  isbn         CHAR(10) CONSTRAINT fk_isbn REFERENCES books (isbn),
  status       VARCHAR2(25) CHECK (status IN ('IN STOCK', 'BACKORDERED', 'FUTURE')),
  status_date  DATE,
  amount       NUMBER
);


PROMPT Oracle DBA 101
PROMPT by Marlene Theriault, Rachel Carmichael, James Viscusi
INSERT INTO authors (id, first_name, last_name)
  VALUES (1, 'Marlene', 'Theriault');

INSERT INTO authors (id, first_name, last_name)
  VALUES (2, 'Rachel', 'Carmichael');

INSERT INTO authors (id, first_name, last_name)
  VALUES (3, 'James', 'Viscusi');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
  VALUES ('72121203', 'Oracle Basics', 'Oracle DBA 101', 563, 39.99, 1999, 1, 2, 3);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72121203', 'BACKORDERED', TO_DATE('06-JUN-2004', 'DD-MON-YYYY'), 1000);

PROMPT Oracle8i: A Beginner''s Guide
PROMPT by Michael Abbey, Michael Corey
INSERT INTO authors (id, first_name, last_name)
  VALUES (4, 'Michael', 'Abbey');

INSERT INTO authors (id, first_name, last_name)
  VALUES (5, 'Michael', 'Corey');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72122048', 'Oracle Basics', 'Oracle8i: A Beginner''s Guide', 765, 44.99, 1999, 4, 5);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72122048', 'IN STOCK', NULL, 5000);

PROMPT Oracle Performance Tuning 101
PROMPT by Gaja Vaidyanatha, Kirtikumar Deshpande, John Kostelac
INSERT INTO authors (id, first_name, last_name)
  VALUES (6, 'Gaja', 'Vaidyanatha');

INSERT INTO authors (id, first_name, last_name)
  VALUES (7, 'Kirtikumar', 'Deshpande');

INSERT INTO authors (id, first_name, last_name)
  VALUES (8, 'John', 'Kostelac');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
  VALUES ('72131454', 'Oracle Basics', 'Oracle Performance Tuning 101', 404, 39.99, 2001, 6, 7, 8);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72131454', 'IN STOCK', NULL, 1000);

PROMPT Oracle9i: A Beginner''s Guide
PROMPT by Michael Abbey, Michael Corey, Ian Abramson
INSERT INTO authors (id, first_name, last_name)
  VALUES (9, 'Ian', 'Abramson');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
  VALUES ('72192798', 'Oracle Basics', 'Oracle9i: A Beginner''s Guide', 535, 39.99, 2002, 4, 5, 9);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72192798', 'IN STOCK', NULL, 1000);

PROMPT Oracle Backup ~& Recovery 101
PROMPT by Kenny Smith, Stephan Haisley
INSERT INTO authors (id, first_name, last_name)
  VALUES (10, 'Kenny', 'Smith');

INSERT INTO authors (id, first_name, last_name)
  VALUES (11, 'Stephan', 'Haisley');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72194618', 'Oracle Basics', 'Oracle Backup ~& Recovery 101', 487, 39.99, 2002, 10, 11);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72194618', 'IN STOCK', NULL, 1000);

PROMPT Oracle Enterprise Manager 101
PROMPT by Lars Bo Vanting, Dirk Schepanek
INSERT INTO authors (id, first_name, last_name)
  VALUES (12, 'Lars', 'Bo');

INSERT INTO authors (id, first_name, last_name)
  VALUES (13, 'Dirk', 'Schepanek');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72223073', 'Oracle Basics', 'Oracle Enterprise Manager 101', 592, 39.99, 2002, 12, 13);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72223073', 'IN STOCK', NULL, 1000);

PROMPT Oracle9i DBA 101
PROMPT by Marlene Theriault, Rachel Carmichael, James Viscusi
INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
  VALUES ('72224746', 'Oracle Basics', 'Oracle9i DBA 101', 500, 39.99, 2002, 1, 2, 3);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72224746', 'IN STOCK', NULL, 1000);

PROMPT Oracle Database 10g A Beginner''s Guide
PROMPT by Michael Abbey, Ian Abramson, Michael Corey
INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
  VALUES ('72230789', 'Oracle Basics', 'Oracle Database 10g A Beginner''s Guide', 448, 39.99, 2004, 4, 9, 5);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72230789', 'IN STOCK', NULL, 10000);

PROMPT Oracle PL/SQL 101
PROMPT by Christopher Allen
INSERT INTO authors (id, first_name, last_name)
  VALUES (14, 'Christopher', 'Allen');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('007212606X', 'Oracle Basics', 'Oracle PL/SQL 101', 420, 39.99, 2001, 14);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('007212606X', 'IN STOCK', NULL, 1000);

PROMPT Oracle E-Business Suite Financials Handbook
PROMPT by David James, Graham Seibert, Simon Russell
INSERT INTO authors (id, first_name, last_name)
  VALUES (15, 'David', 'James');

INSERT INTO authors (id, first_name, last_name)
  VALUES (16, 'Graham', 'Seibert');

INSERT INTO authors (id, first_name, last_name)
  VALUES (17, 'Simon', 'Russell');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
  VALUES ('72132302', 'Oracle Ebusiness', 'Oracle E-Business Suite Financials Handbook', 820, 59.99, 2002, 15, 16, 17);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72132302', 'IN STOCK', NULL, 1000);

PROMPT Oracle E-Business Suite Manufacturing ~& Supply Chain Management
PROMPT by Bastin Gerald, Nigel King, Dan Natchek
INSERT INTO authors (id, first_name, last_name)
  VALUES (18, 'Bastin', 'Gerald');

INSERT INTO authors (id, first_name, last_name)
  VALUES (19, 'Nigel', 'King');

INSERT INTO authors (id, first_name, last_name)
  VALUES (20, 'Dan', 'Natchek');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
  VALUES ('72133791', 'Oracle Ebusiness', 'Oracle E-Business Suite Manufacturing ~& Supply Chain Management', 823, 69.99, 2002, 18, 19, 20);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72133791', 'IN STOCK', NULL, 1000);

PROMPT Oracle9i Application Server Portal Handbook
PROMPT by Steve Vandivier, Kelly Cox
INSERT INTO authors (id, first_name, last_name)
  VALUES (21, 'Steve', 'Vandivier');

INSERT INTO authors (id, first_name, last_name)
  VALUES (22, 'Kelly', 'Cox');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72222492', 'Oracle Ebusiness', 'Oracle9i Application Server Portal Handbook', 544, 59.99, 2002, 21, 22);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72222492', 'IN STOCK', NULL, 1000);

PROMPT Oracle 24x7 Tips and Techniques
PROMPT by Venkat Devraj
INSERT INTO authors (id, first_name, last_name)
  VALUES (23, 'Venkat', 'Devraj');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72119993', 'Oracle Server', 'Oracle 24x7 Tips and Techniques', 1005, 49.99, 2000, 23);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72119993', 'IN STOCK', NULL, 1000);

PROMPT Oracle Developer Advanced Forms and Reports
PROMPT by Peter Koletzke, Paul Dorsey
INSERT INTO authors (id, first_name, last_name)
  VALUES (24, 'Peter', 'Koletzke');

INSERT INTO authors (id, first_name, last_name)
  VALUES (25, 'Paul', 'Dorsey');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72120487', 'Oracle Server', 'Oracle Developer Advanced Forms and Reports', 835, 59.99, 2000, 24, 25);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72120487', 'IN STOCK', NULL, 1000);

PROMPT Oracle8i Advanced PL/SQL Programming
PROMPT by Scott Urman
INSERT INTO authors (id, first_name, last_name)
  VALUES (26, 'Scott', 'Urman');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72121467', 'Oracle Server', 'Oracle8i Advanced PL/SQL Programming', 772, 49.99, 2000, 26);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72121467', 'IN STOCK', NULL, 1000);

PROMPT Oracle8i DBA Handbook
PROMPT by Kevin Loney, Marlene Theriault
INSERT INTO authors (id, first_name, last_name)
  VALUES (27, 'Kevin', 'Loney');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72121882', 'Oracle Server', 'Oracle8i DBA Handbook', 979, 59.99, 1999, 27, 1);


INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72121882', 'IN STOCK', NULL, 1000);

PROMPT Web Services Essentials
PROMPT by Ethan Cerami

PROMPT Oracle DBA Tips ~& Techniques
PROMPT by Sumit Sarin
INSERT INTO authors (id, first_name, last_name)
  VALUES (28, 'Sumit', 'Sarin');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72122455', 'Oracle Server', 'Oracle DBA Tips ~& Techniques', 740, 49.99, 2000, 28);

INSERT INTO inventory (isbn, status, status_date, amount)
  VALUES ('72122455', 'IN STOCK', NULL, 1000);

PROMPT Oracle8i: The Complete Reference (Book/CD-ROM Package)
PROMPT by Kevin Loney, George Koch
INSERT INTO authors (id, first_name, last_name)
  VALUES (29, 'George', 'Koch');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72123648', 'Oracle Server', 'Oracle8i: The Complete Reference (Book/CD-ROM Package)', 1308, 69.99, 2000, 27, 29);

INSERT INTO inventory (isbn, status, status_date, amount)