计算机软件及应用数据库原理数据库高级开发技术

ClicktoaddTitle1游标1ClicktoaddTitle2存储过程2ClicktoaddTitle2触发器3本章内容ClicktoaddTitle1函数4ClicktoaddTitle1嵌入式SQL5ClicktoaddTitle2数据库连接6ClicktoaddTitle2

XML数据库7

在数据库中，游标是一个十分重要的概念。关系数据库管理系统实质是面向集合的，在关系数据库中并没有一种描述表中单一记录的表达形式，除非使用where子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。游标是DBMS为用户开设的一个数据缓冲区，用于存放SQL语句的执行结果。游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL选择语句（Select）相关联。因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。游标概述

每一个游标必须有四个组成部分（1）声明（DECLARE）游标；（2）打开（OPEN）游标；（3）从一个游标中逐条获取（FETCH）并处理记录信息；（4）关闭（CLOSE）或释放（DEALLOCATE）游标。游标游标操作

将RecipeDetail中的处方编号为“1284041”所包含的药品信息声明为MedicineList游标。DECLAREMedicineListCURSORFORSELECTM.Mno,D.Mamount,M.Mname,M.Mprice,M.Munit,M.MtypeFROMRecipeDetailDLEFTJOINMedicineMOND.Mno=M.MnoWHERED.Rno='1284041'游标声明游标

DECLARE@MnoVARCHAR(50);DECLARE@MnameVARCHAR(50);DECLARE@MtypeVARCHAR(50);DECLARE@MunitVARCHAR(50);DECLARE@MamountINTEGER;DECLARE@MpriceDECIMAL(8,2);DECLAREMedicineListCURSORFOR SELECTM.Mno,D.Mamount,M.Mname,M.Mprice,M.Munit,M.Mtype FROMRecipeDetailDLEFTJOINMedicineMOND.Mno=M.Mno WHERED.Rno='1284041';OPENMedicineList;FETCHNEXTFROMMedicineListINTO@Mno,@Mamount,@Mname,@Mprice,@Munit,@Mtype;WHILE(@@Fetch_Status=0) BEGIN PRINT'编号:'+@Mno+'；数量:'+LTRIM(STR(@Mamount))+‘名称:’+@Mname;

FETCHNEXTFROMMedicineListINTO@Mno,@Mamount,@Mname,@Mprice@Mtype; ENDCLOSEMedicineList;DEALLOCATEMedicineList;游标游标示例ClicktoaddTitle1游标1ClicktoaddTitle2存储过程2ClicktoaddTitle2触发器3本章内容ClicktoaddTitle1函数4ClicktoaddTitle1嵌入式SQL5ClicktoaddTitle2数据库连接6ClicktoaddTitle2

XML数据库7

存储过程是一组已被编辑在一起的，存储在服务器上的执行某种功能的预编译SQL语句。它是一种封装重复任务操作的方法，支持用户提供的参数变量，具有强大的编程能力。存储过程具有许多优点：加快程序的执行速度减少网络的数据流量提供了一种安全机制允许程序模块化设计提高编程的灵活性存储过程概述

系统存储过程。以sp_开头，用来进行系统的各项设定、取得信息和相关管理工作。本地存储过程。用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。临时存储过程。临时存储过程分为两种存储过程：一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，只有创建它的用户才能执行它；二是全局临时存储过程，以两个井字号(##)号开始，任意用户都可以执行。远程存储过程。位于远程服务器上的存储过程，使用分布式查询和EXECUTE命令执行。扩展存储过程。扩展存储过程是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。存储过程SQLServer过程类型

重命名存储过程ALTERPROCEDURE旧过程名称RENAMETO新过程名称;执行存储过程CALL/PERFORM/EXECUTEPROCEDURE过程名（[参数1，参数2,…]）;删除存储过程DROPPROCEDURE过程名（）;存储过程操作

利用存储过程计算患者支付处方中药品的总金额。CREATEPROCEDUREprocPaymentSum @RecipeNoVARCHAR(10), @PaymentSumDECIMAL(18,2)OUTPUTAS SELECT@PaymentSum=SUM(Mamount*Mprice) FROMRecipeMasterRMLEFTJOINRecipeDetailRDONRM.Rno=RD.Rno INNERJOINMedicineMONM.Mno=RD.Mno WHERERM.Rno=@RecipeNO存储过程操作

利用存储过程计算患者支付处方中药品的总金额。CREATEPROCEDUREprocPaymentSum @RecipeNoVARCHAR(10), @PaymentSumDECIMAL(18,2)OUTPUTAS SELECT@PaymentSum=SUM(Mamount*Mprice) FROMRecipeMasterRMLEFTJOINRecipeDetailRDONRM.Rno=RD.Rno INNERJOINMedicineMONM.Mno=RD.Mno WHERERM.Rno=@RecipeNO执行存储过程DECLARE@FeeSumDECIMAL(18,2);EXECUTEprocPaymentSum'1282317',@FeeSumOUTPUT;PRINT@FeeSum;存储过程操作示例

在存储过程中利用游标计算患者支付处方中药品的总金额。其中要求对药品类型为“中药”的药品按照价格的90%计算。CREATEPROCEDUREprocPaymentSumCursor @RecipeNoVARCHAR(10), @PaymentSumDECIMAL(18,2)OUTPUTASDECLARE@amountINTEGER;DECLARE@priceDECIMAL(8,2);DECLARE@mtypeVARCHAR(10);DECLAREMedicineListCURSORFORSELECTRD.Mamount,M.Mprice,M.MtypeFROMRecipeMasterRMLEFTJOINRecipeDetailRDONRM.Rno=RD.RnoINNERJOINMedicineMONM.Mno=RD.MnoWHERERM.Rno=@RecipeNO;OPENMedicineList;SET@PaymentSum=0;FETCHNEXTFROMMedicineListINTO@amount,@price,@mtype;WHILE(@@fetch_status=0) BEGINIF@mtype='中药'SET@price=@price*0.9;SET@PaymentSum=@PaymentSum+@amount*@price;FETCHNEXTFROMMedicineListINTO@amount,@price,@mtype; ENDCLOSEMedicineList;DEALLOCATEMedicineList;存储过程存储过程+游标ClicktoaddTitle1游标1ClicktoaddTitle2存储过程2ClicktoaddTitle2触发器3本章内容ClicktoaddTitle1函数4ClicktoaddTitle1嵌入式SQL5ClicktoaddTitle2数据库连接6ClicktoaddTitle2

XML数据库7

从本质上看，触发器是一种特殊的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当使用INSERT，DELETE，UPDATE命令对触发器所保护的数据进行修改时，触发器能够被自动激活，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。除了能够完成复杂的完整性约束以外，还可以在主动数据库（ActiveDatabase）的应用中对不同的外部事件做出及时反应。触发器虽然不是SQL－92标准的一部分，但已经纳入SQL：1999标准。很多数据库厂商在他们的产品中都已经包含对触发器的支持。触发器概述

触发器（Trigger）是数据库模式的一个元素。它是一个能由系统自动执行对数据库修改的语句一个触发器由3部分组成：事件。事件是指对数据库的插入、删除、修改等操作。触发器在这些事件开始发生时将开始工作。条件。触发器将测试条件是否成立。如果条件成立，就执行相应动作，否则什么也不做。动作。如果触发器测试满足预定的条件，那么就由DBMS执行这些动作，即对数据库的操作。触发器性能通常比较低。当运行触发器时，系统处理的大部分时间花费在参照其它表的这一处理上，触发器所参照的其它表的位置决定了操作要花费的时间长短。触发器概述

创建触发器CREATETRIGGER<触发器名称>{BEFORE|AFTER}<触发事件>ON<表名>FOREACH{ROW|STATEMENT}[WHEN<触发条件>]<触发动作体>删除触发器DROPTRIGGER<触发器名称>ON<表名>触发器操作

定义BEFORE行级触发器

CREATETRIGGERUPDATE_SALBEFOREINSERTORUPDATEOFSal,PosONTeacherFOREACHROWASBEGINIF(new.sal<800)AND(new.Pos=‘教授’)THENnew.Sal=800;ENDIF;END;触发器示例ClicktoaddTitle1游标1ClicktoaddTitle2存储过程2ClicktoaddTitle2触发器3本章内容ClicktoaddTitle1函数4ClicktoaddTitle1嵌入式SQL5ClicktoaddTitle2数据库连接6ClicktoaddTitle2

XML数据库7

函数是由一个或多个SQL语句组成的子程序，可用于封装代码以便重新使用。与编程语言中的函数类似，数据库中用户定义函数是接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回的例程。返回值可以是单个标量值或结果集。用户定义函数可以嵌套，嵌套级别最多可达32级。函数概述

使用用户定义函数有以下优点：（1）允许模块化程序设计。（2）只需创建一次函数并将其存储在数据库中，以后便可以在程序中调用任意次。用户定义的函数可以独立于程序源代码进行修改。（3）执行速度更快。与存储过程相似，用户定义函数通过缓存计划并在重复执行时重用它来降低SQL代码的编译开销。这意味着每次使用用户定义函数时均无需重新解析和重新优化，从而缩短了执行时间。（4）减少网络流量。基于某种无法用单一标量的表达式表示的复杂约束来过滤数据的操作，可以表示为函数。然后，此函数便可以在WHERE子句中调用，以减少发送至客户端的数字或行数。函数概述

存储过程与函数一起向用户提供了强大而灵活的编程能力。存储过程和自定义函数的区别：（1）存储过程，功能强大，可以执行包括修改表等一系列数据库操作。用户定义函数不能用于执行一组修改全局数据库状态的操作。（2）存储过程可以使用非确定函数。自定义函数不允许在用户定义函数主体中内置非确定函数。【注：确定性函数是在使用特定的输入值集调用函数的任何时候，它们总是返回相同的结果。】（3）存储过程可返回记录集。自定义函数可以返回表变量。（4）存储过程的返回值不能被直接引用。自定义函数的返回值可以被直接引用。（5）存储过程用EXECUTE语句执行。而自定义函数在查询语句中调用。函数概述

SQLServer中根据函数返回值形式分为三种类型：1.标量函数：返回在RETURNS子句中定义的单个数据值。2.内联表值型函数：内联表值型函数以表的形式返回一个返回值。其返回的表由一个位于RETURN子句中的SELECT命令段从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。用户定义表值函数返回TABLE数据类型。3.多声明表值型函数：多声明表值型函数可以看作标量型和内联表值型函数的结合体。它的返回值是一个表，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值型函数的不足。函数类型

所有用户定义函数都由两部分组成：标题和正文。函数可接受零个或多个输入参数，返回标量值或表。创建标量函数：通过处方编号计算该处方总金额。CREATEFUNCTIONfuncRecipeFee(@recipeIDVARCHAR(10))RETURNSDecimal(18,2)ASBEGIN DECLARE@recipeFeeDecimal(18,2) Select@recipeFee=sum(d.Mamount*m.Mprice)FromRecipeDetaildinnerjoinMedicinemond.Mno=d.MnoWhered.Rno=@recipeID

Return@recipeFeeEND函数操作示例

创建内联表值型函数：统计该医生为所有患者开具的药品金额和数量。CREATEFUNCTIONdbo.funcMedicineSales(@DnoVARCHAR(10))RETURNSTABLEASRETURN

(SELECTM.MnameAs'药品名称',Sum(RD.Mamount)AS'药品数量',SUM(RD.Mamount*M.Mprice)AS'药品总金额'FROMDoctorAsDINNERJOINRecipeMasterAsRMOnD.Dno=RM.DnoINNERJOINRecipeDetailAsRDOnRM.Rno=RD.RnoINNERJOINMedicineASMOnRD.Mno=M.MnoINNERJOINPatientASPOnRM.Pno=P.PnoWHERED.Dno=@DnoGROUPBYM.Mname);调用：Select*FromfuncMedicineSales('82')函数操作示例

创建多声明表值型函数：通过用户指定的部门编号，查询该部门的所有下级部门，包括该部门的子节点、子节点的子节点、……，直至某节点为叶节点为止。CREATEFUNCTIONfuncDescendantDept(@DeptNoVARCHAR(10))RETURNS@DescendantDeptTABLE(DeptNoVARCHAR(10),DeptNameVARCHAR(50),ChildDeptNoVARCHAR(10),ChildDeptNameVARCHAR(50),DeptLevelINT)ASBEGIN

WITHDescendant(DeptNo,DeptName,ChildDeptNo,ChildDeptName,DeptLevel)AS

(SELECTParent.DeptNo,Parent.DeptName,Child.DeptNo,Child.DeptName,1FROMDeptASParentLEFTJOINDeptASChildOnParent.DeptNo= Child.ParentDeptNoWHEREParent.DeptNo=@DeptNo UNIONALLSELECTP.ChildDeptNoASDeptNo,P.ChildDeptNameASDeptName,C.DeptNoASChildDeptNo,C.DeptNameASChildDeptName,P.DeptLevel+1ASDeptLevelFROMDescendantASPINNERJOINDeptASCOnP.ChildDeptNo=C.ParentDeptNo)INSERT@DescendantDeptSELECTDeptNo,DeptName,ChildDeptNo,ChildDeptName,DeptLevelFROMDescendant

RETURNEND;函数操作示例ClicktoaddTitle1游标1ClicktoaddTitle2存储过程2ClicktoaddTitle2触发器3本章内容ClicktoaddTitle1函数4ClicktoaddTitle1嵌入式SQL5ClicktoaddTitle2数据库连接6ClicktoaddTitle2

XML数据库7

SQL语言可以独立使用，称为交互式（InteractiveSQL，ISQL）。但ISQL的功能仅限于数据库上操作，缺少数据处理能力。而一个应用程序既要访问数据，又要处理数据，把SQL嵌入到程序设计语言，如C，C++，Java等，即宿主语言中，将两者的功能相结合起来，是目前解决这个问题的实现途径。这样使用的SQL称为嵌入式SQL（EmbeddedSQL，ESQL）嵌入式SQL概述

ESQL的处理过程对于嵌入式SQL，RDBMS一般采用预编译方法处理，即由RDBMS的预处理程序对源程序进行扫描，识别出ESQL语句，把它们转换成主语言调用语句，以使主语言编译程序能识别它们，然后由主语言的编译程序将纯的主语言编译成目标码嵌入式SQL概述

ESQL与主语言之间的通信数据库与宿主语言程序间信息的传递是通过共享变量实现的。这些共享变量先由宿主语言程序定义，再用SQL的DECLARE语句说明，随后SQL语句就可以引用这些变量。共享变量也就成了SQL和宿主语言之间的接口。用SQL通信区SQLCA向主语言传递SQL语句的执行状态信息在ESQL中，为了能够区分SQL语句与主语言语句，所有的SQL语句都必须加前缀标识“EXECSQL”，并以“END_EXEC”作为语句的结束标志。嵌入式SQL语句的格式如下：EXECSQL<SQL语句>END_EXEC嵌入式SQL概述10.3嵌入式SQL

SQLCA：SQLCommunicationAreaSQLCA是一个数据结构SQLCA的用途SQL语句执行后，DBMS反馈给应用程序信息描述系统当前工作状态描述运行环境这些信息将送到SQL通信区SQLCA中应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句定义SQLCA

用EXECSQLINCLUDESQLCA加以定义使用SQLCASQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE应用程序每执行完一条SQL语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理10.3嵌入式SQL

10.3嵌入式SQL

SQLCA的使用方法SQLCODE=0：SQL语句执行成功，并有满足条件的记录

=100：SQL语句处理完最后一条满足条件的记录或数据库中没有满足条件的记录

〈0：SQL语句执行出错例1：执行DELETE语句后，SQLCODE的不同返回值：

=0：成功删除若干行

=100：没有满足条件的记录

〈0：执行出错无条件删除警告信息违反数据保护规则，拒绝删除操作10.3嵌入式SQL

SQLCA的使用方法

允许嵌入的SQL语句引用宿主语言的程序变量（成为共享变量），但有两条规定如下。引用共享变量前必须加冒号“：”作为前缀标识，以区别数据库中的变量。共享变量由宿主语言的程序定义，并用SQL的DECLARE语句说明。例如在C语言程序中可以按如下形式使用共享变量：EXECSQLBEGINDECLARESECTION；

charPno[10]；

charPname[50]；

charSQL_STATE[6]；EXECSQLENDDECLARESECTION；嵌入式SQL概述例3对某个部门的医生信息，根据用户的要求修改其中某些人的年龄字段。思路查询某个部门全体医生的信息(要查询的部门名由主变量deptname指定）然后根据用户的要求修改其中某些记录的年龄字段10.3嵌入式SQL

............EXECSQLINCLUDESQLCA;EXECSQLBEGINDECLARESECTION;....../*说明主变量deptname,Dno,Dname,Dage,等*/.......EXECSQLENDDECLARESECTION;.....

10.3嵌入式SQL

......gets(deptname);/*为主变量deptname赋值*/......EXECSQLDECLARESXCURSORFORSELECTDno,Dname,DageFROMDoctorWHEREDDept=:deptname

FORUPDATEOFDage;/*说明游标*/EXECSQLOPENSX/*打开游标*/10.3嵌入式SQLWHILE(1){/*用循环结构逐条处理结果集中的记录*/EXECSQLFETCHSXINTO:Dno,:Dname,:Dage;/*将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/if(sqlca.sqlcode<>SUCCESS)break;

/*若所有查询结果均已处理完或出现SQL语句错误，则退出循环*/printf("%s,%s,%d",Dno,Dname,Dage);/*显示该记录*/10.3嵌入式SQLprintf("UPDATEAGE?");/*问用户是否要修改*/scanf("%c",&yn);if(yn='y'oryn='Y')/*需要修改*/{printf("INPUTNEWAGE:");scanf("%d",&NEWAge);/*输入新的年龄值*/

EXECSQLUPDATEStudentSETSage=:NEWAgeWHERECURRENTOFSX;/*修改当前记录的年龄字段*/};10.3嵌入式SQL

............};

EXECSQLCLOSESX;/*关闭游标*/............10.3嵌入式SQLClicktoaddTitle1游标1ClicktoaddTitle2存储过程2ClicktoaddTitle2触发器3本章内容ClicktoaddTitle1函数4ClicktoaddTitle1嵌入式SQL5ClicktoaddTitle2数据库连接6ClicktoaddTitle2

XML数据库7

传统数据库编程方式是“主语言+DML”。虽然ANSI和ISO定义了关系数据库查询语言标准SQL，但传统方式中一个特定的前端应用不能访问不同数据库服务器上的数据。各厂商的SQL版本不同，每个关系数据库管理系统（RDBMS）厂商都对标准SQL进行了独特的扩充或解释，使得不同的RDBMS提供的SQL互不兼容；不同厂商的RDBMS在客户机与数据库服务器之间用了不同的通信协议。与此同时，RDBMS产品却在迅速增加，因此有必要建立一个公共的、与数据库无关的应用程序设计接口（ApplicationProgrammingInterface，API）DB连接访问概述

ODBC（OpenDatabaseConnectivity，开放数据库互连）是微软公司开放服务结构（WOSA，WindowsOpenServicesArchitecture）中有关数据库的一个组成部分，它建立了一组规范，并提供了一组对数据库访问的标准API（应用程序编程接口）。DB连接访问ODBC应用程序应用程序应用程序公共接口API网络软件SQLServerSybaseOracleDB2MySQL应用程序

ODBC的体系结构DB连接访问ODBCSQLServer驱动程序ODBC应用程序FoxPro驱动程序SQLServer数据源Oracle数据源FoxPro数据源驱动程序管理器Oracle驱动程序…其他数据库驱动程序…其他数据源

ODBC的开发使用DB连接访问ODBCSQLAllocHandle(ENV)SQLSetEnvAttrSQLAllocHandle(DBC)SQLConnectSelectSQLSetConnectAttrSQLGetInfoSQLAllocHandle(STMT)SQLSetStndAttrSQLExecDirectORSQLPrepareSQLExecuteORUpdate/Delete/insertSQLNumResultColsSQLBindCOLSQLFetchSQLGetDataSQLCloseCursorSQLFreeHandle(STMT)SQLDiscormectSQLFreeHandle(DBC)SQLFreeHandle(ENV)执行SQL命令使用结果集释放空间ODBC初始化函数使用ODECAPI检索数据

ADO（ActiveXDataObjects，ActiveX数据对象）是Microsoft提出的应用程序接口（API）用以实现访问关系或非关系数据库中的数据。ADO从原来的Microsoft数据接口远程数据对象（RDO）而来。RDO与ODBC一起工作访问关系数据库。DB连接访问ADOSQLAllocHandle(ENV)SQLSetEnvAttrSQLAllocHandle(DBC)SQLConnectSelect

对

象说

明CommandCommand对象定义了将对数据源执行的指定命令Connection代表打开的、与数据源的连接DataControl将数据查询Recordset绑定到一个或多个控件上（如文本框、网格控件或组合框），以便在Web页上显示ADOR.Recordset数据DataFactory实现对客户端应用程序的指定数据源进行读/写数据访问的方法DataSpace创建客户端代理以便自定义位于中间层的业务对象Error包含与单个操作（涉及提供者）有关的数据访问错误的详细信息Field代表使用普通数据类型的数据的列Parameter代表与基于参数化查询或存储过程的Command对象相关联的参数或自变量Property代表由提供者定义的ADO对象的动态特性RecordSet代表来自基本表或命令执行结果的记录的全集，任何时候，Recordset对象所指的当前记录均为集合内的单个记录DB连接访问ADOSQLAllocHandle(ENV)SQLSetEnvAttrSQLAllocHandle(DBC)SQLConnectSelect

DB连接访问JDBCSQLAllocHandle(ENV)SQLSetEnvAttrSQLAllocHandle(DBC)SQLConnectSelectJDBC（JavaDataBaseConnectivity，Java数据库连接）是一种用于执行SQL语句的JavaAPI，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序，同时，JDBC也是一个商标名。将Java语言和JDBC结合起来使程序员只需写一遍程序就可以让它在任何平台上运行，这也是Java语言“编写一次，到处运行”优点的体现。

DB连接访问JDBCSQLAllocHandle(ENV)SQLSetEnvAttrSQLAllocHandle(DBC)SQLConnectSelectJDBC实际上做了3件事：建立与数据库的连接、发送SQL、处理结果。举例Connectioncon=DriverManager.getConnection("jdbc：odbc：wombat"，"login"，"password");Statementstmt=con.createStatement();ResultSetrs=stmt.executeQuery("SELECTDeptNo，DeptName，ManagerFROMDept");While(rs.next()){ StringdeptNo=rs.getString("DeptNo"); StringdeptName=rs.getString("DeptName"); Stringmanager=rs.getString("Manager"); }ClicktoaddTitle1游标1ClicktoaddTitle2存储过程2ClicktoaddTitle2触发器3本