SQL*Plus — schemata — data types — DML & DDL examples — editing commands — using external files — the dual pseudo-table — introduction to transactions — optional exercise — references.
You are now armed to attempt some more complex SQL expressions.
SELECT 'Hello' FROM DUAL -- shows 'Hello' / SELECT SYSDATE FROM DUAL -- shows the date /
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.
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.
Oracle SQL & standard SQL compared:
Oracle SQL reference:
Oracle SQL*Plus quick reference:
Oracle error messages: