Databas – Part III
42
Chapter 8 Advanced SQL
Review Questions
8.1
impedance mismatch.
8.2 Describe the general structure of a PL/SQL block.
A PL/SQL block has up to three parts:
8.3 Describe the control statements in PL/SQL. Give examples to illustrate your answers.
PL/SQL supports the usual conditional, iterative, and sequential flow-of-control mechanisms
including:
Conditional IF statement
Conditional CASE statement
Iteration statement (LOOP)
x:=1;
Databas – Part III
43
Iteration statement (WHILE and REPEAT)
Iteration statement (FOR)
8.4 Describe how the PL/SQL statements differ from the SQL standard. Give examples to
illustrate your answers.
There are two versions of PL/SQL: one is part of the Oracle server, and the other is a separate
8.5 What are SQL cursors? Give an example of the use of an SQL cursor.
PL/SQL uses cursors to allow the rows of a query result to be accessed one at a time. In
44
8.6 What are database triggers and what could they be used for?
8.7 Discuss the differences between BEFORE, AFTER, and INSTEAD OF triggers. Give examples
to illustrate your answers.
The BEFORE keyword indicates that the trigger should be executed before an insert is
8.8 Discuss the differences between row-level and statement-level triggers. Give examples to
illustrate your answers.
8.9 Discuss the advantages and disadvantages of database triggers.
Advantages of triggers include:
Elimination of redundant code: Instead of placing a copy of the functionality of the
trigger in every client application that requires it, the trigger is stored only once in the
Databas – Part III
45
Improved integrity: Triggers can be extremely useful for implementing some types of
Triggers also have disadvantages, which include:
Performance overhead: The management and execution of triggers have a performance
Exercises
8.10 Create a stored procedure for each of the queries specified in Exercises 6.7 6.11.
List full details of all hotels.
DECLARE
vHotelNo Hotel.hotelNo%TYPE;
BEGIN
OPEN hotelCursor;
Databas – Part III
46
DECLARE
vHotelNo Hotel.hotelNo%TYPE;
BEGIN
OPEN hotelCursor;
LOOP
Databas – Part III
47
List the names and addresses of all guests in London, alphabetically ordered by name.
DECLARE
BEGIN
OPEN guestCursor;
EXCEPTION
WHEN OTHERS THEN
Databas – Part III
48
OPEN roomCursor;
EXCEPTION
WHEN OTHERS THEN
DECLARE
vHotelNo Booking.hotelNo%TYPE;
BEGIN
OPEN bookingCursor;
LOOP
Databas – Part III
49
8.11 Create a database trigger for the following situations:
(a) The price of all double rooms must be greater than £100.
(b) The price of double rooms must be greater than the price of the highest single room.
(c) A booking cannot be for a hotel room that is already booked for any of the specified
dates.
50
(d) A guest cannot make two bookings with overlapping dates.
(e) Maintain an audit table with the names and addresses of all guests who make
bookings for hotels in London (do not store duplicate guest details).
Databas – Part III
51
8.12 Create an INSTEAD OF database trigger that will allow data to be inserted into the following
view:
CREATE VIEW LondonHotelRoom AS
8.13 Analyze the RDBMS that you are currently using and determine the support the system