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