Syronex

SQL Tutorial, Part 2

SQL*Plus — schemata — data types — DML & DDL examples — editing commands — using external files — the dual pseudo-table — introduction to transactions — optional exercise — references.

More Oracle SQL

You are now armed to attempt some more complex SQL expressions.

The dual pseudo-table.

Oracle insists that all  SELECT statements be of the form ``SELECT <attribute> FROM <table>''—even when the returned value does not depend on data stored in the database. The DUAL pseudo-table was introduced to allow such statements.

SELECT 'Hello' FROM DUAL   -- shows 'Hello'
/
SELECT SYSDATE FROM DUAL   -- shows the date 
/

Sequence numbers.

A SEQUENCE is an Oracle object that generates integers according to a specific pattern. Sequence numbers are commonly utilised to supply auto-generated primary keys. The default behaviour of a SEQUENCE is to increment its current value by one to get the next. You may already have used sequences in MySQL, using the AUTO_INCREMENT attributes. Note however the two differences with Oracle's sequences: The following code creates a SEQUENCE that we will then use to insert some more values in the books table.

CREATE SEQUENCE book_seq
/
INSERT INTO books VALUES
(
 'Oliver Twist',
 'Charles Dickens',
 book_seq.NEXTVAL,
 '12-SEP-1839'
)
SELECT book_seq.CURRVAL FROM DUAL  -- shows the current value 
/
SELECT book_seq.NEXTVAL FROM DUAL  -- displays the next value 
/

Apart from the the Oracle peculiarities we have already discussed, you can re-use most of your knowledge of SQL. You may want for example to experiment with the UPDATE and DELETE statements.

Introduction to Transactions

Transaction management  is a broad topic to which you have been introduced in the database lectures. You should refer to your notes for a more detailed coverage of the subject, as we will here just remind a few points. A transaction is a logical unit of work , that could be for example the placement of an order. On completion, a transaction needs to be either confirmed —making all the changes permanent—or cancelled —returning the database into the state it was before starting the transaction.
These two actions are performed in SQL by issuing one of the two commands COMMIT or ROLLBACK.

To experiment with transactions, you will need to work in pairs (say Alice and Bob) and allow the other student to read the data in your books table. So Alice will need to enter:

GRANT SELECT ON books TO bob
/

and Bob to enter:

GRANT SELECT ON books TO alice
/

Now Alice should enter some data in her books table. Bob can then attempt to view the newly inserted data by typing:

SELECT * FROM alice.books
/

Note how you can prefix the table name with its schema to reference other students' tables. Can Bob view the changes Alice has made? What happens if Alice COMMITs the transaction? Try also with ROLLBACK.
Try to relate your observations with your understanding of transactions.

Optional Exercise Suggestion

ISBNs (International Standard Book Number) are unique, 10-digit book identifiers used in the publishing industry. With the help of the references given at the end of this document, create a sequence to generate 10-digit integers for use with the books table.

References

You can copy & paste the following URIs (note that you will need a username/password to access Oracle's web site. You can use database@example.com/database):

Oracle SQL & standard SQL compared:
http://www-db.stanford.edu/~ullman/fcdb/oracle/or-nonstandard.html

Oracle SQL reference:
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/toc.htm

Oracle SQL*Plus quick reference:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96525/toc.htm

Oracle error messages:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96525/toc.htm

Contents
SQL
Transactions
Exercise
References
See Also
Oracle PL/SQL
Oracle Data Dictionary
Oracle & Java
Advanced Oracle & Java
Tell Your Friends
Your name

Your friend's email
Add to del.icio.us