嵌入SQL語句.ppt

Chapter 3: SQL（2）,Embedded SQL, ODBC and JDBC,Embedded SQL Application Architectures ODBC OLE DB、ADO JDBC,Embedded SQL,Embedded SQL,The SQL standard defines embeddings of SQL in a variety of programming languages such as Pascal, PL/I, Fortran, C, and Cobol. A language to which SQL queries are embedded is referred to as a host language(宿主語言), and the SQL structures permitted in the host language comprise embedded SQL（嵌入式）. EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL END-EXEC Note: this varies by language. E.g. the Java embedding uses # SQL . ;,嵌入SQL語句,說明性語句 嵌入SQL語句 數(shù)據(jù)定義 可執(zhí)行語句 數(shù)據(jù)控制 數(shù)據(jù)操縱 允許出現(xiàn)可執(zhí)行的高級語言語句的地方，都可以寫可執(zhí)行SQL語句 允許出現(xiàn)說明語句的地方，都可以寫說明性SQL語句 將SQL嵌入到高級語言中混合編程，程序中會含有兩種不同計算模型的語句 SQL語句 描述性的面向集合的語句 負(fù)責(zé)操縱數(shù)據(jù)庫 高級語言語句 過程性的面向記錄的語句 負(fù)責(zé)控制程序流程,工作單元之間的通信方式,SQL通信區(qū) 向主語言傳遞SQL語句的執(zhí)行狀態(tài)信息 主語言能夠據(jù)此控制程序流程 主變量 作用 主語言向SQL語句提供參數(shù) 將SQL語句查詢數(shù)據(jù)庫的結(jié)果交主語言進(jìn)一步處理 使用方法 這些變量由宿主語言的程序定義，并用SQL的DECLARE語句說明。 引用時，這些變量前必須加冒號“：”作為前綴標(biāo)識，以示與數(shù)據(jù)庫中變量有區(qū)別。 游標(biāo)（解決集合性操作語言與過程性操作語言的不匹配） 游標(biāo)定義語句（DECLARE） 游標(biāo)打開語句（OPEN） 游標(biāo)推進(jìn)語句( FETCH ) 游標(biāo)關(guān)閉語句（CLOSE）,SQL通信區(qū),SQLCA： SQL Communication Area SQLCA是一個數(shù)據(jù)結(jié)構(gòu) SQLCA的用途 SQL語句執(zhí)行后，DBMS反饋給應(yīng)用程序信息 描述系統(tǒng)當(dāng)前工作狀態(tài) 描述運行環(huán)境 這些信息將送到SQL通信區(qū)SQLCA中 應(yīng)用程序從SQLCA中取出這些狀態(tài)信息，據(jù)此決定接下來執(zhí)行的語句,SQLCA的使用方法,定義SQLCA 用EXEC SQL INCLUDE SQLCA加以定義 使用SQLCA SQLCA中有一個存放每次執(zhí)行SQL語句后返回代碼的變量SQLCODE 如果SQLCODE等于預(yù)定義的常量SUCCESS，則表示SQL語句成功，否則表示出錯 應(yīng)用程序每執(zhí)行完一條SQL 語句之后都應(yīng)該測試一下SQLCODE的值，以了解該SQL語句執(zhí)行情況并做相應(yīng)處理,主變量,什么是主變量 嵌入式SQL語句中可以使用主語言的程序變量來輸入或輸出數(shù)據(jù) 在SQL語句中使用的主語言程序變量簡稱為主變量（Host Variable） 主變量的用途 輸入主變量 指定向數(shù)據(jù)庫中插入的數(shù)據(jù) 將數(shù)據(jù)庫中的數(shù)據(jù)修改為指定值 指定執(zhí)行的操作 指定WHERE子句或HAVING子句中的條件 輸出主變量 獲取SQL語句的結(jié)果數(shù)據(jù) 獲取SQL語句的執(zhí)行狀態(tài),主變量（續(xù)）,在SQL語句中使用主變量的方法 1) 說明主變量 BEGIN DECLARE SECTION . . (說明主變量) . END DECLARE SECTION 2) 使用主變量 說明之后的主變量可以在SQL語句中任何一個能夠使用表達(dá)式的地方出現(xiàn) 為了與數(shù)據(jù)庫對象名（表名、視圖名、列名等）區(qū)別，SQL語句中的主變量名前要加冒號（:）作為標(biāo)志 在SQL語句之外(主語言語句中)使用主變量的方法 可以直接引用，不必加冒號,Example Query,Specify the query in SQL and declare a cursor for it EXEC SQL BEGIN DECLARE SECTION； Int amount； EXEC SQL END DECLARE SECTION； EXEC SQL declare c cursor for select customer-name, customer-city from depositor, customer, account where depositor.customer-name = customer.customer-name and depositor account-number = account.account-number and account.balance :amount END-EXEC,From within a host language, find the names and cities of customers with more than the variable amount dollars in some account.,游標(biāo),主語言是面向記錄的，一組主變量一次只能存放一條記錄 僅使用主變量并不能完全滿足SQL語句向應(yīng)用程序輸出數(shù)據(jù)的要求 嵌入式SQL引入了游標(biāo)的概念，用來協(xié)調(diào)這兩種不同的處理方式 什么是游標(biāo) 游標(biāo)是系統(tǒng)為用戶開設(shè)的一個數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果 每個游標(biāo)區(qū)都有一個名字 用戶可以用SQL語句逐一從游標(biāo)中獲取記錄，并賦給主變量，交由主語言進(jìn)一步處理,Embedded SQL (Cont.),（1）定義 EXEC SQL DECLARE CURSOR FOR ; 例如： EXEC SQL DECLARE C1 CURSOR FOR SELECT tit_id, tit, roy FROM titles; （2）打開The open statement causes the query to be evaluated EXEC SQL open c END-EXEC （3）逐一取出記錄The fetch statement causes the values of one tuple in the query result to be placed on host language variables. EXEC SQL fetch c into :cn, :cc END-EXEC Repeated calls to fetch get successive tuples in the query result A variable called SQLSTATE in the SQL communication area (SQLCA) gets set to 02000 to indicate no more data is available （4）關(guān)閉The close statement causes the database system to delete the temporary relation that holds the result of the query. EXEC SQL close c END-EXEC Note: above details vary with language., EXEC SQL INCLUDE SQLCA; /* (1) 定義SQL通信區(qū) */ EXEC SQL BEGIN DECLARE SECTION; /* (2) 說明主變量 */ CHAR title_id(7); CHAR title(81); INT royalty; EXEC SQL END DECLARE SECTION; main() EXEC SQL DECLARE C1 CURSOR FOR SELECT tit_id, tit, roy FROM titles; /* (3) 游標(biāo)操作（定義游標(biāo)）*/ /* 從titles表中查詢 tit_id, tit, roy */ EXEC SQL OPEN C1; /* (4) 游標(biāo)操作（打開游標(biāo)）*/,for(;) EXEC SQL FETCH C1 INTO :title_id, :title, :royalty; /* (5) 游標(biāo)操作（將當(dāng)前數(shù)據(jù)放入主變量并推進(jìn)游標(biāo)指針）*/ if (sqlca.sqlcode SUCCESS) /* (6) 利用SQLCA中的狀態(tài)信息決定何時退出循環(huán) */ break; printf(“Title ID: %s, Royalty: %d“, :title_id, :royalty); printf(“Title: %s“, :title); /* 打印查詢結(jié)果 */ EXEC SQL CLOSE C1; /* (7) 游標(biāo)操作（關(guān)閉游標(biāo)）*/ ,Updates Through Cursors,步驟 DECLARE EXEC SQL DECLARE CURSOR FOR FOR UPDATE OF ; EXEC SQL DECLARE CURSOR FOR FOR UPDATE (2) OPEN (3) FETCH 推進(jìn)游標(biāo)指針，并把當(dāng)前記錄從緩沖區(qū)中取出來送至主變量 (4) 檢查該記錄是否是要修改或刪除的記錄，是則處理之 WHERE CURRENT OF WHERE CURRENT OF (5) 重復(fù)第(3)和(4)步，用逐條取出結(jié)果集中的行進(jìn)行判斷和處理 (6) CLOSE,例 對某個系（系名由主變量deptname指定）的學(xué)生信息，根據(jù)用戶的要求修改其中某些人的年齡字段。 思路 查詢某個系全體學(xué)生的信息 然后根據(jù)用戶的要求修改其中某些記錄的年齡字段,例題（續(xù)）,EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* 說明主變量 deptname,HSno,HSname,HSsex,HSage,NEWAge等*/ EXEC SQL END DECLARE SECTION; gets(deptname); /* 為主變量deptname賦值 */ EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname FOR UPDATE OF Sage; /* 說明游標(biāo) */ EXEC SQL OPEN SX /* 打開游標(biāo) */ WHILE(1) /* 用循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 將游標(biāo)指針向前推進(jìn)一行，然后從結(jié)果集 中取當(dāng)前行，送相應(yīng)主變量*/ if (sqlca.sqlcode SUCCESS) break; /* 若所有查詢結(jié)果均已處理完或 出現(xiàn)SQL語句錯誤，則退出循環(huán) */ printf(“%s, %s, %s, %d“, Sno, Sname, Ssex, Sage); /* 顯示該記錄 */ printf(“UPDATE AGE ? “); /* 問用戶是否要修改 */ scanf(“%c“, ,例題（續(xù)）,if (yn=y or yn=Y) /* 需要修改 */ printf(“INPUT NEW AGE: “); scanf(“%d“, /* 關(guān)閉游標(biāo) */ ,例題（續(xù)）,例4 對某個系的學(xué)生信息，根據(jù)用戶的要求刪除其中某些人的記錄。 EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* 說明主變量 deptname,HSno,HSname,HSsex,HSage等*/ EXEC SQL END DECLARE SECTION; gets(deptname); /* 為主變量deptname賦值 */ EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname FOR UPDATE; /* 說明游標(biāo) */ EXEC SQL OPEN SX /* 打開游標(biāo) */,例題（續(xù)）,WHILE(1) /* 用循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 將游標(biāo)指針向前推進(jìn)一行，然后從結(jié) 果集中取當(dāng)前行，送相應(yīng)主變量*/ if (sqlca.sqlcode SUCCESS) break; /* 若所有查詢結(jié)果均已處理完或出現(xiàn)SQL語句錯誤，則退出循環(huán) */ printf(“%s, %s, %s, %d“, Sno, Sname, Ssex, Sage); /* 顯示該記錄 */ printf(“DELETE ? “); /* 問用戶是否要刪除 */ scanf(“%c“, /* 關(guān)閉游標(biāo) */,Dynamic SQL,Allows programs to construct and submit SQL queries at run time. Example of the use of dynamic SQL from within a C program. char * sqlprog = “update account set balance = balance * 1.05 where account-number = ?” EXEC SQL prepare dynprog from :sqlprog; char account 10 = “A-101”; EXEC SQL execute dynprog using :account; The dynamic SQL program contains a ?, which is a place holder for a value that is provided when the SQL program is executed.,Application Architectures,Application Architectures,Applications can be built using one of two architectures Two tier model Application program running at user site directly uses JDBC/ODBC to communicate with the database Three tier model Users/programs running at user sites communicate with an application server. The application server in turn communicates with the database,Two-tier Model,E.g. Java code runs at client site and uses JDBC to communicate with the backend server Problems: Security: passwords available at client site, all database operation possible More code shipped to client Not appropriate across organizations, or in large ones like universities,Three Tier Model,Application/HTTP Server,Servlets,JDBC,Network,HTTP/Application Specific Protocol,Three-tier Model (Cont.),E.g. Web client + Java Servlet using JDBC to talk with database server Client sends request over http or application-specific protocol Application or Web server receives request Request handled by CGI program or servlets Security handled by application at server Better security,ODBC,ODBC,Open DataBase Connectivity(ODBC) standard standard for application program to communicate with a database server. ODBC是一個調(diào)用層的接口 ODBC定義了標(biāo)準(zhǔn)的SQL語法 ODBC提供一個驅(qū)動程序管理器來管理并同時訪問多個DBMS系統(tǒng),應(yīng)用程序的主要功能是：調(diào)用ODBC函數(shù)，遞交SQL語句給DBMS，檢索出結(jié)果，并進(jìn)行處理。 驅(qū)動程序管理器是一個動態(tài)連接庫（DLL），用于連接各種DBS的DBMS驅(qū)動程序（如Oracle、Foxpro、Sybase等驅(qū)動程序），管理應(yīng)用程序和DBMS驅(qū)動程序之間的交互作用（通信）。 應(yīng)用程序通過調(diào)用驅(qū)動程序所支持的函數(shù)來操縱數(shù)據(jù)庫。驅(qū)動程序也是一個動態(tài)連接庫（DLL）。 單層驅(qū)動 多層驅(qū)動 兩層 三層 數(shù)據(jù)源,網(wǎng)絡(luò)環(huán)境下基于單層驅(qū)動程序的ODBC結(jié)構(gòu),基于多層驅(qū)動程序的ODBC結(jié)構(gòu)(兩層結(jié)構(gòu)),基于網(wǎng)關(guān)機(jī)制的多層驅(qū)動程序的ODBC結(jié)構(gòu)（三層結(jié)構(gòu)）,ODBC (Cont.),ODBC program first allocates an SQL environment, then a database connection handle. Opens database connection using SQLConnect(). Parameters for SQLConnect: connection handle, the server to which to connect the user identifier, password Must also specify types of arguments: SQL_NTS denotes previous argument is a null-terminated string.,ODBC Code,int ODBCexample() RETCODE retcode; HENV env; /* environment */ HDBC conn; /* database connection */ HSTMT hstmt /* 語句 */ SQLAllocEnv( ,有準(zhǔn)備地執(zhí)行SQL語句的函數(shù)（用于動態(tài)sql） (1) SQL語句預(yù)備函數(shù) 其格式如下：SQLPrepare（hstmt，szSqlStr，cbSqlStr）。其中，參數(shù)hstmt是一個有效的語句句柄，參數(shù)szSqlStr和cbSqlStr分別表示將要執(zhí)行的SQL語句的字符串及其長度。 (2) SQL語句執(zhí)行函數(shù) 其格式如下：SQLExecute（hstmt）。其中參數(shù)hstmt是一個有效的語句句柄。,while（RETCODE_IS_SUCCESSFUL（retcode） retcode=SQLFetch（hstmt）； /*推進(jìn)光標(biāo)*/ if（RETCODE_IS_SUCCESSFUL（retcode） do rcGetData = SQLGetData（hstmt，1，SQL_C_CHAR， szBuffer，sizeof（szBuffer），&cbValue）；/*獲取數(shù)據(jù)*/ DISPLAY_MEMO（szBuffer，cbValue）；*顯示* while（ rcGetData!=SQL_NO_DATA_FOUND）； ,ODBC Code (Cont.),Main body of program char branchname80; float balance; int lenOut1, lenOut2; HSTMT stmt; SQLAllocStmt(conn, ,ODBC Code (Cont.),SQLBindCol() binds C language variables to attributes of the query result When a tuple is fetched, its attribute values are automatically stored in corresponding C variables. Arguments（參數(shù)） to SQLBindCol() ODBC stmt variable, attribute position in query result The type conversion from SQL to C. The address of the variable. For variable-length types like character arrays, The maximum length of the variable Location to store actual length when a tuple is fetched. Note: A negative value returned for the length field indicates null value Good programming requires checking results of every function call for errors; we have omitted most checks for brevity.,More ODBC Features,Prepared Statement SQL statement prepared: compiled at the database Can have placeholders（占位符）: E.g. insert into account values(?,?,?) Repeatedly executed with actual values for the placeholders Metadata features finding all the relations in the database and finding the names and types of columns of a query result or a relation in the database. By default, each SQL statement is treated as a separate transaction that is committed automatically. Can turn off automatic commit on a connection SQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0) transactions must then be committed or rolled back explicitly by SQLTransact(conn, SQL_COMMIT) or SQLTransact(conn, SQL_ROLLBACK),ODBC Conformance Levels,Conformance levels specify subsets of the functionality defined by the standard. Core Level 1 requires support for metadata querying Level 2 requires ability to send and retrieve arrays of parameter values and more detailed catalog information. 可以用SQLGetFunction函數(shù)來得到確定驅(qū)動程序是否支持某一特定函數(shù) SQL符合性 最小級SQL DDL:CREATE/DROP TABLE 核心級SQL ALTER TABLE,CERETE/DROP INDEX,CREATE/DROP VIEW 擴(kuò)展級SQL 可以用SQLGetInfo函數(shù)來得到SQL兼容性級別和所支持的SQL關(guān)鍵字,ODBC API與SQL CLI之間的協(xié)調(diào),Microsoft公司于1991年11月提出了一個關(guān)于ODBC的體系結(jié)構(gòu)，并在1992年發(fā)布了ODBC 1.0規(guī)范，隨后以軟件開發(fā)工具包（SDK）形式出版并發(fā)行了這種面向SQL的API。 數(shù)據(jù)庫公司國際財團(tuán)SQL Access Group（SAG）和標(biāo)準(zhǔn)化組織XOpen在1992年聯(lián)合出版了一個規(guī)范，該規(guī)范定義了調(diào)用級界面（Call Level Interface），CLI）標(biāo)準(zhǔn)。 1993年，Microsoft公司推出了ODBC 2.0規(guī)范，并于1994年出版了ODBC 2.0 SDK。 Microsoft在1998年出版的ODBC 3.0規(guī)范中，作了一些修改，目的是更進(jìn)一步接近SQL標(biāo)準(zhǔn)。 SQL3（即SQL99）的第三部分介紹了SQLCLI。,OLE DB、ADO,Role of OBDC,Role of OLE DB,OLE DB breaks the features and functions of a DBMS into COM objects, making it easier for vendors to implement portions of functionality This characteristic overcomes a major disadvantage of ODBC With ODBC, a vendor must create an ODBC driver for almost all DBMS features and functions in order to participate in ODBC at all,OLE DB Goals,Create object interfaces for DBMS functionality pieces Query, update, transaction management, etc. Increase flexibility Allow data consumers to use only the objects they need Allow data providers to expose pieces of DBMS functionality Providers can deliver functionality in multiple interfaces Interfaces are standardized and extensible Object interface over any type of data Relational and non-relational database, ODBC or native, VSAM and other files, Email, etc. Do not force data to be converted or moved from where it is,組 成,Data Providers 數(shù)據(jù)提供者 凡是透過OLE DB 將數(shù)據(jù)提供出來的，就是數(shù)據(jù)提供者。例如SQL Server 數(shù)據(jù)庫中的數(shù)據(jù)表，或是附文件名為mdb 的Access 數(shù)據(jù)庫檔案等，都是Data Provider。 Data Consumers 數(shù)據(jù)使用者 凡是使用OLE DB 提供數(shù)據(jù)的程序或組件，都是OLE DB 的數(shù)據(jù)使用者。換句話說，凡是使用ADO 的應(yīng)用程序或網(wǎng)頁都是OLE DB 的數(shù)據(jù)使用者。 Service Components 服務(wù)組件 數(shù)據(jù)服務(wù)組件可以執(zhí)行數(shù)據(jù)提供者以及數(shù)據(jù)使用者之間數(shù)據(jù)傳遞的工作，數(shù)據(jù)使用者要向數(shù)據(jù)提供者要求數(shù)據(jù)時，是透過OLE DB 服務(wù)組件的查詢處理器執(zhí)行查詢的工作，而查詢到的結(jié)果則由指針引擎來管理。,OLE DB Data Providers,OLE DB has two types of data providers Tabular data provider exposes data via rowsets Examples: DBMS, spreadsheets, 電子郵件 Service provider is a transformer of data through OLE DB interfaces It is both a consumer and a provider of transformed data Examples: query processors, XML document creator,Role of ADO,ADO,ADO (Active Data Objects) characteristics Simple object model for OLE DB data consumers Can be used from VBScript, JScript, Visual Basic, Java, C#, C+ Single Microsoft data access standard Data access objects are the same for all types of OLE DB data,JDBC,JDBC,JDBC is a Java API for communicating with database systems supporting SQL JDBC supports a variety of features for querying and updating data, and for retrieving query results JDBC also supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributes Model for communicating with the database: Open a connection Create a “statement” object Execute queries using the Statement object to send queries and fetch results Exception mechanism to handle errors,JDBC Code,public static void JDBCexample(String dbid, String userid, String passwd) try Class.forName (“oracle.jdbc.driver.OracleDriver”); /裝載合適的驅(qū)動程序 Connection conn = DriverManager.getConnection( “jdbc:oracle:thin:aura.bell-:2000:bankdb“, userid, passwd); Statement stmt = conn.createStatement(); Do Actual Work . stmt.close(); conn.close(); catch (SQLException sqle) System.out.println(“SQLException : “ + sqle); ,JDBC Code (Cont.),Update to database try stmt.executeUpdate( “insert into account values (A-9732, Perryridge, 1200)“); catch (SQLException sqle) System.out.println(“Could not insert tuple. “ + sqle); Execute query and fetch and print results ResultSet rset = stmt.executeQuery( “select branch_name, avg(balance) from account group by branch_name“); while (rset.next() System.out.println( rset.getString(“branch_name“) + “ “ + rset.getFloat(2); ,JDBC Code Details,Getting result fields: rs.getString(“branchname”) and rs.getString(1) equivalent if branchname is the first argument of select result. Dealing with Null values int a = rs.getInt(“a”); if (rs.wasNull() Systems.out.println(“Got null value”);,Prepared Statement,Prepared statement allows queries to be compiled and executed multiple times with different arguments PreparedStatement pStmt = conn.prepareStatement( “insert into account values(?,?,?)”); pStmt.setString(1, “A-9732“); pStmt.setString(2, “Perryridge“); pStmt.setInt(3, 1200); pStmt.executeUpdate(); pStmt.setString(1, “A-9733“); pStmt.executeUpdate(); Beware: If value to be stored in database contains a single quote or other special character, prepared statements work fine, but creating a query string and executing it directly would result in a syntax error!（ prepared statements 不會檢查語法）,Transactions in JDBC,As with ODBC, each statement gets committed automatically in JDBC To turn off auto commit use conn.setAutoCommit(false); To commit or abort transactions use mit() or conn.rollback() To turn auto commit on again, use conn.setAutoCommit(true);,Procedure and Function Calls in JDBC,JDBC provides a class CallableStatement which allows SQL stored procedures/functions to be invoked.（存儲過程的調(diào)用） CallableStatement cs1 = conn.prepareCall( “call proc (?,?)” ) ; CallableStatement cs2 = conn.prepareCall( “? = call func (?,?)” );,Result Set MetaData,The class ResultSetMetaData provides information about all the columns of the ResultSet. Instance of this class is obtained by getMetaData( ) function of ResultSet. Provides Functions for getting number of columns, column name, type, precision, scale, table from which the column is derived etc. ResultSetMetaData rsmd = rs.getMetaData ( ); for ( int i = 1; i = rsmd.getColumnCount( ); i+ ) String name = rsmd.getColumnName(i);/*獲得字段名*/ String typeName = rsmd.getColumnTypeName(i); ,D