slides10 - Schedule Today: Feb. 5 (T) x x Triggers, PL/SQL....

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
Winter 2002 Arthur Keller – CS 180 10–1 Schedule Today: Feb. 5 (T) Triggers, PL/SQL. Read Sections 7.4, 8.2. Assignment 4 due. Feb. 7 (TH) PL/SQL, Embedded SQL, CLI, JDBC. Read Sections 8.1, 8.3-8.5. Feb. 12 (T) Advising Day. No class. Reminder: Midterm is Feb. 14 (TH) Covers material through Feb. 7 (TH) lecture and readings (Chapters 1-3, 5-7, 8.1-8.5). Feb. 19 (T) Object-Relational Systems. Read Sections 4.5, 9.4-9.5. Assignment 5 due.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Winter 2002 Arthur Keller – CS 180 10–2 Modification to Views Via Triggers Oracle allows us to “intercept” a modification to a view through an instead-of trigger. Example Likes(drinker , beer ) Sells(bar , beer , price) Frequents(drinker , bar ) CREATE VIEW Synergy AS SELECT Likes.drinker, Likes.beer, Sells.bar FROM Likes, Sells, Frequents WHERE Likes.drinker = Frequents.drinker AND Likes.beer = Sells.beer AND Sells.bar = Frequents.bar;
Background image of page 2
Winter 2002 Arthur Keller – CS 180 10–3 CREATE TRIGGER ViewTrig INSTEAD OF INSERT ON Synergy FOR EACH ROW BEGIN INSERT INTO Likes VALUES( :new.drinker, :new.beer); INSERT INTO Sells(bar, beer) VALUES(:new.bar, :new.beer); INSERT INTO Frequents VALUES( :new.drinker, :new.bar); END; . run
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Winter 2002 Arthur Keller – CS 180 10–4 SQL Triggers Read in text. Some differences, including: 1. 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.
Background image of page 4
Winter 2002 Arthur Keller – CS 180 10–5 PL/SQL Oracle’s version of PSM (Persistent, Stored Modules). Use via sqlplus . A compromise between completely procedural programming and SQL’s very high-level, but limited statements. Allows local variables, loops, procedures, examination of relations one tuple at a time. Rough form: DECLARE declarations BEGIN executable statements END; . run; DECLARE portion is optional. Dot and run (or a slash in place of run; ) are needed to end the statement and execute it.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Winter 2002 Arthur Keller – CS 180 10–6 Simplest Form: Sequence of Modifications Likes(drinker , beer ) BEGIN INSERT INTO Likes VALUES('Sally', 'Bud'); DELETE FROM Likes WHERE drinker = 'Fred' AND beer = 'Miller'; END; . run;
Background image of page 6
Arthur Keller – CS 180 10–7 Procedures Stored database objects that use a PL/SQL statement in their body. Procedure Declarations
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 29

slides10 - Schedule Today: Feb. 5 (T) x x Triggers, PL/SQL....

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online