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:- in MySQL, numeric fields using AUTO_INCREMENT need to be declared as such, in Oracle sequences are separate entities
- MySQL increments the sequence when required, you do not have to do it explicitly
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:
//www-db.stanford.edu/~ullman/fcdb/oracle/or-nonstandard.html
Oracle SQL reference:
//download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/toc.htm
Oracle SQL*Plus quick reference:
//download-west.oracle.com/docs/cd/B10501_01/server.920/a96525/toc.htm
Oracle error messages:
//download-west.oracle.com/docs/cd/B10501_01/server.920/a96525/toc.htm
SQL
Transactions
Exercise
References
See Also
Oracle PL/SQL
Oracle Data Dictionary
Oracle & Java
Advanced Oracle & Java
Tell Your Friends Add to del.icio.us