Download - CS122aSlides14.ppt
ICS122A / EECS116Introduction to Data ManagementSpring 2009
Prof. Mike Carey
Bren School of ICS
UC Irvine
Slides based on previous CS122a lecture notes as well as material borrowed from U-Wisconsin,
Stanford, & Berkeley
CS122A/EECS116 Notes 14 2
Announcements• Midterm grades
– Choice (a) in Question 2 is also correct – if you lost points by saying so, you can visit Vivek for a grade correction
• Homework and project related notes– HW #5 is due tonight by midnight– HW #6 will be available by that time as well– Project Part 3 is due on Thursday by midnight
(Please include your latest E-R and relational schemas)
• This week: Database programming concepts– Today: Client programming and stored procedures– Thursday: Transactions and transaction management
(Reminder: This Thursday’s office hour will be 12:30-1:30PM)
• Any Q&A before we move ahead…?
Overview• Writing SQL Applications
– Basic concepts Host languages Compilation of embedded SQL program s
– Several major approaches Direct embedding of SQL
E.g. embedded SQL, SQLJ (examples in DB2) Using libraries provided by host languages
E.g. CLI (Call-Level Interface), ODBC (Open Database Connectivity), Java Database Connectivity (JDBC)
Object-relational mapping (e.g., Hibernate and JPA)• Stored Procedures (a.k.a. Persistent Stored Modules (PSM))
– Motivation and benefits – Syntax of stored procedures (w/examples for DB2 & MySQL)– Parameters and parameter passing
Notes 14 3CS122A/EECS116
Embedded SQL • Direct SQL is rarely used in practice (outside development)
• Instead, SQL is usually “embedded” in application code
• Q: Why not use only one language?
Host language +embedded SQL Preprocessor Host Language + function
calls
Host language compilerHost language
program
• Advantages – Preprocessing of (static) parts of queries
• Disadvantages– Needs pre-compiler
– Needs to be bound to a database
SQL library
Notes 14 4CS122A/EECS116
The Impedance Mismatch Problem• No construct in host languages for manipulating relations
– Host languages manipulate instances: variables, values, pointers
– SQL statements manipulate relations: sets, bags
– E.g. what if a query returns more than one tuple?!
• Interface between SQL and host languages:– Host variables
Values are passed between a SQL statement and the rest of the program Colon precedes a shared variable that occurs within a SQL statement
– SQL communications area EXEC SQL INCLUDE SQLCA Defines: status codes, error messages, etc.
– EXEC SQL precedes each SQL-related statement in a host program
Notes 14 5CS122A/EECS116
Example
Void simpleInsert() { EXEC SQL BEGIN DECLARE SECTION; char productName[20], company[30]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; /* get values for productName and company somehow */ … EXEC SQL INSERT INTO Product(name, company) VALUES (:productName, :company); }
** Shared variables distinguished from SQL identifiers by ‘:’ (colon)
Notes 14 6CS122A/EECS116
Single-Row Select StatementsVoid getPrice() { EXEC SQL BEGIN DECLARE SECTION; char productName[20], company[30]; integer price; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; /* read value of product name */ … EXEC SQL SELECT price INTO :price
FROM Product WHERE Product.name = :productName;
/* print out value of price */ … }
Notes 14 7CS122A/EECS116
SELECT list and INTO list must “match” (variable per column of result and type-compatible)
Multi-Row Select Statements: CursorsMotivation: Iterate over the (many) results of a query!
EXEC SQL DECLARE cursorName CURSOR FOR SELECT …. FROM …. WHERE …. ;
EXEC SQL OPEN cursorName;
while (true) { EXEC SQL FETCH cursorName INTO :var1, :var2, … ;
if (NO_MORE_TUPLES) break; /* do something with values */ … } EXEC SQL CLOSE cursorName; NO_MORE_TUPLES: SQLSTATE set to
‘02000’
** Variable SQLSTATE provides error information and execution status reports (e.g., ‘00000’ means that an operation completed without a problem)
Notes 14 8CS122A/EECS116
Again, SELECT list and INTO list must “match” (variable per column of result and type-compatible)
More on Cursors
• Order can be determined by ORDER BY clause in query
• Cursors can modify a relation as well as just read it– UPDATE … WHERE CURRENT OF cursorName
• Cursors can be protected against changes to the underlying relations (more on this next time)
• “Scrollable cursors” can scroll: go to first, go to last, step forward, step backward, or move to N, +N, or -N
• Checking errors / handling exceptions (anytime, actually)– if (SQLCODE < 0) { … }
– Also see Wikipedia!
– http://en.wikipedia.org/wiki/Cursor_(databases)
Notes 14 9CS122A/EECS116
Summary of Embedded SQL• Declarations
– SQLCA (and SQLCODE)– BEGIN DECLARE SECTION … END DECLARE SECTION
• Simple Statements – EXEC SQL <sql statement>;
• Statements with parameters• Statements yielding a single tuple• Statements with many answer tuples• Different from dynamic SQL based approaches
– Those form and pass SQL statement strings at runtime
Notes 14 10CS122A/EECS116
Notes 14 11
• What is a stored procedure?– A series of SQL statements stored in the DB server to
accomplish a particular task (*persistent stored module)– Most often imperative, involving conditional
statements, looping statements, side effects, etc.– Pre-compiled for efficiency– Can be invoked multiple times by making a call to the
procedure with the appropriate argument values• Can also be written using other languages
– e.g., C, Java, … with embedded or dynamic SQL• Different vendors have different implementations
– Must check the manual and sample programs!
Stored Procedures (also called PSM*)
CS122A/EECS116
Advantages of Stored Procedures
• Performance gains resulting from– Statement pre-compilation
– Reduction in network traffic
• Separation of concerns for application developers• Testability, stability, and maintainability • Security (use instead of queries and/or updates)
Notes 14 12CS122A/EECS116
Notes 14 13
CREATE PROCEDURE <name> (<parameters>) BEGIN
<procedure statements>END@
Compound statement: BEGIN … END <procedure statements> can have multiple SQL statements Local variables can be declared within a compound statement
A different delimiter is needed for ending and for running (you’ll see why)
Parameters are in mode-name-type format, where mode is one of: IN (default): can change inside the procedure, but remains unchanged outside OUT: No value is supplied to the procedure, but return value can be set inside
the procedure and is available outside the procedure upon return INOUT: A value can be passed in, modifed, and then passed back out again
Declaring Stored Procedures (DB2)
CS122A/EECS116
Notes 14 14
CREATE TABLE emp(id INTEGER, salary FLOAT);
CREATE PROCEDURE UPDATE_EMP (IN idNum INTEGER, IN rate FLOAT) LANGUAGE SQL BEGIN
UPDATE EMP SET salary = salary* (1.0 + rate) WHERE id = idNum;
END @
• Name: UPDATE_EMP• The two parameters are of data type INTEGER and FLOAT; both are input
(IN) parameters by default• LANGUAGE SQL indicates that this is an SQL procedure, so a procedure
body follows the other parameters• The procedure body consists of a single SQL UPDATE statement, which
updates rows in the emp table• '@' is the default terminating character for stored procedures in DB2
Example Stored Procedure (DB2)
CS122A/EECS116
Notes 14 15
CALL UPDATE_EMP (2, 0.1);
Calling Stored ProceduresId Salary 1 100 2 200 3 300
Id Salary 1 100 2 220 3 300
CALL UPDATE_EMP (3, 0.2); Id Salary 1 100 2 220 3 360
CS122A/EECS116
emp
Notes 14 16
• Can call a stored procedure via CALL or from within a trigger or from another procedure (like calling a function in a “normal” language)
• To call a target SQL procedure from within another SQL procedure, you simply include a CALL statement with the appropriate number and types of parameters
CREATE PROCEDURE NEST_SALES(OUT budget DECIMAL(11,2)) LANGUAGE SQL
BEGIN DECLARE total INTEGER DEFAULT 0; CALL SALES_TARGET(2009, total); call another
procedure SET budget = total * 10000; END @
• “budget” is an output variable (as is total, for that matter)
Calling Stored Procedures (cont.)
CS122A/EECS116
Notes 14 17
CASE statement FOR statement GOTO statement IF statement ITERATE statement RETURN statement WHILE statement
…
(Similar to other programming languages)
Valid Body StatementsConditional Statements
IF <condition> THEN<statement(s)>
ELSE<statement(s)>
END IF;
LoopsLOOP
……
EXIT WHEN <condition>……
END LOOP; The best way to begin programming:
– Start with a template
– Modify the template to write your own program!
CS122A/EECS116
Stored Functions
• A stored function – Like a stored procedure, but it returns a result value
RETURN keyword used to determine what is passed back
– Can be called from inside an SQL SELECT statement just like ordinary SQL functions are used Roughly like a subquery in terms of its role
– Has only IN parameters – Stored functions also have slightly more limitations
than stored procedures (in order to ensure that they are actually “safe” to use as functions) E.g., no database updates or other side effects
Notes 14 18CS122A/EECS116
Examples in MySQL
• For both MySQL and DB2, be sure to check their online documentation and/or examples for more precise syntactic details ()
Notes 14 19CS122A/EECS116
Notes 14 20
CREATE PROCEDURE UPDATE_SALARY_IF (IN employee_number INT, IN rating SMALLINT) LANGUAGE SQL BEGIN
IF (rating = 1) THEN BEGIN
DECLARE counter INT; SET counter = 10;
WHILE (counter > 0) DO UPDATE employee SET salary = salary * 1.10, bonus = 1000 WHERE empno = employee_number; SET counter = counter – 1; END WHILE;
END ELSEIF (rating = 2) THEN UPDATE employee SET salary = salary * 1.05, bonus = 500 WHERE empno = employee_number; ELSE UPDATE employee SET salary = salary * 1.03, bonus = 0 WHERE empno = employee_number; END IF;
END @
Example 1
CS122A/EECS116
Notes 14 21
CREATE PROCEDURE BUMP_SALARY_IF (IN deptnumber SMALLINT) LANGUAGE SQL BEGIN DECLARE v_salary DOUBLE; DECLARE v_years SMALLINT; DECLARE v_id SMALLINT; DECLARE at_end INT DEFAULT 0; DECLARE not_found CONDITION FOR SQLSTATE '02000'; “not found”
reached -- CAST salary as DOUBLE because SQL procedures do not support DECIMAL DECLARE C1 CURSOR FOR SELECT id, CAST(salary AS DOUBLE), years FROM staff WHERE deptno = deptnumber; DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
Example 2
CS122A/EECS116
Notes 14 22CS122A/EECS116
OPEN C1; FETCH C1 INTO v_id, v_salary, v_years; WHILE at_end = 0 DO IF (v_salary < 2000 * v_years) THEN UPDATE staff SET salary = 2150 * v_years WHERE id = v_id; ELSEIF (v_salary < 5000 * v_years) THEN IF (v_salary < 3000 * v_years) THEN UPDATE staff SET salary = 3000 * v_years WHERE id = v_id; ELSE UPDATE staff SET salary = v_salary * 1.10 WHERE id = v_id; END IF; ELSE UPDATE staff SET job = 'PREZ' WHERE id = v_id; END IF; FETCH C1 INTO v_id, v_salary, v_years; END WHILE; CLOSE C1; END
Notes 14 23
Example 2 Description
This procedure receives a department number as an input parameter A WHILE statement in the procedure body loops over the id, salary, and
years of service for each employee in the department An IF statement within the WHILE statement updates salaries for each
employee depending on their years of service and current salary When all employee records in the department have been processed, the
FETCH statement that retrieves employee records receives SQLSTATE value of ‘02000’
A not_found condition handler makes the search condition for the WHILE statement false, so execution of the WHILE statement ends
CS122A/EECS116