Queries in PSM The following rules apply to the use of queries: - PDF document

Queries in PSM • The following rules apply to the use of queries: CS 235: 1. Queries returning a single value can be Introduction to Databases used in assignments 2. Queries returning a single tuple can be used Svetlozar Nestorov with INTO. 3. Queries returning several tuples can be used via a cursor. Lecture Notes #15 Cursors Fetching Tuples From a Cursor • A cursor serves as a tuple-variable that • Get next tuple: ranges over the tuples of the result of a FETCH c INTO a1, a2, …, ak; query. – a1, a2, …, ak are the attributes of the result of DECLARE c CURSOR FOR (<query>); the query of c. – c is moved to the next tuple. • Opening a cursor evaluates <query>. • A cursor is used by creating a loop around OPEN c; FETCH. • Closed with CLOSE c; End of Cursor Cursor Structure DECLARE c CURSOR FOR… • SQL operations return status in … SQLSTATE (in PSM). cursorLoop: LOOP • FETCH returns ‘02000’ in SQLSTATE … when no more tuples are found. FETCH c INTO…; • Useful declaration: IF NotFound THEN LEAVE cursorLoop; DECLARE NotFound CONDITION FOR END IF; SQLSTATE ‘02000’ … END LOOP; 1

Cursor Example Example BEGIN • Write a procedure that makes free all beers OPEN c; sold for more than $5 at Spoon. menuLoop: LOOP FETCH c INTO aBeer, aPrice; CREATE PROCEDURE FreeBeer() IF NotFound THEN LEAVE menuLoop END IF; DECLARE aBeer VARCHAR[30]; IF aPrice > 5.00 THEN DECLARE aPrice REAL; UPDATE Sells SET price = 0 DECLARE NotFound CONDITION FOR WHERE bar = ‘Spoon’ and beer = aBeer; SQLSTATE ‘02000’; END IF; DECLARE CURSOR c FOR END LOOP; CLOSE c; SELECT beer, price FROM Sells WHERE bar = END; ‘Spoon’; MySQL Routines Procedures • MySQL’s version of PSM (Persistent, CREATE PROCEDURE <name>(<arglist>) Stored Modules). BEGIN – Stored procedures. <declarations> – Functions. <statements> • Brand new feature (in 5.0). END; – Adheres to standards (similar to IBM’s DB2, different from Oracle PL/SQL). – Bugs possible (bugs.mysql.com) Functions Arguments CREATE PROCEDURE <name>(<arglist>) • Argument list has name-mode-type triples. RETURNS <type> – Mode: IN, OUT, or INOUT for read-only, write- BEGIN only, read/write, respectively. <declarations> – Types: standard SQL. <statements> END; 2

Example Declarations • A procedure to add a beer and price to Spoon’s menu: • Variables DELIMITER // • Conditions CREATE PROCEDURE addSpoonMenu( IN b CHAR(20), • Cursors IN p REAL) • Handlers BEGIN • Must be declared in this order! INSERT INTO Sells VALUES(‘Spoon', b, p); END;// DELIMITER ; CALL addSpoonMenu(‘Guinness’, 7.50); Conditions Handlers DECLARE <condName> • Define what to do in case of errors (or conditions) CONDITION FOR SQLSTATE <errorStr> DECLARE { EXIT | CONTINUE } HANDLER FOR DECLARE <condName> {<errorNum> | CONDITION FOR <errorNumber> SQLSTATE <errorStr> | <condName> } • The following conditions are predefined: SQL statement – NOT FOUND (no more rows) – SQLEXCEPTION (error) • Common practice: set a flag for CONTINUE handlers and check inside stored procedure. – SQLWARNING (warning) Body Constructs Queries in Routines • Assignments : 1. Single-row selects allow retrieval into a SET<variable> = <expression> variable of the result of a query that is – Variables must be declared. • Branches guaranteed to produce one tuple. IF <condition> THEN 2. Cursors allow the retrieval of many <statement(s)> ELSE tuples, with the cursor and a loop used to <statement(s)> process each in turn. END IF; 3

Cursors in MySQL Example (1/3) • The cursor declaration is: • The FreeBeer in MySQL: DECLARE <curName> CREATE PROCEDURE FreeBeer() CURSOR FOR <query>; BEGIN • Fetching is done with: DECLARE aBeer CHAR(20); FETCH c INTO <variables>; DECLARE aPrice REAL; DECLARE flag INT DEFAULT 0; Example (2/3) Example (3/3) DECLARE menu CURSOR FOR OPEN menu; SELECT beer, price FROM Sells REPEAT FETCH menu INTO aBeer, aPrice; WHERE bar = ‘Spoon’; IF aPrice > 5.00 THEN DECLARE CONTINUE HANDLER UPDATE Sells SET price = 0 FOR NOT FOUND WHERE bar = ‘Spoon’ AND beer = aBeer; SET flag = 1; END IF; UNTIL flag = 1 END REPEAT; CLOSE menu; END;// 4