autonomous.sql

介绍Oracle PL SQL编程

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

PROMPT Oracle High-Performance SQL Tuning
PROMPT by Donald Burleson
INSERT INTO authors (id, first_name, last_name)
  VALUES (35, 'Donald', 'Burleson');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72190582', 'Oracle Server', 'Oracle High-Performance SQL Tuning', 656, 49.99, 2001, 35);

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

PROMPT Oracle9i for Windows(R) Handbook
PROMPT by Anand Adkoli, Rama Velpuri
INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72190922', 'Oracle Server', 'Oracle9i for Windows(R) Handbook', 528, 49.99, 2002, 33, 32);

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

PROMPT Oracle9i PL/SQL Programming
PROMPT by Scott Urman
INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72191473', 'Oracle Server', 'Oracle9i PL/SQL Programming', 664, 49.99, 2002, 26);

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

PROMPT Oracle9i DBA Handbook
PROMPT by Kevin Loney, Marlene Theriault
INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72193743', 'Oracle Server', 'Oracle9i DBA Handbook', 1008, 59.99, 2002, 27, 1);

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

PROMPT Oracle9i Web Development
PROMPT by Brad Brown
INSERT INTO authors (id, first_name, last_name)
  VALUES (36, 'Brad', 'Brown');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72193883', 'Oracle Server', 'Oracle9i Web Development', 1120, 59.99, 2001, 36);

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

PROMPT Oracle 9i JDBC Programming
PROMPT by Jason Price
INSERT INTO authors (id, first_name, last_name)
  VALUES (37, 'Jason', 'Price');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72222549', 'Oracle Server', 'Oracle 9i JDBC Programming', 608, 49.99, 2002, 37);

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

PROMPT Oracle9i UNIX Administration Handbook
PROMPT by Donald Burleson
INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72223049', 'Oracle Server', 'Oracle9i UNIX Administration Handbook', 560, 49.99, 2002, 35);

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

PROMPT Oracle 9i New Features
PROMPT by Robert Freeman
INSERT INTO authors (id, first_name, last_name)
  VALUES (38, 'Robert', 'Freeman');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72223855', 'Oracle Server', 'Oracle 9i New Features', 244, 29.99, 2002, 38);

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

PROMPT Oracle9i: The Complete Reference
PROMPT by Kevin Loney, George Koch
INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72225211', 'Oracle Server', 'Oracle9i: The Complete Reference', 1256, 74.99, 2002, 27, 29);

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

PROMPT Oracle9i RMAN Backup ~& Recovery
PROMPT by Robert Freeman, Matthew Hart
INSERT INTO authors (id, first_name, last_name)
  VALUES (39, 'Matthew', 'Hart');

INSERT INTO authors (id, first_name, last_name)
  VALUES (52, 'Dave', 'Faulkner');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72226625', 'Oracle Server', 'Oracle9i RMAN Backup ~& Recovery', 608, 49.99, 2003, 38, 39);

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

PROMPT Oracle Database 10g New Features
PROMPT by Robert Freeman
INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72229470', 'Oracle Server', 'Oracle Database 10g New Features', 272, 34.99, 2004, 38);

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

PROMPT Oracle Database 10g XML ~& SQL Design, Build, ~& Manage XML Applications in Java, C, C++, ~& PL/SQL
PROMPT by Mark Scardina, Ben Chang, Jinyu Wang
INSERT INTO authors (id, first_name, last_name)
  VALUES (40, 'Mark', 'Scardina');

INSERT INTO authors (id, first_name, last_name)
  VALUES (41, 'Ben', 'Chang');

INSERT INTO authors (id, first_name, last_name)
  VALUES (53, 'George', 'Hardman');

INSERT INTO authors (id, first_name, last_name)
  VALUES (42, 'Jinyu', 'Wang');

INSERT INTO authors (id, first_name, last_name)
  VALUES (51, 'Scott', 'Boudreaux');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
  VALUES ('72229527', 'Oracle Server', 'Oracle Database 10g XML ~& SQL Design, Build, ~& Manage XML Applications in Java, C, C++, ~& PL/SQL', 656, 49.99, 2004, 40, 41, 42);

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

PROMPT Oracle Database 10g SQL
PROMPT by Jason Price
INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72229810', 'Oracle Server', 'Oracle Database 10g SQL', 624, 49.99, 2004, 37);

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

PROMPT Oracle Database 10g Linux Administration
PROMPT by Wim Coekaerts
INSERT INTO authors (id, first_name, last_name)
  VALUES (43, 'Wim', 'Coekaerts');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72230533', 'Oracle Server', 'Oracle Database 10g Linux Administration', 560, 49.99, 2004, 43);

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

PROMPT Oracle Database 10g PL/SQL Programming
PROMPT by Ron Hardman, Mike McLaughlin, and Scott Urman
INSERT INTO authors (id, first_name, last_name)
  VALUES (44, 'Ron', 'Hardman');

INSERT INTO authors (id, first_name, last_name)
  VALUES (49, 'Mike', 'McLaughlin');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
  VALUES ('72230665', 'Oracle Server', 'Oracle Database 10g PL/SQL Programming', 1008, 54.99, 2004, 44, 49, 26);

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

PROMPT Unbreakable Oracle by Design Oracle Database 10g Security
PROMPT by David Knox
INSERT INTO authors (id, first_name, last_name)
  VALUES (45, 'David', 'Knox');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1)
  VALUES ('72231300', 'Oracle Server', 'Unbreakable Oracle by Design Oracle Database 10g Security', 560, 59.99, 2004, 45);

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

PROMPT Oracle Database 10g High Availability with RAC, Flashback, and Data Guard
PROMPT by Matthew Hart, Scott Jesse
INSERT INTO authors (id, first_name, last_name)
  VALUES (46, 'Scott', 'Jesse');

INSERT INTO authors (id, first_name, last_name)
  VALUES (50, 'Tom', 'Doebler');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2)
  VALUES ('72254289', 'Oracle Server', 'Oracle Database 10g High Availability with RAC, Flashback, and Data Guard', 496, 59.99, 2004, 39, 46);

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

PROMPT Oracle PL/SQL Tips and Techniques
PROMPT by Rich Niemic, Brad Brown, Joe Trezzo
INSERT INTO authors (id, first_name, last_name)
  VALUES (47, 'Rich', 'Niemic');

INSERT INTO authors (id, first_name, last_name)
  VALUES (48, 'Joe', 'Trezzo');

INSERT INTO books (isbn, category, title, num_pages, price, copyright, author1, author2, author3)
  VALUES ('78824389', 'Oracle Server', 'Oracle PL/SQL Tips and Techniques', 942, 49.99, 1999, 47, 36, 48);

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

COMMIT;

SET SERVEROUTPUT ON

PROMPT 
PROMPT ** Create a procedure using PRAGMA AUTONOMOUS_TRANSACTION
PROMPT

CREATE OR REPLACE PROCEDURE logging_ins (
   i_username IN VARCHAR2,
   i_datetime IN TIMESTAMP)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

   INSERT INTO logging (username, datetime)
   VALUES (i_username, i_datetime);

   commit;

END;
/


PROMPT
PROMPT
PROMPT ** Create a procedure that calls the autonomous block
PROMPT

/*
  Sample Data:
  
  EXEC book_ins('12345678','Oracle Server','Oracle Information Retrieval with Oracle Text',440, 35.99, 2005, 44, null, null);
 */

CREATE OR REPLACE PROCEDURE book_ins (
   i_isbn IN BOOKS.ISBN%TYPE,
   i_category IN BOOKS.CATEGORY%TYPE,
   i_title IN BOOKS.TITLE%TYPE,
   i_num_pages IN BOOKS.NUM_PAGES%TYPE,
   i_price IN BOOKS.PRICE%TYPE,
   i_copyright IN BOOKS.COPYRIGHT%TYPE,
   i_author1 IN BOOKS.AUTHOR1%TYPE,
   i_author2 IN BOOKS.AUTHOR1%TYPE,
   i_author3 IN BOOKS.AUTHOR1%TYPE)
IS
BEGIN

   INSERT INTO books (ISBN, CATEGORY, 
                 TITLE, NUM_PAGES,
                 PRICE, COPYRIGHT, AUTHOR1,
                 AUTHOR2, AUTHOR3)
   VALUES (i_isbn, i_category, 
        i_title, i_num_pages,
        i_price, i_copyright, i_author1, 
        i_author2, i_author3);

   LOGGING_INS('PLSQL', systimestamp);

   ROLLBACK;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/


/* Run the following to test

BEGIN
 book_ins('12345678',
 'Oracle Server',
 'Oracle Information Retrieval with Oracle Text',
 440, 
 35.99, 
 2005, 
 44, 
 null, 
 null);
END;
/

COL username FORMAT A10
COL datetime FORMAT A30
SELECT *
FROM logging;

SELECT *
FROM books
WHERE isbn = '12345678';

*/

PROMPT
PROMPT Create the LOGGING_INS_ERROR procedure that fails to commit/rollback
PROMPT


CREATE OR REPLACE PROCEDURE logging_ins_error (
   i_username IN VARCHAR2,
   i_datetime IN TIMESTAMP)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

   INSERT INTO logging (username, datetime)
   VALUES (i_username, i_datetime);

   -- NO COMMIT OR ROLLBACK

END;
/

PROMPT
PROMPT Execute the LOGGING_INS_ERROR procedure -- this will error
PROMPT


EXEC LOGGING_INS_ERROR('PLSQL', systimestamp)

SET ESCAPE OFF