Docstoc

Views

Document Sample
Views Powered By Docstoc
					  CS 541
 Constraints
September 16, 2002




    Chris Clifton - CS541   1
       Defining a Database Schema
CREATE TABLE name (list of elements).
• Principal elements are attributes and their types, but key
  declarations and constraints also appear.
• Similar CREATE X commands for other schema elements X:
  views, indexes, assertions, triggers.
• “DROP X name” deletes the created element of kind X with
  that name.

Example
       CREATE TABLE Sells (
          bar CHAR(20),
          beer VARCHAR(20),
          price REAL
       );
       DROP TABLE Sells;
Fall 2002                  Chris Clifton - CS541           2
                                Constraints
Commercial relational systems allow much more “fine-tuning”
of constraints than do the modeling languages we learned earlier.
• In essence: SQL programming is used to describe
    constraints.

Outline
1.      Primary key declarations.
2.      Foreign-keys = referential integrity constraints.
3.      Attribute- and tuple-based checks = constraints within
        relations.
4.      SQL Assertions = global constraints.
            Not found in Oracle.
5. Oracle Triggers.
            A substitute for assertions.

     Fall 2002                       Chris Clifton - CS541       3
                    Declaring Keys
Use PRIMARY KEY or UNIQUE.
• But only one primary key, many UNIQUEs allowed.
• SQL permits implementations to create an index
  (data structure to speed access given a key value)
  in response to PRIMARY KEY only.
       But   PostgreSQL and Oracle create indexes for both.
• SQL does not allow nulls in primary key, but
  allows them in “unique” columns (which may
  have two or more nulls, but not repeated non-null
  values).


Fall 2002                  Chris Clifton - CS541               4
                     Declaring Keys
Two places to declare:
1. After an attribute’s type, if the attribute is a
   key by itself.
2. As a separate element.
           Essential if key is >1 attribute.




Fall 2002                  Chris Clifton - CS541   5
                    Example
        CREATE TABLE Sells (
          bar CHAR(20),
          beer VARCHAR(20),
          price REAL,
          PRIMARY KEY(bar,beer)
        );




Fall 2002            Chris Clifton - CS541   6
                      Example
        CREATE TABLE Sells (
           bar CHAR(20),
           beer VARCHAR(20),
           price REAL,
           UNIQUE(bar,beer)
        );
is different than:
      CREATE TABLE Sells (
          bar CHAR(20) UNIQUE,
          beer VARCHAR(20) UNIQUE,
          price REAL
       );
Fall 2002              Chris Clifton - CS541   7
Other Properties You Can Give to Attributes
 1. NOT NULL = every tuple must have a real value
    for this attribute.
 2. DEFAULT value = a value to use whenever no
    other value of this attribute is known.
  Example
       CREATE TABLE Drinkers (
        name CHAR(30) PRIMARY KEY,
        addr CHAR(50)
                DEFAULT '123 Sesame St',
        phone CHAR(16)
       );

 Fall 2002             Chris Clifton - CS541        8
        INSERT INTO Drinkers(name)
        VALUES('Sally')
results in the following tuple:
            name     addr                           phone
            Sally    123 Sesame St.                 NULL
• Primary key is by default not NULL.
• This insert is legal.
       OK       to list a subset of the attributes and values for only
            this subset.
• But if we had declared
             phone CHAR(16) NOT NULL
    then the insertion could not be made.
Fall 2002                       Chris Clifton - CS541                     9
            Interesting Defaults
• DEFAULT CURRENT_TIMESTAMP
• SEQUENCE
    CREATE SEQUENCE customer_seq;
    CREATE TABLE Customer (
       customerID INTEGER
         DEFAULT nextval('customer_seq'),
       name VARCHAR(30)
    );



Fall 2002          Chris Clifton - CS541    10
                      Foreign Keys
In relation R a clause that “attribute A references S(B)”
says that whatever values appear in the A column of R
must also appear in the B column of relation S.
• B must be declared the primary key for S.

Example
    CREATE TABLE Beers (
        name CHAR(20) PRIMARY KEY,
        manf CHAR(20)
    );

    CREATE TABLE Sells (
        bar CHAR(20),
        beer CHAR(20) REFERENCES Beers(name),
        price REAL
    );
  Fall 2002                 Chris Clifton - CS541           11
Alternative: add another element declaring the
  foreign key, as:
      CREATE TABLE Sells (
         bar CHAR(20),
         beer CHAR(20),
         price REAL,
         FOREIGN KEY beer REFERENCES
                     Beers(name)
      );
• Extra element essential if the foreign key is more
  than one attribute.

Fall 2002             Chris Clifton - CS541            12
        What Happens When
a Foreign Key Constraint is Violated?
•      Two ways:
1.     Insert or update a Sells tuple so it refers to a nonexistent
       beer.
           Always rejected.
2.     Delete or update a Beers tuple that has a beer value
       some Sells tuples refer to.
      a)    Default: reject.
      b)    Cascade: Ripple changes to referring Sells tuple.

Example
•      Delete “Bud.” Cascade deletes all Sells tuples that
       mention Bud.
•      Update “Bud” to “Budweiser.” Change all Sells tuples
       with “Bud” in beer column to be “Budweiser.”
Fall 2002                      Chris Clifton - CS541             13
      c)    Set Null: Change referring tuples to have
            NULL in referring components.

Example
• Delete “Bud.” Set-null makes all Sells
  tuples with “Bud” in the beer component
  have NULL there.
• Update “Bud” to “Budweiser.” Same
  change.

Fall 2002                 Chris Clifton - CS541         14
                      Selecting a Policy
Add ON [DELETE, UPDATE] [CASCADE, SET NULL] to declaration of
  foreign key.

Example
   CREATE TABLE Sells (
       bar CHAR(20),
       beer CHAR(20),
       price REAL,
       FOREIGN KEY beer REFERENCES Beers(name)
              ON DELETE SET NULL
              ON UPDATE CASCADE
   );
• “Correct” policy is a design decision.
      E.g., what does it mean if a beer goes away? What if a beer changes its name?

    Fall 2002                     Chris Clifton - CS541                     15
            Attribute-Based Checks
Follow an attribute by a condition that must hold for
  that attribute in each tuple of its relation.
• Form: CHECK (condition).
       Condition may   involve the checked attribute.
       Other attributes and relations may be involved, but only
        in subqueries.
       Oracle: No subqueries allowed in condition.

• Condition is checked only when the associated
  attribute changes (i.e., an insert or update occurs).



Fall 2002                 Chris Clifton - CS541               16
                              Example
    CREATE TABLE Sells (
        bar CHAR(20),
        beer CHAR(20) CHECK(
               beer IN (SELECT name
                      FROM Beers)
        ),
        price REAL CHECK(
               price <= 5.00
        )
    );
• Check on beer is like a foreign-key constraint, except:
       The check occurs only when we add a tuple or change the beer in an
        existing tuple, not when we delete a tuple from Beers.

  Fall 2002                     Chris Clifton - CS541                        17
  CS 541
 Constraints
September 18, 2002




    Chris Clifton - CS541   18
             Tuple-Based Checks
Separate element of table declaration.
• Form: like attribute-based check.
• But condition can refer to any attribute of
  the relation.
      Orto other relations/attributes in subqueries.
      Again: Oracle forbids the use of subqueries.

• Checked whenever a tuple is inserted or
  updated.

Fall 2002               Chris Clifton - CS541           19
                   Example
Only Joe's Bar can sell beer for more than $5.
      CREATE TABLE Sells (
         bar CHAR(20),
         beer CHAR(20),
         price REAL,
         CHECK(bar = 'Joe''s Bar' OR
              price <= 5.00)
      );


Fall 2002           Chris Clifton - CS541    20
                   SQL Assertions
• Database-schema constraint.
• Not present in Oracle.
• Checked whenever a mentioned relation
  changes.
• Syntax:
            CREATE ASSERTION < name>
            CHECK(<condition>);



Fall 2002               Chris Clifton - CS541   21
                    Example
No bar may charge an average of more than $5 for beer.
    Sells(bar, beer, price)
    CREATE ASSERTION NoRipoffBars
    CHECK(NOT EXISTS(
             SELECT bar
             FROM Sells
             GROUP BY bar
             HAVING 5.0 < AVG(price)
             )
    );
• Checked whenever Sells changes.
 Fall 2002            Chris Clifton - CS541        22
                     Example
There cannot be more bars than drinkers.
      Bars(name, addr, license)
      Drinkers(name, addr, phone)
      CREATE ASSERTION FewBar
      CHECK(
          (SELECT COUNT(*) FROM Bars) <=
          (SELECT COUNT(*) FROM Drinkers)
      );

• Checked whenever Bars or Drinkers changes.


Fall 2002             Chris Clifton - CS541    23
            Triggers (Oracle Version)
Often called event-condition-action rules.
• Event = a class of changes in the DB, e.g., “insertions
    into Beers.”
• Condition = a test as in a where-clause for whether or not
    the trigger applies.
• Action = one or more SQL statements.
• Differ from checks or SQL assertions in that:
      1.    Triggers invoked by the event; the system doesn’t have to figure
            out when a trigger could be violated.
      2.    Condition not available in checks.




Fall 2002                       Chris Clifton - CS541                      24
                     Example
Whenever we insert a new tuple into Sells, make sure the
 beer mentioned is also mentioned in Beers, and insert it
 (with a null manufacturer) if not.
   Sells(bar, beer, price)
   CREATE OR REPLACE TRIGGER BeerTrig
   AFTER INSERT ON Sells
   FOR EACH ROW
   WHEN(new.beer NOT IN
                   (SELECT name FROM Beers))
       BEGIN
             INSERT INTO Beers(name)
             VALUES(:new.beer);
       END;
   .
   run
  Fall 2002            Chris Clifton - CS541         25
                            Options
1. Can omit OR REPLACE. But if you do, it is an error if
   a trigger of this name exists.
2. AFTER can be BEFORE.
3. If the relation is a view, AFTER can be INSTEAD OF.
         Useful for allowing “modifications” to a view; you modify
          the underlying relations instead.
4. INSERT can be DELETE or UPDATE OF <attribute>.
         Also, several conditions like INSERT ON Sells can be
          connected by OR.
5. FOR EACH ROW can be omitted, with an important
   effect: the action is done once for the relation(s)
   consisting of all changes.
  Fall 2002                  Chris Clifton - CS541               26
                                   Notes
• There are two special variables new and old, representing
  the new and old tuple in the change.
           old makes no sense in an insert, and new makes no sense in a
            delete.
• Notice: in WHEN we use new and old without a colon, but in
  actions, a preceding colon is needed.
• The action is a PL/SQL statement.
           Simplest form: surround one or more SQL statements with BEGIN
            and END.
           However, select-from-where has a limited form.




Fall 2002                        Chris Clifton - CS541                      27
• Triggers are part of the database schema,
  like tables or views.
• Important Oracle constraint: the action
  cannot change the relation that triggers the
  action.
      Worse,       the action cannot even change a relation
            connected to the triggering relation by a
            constraint, e.g., a foreign-key constraint.




Fall 2002                   Chris Clifton - CS541          28
                     Example
Maintain a list of all the bars that raise their price for
 some beer by more than $1.
      Sells(bar, beer, price)
      RipoffBars(bar)
      CREATE TRIGGER PriceTrig
      AFTER UPDATE OF price ON Sells
      FOR EACH ROW
      WHEN(new.price > old.price + 1.00)
         BEGIN
               INSERT INTO RipoffBars
               VALUES(:new.bar);
         END;
      .
      run
Fall 2002              Chris Clifton - CS541             29
    CS 541
    Views
September 20, 2002




    Chris Clifton - CS541   30
                             Views
An expression that describes
a table without creating it.




• View definition form is:
             CREATE VIEW <name> AS <query>;

 Fall 2002                  Chris Clifton - CS541   35
                          Example
The view CanDrink is the set of drinker-beer pairs such that
  the drinker frequents at least one bar that serves the beer.
       CREATE VIEW CanDrink AS
          SELECT drinker, beer
          FROM Frequents, Sells
          WHERE Frequents.bar = Sells.bar;

 Querying Views
Treat the view as if it were a materialized relation.

 Example
            SELECT beer
            FROM CanDrink
            WHERE drinker = ‘Sally’;

Fall 2002                  Chris Clifton - CS541                 36
            Semantics of View Use



Example




Fall 2002          Chris Clifton - CS541   37
            Compose




Fall 2002   Chris Clifton - CS541   38
            Optimize Query
1. Push selections down tree.
2. Eliminate unnecessary projections.




Fall 2002        Chris Clifton - CS541   39
 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;

   Fall 2002                 Chris Clifton - CS541              40
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

Fall 2002           Chris Clifton - CS541   41
                  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.



Fall 2002               Chris Clifton - CS541          42
                               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.
   Fall 2002                   Chris Clifton - CS541            43
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;

 Fall 2002             Chris Clifton - CS541   44
                    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;

Fall 2002              Chris Clifton - CS541      45
• Argument list has name-mode-type triples.
      Mode:  IN, OUT, or IN OUT for read-only,
       write-only, read/write, respectively.
      Types: standard SQL + generic types like
       NUMBER = any integer or real type.
      Since types in procedures must match their
       types in the DB schema, you should generally
       use an expression of the form
               relation.attribute %TYPE
       to capture the type correctly.


Fall 2002             Chris Clifton - CS541           46
                         Example
A procedure to take a beer and price and add it to Joe’s menu.
    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 procedure; it doesn’t execute the
  procedure.
  Fall 2002                Chris Clifton - CS541                 47
            Invoking Procedures
A procedure call may appear in the body of a
  PL/SQL statement.
• Example:
      BEGIN
         joeMenu('Bud', 2.50);
         joeMenu('MooseDrool', 5.00);
      END;
      .
      run;

Fall 2002           Chris Clifton - CS541      48
                    Assignment
Assign expressions to declared variables with :=.
Branches
      IF <condition> THEN
          <statement(s)>
      ELSE
          <statement(s)>
      END IF;
• But in nests, use ELSIF in place of ELSE IF.
Loops
      LOOP
               . . .
         EXIT WHEN <condition>
               . . .
      END LOOP;

Fall 2002              Chris Clifton - CS541        49
            Queries in PL/SQL
1. Single-row selects allow retrieval into a
   variable of the result of a query that is
   guaranteed to produce one tuple.
2. Cursors allow the retrieval of many
   tuples, with the cursor and a loop used to
   process each in turn.




Fall 2002         Chris Clifton - CS541         50
                 Single-Row Select
• Select-from-where in PL/SQL must have an INTO clause listing
  variables into which a tuple can be placed.
• It is an error if the select-from-where returns more than one
  tuple; you should have used a cursor.
Example
• Find the price Joe charges for Bud (and drop it on the floor).
    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
   Fall 2002                  Chris Clifton - CS541                51
 Functions (PostgreSQL Version)
Server-side functions can be written in several
  languages:
• SQL
• PL/PGSQL
• PL/TCL
• PL/Perl
• C


Fall 2002         Chris Clifton - CS541       52
SQL Functions (PostgreSQL Version)
Like Oracle stored procedures.
CREATE FUNCTION requires the following
  information:
• Function name
• Number of function arguments
• Data type of each argument
• Function return type
• Function action
• Language used by the function action

Fall 2002       Chris Clifton - CS541    53
                     Example
• A simple SQL function to convert a temperature
  from Fahrenheit to centigrade degrees.
      CREATE FUNCTION ftoc(float)
      RETURNS float
      AS 'SELECT ($1 - 32.0) * 5.0 / 9.0;'
      LANGUAGE 'sql';
      SELECT ftoc(68);
                 ftoc
                ------
                   20
                (1 row)

Fall 2002             Chris Clifton - CS541        54
              Functions (Continued)
• SQL functions can return multiple values using
  SETOF.
• Function actions can also contain INSERTs,
  UPDATEs, and DELETEs as well as multiple
  queries separated by semicolons.
• Arguments: $1 is automatically replaced by the
  first argument of the function call.
  $2 is the second argument, etc.



  Fall 2002          Chris Clifton - CS541    55
                    Example
SQL server-side function to compute a sales tax.
CREATE FUNCTION tax(numeric)
RETURNS numeric
AS 'SELECT ($1 *
  0.06::numeric(8,2))::numeric(8,2);'
LANGUAGE 'sql';
SELECT tax(100);
              tax
            ------
              6.00
            (1 row)
Notice the casts to NUMERIC(8,2) using the double-
  colon form of type casting, rather than CAST.
Fall 2002            Chris Clifton - CS541         56
   Server Side Functions in SQL Queries
CREATE TABLE part (
        part_id     INTEGER,
        name        CHAR(10),
        cost        NUMERIC(8,2),
        weight      FLOAT
);
INSERT INTO part VALUES (637, 'cable', 14.29, 5);
INSERT INTO part VALUES (638, 'sticker', 0.84, 1);
INSERT INTO part VALUES (639, 'bulb', 3.68, 3);
SELECT part_id, name, cost, tax(cost), cost+tax(cost) AS total
FROM part
ORDER BY part_id;
            part_id |    name    | cost | tax | total
           ---------+------------+-------+------+-------
                637 | cable      | 14.29 | 0.86 | 15.15
                638 | sticker    | 0.84 | 0.05 | 0.89
                639 | bulb       | 3.68 | 0.22 | 3.90
           (3 rows)

   Fall 2002                  Chris Clifton - CS541              57
                  Example: Shipping
CREATE FUNCTION shipping(numeric)
RETURNS numeric
AS 'SELECT CASE
 WHEN $1 < 2             THEN CAST(3.00 AS numeric(8,2))
 WHEN $1 >= 2 AND $1 < 4 THEN CAST(5.00 AS numeric(8,2))
 WHEN $1 >= 4            THEN CAST(6.00 AS numeric(8,2))
END;'
LANGUAGE 'sql';
SELECT part_id, trim(name) AS name, cost, tax(cost), cost+tax(cost)
   AS subtotal, shipping(weight),
   cost+tax(cost)+shipping(weight) AS total
FROM part
ORDER BY part_id;
part_id | name    | cost | tax | subtotal | shipping | total
--------+---------+-------+------+----------+----------+------
    637 | cable | 14.29 | 0.86 |      15.15 |     6.00 | 21.15
    638 | sticker | 0.84 | 0.05 |      0.89 |     3.00 | 3.89
    639 | bulb    | 3.68 | 0.22 |      3.90 |     5.00 | 8.90
(3 rows)
    Fall 2002                 Chris Clifton - CS541              58
    Triggers (PostgreSQL Version)
Create a function for states that uses the new
  RECORD variable to perform the following
  actions:
• Reject a state code that is not exactly two
  alphabetic characters
• Reject a state name that contains
  nonalphabetic characters
• Reject a state name less than three characters
  in length
• Uppercase the state code
• Capitalize the state name
 Fall 2002          Chris Clifton - CS541          59
                Example Function
CREATE FUNCTION trigger_insert_update_statename()
RETURNS opaque
AS 'BEGIN
    IF new.code ! ''[A-Za-z][A-Za-z]$''
    THEN RAISE EXCEPTION ''State code must be two alphabetic
                                  characters.'';
    END IF;
    IF new.name ! ''[A-Za-z ]*$''
    THEN RAISE EXCEPTION ''State name must be only alphabetic
                         characters.'';
    END IF;
    IF length(trim(new.name)) < 3
    THEN RAISE EXCEPTION ''State name must longer than two
                         characters.'';
    END IF;
    new.code = upper(new.code); -- uppercase statename.code
    new.name = initcap(new.name); -- capitalize statename.name
    RETURN new;
   END;'
LANGUAGE 'plpgsql';
 Fall 2002                 Chris Clifton - CS541                 60
     Trigger (PostgreSQL Version)
    CREATE TRIGGER trigger_statename
    BEFORE INSERT OR UPDATE
    ON statename
    FOR EACH ROW
    EXECUTE PROCEDURE
      trigger_insert_update_statename()
    ;




Fall 2002          Chris Clifton - CS541   61
               Example Execution
INSERT INTO statename VALUES ('a', 'alabama');
ERROR: State code must be two alphabetic characters.
INSERT INTO statename VALUES ('al', 'alabama2');
ERROR: State name must be only alphabetic characters.
INSERT INTO statename VALUES ('al', 'al');
ERROR: State name must longer than two characters.
INSERT INTO statename VALUES ('al', 'alabama');
INSERT 292898 1
SELECT * FROM statename;
code |              name
-----+--------------------------------
AL | Alabama
 (1 row)

  Fall 2002               Chris Clifton - CS541         62
                                Cursors
Declare by:
      CURSOR <name> IS
          select-from-where statement
• Cursor gets each tuple from the relation produced by the select-from-
  where, in turn, using a fetch statement in a loop.
           Fetch statement:
             FETCH <cursor name> INTO
                        variable list;
• Break the loop by a statement of the form:
        EXIT WHEN <cursor name> %NOTFOUND;
       True when there are no more tuples to get.
• Open and close the cursor with OPEN and CLOSE.




Fall 2002                       Chris Clifton - CS541                     63
                      Example
A procedure that examines the menu for Joe’s
 Bar and raises by $1.00 all prices that are
 less than $3.00.
            Sells(bar, beer, price)
• This simple price-change algorithm can be
  implemented by a single UPDATE statement,
  but more complicated price changes could
  not.


Fall 2002              Chris Clifton - CS541   64
      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
                          UDPATE Sells
                          SET price = thePrice + 1.00
                          WHERE bar = 'Joe''s Bar'
                                 AND beer = theBeer;
                  END IF;
          END LOOP;
          CLOSE c;
        END;
      .
      run
Fall 2002                 Chris Clifton - CS541         65
               Row Types
Anything (e.g., cursors, table names) that has
  a tuple type can have its type captured with
  %ROWTYPE.
• We can create temporary variables that have
  tuple types and access their components
  with dot.
• Handy when we deal with tuples with many
  attributes.


Fall 2002         Chris Clifton - CS541      66
                           Example
The same procedure with a tuple variable 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
Fall 2002                    Chris Clifton - CS541             67

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:15
posted:12/4/2011
language:English
pages:63