電子課件數(shù)據(jù)庫基礎(chǔ)與應(yīng)用（微課版 第3版）第7章

1、 內(nèi)容可修改電子課件數(shù)據(jù)庫基礎(chǔ)與應(yīng)用（微課版 第3版）第7章第7章SQL程序設(shè)計 SQL的局限性SQL的設(shè)計目標簡單明確，就是完成對數(shù)據(jù)庫的增、刪、改、查操作。 SQL不具有高級程序設(shè)計語言的變量、流程控制語句等功能，因此，只能通過交互的方式使用數(shù)據(jù)庫，而不能通過編程的方式達到自動化的訪問數(shù)據(jù)庫。SQL程序設(shè)計的方式直接SQL：使用DBMS提供的客戶端程序查詢，使用DBMS提供的編程 語言(包含了SQL)，訪問數(shù)據(jù)庫；嵌入式SQL：使用高級程序設(shè)計語言編寫程序，程序中嵌入了SQL 語句，用于訪問數(shù)據(jù)庫；CLI：定義了一組接口(ODBC)，用于訪問數(shù)據(jù)庫。這組接口獨立于DBMS 使用高級程序設(shè)計

2、語言編寫程序，通過調(diào)用接口訪問數(shù)據(jù)庫。應(yīng)用程序CLISQL ServerMySQL.CLI解決的問題：DBMS的無關(guān)性應(yīng)用程序SQL ServerOracleMySQLDBMS應(yīng)用程序CLIOracle驅(qū)動程序OracleODBCJDBC7.1 嵌入式SQL在高級程序設(shè)計語言中使用SQL語句操作數(shù)據(jù)庫需要解決兩個問題：采用某種語法形式使編譯程序能區(qū)分SQL語句和宿主語言的語句提供一種機制在SQL和宿主語言之間交換數(shù)據(jù)和執(zhí)行狀態(tài)。7.1.1 嵌入式SQL的語法SQL語句加前綴EXEC SQL ：EXECSQLDROPTABLESC;EXECSQLSELECT*FROMStudent;EXECSQ

3、LGRANTUPDATEONStudentTOUser1;7.1.1 嵌入式SQL的語法為了不影響宿主語言的編譯器，DBMS提供了一個預(yù)編譯器預(yù)編譯器可識別嵌入式SQL語句，將它們轉(zhuǎn)換成SQL函數(shù)庫的函數(shù)調(diào)用， 將最初的宿主語言和嵌入式SQL的混合體轉(zhuǎn)換成宿主語言的代碼宿主語言的編譯器進行通常的編譯和連接操作，最終生成可執(zhí)行代碼， 完成過程控制和數(shù)據(jù)庫操作 7.1.2 嵌入式SQL與宿主語言通過變量交換數(shù)據(jù)EXECSQLBEGINDECLARESECTION按照宿主語言的語法定義的變量EXECSQLENDDECLARESECTIONEXECSQLBEGINDECLARESECTIONcharS

4、no8;charSname9;charSsex3;shortSage;charSdept21;charSQLSTATE6;shortgradenullflagEXECSQLENDDECLARESECTION7.1.3 SELECT：單條記錄EXECSQLSELECTALL|DISTINCT,INTO,FROM,WHEREGROUPBYHAVINGORDERBYASC|DESC;INTO 子句將SQL的查詢結(jié)果傳入宿主語言條件表達式中可以使用變量將宿主語言的數(shù)據(jù)傳入SQL宿主變量前要加: 號。7.1.3 SELECT：單條記錄例7.1 查詢某個學(xué)生的信息，這個學(xué)生的學(xué)號已存放于宿主變量Sno，將

5、查詢得到的學(xué)生信息存放到宿主變量。 EXECSQLSELECTSname,Ssex,Sage,Sdept INTO:Sname,:Ssex,:Sage,:Sdept FROMStudent WHERESno=:Sno;7.1.3 SELECT：單條記錄例7.2 查詢某個學(xué)生選修課程號為1024的成績。EXECSQLSELECTGrade INTO:grade:gradenullflag FROMSC WHERESno=:SnoANDCno=1024;由于學(xué)生的成績可能是空值，所以這里使用了指示變量gradenullflag，如果gradenullflag=1，則查詢返回的grade值為NULL

6、7.1.4 SELECT：多條記錄-游標游標（Cursor）是系統(tǒng)開設(shè)的一個數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果。游標有一個名稱，可以通過游標逐一獲取記錄，并賦予宿主語言的宿主變量，由宿主語言做進一步的處理 記錄1記錄2.記錄n當前位置結(jié)果集游標包括以下兩個部分。游標結(jié)果集游標的位置7.1.4 SELECT：多條記錄-游標EXECSQLDECLAREcursor-nameINSENSITIVESCROLLCURSOR FOR SELECTstatement FORREADONLY1、聲明游標：INSENSITIVE: 基本表的變化(插入、刪除、修改)不影響游標的結(jié)果SCROLL: 可以非順序存

7、取的方式讀取游標的結(jié)果FOR READ ONLY: 不會通過游標修改基本表的數(shù)據(jù)7.1.4 SELECT：多條記錄-游標EXECSQLDECLAREdept_computerCURSORFOR SELECT* FROMStudent WHERESdept=計算機 FORREADONLY;例7.3 聲明存取計算機系全體學(xué)生信息的游標。7.1.4 SELECT：多條記錄-游標EXEC SQL OPEN 2、打開游標：打開游標后，DBMS執(zhí)行與游標相關(guān)聯(lián)的SELECT語句，獲取查詢結(jié)果，游標指向第1條記錄 7.1.4 SELECT：多條記錄-游標EXECSQLFETCHFROMcursor_name

8、INTOvariable-list;3、存取數(shù)據(jù)：EXECSQLFETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|RELATIVEn FROMcursor_name INTOvariable-list;NEXT：下一條記錄PRIOR：上一條記錄FIRST：第1條記錄LAST：最后1條記錄ABSOLUTE n：第n條記錄RELATIVE n：相對當前記錄的第n條記錄7.1.4 SELECT：多條記錄-游標EXECSQLCLOSEcursor_name;4、關(guān)閉游標：釋放游標占用的資源7.1.4 SELECT：多條記錄-游標EXECSQLDECLAREdept_comput

9、erCURSORFORSELECT*FROMStudentWHERESdept=計算機FORREADONLY;EXECSQLOPENdept_computer;EXECSQLFETCHdept_computerINTO:Sno,:Sname,:Ssex,:Sage,:Sdept;while(strcmp(SQLSTATE,02000)!=0)- 輸出語句，略EXECSQLFETCHdept_computer INTO:Sno,:Sname,:Ssex,:Sage,:Sdept;EXECSQLCLOSEdept_computer;例7.4 通過游標dept_computer讀取每個學(xué)生的信息并顯

10、示。SQLSTATE：SQL執(zhí)行結(jié)果碼02000:是否還有下一條記錄7.1.4 使用游標修改數(shù)據(jù)UPDATEtable-nameSETcolumn-name=expressionWHERECURRENTOFcursor_nameDELETEFROMtable-nameWHERECURRENTOFcursor_name游標的另一個作用是修改數(shù)據(jù)庫的數(shù)據(jù)：聲明游標時，沒有添加FOR READ ONLY關(guān)鍵字。一般不要修改基于多表的游標。7.1.4 使用游標修改數(shù)據(jù)例7.5 通過游標dept_computer將每個學(xué)生的年齡加1。EXECSQLFETCHdept_computer;while(str

11、cmp(SQLSTATE,02000)!=0)EXECSQLUPDATEStudentSETSage=Sage+1 WHERECURRENTOFdept_computer;FETCHdept_computer;7.1.5 動態(tài)SQL前面介紹的SQL叫作靜態(tài)SQL，完整的SQL語句在編譯時就已確定。在某些應(yīng)用中，在編譯時只能知道SQL語句的一部分，還有一些細節(jié)有待在人機交互時根據(jù)某些條件才能構(gòu)造出來。SQL提供了在宿主語言中構(gòu)造、準備和執(zhí)行SQL語句的指令，這些指令被稱為動態(tài)SQL :PREPARE、EXECUTEEXECUTE IMMEDIATE7.1.5 動態(tài)SQLPREPARE指令把存放在

12、宿主變量的一個字符串“準備”為一個SQL語句，所謂“準備”，就是通過與DBMS的通信，分析SQL語句和生成執(zhí)行計劃。 PREPAREstmt_nameFROM:host-variable:host-variable 是存放字符串的宿主變量，不能出現(xiàn)某些特定的SQL語句，如SELECT INTO等，也不能出現(xiàn)注釋和宿主變量，需要使用宿主變量作為參數(shù)的地方可以用“?”代替，在執(zhí)行語句時，由USING為參數(shù)賦值。7.1.5 動態(tài)SQLEXECUTE指令用來執(zhí)行由PREPARE準備的SQL語句。EXECUTEprepared_stmt_nameUSING:host-variable,.prepared

13、_stmt_name是由某個PREPARE準備的SQL語句USING后面的宿主變量用于替換SQL語句的“？”參數(shù)， 有幾個“?”參數(shù)，就必須有幾個宿主變量，數(shù)據(jù)類型必須兼容， 并且按照位置對應(yīng)的原則進行替換。 7.1.5 動態(tài)SQL例7.6 生成一個向SC表插入任意元組的SQL語句。EXECSQLBEGINDECLARESECTION;charprep=INSERTINTOscVALUES(?,?,?);charsno8;charcno5;shortgrade;EXECSQLENDDECLARESECTION;EXECSQLPREPAREprep_statFROM:prep;while(str

14、cmp(SQLSTATE,00000)=0)scanf(%s,sno);ifstrcmp(Sno,0000000)=0)break;scanf(%s,cno);scanf(%d,grade);EXECSQLEXECUTEprep_statUSING:sno,:cno,:grade;00000:SQL語句執(zhí)行成功7.1.5 動態(tài)SQLEXECUTE IMMEDIATE語句結(jié)合了PREPARE和EXECUTE指令的功能， 準備一個SQL語句并立即執(zhí)行它。因為準備一個SQL語句需要與DBMS通信，開銷比較大， 所以，PREPARE和EXECUTE方式適合于準備的語句要多次執(zhí)行的情形，EXECUTE

15、IMMEDIATE方式適用于只執(zhí)行一次的情形。 EXECUTEIMMEDIATE:host-variable7.2 服務(wù)器端的存儲過程和存儲函數(shù)存儲過程是由SQL語句和控制流語句編寫的過程，經(jīng)編譯和優(yōu)化后， 存儲于數(shù)據(jù)庫服務(wù)器，由應(yīng)用程序調(diào)用執(zhí)行。存儲過程的優(yōu)點：確保數(shù)據(jù)訪問和操作的一致性，提高了應(yīng)用程序的可維護性。提高了系統(tǒng)的執(zhí)行效率。提供一種安全機制。減少了網(wǎng)絡(luò)的流量負載。7.2 服務(wù)器端的存儲過程和存儲函數(shù)ClientServerSnoSnameSsexSageSdept2000012王林男19計算機2000113張大民男18管理2000256顧芳女19管理2000278姜凡男19管理2

16、000014葛波女18計算機CREATEPROCEDURE. routine-body;CREATEFUCTION.routine-body;調(diào)用存儲過程使用存儲函數(shù)7.2 服務(wù)器端的存儲過程和存儲函數(shù)例7.7 輸出某門課程成績最好的學(xué)生的姓名和所在系，如果有多個這樣的 學(xué)生，則任意輸出一個 CREATEPROCEDUREcourse_list(INvcnochar(4),OUTvsnamechar(8),OUTvsdeptchar(2)DECLAREmax_gradeint;DECLAREvgradeint;DECLAREvsnochar(7);DECLAREcur_scCURSORFORS

17、ELECTSno,GradeFROMSCWHERECno=vcno;7.2 服務(wù)器端的存儲過程和存儲函數(shù)BEGINSELECTMAX(grade)INTOmax_gradeFROMSCWHERECno=vcno;OPENcur_sccur_loop:LOOPFETCHcur_scINTOvsno,vgrade;IFvgrade=max_gradeTHENSELECTSname,SdeptINTOvsname,vsdeptFROMStudentWHERESno=vsno;LEAVEcur_loop;ENDIF;ENDLOOP;CLOSEcur_sc;END;7.3 觸發(fā)器觸發(fā)器是用戶定義在表或視

18、圖上的一類由事件驅(qū)動的存儲過程。監(jiān)控的事件發(fā)生后，觸發(fā)器自動運行。觸發(fā)器可以維護行級數(shù)據(jù)完整性。觸發(fā)器可以實施比用CHECK定義的約束更為復(fù)雜的約束。觸發(fā)器可以評估數(shù)據(jù)修改前后的表的狀態(tài)，并根據(jù)其差異采取對策。ON事件IF前提條件THEN動作7.3 觸發(fā)器：事件用戶向數(shù)據(jù)庫管理系統(tǒng)提交INSERT、UPDATE和DELETE語句后，數(shù)據(jù)庫管理系統(tǒng)會產(chǎn)生以下的事件：INSERTUPDATEDELETE7.3 觸發(fā)器：觸發(fā)策略觸發(fā)器的觸發(fā)策略是指在觸發(fā)器激活之后何時檢查觸發(fā)器的前提條件。立即檢查延遲檢查，即在事務(wù)結(jié)束時檢查7.3 觸發(fā)器：觸發(fā)器的執(zhí)行時機如果觸發(fā)器的觸發(fā)策略是延遲檢查，則觸發(fā)器的執(zhí)

19、行也必然被延遲， 直到事務(wù)結(jié)束才執(zhí)行；如果觸發(fā)器的觸發(fā)策略是立即檢查，則觸發(fā)器動作的執(zhí)行也有兩種選擇：延遲到觸發(fā)事務(wù)結(jié)束時再執(zhí)行立即執(zhí)行在產(chǎn)生觸發(fā)事件的語句之前執(zhí)行，稱為BEFORE觸發(fā)器；在產(chǎn)生觸發(fā)事件的語句之后執(zhí)行，稱為AFTER觸發(fā)器；不執(zhí)行產(chǎn)生觸發(fā)事件的語句，稱為INSTEAD OF觸發(fā)器，7.3 觸發(fā)器：觸發(fā)器的粒度INSERT、DELETE、UPDATE語句產(chǎn)生相應(yīng)的觸發(fā)事件，這些語句可能操作多個元組。語句級粒度：一條語句產(chǎn)生一個觸發(fā)事件， 即使這個語句沒有操作任何一個元組（如沒有滿足條件的元組）；行級粒度L：插入、刪除、修改一個元組就產(chǎn)生一個觸發(fā)事件， 并且視改變不同的元組為不同

20、的事件，則觸發(fā)器可能被多次執(zhí)行。7.3 觸發(fā)器：沖突在一個表或視圖上，針對一類事件，可以定義多個觸發(fā)器。當事件發(fā)生時，DBMS需要合理地調(diào)度這些觸發(fā)器。有序沖突解決方案：按照一定的次序依次計算觸發(fā)器的前提條件， 當一個觸發(fā)器的前提條件為真時，執(zhí)行觸發(fā)器， 然后判斷下一個觸發(fā)器的前提條件。分組沖突解決方案：同時計算所有觸發(fā)器的前提條件， 然后調(diào)度執(zhí)行所有前提條件為真的觸發(fā)器。7.3 觸發(fā)器：定義CREATETRIGGERtrigger-nameBEFORE|AFTERINSERT|DELETE|UPDATEOFcolumn-name-listONTable-name|View-nameREFER

21、ENCINGOLDAStuple-nameNEWAStuple-nameOLDTABLEASold-table-nameNEWTABLEASnew-table-nameFOREACHROW|STATEMENTWHEN(precondition)Statement-listSQL:1999的語法：7.3 觸發(fā)器：SQL SERVER的示例例7.10 在SC表上建立一個AFTER INSERT觸發(fā)器，顯示由哪個用戶 插入了一行數(shù)據(jù)。CREATETRIGGERscInsertONSCAFTERINSERTASPRINTOneRowInsertedBy+USER_NAME()7.3 觸發(fā)器：SQL S

22、ERVER的示例觸發(fā)器執(zhí)行時產(chǎn)生兩種臨時的特殊表：DELETED表和INSERTED表。DELETED表：存放被刪除和修改的舊數(shù)據(jù)。 在執(zhí)行DELETE語句時，從觸發(fā)器所在的表中刪除元組， 并將被刪除的元組保存到DELETED表。 UPDATE語句將修改前的數(shù)據(jù)轉(zhuǎn)移到DELETED表。INSERTED表：存放被插入和修改的新數(shù)據(jù)。 當執(zhí)行INSERT語句時，新的記錄插入到觸發(fā)器所在的表和INSERTED表。 UPDATE語句將修改后的數(shù)據(jù)也復(fù)制到INSERTED表。7.3 觸發(fā)器：SQL SERVER的示例例7.12 編寫一個觸發(fā)器，記錄向Student表插入記錄的用戶、 時間和插入的學(xué)號（關(guān)

23、鍵字）。CREATETRIGGERlogStudentONStudentAFTERINSERTASDECLARErecordKeychar(7)SELECTrecordKey=SnoFROMINSERTEDINSERTINTOEXAMPLELOG(operatingTime,TableName,userName,recordKey)VALUES(getdate(), Student, user_name(), recordKey)7.4.1 JDBC：概述JDBC由若干類和接口組成，用于編寫與平臺和DBMS無關(guān)的代碼，非常適合使用Java語言開發(fā)客戶機/服務(wù)器應(yīng)用程序。包：Java.sql包：

24、提供訪問和處理客戶機的數(shù)據(jù)源的APIJavax.sql包：提供服務(wù)器的數(shù)據(jù)源訪問和處理的API類和接口：DriverManager類：用于處理驅(qū)動程序的加載。Connection接口：與數(shù)據(jù)庫建立連接。Statement接口：用于執(zhí)行SQL語句ResultSet接口：用于保存查詢結(jié)果。7.4.1 JDBC：驅(qū)動程序管理器驅(qū)動程序管理器由java.sql.DriverManager類實現(xiàn)，負責(zé)管理JDBC驅(qū)動程序。DriverManager類主要跟蹤已經(jīng)加載的JDBC驅(qū)動程序，在數(shù)據(jù)庫和驅(qū)動程序之間建立連接，也處理如登錄時間限制、跟蹤信息等工作。JDBC通過驅(qū)動程序提供應(yīng)用系統(tǒng)與數(shù)據(jù)庫平臺的獨立

25、性。驅(qū)動程序與具體的DBMS有關(guān)。7.4.2 JDBC的工作流程1、加載驅(qū)動程序Class.forName(drivername); 2、建立與數(shù)據(jù)庫的連接Connection conn=DriverManager.getConnection(dbURL,userName,userPwd); 3、建立語句對象Statement st=conn.createStatement();7.4.2 執(zhí)行查詢executeQuery()方法建立了一個結(jié)果集。結(jié)果集有多種類型，結(jié)果集的類型決定了游標的移動方式和操作類型。結(jié)果集的類型由createStatement方法確定createStatement(i

26、ntresultSetType,intresultSetConcurrency)ResultSet rs=st.executeQuery(SELECT*FROMStudent);7.4.2 執(zhí)行查詢參數(shù)resultSetType指明結(jié)果集的類型：TYPE_FORWORD_ONLY：結(jié)果集的游標只能向前移動。TYPE_SCROLL_INSENSITIVE：游標向前或向后雙向移動， 結(jié)果集不反映數(shù)據(jù)的最新變化。TYPE_SCROLL_SENSITIVE：游標向前或向后雙向移動， 結(jié)果集反映數(shù)據(jù)的最新變化。參數(shù)resultSetConcurrency決定結(jié)果集的更新方式：CONCUR_READ_ON

27、LY：不可以修改結(jié)果集。CONCUR_UPDATABLE：可以修改結(jié)果集。7.4.2 執(zhí)行查詢結(jié)果集是一個游標7.4.2 執(zhí)行查詢ResultSet接口移動游標的方法：beforeFirst()：移動到結(jié)果集的開始位置（第一條記錄之前）afterLast：移動到結(jié)果集的結(jié)束位置（最后一條記錄之后）first()：移動到第一條記錄last()：移動到最后一條記錄next()：移動到下一條記錄previous()：移動到上一條記錄absolute(int row)：移動到row指定的記錄，絕對位置relative(int row)：從當前記錄開始，上移或下移row條記錄7.4.2 執(zhí)行查詢Resu

28、ltSet接口讀取字段的方法：ResultSet接口提供了一組get方法用于獲取當前記錄的字段值。使用字段號獲取字段的值，字段號從1開始，自左至右增加。使用字段名可讀性強，使用字段號的效率比較高。例如： 方法getString()用于讀取字符型字段，方法getInt()用于讀取整型字段。7.4.3 執(zhí)行修改：executeUpdate Statement接口提供了executeUpdate(String sql)方法實現(xiàn)對表的更新操作，參數(shù)sql表示Insert、Update和Delete語句，該方法的返回值是int類型，表示受影響的記錄數(shù)。 Stringsql=DELETEFROMStude

29、ntWHERESno=2000999;st.executeUpdate(sql);7.4.2 執(zhí)行修改：executeUpdate 如果需要使用事務(wù)：Conne.setAutoCommit(false);Stringsql=DELETEFROMStudentWHERESno=2000999;st.executeUpdate(sql);mit(); / 或conn.rollback(); 7.4.2 執(zhí)行修改：結(jié)果集 更新操作update方法組：更新結(jié)果集當前記錄的指定字段的值，例如updateInt、 updateString等，updateRow()：向數(shù)據(jù)庫提交更新操作，包含了commit

30、的功能cancelUpdateRow()：撤銷向數(shù)據(jù)庫所做的所有更新操作。刪除操作deleteRow()：將當前記錄從數(shù)據(jù)庫刪除7.4.2 執(zhí)行修改：結(jié)果集 插入操作moveToInsertRow()：將游標移動到插入行，update方法組：對插入行的字段賦值。insertRow()：將插入行的數(shù)據(jù)寫入數(shù)據(jù)庫7.4.2 執(zhí)行修改：結(jié)果集 例7.14 向Student表插入一名學(xué)生的信息。st=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE );rs=st.executeQuery(S

31、ELECT*FROMStudent);rs.moveToInsertRow();rs.updateString(Sno,2000999);rs.updateString(Sname,馬翔);rs.updateString(Ssex,男);rs.updateInt(Sage,20);rs.updateString(Sdept,計算機);rs.insertRow();7.4.3 示例 publicclassDBManagerprivateConnectionconn=null;/連接數(shù)據(jù)庫privateStatementstement=null;/查詢語句privateStatementupdat

32、e=null;/更新語句privateResultSetrst=null;/使用游標處理查詢結(jié)果publicDBManager()openCon();privatevoidopenCon()StringdriverName=com.microsoft.jdbc.sqlserver.SQLServerDriver;StringdbURL=jdbc:microsoft:sqlserver:/localhost:1433;DatabaseName=pubs;StringuserName=sa;StringuserPwd=;tryClass.forName(driverName);conn=Drive

33、rManager.getConnection(dbURL,userName,userPwd);catch(Exceptione)e.printStackTrace();7.4.3 示例 publicConnectiongetConnection()returnconn;publicvoidbegin()throwsSQLExceptiontryconn.setAutoCommit(false);catch(SQLExceptionex)throwex;publicvoidcommit()throwsSQLExceptiontrymit();catch(SQLExceptionex)throwex;7.4.3 示例 publicvoidrollback()throwsSQLExceptiontryconn.rollback();catch(SQLExceptionex)throwex;publicResultSetselect(Stringsql)throwsSQLExceptionstement=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_