An Introduction to SQL II

1. An Introduction to SQL II

2. Transactions • A transaction is a logical unit of work • Typically involves several database operations • COMMIT [WORK] • validates a transaction • The keyword WORK can be omitted • ROLLBACK [WORK] • Invalidates a transaction • There is no explicit BEGIN TRANSACTION statement

3. An Example of transaction(pseudo code) /* Begin Transaction: move some money from account A to account B */ Update account A; /* withdrawal */ Update account B; /* deposit */ COMMIT /* end of a transaction */ • Transactions are guaranteed to be executed in their entirety.

4. Embedded SQL • Dual-mode principle • SQL statements can be executed in interactive mode or in application programs • Embedded SQL statements • Are prefixed by EXEC SQL • End with special terminator symbol(‘;’ in PL/1) • Can include references to host variables • Executable SQL can be inserted anywhere in executable host language statements

5. Embedded SQL(cont.1) • INTO clause • Is used to reference host variable • SQLSTATE • All embedded SQL statements must include host variable, SQLSTATE • 00000: executed successfully • 02000: executed but no data was found to satisfy the request

6. Embedded SQL(cont.2) • WHENEVER statement • Is used to simplify the test of SQLSTATE • FORMAT: • EXEC SQL WHENEVER <condition><action>; • <condition> • SQLERROR: error occurred • NOT FOUND(02000) • <action> • CONTINUE, or GOTO statement

7. Embedded SQL(cont.3) • Example in PL/1 (p.90, Kp.107) EXEC SQL BEGIN DECLARE SECTION; DCL SQLSTATE CHAR(5); DCL P# CHAR(6); DCL WEIGHT FIXED DECIMAL(3); EXEC SQL END DECLARE SECTION; P# = ‘P2’; EXEC SQL SELECT P.WEIGHT INTO :WEIGHT FROM P WHERE P.P# = :P#; IF SQLSTATE = ‘00000’ THEN …; /* WEIGHT=retrieved value */ ELSE …; /* some exception occurred */

8. Embedded SQL(cont.4) • Singleton SELECT • Get status and city for the supplier whose supplier number is given by the host variable GIVENS# EXEC SQL SELECT STATUS, CITY INTO :RANK, :CITY FROM S WHERE S# = :GIVENS#; ※ Singleton  a single object

9. Embedded SQL(cont.5) • INSERT • Insert a new part (part number, name, and weight given by host variables P#, PNAME, PWT, respectively; color and city unknown) into table P. EXEC SQL INSERT INTO P(P#, PNAME, WEIGHT) VALUES (:P#, :PNAME, :PWT);

10. Embedded SQL(cont.6) • UPDATE • Increase the status of all London suppliers by the amount given by the host variable RAISE. EXEC SQL UPDATE S SET STATUS = STATUS + :RAISE WHERE CITY = ‘London’;

11. Embedded SQL(cont.6) • Singleton SELECT • Delete all shipments for suppliers whose city is given by the host variable CITY. EXEC SQL DELETE FROM SP WHERE :CITY = (SELECT CITY FROM S WHERE S.S# = SP.S#); ※ The result of inner SELECT statement: the cities’ of suppliers who have shipped.

12. Embedded SQL(cont.7) • Operations involving cursors • Cursors: a mechanism for accessing the rows one by one • Format: EXEC SQL DECLARE cursor CURSOR FOR <table-expression> [ORDER BY order-item-commalist] • Executable statements to operate on cursors: OPEN, FETCH, CLOSE

13. Embedded SQL(cont.8) • Format • EXEC SQL OPEN <cursor name>; • EXEC SQL FETCH <cursor name> INTO <host variable reference commalist> • EXEC SQL CLOSE <cursor name>

14. Embedded SQL(cont.9) • Multi-row retrieval example(P.94, Kp.111) EXEC SQL DECLARE X CURSOR FOR SELECT S.S#,S.SNAME,S.STATUS FROM S WHERE S.CITY=:Y ORDER BY S# ASC; EXEC SQL OPEN X; DO for all S rows accessible via X EXEC SQL FETCH X INTO :S#, :SNAME, :STATUS; . . . . . . END; EXEC SQL CLOSE X;