Mo dication to Views Via T riggers Oracle allo ws us to - PDF document

Mo di�cation to Views Via T riggers Oracle allo ws us to \in tercept" a mo di�cation 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; 1

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 2

SQL T riggers Read in text. � Some di�erences, including: � 1. The Oracle restriction ab out not mo difying the relation of the trigger or other relations link ed to it b y constrain ts is not presen t in SQL (but Oracle is real; SQL is pap er). 2. The action in SQL is a list of (restricted) SQL statemen ts, not a PL/SQL statemen t. 3

PL/SQL Oracle's v ersion of PSM (P ersisten t, Stored � Mo dules). ✦ Use via sqlplus . A compromise b et w een completely pro cedural � programming and SQL's v ery high-lev el, but limited statemen ts. Allo ws lo cal v ariables, lo ops, pro cedures, � examination of relations one tuple at a time. Rough form: � DECLARE declarations BEGIN executable statemen ts END; . run; p ortion is optional. DECLARE � Dot and (or a slash in place of run; ) are run � needed to end the statemen t and execute it. 4

Simplest F orm: Sequence of Mo di�cations Likes(drinker , beer ) BEGIN INSERT INTO Likes VALUES('Sally', 'Bud'); DELETE FROM Likes WHERE drinker = 'Fred' AND beer = 'Miller'; END; . run; 5

Pro cedures Stored database ob jects that use a PL/SQL statemen t in their b o dy . Pro cedure Declarations CREATE OR REPLACE PROCEDURE < name > ( < arglist > ) AS < declarations > BEGIN < PL/SQL statemen ts > END; . run; 6

Argumen t list has name-mo de-t yp e triples. � ✦ Mo de: IN, OUT, or IN OUT for read- only , write-only , read/write, resp ectiv ely . ✦ T yp es: standard SQL + generic t yp es lik e = an y in teger or real t yp e. NUMBER ✦ Since t yp es in pro cedures matc h must their t yp es in the DB sc hema, y ou should generally use an expression of the form relation.attribut e %TYP E to capture the t yp e correctly . 7

Example A pro cedure to tak e a b eer and price and add it to Jo e's men u. Sells(bar , beer , price) CREATE PROCEDURE joeMenu( b IN Sells.beer%TYPE, p IN Sells.price%TYPE ) AS BEGIN INSERT INTO Sells VALUES('Joe''s Bar', b, p); END; . run; Note \ run " only stores the pro cedure; it � do esn't execute the pro cedure. 8

In v oking Pro cedures A pro cedure call ma y app ear in the b o dy of a PL/SQL statemen t. Example: � BEGIN joeMenu('Bud', 2.50); joeMenu('MooseDr ool', 5.00); END; . run; 9

Assignmen t Assign expressions to declared v ariables with := . Branc hes < condition > IF THEN < statemen t(s) > ELSE < statemen t(s) > END IF; But in nests, use in place of IF . ELSIF ELSE � Lo ops LOOP . . . < condition > EXIT WHEN . . . END LOOP; 10

Queries in PL/SQL 1. allo w retriev al in to a Single-r ow sele cts v ariable of the result of a query that is guaran teed to pro duce one tuple. 2. Cursors allo w the retriev al of man y tuples, with the cursor and a lo op used to pro cess eac h in turn. 11

Single-Ro w Select Select-from-where in PL/SQL ha v e an must � clause listing v ariables in to whic h a tuple INTO can b e placed. It is an if the select-from-where returns err or � more than one tuple; y ou should ha v e used a cursor. Example Find the price Jo e c harges for Bud (and drop it on the �o or). Sells(bar , beer , price) DECLARE p Sells.price%TYPE ; BEGIN SELECT price INTO p FROM Sells WHERE bar = 'Joe''s Bar' AND beer = 'Bud'; END; . run 12

Cursors Declare b y: < name > CURSOR IS select-from-where statemen t Cursor gets eac h tuple from the relation � pro duced b y the select-from-where, in turn, using a in a lo op. fetch statement ✦ F etc h statemen t: < cursor name > FETCH INTO v ariable list ; Break the lo op b y a statemen t of the form: � < cursor name > %NOTFOUND; EXIT WHEN ✦ T rue when there are no more tuples to get. Op en and close the cursor with and OPEN � CLOSE . 13

Example A pro cedure that examines the men u for Jo e's Bar and raises b y $1.00 all prices that are less than $3.00. Sells(bar , beer , price) This simple price-c hange algorithm can b e � implemen ted b y a single statemen t, UPDATE but more complicated price c hanges could not. 14

CREATE PROCEDURE joeGouge() AS theBeer Sells.beer%TYPE; thePrice Sells.price%TYPE; CURSOR c IS SELECT beer, price FROM Sells WHERE bar = 'Joe''s bar'; BEGIN OPEN c; LOOP FETCH c INTO theBeer, thePrice; EXIT WHEN c%NOTFOUND; IF thePrice < 3.00 THEN UPDATE Sells SET price = thePrice + 1.00 WHERE bar = 'Joe''s Bar' AND beer = theBeer; END IF; END LOOP; CLOSE c; END; . run 15

Ro w T yp es An ything (e.g., cursors, table names) that has a tuple t yp e can ha v e its t yp e captured with %ROWTYPE . W e can create temp orary v ariables that ha v e � tuple t yp es and access their comp onen ts with dot. Handy when w e deal with tuples with man y � attributes. 16

Example The same pro cedure with a tuple v ariable bp . CREATE PROCEDURE joeGouge() AS CURSOR c IS SELECT beer, price FROM Sells WHERE bar = 'Joe''s bar'; bp c%ROWTYPE; BEGIN OPEN c; LOOP FETCH c INTO bp; EXIT WHEN c%NOTFOUND; IF bp.price < 3.00 THEN UDPATE Sells SET price = bp.price + 1.00 WHERE bar = 'Joe''s Bar' AND beer = bp.beer; END IF; END LOOP; CLOSE c; END; . run 17