REM $Id: insert_books.sql,v 1.1 2001/11/30 23:19:50 bill Exp $
REM From "Learning Oracle PL/SQL"

REM Script to insert various sample records for books and book_copies tables

REM Note: Must SET DEFINE OFF or else SQL*Plus will intercept the ampersand
REM characters (&)before they get sent to the server.  SQL*Plus usually uses
REM an ampersand to denote a variable that requires the user to supply a
REM value interactively.

SET DEFINE OFF

DECLARE
   PROCEDURE insert_book_no_complaints(isbn_in IN VARCHAR2, title_in IN VARCHAR2,
      summary_in IN VARCHAR2, author_in IN VARCHAR2, date_published_in IN DATE,
      page_count_in IN NUMBER)
   IS
   BEGIN
      INSERT INTO books (isbn, title, summary, author, date_published,
                         page_count)
      VALUES (isbn_in, title_in, summary_in, author_in, date_published_in,
              page_count_in);
   EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
         NULL;
   END;

   PROCEDURE insert_copy_no_complaints(barcode_id_in IN VARCHAR2,
      isbn_in IN VARCHAR2)
   IS
   BEGIN
      INSERT INTO book_copies (barcode_id, isbn)
      VALUES (barcode_id_in, isbn_in);
   EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
         NULL;
   END;

BEGIN

   insert_book_no_complaints ('0-596-00180-0',
      'Learning Oracle PL/SQL',
      'Beginner''s guide to Oracle''s PL/SQL Programming Language',
      'Bill Pribyl with Steven Feuerstein',
      '29-Nov-2001',
      401);

   insert_book_no_complaints ('1-56592-335-9',
      'Oracle PL/SQL Programming',
      'Reference for PL/SQL developers, including examples and best practice '
         || 'recommendations.',
      'Feuestein, Steven, and Bill Pribyl',
      '01-SEP-1997',
      987);

   insert_book_no_complaints ('1-56592-457-6',
      'Oracle PL/SQL Language Pocket Reference',
      'Quick-reference guide for Oracle PL/SQL developers.  Includes Oracle8i '
         || 'coverage.',
      'Feuerstein, Steven, Bill Pribyl, Chip Dawes',
      '01-APR-1999', 94);

   insert_book_no_complaints ('0-14071-483-9',
      'The tragedy of King Richard the Third',
      'Modern publication of popular Shakespeare historical play in which a '
         || 'treacherous royal attempts to steal the crown but dies horseless '
         || 'in battle.',
      'Shakespeare, William',
      '01-AUG-2000',
      158);

   insert_book_no_complaints ('0-14-071415-4',
      'The Tempest',
      'Duke and daughter on enchanted island encounters former enemies in this '
         || 'comic tale of mystery, love, magic, and (ultimately) forgiveness.',
      'Shakespeare, William',
      '01-JAN-1959',
      120);

   insert_book_no_complaints ('0-672-31798-2',
      'Sams Teach Yourself PL/SQL in 21 Days, Second Edition',
      'Tutorial for Oracle''s procedural language organized around presenting '
         || 'language features in a three-week learning schedule.',
      'Gennick, Jonathan, with Tom Luers',
      '01-DEC-1999',
      692);

   insert_book_no_complaints ('0-07-882438-9',
      'Oracle PL/SQL Tips & Techniques',
      'Voluminous tome presenting tips, techniques, and reference material on '
         || 'Oracle''s PL/SQL.',
      'Trezzo, Joseph C.',
      '01-JUL-1999',
      942);

   insert_book_no_complaints ('0-13-794314-8',
      'Building Intelligent Databases with Oracle PL/SQL Triggers and Stored '
         || 'Procedures-2nd ed.',
      'Programmer''s guide to PL/SQL, targeted toward building reusable '
         || 'components for large Oracle applications.',
      'Owens, Kevin T.',
      '01-JUN-1999',
      544);

   insert_book_no_complaints ('1-56592-674-9',
      'Oracle PL/SQL Developer''s Workbook',
      'Beginner, intermediate, and advanced exercises designed to test the '
         || 'reader''s knowledge of Oracle''s PL/SQL programming language.',
      'Feuerstein, Steven, with Andrew Odewahn',
      '01-May-1999',
      588);


   insert_copy_no_complaints ('92644384', '0-596-00180-0');
   insert_copy_no_complaints ('92644385', '0-596-00180-0');
   insert_copy_no_complaints ('92644386', '0-596-00180-0');
   insert_copy_no_complaints ('89679588', '1-56592-335-9');
   insert_copy_no_complaints ('89679589', '1-56592-335-9');
   insert_copy_no_complaints ('29846701', '1-56592-457-6');
   insert_copy_no_complaints ('29846702', '1-56592-457-6');
   insert_copy_no_complaints ('29846703', '1-56592-457-6');
   insert_copy_no_complaints ('12983106', '0-14-071415-4');

END;
/

COMMIT;

SET DEFINE ON