Mega Code Archive
How INSERTs work with PLSQL
<![CDATA[
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.
]]>