北大青鸟SQL Server 2005教案第7章SQL Server 2005的开发与实务

1、第第7章章 SQL Server 2005的开发与实务的开发与实务本章要点：本章要点：本章主要介绍SQL Server 2005开发的基础知识，T-SQL语言流程控制语句，使用函数、运算符、存储过程、批、事务和锁、使用游标等内容。7.1 基础知识基础知识 7.2 流程控制语句流程控制语句 7.3 使用函数使用函数 7.4 运算符运算符 第第7章章 SQL Server 2005的开发与实务的开发与实务第第7章章 SQL Server 2005的开发与实务的开发与实务7.5 用户自定义函数用户自定义函数7.6 存储过程存储过程7.7 批、事务和锁批、事务和锁 7.8 使用游标使用游标 7.1 基

2、础知识基础知识 7.1.1 Transact-SQL语句的语法格式约定 7.1.2 使用批处理 7.1.3 检查执行结果 7.1.1 Transact-SQL语句的语法格式约定（1）大写字母：代表Transact-SQL保留的关键字（2）小写字母：表示表达式、标识符等。 （3）大括号“”：大括号中的内容为必选参数 。（4）方括号“ ”：它所列出的项为可选项（5）竖线“|”竖线表示参数之间是“或”的关系 。（6）省略号“”：表示重复前面的语法项目。 7.1.2 使用批处理 批处理是由一个或多个Transact_SQL语句组成的，应用程序将这些语句作为一个单元提交给SQL Server，并由SQL

3、 Server编译成一个执行计划，然后作为一个整体来执行 。7.1.2 使用批处理在建立批处理时应该注意下面几点： （1）不能在修改表中的一个字段后，立即在同一个批处理中引用这个新的字段。 （2）不能在删除一个对象之后，又在同一个批处理中引用该对象。 （3）不能在定义一个CHECK约束后，立即在同一个批处理中使用该约束。（4）使用SET语句设置的某些SET选项不能应用于同一个批处理中的查询。 7.1.2 使用批处理（5）如果一个批处理中的第一个语句是执行某些存储过程的EXECUTE语句，则关键字EXECUTE语句可以省略不写，否则必须使用EXECUTE关键字 。（6）CREATE DEFAUL

4、T、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER、CREATE VIEW语句，在一个批处理中只能提交一个。7.1.3 检查执行结果 可以调用dbresults函数检查批命令是否返回执行结果，dbresults函数的语法格式： RETCODE dbresults（ PDBPROCESS dbproc ） 7.1.3 检查执行结果该函数的返回值：（1）SUCCESS：函数执行结果成功。 （2）NO_MORE_RESULT：批命令的执行结果已经被处理完。 （3）NO_MORE RPC_RESULTS：批存储过程的执行结果已经被处理完。（4）FAIL：函数执行

5、失败。 7.2 流程控制语句 7.2.1 BEGINEND语句 7.2.2 IFELSE语句 7.2.3 GOTO语句 7.2.4 WHILE、BREAK、CONTINUE语句7.2.5 WAITFOR语句 7.2.6 RETURN语句 7.2.7 CASE表达式 流程控制语句用于控制Transact-SQL语句、语句块或存储过程的执行流程。 7.2.1 BEGINEND语句 BEGINEND语句将多条Transact-SQL语句封装起来，用来定义一个语句块，使这些对象作为一个整体执行。 BEGINEND语句的语法结构： BEGINSQL语句1SQL语句2END 例： USE testGOIF

6、 EXISTS(SELECT * FROM Products WHERE ProductID=A001) BEGINDELETE Products WHERE ProductID=A001PRINT ProductIDA001已被删除 ENDELSEPRINTID不存在7.2.2 IFELSE语句 利用IFELSE语句能够对一个条件进行测试，并根据测试的结构来执行相应的操作，IFELSE语句的语法结构： IF 布尔表达式 SQL语句块ELSE SQL语句块例： DECLARE name ncharSET name = 002 IF EXISTS (SELECT * FROM Products

7、WHERE ProductID = name) BEGIN DELETE Products WHERE ProductID = A002 PRINT ID002 is deleted END ELSE BEGIN PRINT PRINT ID not findEND7.2.3 GOTO语句 GOTO语句的作用是将程序的流程控制无条件的转移到用户指定的标号处。使用GOTO语句时，首先定义一个标签，语法格式： 标签： 然后使用GOTO语句转移到所定义的标签处，其语法格式为： GOTO标签 例：求10的阶乘 DECLARE r int,times intSELECT r=1,times=1label

8、1: SELECT r=r *times SELECT times=times+1 IF times= 0 BEGIN SET k = k + 2 IF k 100 BEGIN SELECT 1到100之间的偶数和 = sum BREAK END IF(k%2)!= 0 CONTINUE ELSE SET sum = sum + k END7.2.5 WAITFOR语句 WAITFOR语句指定一个时刻或延缓一段时间来执行一个Transact-SQL语句、语句块存储过程，其语法结构： WAITFOR DELAY 时间| TIME 时间例：设置在8：30 BEGIN WAITFOR TIME 8：

9、30：00 SELECT * FROM OrdersEND7.2.6 RETURN语句 RETURN语句使程序从查询或存储过程中无条件返回，其后面的语句不再执行。RETURN语句的语法格式： RETURN 整数表达式 例：存储过程检查合同是否有效 CREATE PROCEDURE check_contact para varchar（60）AS IF（SELECT contract FROM author WHERE au_lname = name）= 1 RETURN 1 ELSE RETURN - 1007.2.7 CASE表达式表达式CASE表达式用于多条件分支选择 RETURN语句的语

10、法格式： （1）简单的CASE表达式：CASE 表达式 WHEN 表达式 THEN 表达式 ELSE 表达式END（2）搜索型CASE表达式。CASE WHEN 布尔表达式 THEN 表达式 ELSE表达式END例：在CASE表达式中查询作者签约情况 SELECT name=convert（varchar（15）,au_name）, contract= CASE WHEN contract=0 THEN invalid contract WHEN contract0 THEN valid contract end FROM authors 7.3内部函数 7.3.1 系统函数 7.3.2 日期

11、函数 7.3.3 字符串函数 7.3.4 数学函数7.3.5 集合函数 7.3.6 行集函数 7.3.1 系统函数系统函数对SQL Server服务器和数据库对象进行操作，并返回服务器配置和数据库对象数值等信息，它为用户提供一种便捷的系统表检索手段，系统函数可用于选择列表、WHERE子句以及其他允许使用表达式的地方 。7.3.2 日期函数 日期函数用于处理datetime和smalldatetime类型的数据 7.3.3 字符串函数 字符串函数实现字符之间的转换、查找、截取等操作。 7.3.4 数学函数 数学函数实现三角运算、指数运算、对数运算等数学运算。 7.3.5 集合函数 7.3.6 行

12、集函数 行集函数返回对象，该对象可在Transact-SQL语句中用作表引用。所有行集函数都不具有确定性。 Transact-SQL编程语言提供了CONTAINSTABLE、FREETEXTTABLE、OPENDATASOURCE、OPENQUERY、OPENROWSET和OPENXML等行集函数。7.4 运算符运算符 7.4.1 算术运算符 7.4.2 赋值运算符 7.4.3 位运算符 7.4.4 比较运算符7.4 运算符运算符7.4.5 逻辑运算符 7.4.6 字符串串联运算符 7.4.7 一元运算符 7.4.8 运算符的优先顺序 7.4.1 算术运算符 7.4.2 赋值运算符 Trans

13、act-SQL 有一个赋值运算符，即等号（=）。也可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系 7.4.3 位运算符 7.4.4 比较运算符 7.4.5 逻辑运算符 7.4.6 字符串串联运算符 字符串串联运算符允许通过加号（+）进行字符串串联，这个加号也被称为字符串串联运算符。其他所有的字符串操作都可以通过字符串函数进行处理。 7.4.7 一元运算符 一元运算符只对一个表达式执行操作，这个表达式可以是数字数据类型分类中的任何一种数据类型 。7.4.8 运算符的优先顺序 运算符有下面这些优先等级：（1）+（正）、-（负）、（按位 NOT）。（2）*（乘）、/（除）、%（模）。 （

14、3）+（加）、（+ 串联）、-（减）。 （4）=、=、=、!=、!、!比较运算符。 （5）（位异或）、&（位与）、|（位或）。 7.4.8 运算符的优先顺序（6）NOT。 （7）AND。 （8）ALL、ANY、BETWEEN、IN、LIKE、OR、SOME。 （9）=（赋值）。 7.5 用户自定义函数 7.5.1 创建数量型用户自定义函数 7.5.2 创建直接表值用户自定义函数 7.5.3 创建多语句表值用户自定义函数7.5.4 使用企业管理器创建用户自定义函数 7.5.5 用户自定义函数的查看、修改与删除 7.5.1 创建数量型用户自定义函数语法格式： 例：在Test数据库中，创建名

15、为OrderMoney的函数，用于计算一个订单的金额。USE TestGOCREATE FUNCTION OrderMoney(orderID int)RETURNS moneyASBEGIN DECLARE money money SET money=(SELECT SUM(UnitPrice*(1-Discount)*Quantity ) FROM Order Details WHERE OrderID=orderID GROUP BY OrderID) RETURN moneyEND7.5.2 创建直接表值用户自定义函数 语法格式：参数说明：（1）TABLE：指定返回值为一个表。（2）s

16、elect-statement：单个SELECT语句确定返回的表的数据。例：创建一个名为“ProductInfo”的函数用于返回产品表中属于同一个分类的产品的部分信息。USE TestUSE TestGOCREATE FUNCTION ProductInfo(categoryID int)RETURNS TABLEASRETURN (SELECT ProductID,CategoryID,Unitprice FROM Products WHERE CategoryID=categoryID)7.5.3 创建多语句表值用户自定义函数 语法格式：参数说明：return_variable：一个TAB

17、LE类型的变量用于存储和累积返回的表中的数据行。其余参数与数量型用户自定义函数相同。例：在Test数据库中创建一个多语句表值自定义函数，它可以返回Employees表的LastName或LastName与FirstName的组合，这个取决于用户提供的参数。 USE TestGOCREATE FUNCTION f_employees(len nvarchar(10)RETURNS f_employees TABLE (employeeID INT PRIMARY KEY NOT NULL, employeeName NVARCHAR(61) NOT NULL)ASBEGIN IF len=sho

18、rname INSERT INTO f_employees SELECT EmployeeID,LastName FROM Employees ELSE IF len=longname INSERT INTO f_employees SELECT EmployeeID,LastName+ +FirstName FROM Employees RETURNEND7.5.4 使用企业管理器创建用户自定义函数 7.5.5 用户自定义函数的查看、修改与删除 1. 查看与修改自定义函数查看与修改自定义函数2. 删除自定义函数删除自定义函数 7.6 存储过程 7.6.1 使用向导创建存储过程 7.6.2 使

19、用使用CREATE PROCEDURE语语句建立存储过程句建立存储过程7.6.3 创建临时存储过程创建临时存储过程7.6.4 存储过程调用存储过程调用7.6.5 删除存储过程删除存储过程7.6.1 使用向导创建存储过程7.6.2 使用使用CREATE PROCEDURE语句建语句建立存储过程立存储过程使用CREATE PROCEDURE语句建立存储过程，其语法格式：CREATE PROCEDURE procedure_name ;numberparameter data_typeVARYING =default OUTPUT ,.nWITH RECOMPILE|ENCRYPTION|RECOM

20、PILE,ENCRYPTIONFOR REPLICATIONASSql_statement,.n例：语句在Test数据库中建立一个名为“st_scr”的存储过程，用于检索CoustomerID=CO10的订单信息，代码如下：IF EXISTS(SELECT name FROM sysobjects/*如果存储过程已经存在，则将其删除*/ WHERE name=st_scr and type=p ) DROP PROCEDURE st_scrGOCREATE PROCEDURE st_scr AS /*建立存储过程*/ SELECT * FROM Orders WHERE CustomerID=

21、C010GOEXECUTE st_scr/*执行存储过程*/GO7.6.3 创建临时存储过程创建临时存储过程临时存储过程只在本次连接中有效，其语法格式为：CREATE PROCEDURE # # procedure_name ;numberparameter data_typeVARYING =default OUTPUT ,.nWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONASSql_statement,.n例：语句在Test数据库中建立一个名为“st_scr”的存储过程，用于检索CoustomerID=ANTON的

22、订单信息，代码如下：IF EXISTS(SELECT name FROM sysobjects/*如果存储过程已经存在，则将其删除*/ WHERE name=st_scr and type=p ) DROP PROCEDURE #st_scrGOCREATE PROCEDURE #st_scr AS /*建立存储过程*/ SELECT * FROM Orders WHERE CustomerID=ANTONGO7.6.4 存储过程调用 其语法格式为：EXECUTEreturn_status=procedure_name ;number|procedure_name_varparameter=

23、value|variable OUTPUT|DEFAULT,.nWITH RECOMPILE 例：存储过程的应用 IF EXISTS (SELECT name FROM sysobjects WHERE name=strconnect AND type=p) DROP PROCEDURE strconnectGOCREATE PROC strconnecta VARCHAR(10),b VARCHAR(10),result VARCHAR(20) OUTPUTASSET result=a+b7.6.6 删除存储过程 1. 使用使用SQL Server management studio向导删除

24、存储过程向导删除存储过程2. 使用使用DROP PROCEDURE语句删除语句删除存储过程存储过程使用使用DROP PROCEDURE语句可以删除存语句可以删除存储过程，其语法格式如下：储过程，其语法格式如下：DROP PROCEDURE 存储过程名称存储过程名称7.7 批、事务和锁 7.7.1 批 7.7.2 事务 7.7.3 锁 7.7.1 批 批是一组SQL语句集合，一个批以批结束语句Go而结束。 建立批时应该注意下面事项： （1）CREATE VIEW、CREATE RULE、CREATE DEFAULT、CREATE TRIGGER、CREATE PROCEDURE语句应该在批中单独

25、提交，它们不能与其他Transact-SQL语句一起构成一个批。 7.7.1 批（2）EXEC语句为批中第一个语句时，该关键字可以省略，否则必须使用关键字。 （3）使用ALTER TABLE语句修改表结构后，不能在同一个批中参照新定义列。 7.7.2 事务 在SQL Server系统中有下面三种事务模式：（1）自动提交事务（2）显式事务 （3）隐性事务 事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性，称为 ACID（原子性、一致性、隔离性和持久性）属性。 7.7.2 事务1. 启动事务启动事务 在SQL Server 中，可以按显式、自动提交或隐性模式启动事务。 2. 结束事务结束事务 可以使用 COMMIT 或 ROLLBACK 语句结束事务。 7.7.3 锁 SQL Server 使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式：1. 共享锁 2. 更新锁 3. 排它锁 4. 意向锁5. 架构锁 6. 大容量更新锁 7. 死锁 7.8 使用游标 7.8.1 游标类别 7.8.2 游标声明 7.8.3 游标函数 7.8.4 关闭7