Transcript Triggers

Schedule
• Today
Triggers,
Procedures, PL/SQL.
Read Sections 7.4-7.5, 8.1, 8.5.
• Next
Transaction
concepts, security
Read Sections 8.6 – 8.7
SCU Fall 2002
JoAnne Holliday
10–1
SQL Triggers
•
Some differences, including:
The Oracle restriction about not modifying
the relation of the trigger or other relations
linked to it by constraints is not present in
SQL (but Oracle is real; SQL is paper).
2. The action in SQL is a list of (restricted) SQL
statements, not a PL/SQL statement.
1.
SCU Fall 2002
JoAnne Holliday
10–2
Procedures
Stored database objects that use a PL/SQL statement
in their body.
Procedure Declarations
CREATE OR REPLACE PROCEDURE
<name>(<arglist>) AS
<declarations>
BEGIN
<PL/SQL statements>
END;
.
run;
SCU Fall 2002
JoAnne Holliday
10–3
Embedding SQL in a Host Language
• The cursor area is an area for the results of
select statements that result in multiple rows.
• The cursor pointer is a pointer to a row in
the collection of rows retrieved by a SQL
query.
• Example: From within a host language, find
the names and account numbers of customers
with more than the variable amt dollars in
some account.
SCU Fall 2002
JoAnne Holliday
10–4
Embedding SQL
First, declare a cursor and specify the query in SQL:
EXEC SQL
declare c cursor for
select customer-name, account#
from Depositor, Account
where
Depositor.account#=Account.account#
and
Account.balance > :amt
END-EXEC
This defines the cursor c.
SCU Fall 2002
JoAnne Holliday
10–5
Embedded SQL
• The query is not done until you do an
open, then the query is done and the result
is put into the cursor area. (The result may
be larger than the actual buffer that is
allocated as cursor c. The system takes care
of that.)
EXEC SQL
open c
END-EXEC
SCU Fall 2002
JoAnne Holliday
10–6
Embedded SQL
• You can only look at a single row at a time.
This is done with the fetch statement. If
cn and an are variables in the host
language, this will cause the values of one
row in the result to be placed in the
variables:
EXEC SQL
fetch c into :cn :an
END-EXEC
SCU Fall 2002
JoAnne Holliday
10–7
Embedded SQL
• Repeated calls to fetch get successive
rows in the query result. The close
statement causes the database system to
delete the temporary relation that holds the
results of the query.
EXEC SQL
close c
END-EXEC
SCU Fall 2002
JoAnne Holliday
10–8
Using Java and JDBC
• Start with a Connection object, unique to DBMS
and installation (see web page).
• Method createStatement() returns an object of class
Statement (if there is no argument) or
PreparedStatement if there is an SQL statement as
argument.
SCU Fall 2002
JoAnne Holliday
10–9
JDBC
Statement stat1 = myCon.createStatement();
PreparedStatement stat2 =
myCon.createStatement(
"SELECT beer, price " +
"FROM Sells" +
"WHERE bar = 'Joe''s Bar'"
);
myCon is a connection, stat1 is an “empty”
statement object, and stat2 is a (prepared) statement
object that has an SQL statement associated.
SCU Fall 2002
JoAnne Holliday
10–10
Executing Statements
• JDBC distinguishes queries (statements that return
data) from updates (statements that only affect the
database).
• Methods executeQuery() and executeUpdate() are
used to execute these two kinds of SQL statements.
 They
must have an argument if applied to a Statement,
never if applied to a PreparedStatement.
• When a query is executed, it returns an object of
class ResultSet.
SCU Fall 2002
JoAnne Holliday
10–11
Example
stat1.executeUpdate(
"INSERT INTO Sells" +
"VALUES('Brass Rail', 'Bud', 3.00)"
);
ResultSet Menu = stat2.executeQuery();
SCU Fall 2002
JoAnne Holliday
10–12
Getting the Tuples of a ResultSet
• Method Next() applies to a ResultSet and moves a
“cursor” to the next tuple in that set.
 Apply
Next() once to get to the first tuple.
 Next() returns FALSE if there are no more tuples.
• While a given tuple is the current of the cursor, you
can get its ith component by applying to a ResultSet
a method of the form get X(i), where X is the
name for the type of that component.
SCU Fall 2002
JoAnne Holliday
10–13
Example
while(Menu.Next()) {
theBeer = Menu.getString(1);
thePrice = Menu.getFloat(2);
...
}
SCU Fall 2002
JoAnne Holliday
10–14