电子课件数据库基础与应用（微课版 第3版）第7章

1、 内容可修改电子课件数据库基础与应用（微课版 第3版）第7章第7章SQL程序设计 SQL的局限性SQL的设计目标简单明确，就是完成对数据库的增、删、改、查操作。 SQL不具有高级程序设计语言的变量、流程控制语句等功能，因此，只能通过交互的方式使用数据库，而不能通过编程的方式达到自动化的访问数据库。SQL程序设计的方式直接SQL：使用DBMS提供的客户端程序查询，使用DBMS提供的编程 语言(包含了SQL)，访问数据库；嵌入式SQL：使用高级程序设计语言编写程序，程序中嵌入了SQL 语句，用于访问数据库；CLI：定义了一组接口(ODBC)，用于访问数据库。这组接口独立于DBMS 使用高级程序设计

2、语言编写程序，通过调用接口访问数据库。应用程序CLISQL ServerMySQL.CLI解决的问题：DBMS的无关性应用程序SQL ServerOracleMySQLDBMS应用程序CLIOracle驱动程序OracleODBCJDBC7.1 嵌入式SQL在高级程序设计语言中使用SQL语句操作数据库需要解决两个问题：采用某种语法形式使编译程序能区分SQL语句和宿主语言的语句提供一种机制在SQL和宿主语言之间交换数据和执行状态。7.1.1 嵌入式SQL的语法SQL语句加前缀EXEC SQL ：EXECSQLDROPTABLESC;EXECSQLSELECT*FROMStudent;EXECSQ

3、LGRANTUPDATEONStudentTOUser1;7.1.1 嵌入式SQL的语法为了不影响宿主语言的编译器，DBMS提供了一个预编译器预编译器可识别嵌入式SQL语句，将它们转换成SQL函数库的函数调用， 将最初的宿主语言和嵌入式SQL的混合体转换成宿主语言的代码宿主语言的编译器进行通常的编译和连接操作，最终生成可执行代码， 完成过程控制和数据库操作 7.1.2 嵌入式SQL与宿主语言通过变量交换数据EXECSQLBEGINDECLARESECTION按照宿主语言的语法定义的变量EXECSQLENDDECLARESECTIONEXECSQLBEGINDECLARESECTIONcharS

4、no8;charSname9;charSsex3;shortSage;charSdept21;charSQLSTATE6;shortgradenullflagEXECSQLENDDECLARESECTION7.1.3 SELECT：单条记录EXECSQLSELECTALL|DISTINCT,INTO,FROM,WHEREGROUPBYHAVINGORDERBYASC|DESC;INTO 子句将SQL的查询结果传入宿主语言条件表达式中可以使用变量将宿主语言的数据传入SQL宿主变量前要加: 号。7.1.3 SELECT：单条记录例7.1 查询某个学生的信息，这个学生的学号已存放于宿主变量Sno，将

5、查询得到的学生信息存放到宿主变量。 EXECSQLSELECTSname,Ssex,Sage,Sdept INTO:Sname,:Ssex,:Sage,:Sdept FROMStudent WHERESno=:Sno;7.1.3 SELECT：单条记录例7.2 查询某个学生选修课程号为1024的成绩。EXECSQLSELECTGrade INTO:grade:gradenullflag FROMSC WHERESno=:SnoANDCno=1024;由于学生的成绩可能是空值，所以这里使用了指示变量gradenullflag，如果gradenullflag=1，则查询返回的grade值为NULL

6、7.1.4 SELECT：多条记录-游标游标（Cursor）是系统开设的一个数据缓冲区，存放SQL语句的执行结果。游标有一个名称，可以通过游标逐一获取记录，并赋予宿主语言的宿主变量，由宿主语言做进一步的处理 记录1记录2.记录n当前位置结果集游标包括以下两个部分。游标结果集游标的位置7.1.4 SELECT：多条记录-游标EXECSQLDECLAREcursor-nameINSENSITIVESCROLLCURSOR FOR SELECTstatement FORREADONLY1、声明游标：INSENSITIVE: 基本表的变化(插入、删除、修改)不影响游标的结果SCROLL: 可以非顺序存

7、取的方式读取游标的结果FOR READ ONLY: 不会通过游标修改基本表的数据7.1.4 SELECT：多条记录-游标EXECSQLDECLAREdept_computerCURSORFOR SELECT* FROMStudent WHERESdept=计算机 FORREADONLY;例7.3 声明存取计算机系全体学生信息的游标。7.1.4 SELECT：多条记录-游标EXEC SQL OPEN 2、打开游标：打开游标后，DBMS执行与游标相关联的SELECT语句，获取查询结果，游标指向第1条记录 7.1.4 SELECT：多条记录-游标EXECSQLFETCHFROMcursor_name

8、INTOvariable-list;3、存取数据：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、关闭游标：释放游标占用的资源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读取每个学生的信息并显

10、示。SQLSTATE：SQL执行结果码02000:是否还有下一条记录7.1.4 使用游标修改数据UPDATEtable-nameSETcolumn-name=expressionWHERECURRENTOFcursor_nameDELETEFROMtable-nameWHERECURRENTOFcursor_name游标的另一个作用是修改数据库的数据：声明游标时，没有添加FOR READ ONLY关键字。一般不要修改基于多表的游标。7.1.4 使用游标修改数据例7.5 通过游标dept_computer将每个学生的年龄加1。EXECSQLFETCHdept_computer;while(str

11、cmp(SQLSTATE,02000)!=0)EXECSQLUPDATEStudentSETSage=Sage+1 WHERECURRENTOFdept_computer;FETCHdept_computer;7.1.5 动态SQL前面介绍的SQL叫作静态SQL，完整的SQL语句在编译时就已确定。在某些应用中，在编译时只能知道SQL语句的一部分，还有一些细节有待在人机交互时根据某些条件才能构造出来。SQL提供了在宿主语言中构造、准备和执行SQL语句的指令，这些指令被称为动态SQL :PREPARE、EXECUTEEXECUTE IMMEDIATE7.1.5 动态SQLPREPARE指令把存放在

12、宿主变量的一个字符串“准备”为一个SQL语句，所谓“准备”，就是通过与DBMS的通信，分析SQL语句和生成执行计划。 PREPAREstmt_nameFROM:host-variable:host-variable 是存放字符串的宿主变量，不能出现某些特定的SQL语句，如SELECT INTO等，也不能出现注释和宿主变量，需要使用宿主变量作为参数的地方可以用“?”代替，在执行语句时，由USING为参数赋值。7.1.5 动态SQLEXECUTE指令用来执行由PREPARE准备的SQL语句。EXECUTEprepared_stmt_nameUSING:host-variable,.prepared

13、_stmt_name是由某个PREPARE准备的SQL语句USING后面的宿主变量用于替换SQL语句的“？”参数， 有几个“?”参数，就必须有几个宿主变量，数据类型必须兼容， 并且按照位置对应的原则进行替换。 7.1.5 动态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语句执行成功7.1.5 动态SQLEXECUTE IMMEDIATE语句结合了PREPARE和EXECUTE指令的功能， 准备一个SQL语句并立即执行它。因为准备一个SQL语句需要与DBMS通信，开销比较大， 所以，PREPARE和EXECUTE方式适合于准备的语句要多次执行的情形，EXECUTE

15、IMMEDIATE方式适用于只执行一次的情形。 EXECUTEIMMEDIATE:host-variable7.2 服务器端的存储过程和存储函数存储过程是由SQL语句和控制流语句编写的过程，经编译和优化后， 存储于数据库服务器，由应用程序调用执行。存储过程的优点：确保数据访问和操作的一致性，提高了应用程序的可维护性。提高了系统的执行效率。提供一种安全机制。减少了网络的流量负载。7.2 服务器端的存储过程和存储函数ClientServerSnoSnameSsexSageSdept2000012王林男19计算机2000113张大民男18管理2000256顾芳女19管理2000278姜凡男19管理2

16、000014葛波女18计算机CREATEPROCEDURE. routine-body;CREATEFUCTION.routine-body;调用存储过程使用存储函数7.2 服务器端的存储过程和存储函数例7.7 输出某门课程成绩最好的学生的姓名和所在系，如果有多个这样的 学生，则任意输出一个 CREATEPROCEDUREcourse_list(INvcnochar(4),OUTvsnamechar(8),OUTvsdeptchar(2)DECLAREmax_gradeint;DECLAREvgradeint;DECLAREvsnochar(7);DECLAREcur_scCURSORFORS

17、ELECTSno,GradeFROMSCWHERECno=vcno;7.2 服务器端的存储过程和存储函数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 触发器触发器是用户定义在表或视

18、图上的一类由事件驱动的存储过程。监控的事件发生后，触发器自动运行。触发器可以维护行级数据完整性。触发器可以实施比用CHECK定义的约束更为复杂的约束。触发器可以评估数据修改前后的表的状态，并根据其差异采取对策。ON事件IF前提条件THEN动作7.3 触发器：事件用户向数据库管理系统提交INSERT、UPDATE和DELETE语句后，数据库管理系统会产生以下的事件：INSERTUPDATEDELETE7.3 触发器：触发策略触发器的触发策略是指在触发器激活之后何时检查触发器的前提条件。立即检查延迟检查，即在事务结束时检查7.3 触发器：触发器的执行时机如果触发器的触发策略是延迟检查，则触发器的执

19、行也必然被延迟， 直到事务结束才执行；如果触发器的触发策略是立即检查，则触发器动作的执行也有两种选择：延迟到触发事务结束时再执行立即执行在产生触发事件的语句之前执行，称为BEFORE触发器；在产生触发事件的语句之后执行，称为AFTER触发器；不执行产生触发事件的语句，称为INSTEAD OF触发器，7.3 触发器：触发器的粒度INSERT、DELETE、UPDATE语句产生相应的触发事件，这些语句可能操作多个元组。语句级粒度：一条语句产生一个触发事件， 即使这个语句没有操作任何一个元组（如没有满足条件的元组）；行级粒度L：插入、删除、修改一个元组就产生一个触发事件， 并且视改变不同的元组为不同

20、的事件，则触发器可能被多次执行。7.3 触发器：冲突在一个表或视图上，针对一类事件，可以定义多个触发器。当事件发生时，DBMS需要合理地调度这些触发器。有序冲突解决方案：按照一定的次序依次计算触发器的前提条件， 当一个触发器的前提条件为真时，执行触发器， 然后判断下一个触发器的前提条件。分组冲突解决方案：同时计算所有触发器的前提条件， 然后调度执行所有前提条件为真的触发器。7.3 触发器：定义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 触发器：SQL SERVER的示例例7.10 在SC表上建立一个AFTER INSERT触发器，显示由哪个用户 插入了一行数据。CREATETRIGGERscInsertONSCAFTERINSERTASPRINTOneRowInsertedBy+USER_NAME()7.3 触发器：SQL S

22、ERVER的示例触发器执行时产生两种临时的特殊表：DELETED表和INSERTED表。DELETED表：存放被删除和修改的旧数据。 在执行DELETE语句时，从触发器所在的表中删除元组， 并将被删除的元组保存到DELETED表。 UPDATE语句将修改前的数据转移到DELETED表。INSERTED表：存放被插入和修改的新数据。 当执行INSERT语句时，新的记录插入到触发器所在的表和INSERTED表。 UPDATE语句将修改后的数据也复制到INSERTED表。7.3 触发器：SQL SERVER的示例例7.12 编写一个触发器，记录向Student表插入记录的用户、 时间和插入的学号（关

23、键字）。CREATETRIGGERlogStudentONStudentAFTERINSERTASDECLARErecordKeychar(7)SELECTrecordKey=SnoFROMINSERTEDINSERTINTOEXAMPLELOG(operatingTime,TableName,userName,recordKey)VALUES(getdate(), Student, user_name(), recordKey)7.4.1 JDBC：概述JDBC由若干类和接口组成，用于编写与平台和DBMS无关的代码，非常适合使用Java语言开发客户机/服务器应用程序。包：Java.sql包：

24、提供访问和处理客户机的数据源的APIJavax.sql包：提供服务器的数据源访问和处理的API类和接口：DriverManager类：用于处理驱动程序的加载。Connection接口：与数据库建立连接。Statement接口：用于执行SQL语句ResultSet接口：用于保存查询结果。7.4.1 JDBC：驱动程序管理器驱动程序管理器由java.sql.DriverManager类实现，负责管理JDBC驱动程序。DriverManager类主要跟踪已经加载的JDBC驱动程序，在数据库和驱动程序之间建立连接，也处理如登录时间限制、跟踪信息等工作。JDBC通过驱动程序提供应用系统与数据库平台的独立

25、性。驱动程序与具体的DBMS有关。7.4.2 JDBC的工作流程1、加载驱动程序Class.forName(drivername); 2、建立与数据库的连接Connection conn=DriverManager.getConnection(dbURL,userName,userPwd); 3、建立语句对象Statement st=conn.createStatement();7.4.2 执行查询executeQuery()方法建立了一个结果集。结果集有多种类型，结果集的类型决定了游标的移动方式和操作类型。结果集的类型由createStatement方法确定createStatement(i

26、ntresultSetType,intresultSetConcurrency)ResultSet rs=st.executeQuery(SELECT*FROMStudent);7.4.2 执行查询参数resultSetType指明结果集的类型：TYPE_FORWORD_ONLY：结果集的游标只能向前移动。TYPE_SCROLL_INSENSITIVE：游标向前或向后双向移动， 结果集不反映数据的最新变化。TYPE_SCROLL_SENSITIVE：游标向前或向后双向移动， 结果集反映数据的最新变化。参数resultSetConcurrency决定结果集的更新方式：CONCUR_READ_ON

27、LY：不可以修改结果集。CONCUR_UPDATABLE：可以修改结果集。7.4.2 执行查询结果集是一个游标7.4.2 执行查询ResultSet接口移动游标的方法：beforeFirst()：移动到结果集的开始位置（第一条记录之前）afterLast：移动到结果集的结束位置（最后一条记录之后）first()：移动到第一条记录last()：移动到最后一条记录next()：移动到下一条记录previous()：移动到上一条记录absolute(int row)：移动到row指定的记录，绝对位置relative(int row)：从当前记录开始，上移或下移row条记录7.4.2 执行查询Resu

28、ltSet接口读取字段的方法：ResultSet接口提供了一组get方法用于获取当前记录的字段值。使用字段号获取字段的值，字段号从1开始，自左至右增加。使用字段名可读性强，使用字段号的效率比较高。例如： 方法getString()用于读取字符型字段，方法getInt()用于读取整型字段。7.4.3 执行修改：executeUpdate Statement接口提供了executeUpdate(String sql)方法实现对表的更新操作，参数sql表示Insert、Update和Delete语句，该方法的返回值是int类型，表示受影响的记录数。 Stringsql=DELETEFROMStude

29、ntWHERESno=2000999;st.executeUpdate(sql);7.4.2 执行修改：executeUpdate 如果需要使用事务：Conne.setAutoCommit(false);Stringsql=DELETEFROMStudentWHERESno=2000999;st.executeUpdate(sql);mit(); / 或conn.rollback(); 7.4.2 执行修改：结果集 更新操作update方法组：更新结果集当前记录的指定字段的值，例如updateInt、 updateString等，updateRow()：向数据库提交更新操作，包含了commit

30、的功能cancelUpdateRow()：撤销向数据库所做的所有更新操作。删除操作deleteRow()：将当前记录从数据库删除7.4.2 执行修改：结果集 插入操作moveToInsertRow()：将游标移动到插入行，update方法组：对插入行的字段赋值。insertRow()：将插入行的数据写入数据库7.4.2 执行修改：结果集 例7.14 向Student表插入一名学生的信息。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;/连接数据库privateStatementstement=null;/查询语句privateStatementupdat

32、e=null;/更新语句privateResultSetrst=null;/使用游标处理查询结果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_