========================================================================= LECTURE NOTES - PROGRAMMING WITH SQL ========================================================================= You're starting by using "direct" (or "interactive") SQL. In reality SQL is rarely used in this way, instead it's specified within programs. Embedded SQL ------------ (1) Host program with special SQL directives and commands goes through a special DBMS preprocessor to produce ... (2) Host program with special DBMS function calls is compiled and linked with a special DBMS library to produce ... (3) Executable code that calls the DBMS Example: Oracle's Pro*C Dynamic SQL ----------- Skip step (1) above: Program assembles SQL statements at run-time, sends them to DBMS via specific call-level interfaces. Examples: SQL/CLI (for C/C++ programs) and JDBC (for Java programs) Three Main Issues ----------------- (a) How are values passed from the program into SQL commands? (b) How are results of SQL commands returned into program variables? (c) How do we deal with set-valued (i.e., relation-valued) results? Details are mechanism- and language-dependent. But one common feature is usually available for (c): Cursors ------- For set-valued results: associate handle (cursor) with query, get tuples in result one at a time through handle. Example: Print names and GPAs of all students who applied to Santa Cruz Step 1: declare cursor CURSOR SCinfo FOR: (SELECT DISTINCT name, GPA FROM Student, Apply WHERE Student.ID = Apply.ID AND Apply.location = "Santa Cruz") Step 2: run query using iteration OPEN SCinfo; GET NEXT SCinfo INTO :n, :g; WHILE (NOT EOCursor) Print(:n, :g); GET NEXT SCinfo INTO :n, :g; ENDWHILE; CLOSE SCinfo; Modifying data through cursors ------------------------------ DELETE FROM R WHERE CURRENT OF CURSOR UPDATE R SET attributes = values WHERE CURRENT OF CURSOR Q: For what kinds of queries do these operations make sense? Scrolling Cursors ----------------- Think of cursor as a pointer into a relation holding the result: - FETCH NEXT - FETCH PRIOR - FETCH FIRST - FETCH LAST - FETCH RELATIVE +/- num - FETCH ABSOLUTE +/- num Q: Are scrolling cursors more difficult or expensive to support? DBMS programming languages -------------------------- Many DBMS's provide their own mini-programming language that includes SQL, variables, control constructs, procedures, etc. - Smoothes over the discontinuity between the programming language and SQL - Runs in the DBMS ** System can store procedures and functions written in the DBMS programming language, invoked by users or applications Oracle: PL/SQL -------------- Example: procedure to input student ID, output GPA CREATE PROCEDURE getGPA( i Student.ID%TYPE; g OUT Student.GPA%TYPE) AS BEGIN SELECT GPA INTO g FROM Student WHERE ID = i END; Could also write as FUNCTION with return type Student.GPA%TYPE. Example: Print names and GPAs of all students who applied to Santa Cruz DECLARE n Student.name%TYPE; g Student.GPA%TYPE CURSOR SCinfo IS SELECT name, GPA FROM Student, Apply WHERE Student.ID = Apply.ID AND Apply.location = "Santa Cruz"; BEGIN OPEN SCinfo; LOOP FETCH SCinfo INTO n, g; EXIT WHEN SCinfo%NOTFOUND; END LOOP; CLOSE SCinfo; END; PL/SQL also has: - Exception handling - ROWTYPE - IF-THEN-ELSE - WHILE loops - More... Can execute PL/SQL interactively or call it from programs. Primarily used for "stored procedures" and in triggers. Big picture -----------