[工学]数据库英文课件11.ppt

Chapter 7System Aspects of SQL SQL in a Programming Environment Transactions Authorization 7.1 SQL in a Programming Environment Host Languages: Any conventional language can be a host language, that is, a language in which SQL calls are embedded. The use of a host/SQL combination allows us to do anything computable, yet still get the very-high-level SQL interface to the database. 7.1.1 Embedded SQL Key idea: Use a preprocessor to turn SQL statements into procedure calls that fit with the host-language code surrounding. All embedded SQL statements begin with EXEC SQL, so the preprocessor can find them easily. Shared Variables To connect SQL and the host-language program, the two parts must share some variables. Declarations of shared variables are bracketed by: EXEC SQL BEGIN DECLARE SECTION; host-language declarations EXEC SQL END DECLARE SECTION; Use of Shared Variables In SQL, the shared variables must be preceded by a colon. They may be used as constants provided by the host-language program. They may get values from SQL statements and pass those values to the host-language program. In the host language, shared variables behave like any other variable. Example: C Plus SQL——Insert a new studio void printNetWorth() { EXEC SQL BEGIN DECLARE SECTION; char char studioName[15]; int presNetWorth; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; /* print request that studio name and address be entered and read response into variables studioName and studioAddr */ EXEC SQL INSERT INTO Studio(name,address) VALUES(:studioName, :studioAddr); } Cursor Statements Declare a cursor c with: EXEC SQL DECLARE c CURSOR FOR query; Open and close cursor c with: EXEC SQL OPEN CURSOR c; EXEC SQL CLOSE CURSOR c; Fetch from c by: EXEC SQL FETCH c INTO variable(s); Macro NOT FOUND is true if and only if the FETCH fails to find a tuple. 7.1.2 Dynamic SQL Most applications use specific queries and modification statements in their interaction with the