How INSERTs work with PL/SQL : Insert « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. Insert

SQL> CREATE TABLE authors (
  2    id         NUMBER PRIMARY KEY,
  3    first_name VARCHAR2(50),
  4    last_name  VARCHAR2(50)
  5  );

Table created.

SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (1, 'Marlene', 'Theriault');

1 row created.

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

1 row created.

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

1 row created.

SQL>
SQL> CREATE TABLE books (
  2    isbn      CHAR(10) PRIMARY KEY,
  3    category  VARCHAR2(20),
  4    title     VARCHAR2(100),
  5    num_pages NUMBER,
  6    price     NUMBER,
  7    copyright NUMBER(4),
  8    author1   NUMBER CONSTRAINT books_author1
  9              REFERENCES authors(id),
 10    author2   NUMBER CONSTRAINT books_author2
 11              REFERENCES authors(id),
 12    author3   NUMBER CONSTRAINT books_author3
 13              REFERENCES authors(id)
 14  );

Table created.

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

1 row created.

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

1 row created.

SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
  2
  3     v_isbn BOOKS.ISBN%TYPE := '12345678';
  4     v_category BOOKS.CATEGORY%TYPE := 'Oracle Server';
  5     v_title BOOKS.TITLE%TYPE := 'Oracle Information Retrieval';
  6
  7  BEGIN
  8
  9     INSERT INTO books (ISBN,CATEGORY,TITLE,NUM_PAGES,PRICE,
 10                        COPYRIGHT,AUTHOR1)
 11     VALUES (v_isbn, v_category, v_title, 450, 39.95,
 12             2005, 2);
 13
 14     COMMIT;
 15
 16  EXCEPTION
 17     WHEN OTHERS
 18     THEN
 19        DBMS_OUTPUT.PUT_LINE(SQLERRM);
 20        ROLLBACK;
 21  END;
 22  /

PL/SQL procedure successfully completed.

SQL> drop table books;

Table dropped.

SQL>
SQL> drop table authors;

Table dropped.