数据库系统13--SQL Server 编程.ppt

1、第 13 讲 SQL Server 编程,一、游标 1 游标概述 2 创建和使用游标 3 定位、更新和删除游标数据 二、自定义函数 1 自定义函数概述 2 使用和管理自定义函数,一、游标 1 游标概述,在数据库应用程序的开发过程中，常常会遇 到这种情况：即要对某一结果集中的数据逐行地 进行操作，而一般的 SQL 语句往往同时对某个 集合中的所有数据行操作，不能满足上述的要求. 因此必须要借助 SQL Server 所提供的“游标”来 进行面向单条记录的数据处理，它为我们提供了 一种优秀的解决方案.,当写一个应用程序时，基于数据集的操作 会有较好的灵活性. 例如，要将 pubs 数据库中的所有居

2、住在 Menlo Park 的 authors 的 ZIP 代码改变为 94024，则可以用下面的更新语句来进行. UPDATE authors SET ZIP=94024 WHERE city=Menlo Park 要在数据集上进行各种不同的操作，可以 用两种方法：一是在数据集上进行多个操作； 二是首先得到整个数据集，然后基于它们的值 进行所需要的操作，即基于游标的操作.,游标操作的最大好处就是可以在一个数据 集中，对某个独立于该集的其他数据行进行条 件操作，即你可以在单行数据上提交 SQL 命 令. 这种处理在复杂的应用程序中有如下好处: 提高性能：因为基于数据集的操作比基 于游标的操作需

3、要更多的系统资源. 更好的事物控制能力：当处理数据时， 可以控制任何独立于其他行的给定行. 提高效率：在一个大的数据集上进行很 多操作时（如调用存储过程）， 则数据库逐行 处理数据的效率高于在整个数据集上逐个操作 每个任务, 因为数据是保留在内存缓冲区中的.,游标提供了一种对从表中检索出的数据进 行操作的灵活手段，就其本质而言，游标实际 上就是一个指针，它能识别一个集合内的指定 工作行. 游标总是与一条 SQL 语句相关联的， 这是因为它是由结果集和结果集中指向特定记 录的游标位置所组成的. 如果要对一个结果集 进行处理，那么必须要首先声明一个指向该结 果集的游标.,游标允许应用程序对查询语句

4、 SELECT 返回的行结果中的每一行进行相同或不同的操 作，而不是一次对整个结果集进行同一种操作; 它还提供了针对游标位置而对表中的数据进行 删除和更新的能力；而且，它还把作为面向集 合的数据库管理系统和面向行的程序设计两者 联系起来，使两个数据处理方式得以沟通.,使用 SQL Server 游标通常有下面的 5 种状态. DECLARE：对于用于查询的游标， SQL Server 首先要使它有效，即在共享的存 储器中创建一个游标定义的结构. OPEN：提取游标标识的. FETCH：从游标返回数据，这时可以 进行任何需要的操作. CLOSE：关闭先前打开的游标，释放 游标结果占据的锁. DE

5、ALLOCATE：释放 DECLARE 语 句所使用的共享存储器, 而不再允许其他过程 打开这个游标.,2 创建和使用游标,如果要在应用程序中使用游标，需要进行 一下步骤. （1）声明游标. （2）打开游标，提取数据. （3）在游标的当前行进行各种操作（插 入、更新、删除）. （4）关闭、释放游标., 声明和打开游标 例： 声明一个只读游标从 pubs 数据库的 employee 表中读取数据. DECLARE cur_employee_readonly CURSOR FOR SELECT emp_id, lname, job_id, pub_id FROM employee ORDER BY

6、 emp_id FOR READ ONLY,例： 声明一个游标从 pubs 数据库的 employee 表中提取数据. DECLARE cur_employee_scroll SCROLL CURSOR FOR SELECT emp_id, lname, job_id, pub_id FROM employee ORDER BY emp_id 有了 SCROLL 关键字， 就可以在应用中 对行位置进行定位.,例： 声明一个更新游标从 pubs 数据库的 employee 表中读取数据. DECLARE cur_employee_update SCROLL CURSOR FOR SELECT e

7、mp_id, lname, job_id, pub_id FROM employee ORDER BY emp_id FOR UPDATE OPEN cur_employee_update,例： 创建一个游标选择工作号在1和10 之 间的雇员的姓名并提取游标. DECLARE cur_emp_name SCROLL CURSOR FOR SELECT LastName, FirstName, EmployeeID FROM employees WHERE EmployeeID BETWEEN 1 AND 10 /* 打开游标 */ OPEN cur_emp_name /* 提取游标 */ FE

8、TCH FROM cur_emp_name,对于游标的提取，可以采用一些关键字来 确定所需要的行. NEXT：返回结果集中当前行的下一行. PRIOR：若使用了关键字 SCROLL ， 则返回结果集中当前行的前一行. FIRST、LAST: 返回结果集中首、末行. ABSOLUTE n/nvar：返回结果集中 的第 n 行或 nvar 行数据. RELATIVE n/nvar：读取游标相对于 当前位置起向前或向后的第 n 行或 nvar 行 数据., 关闭游标 关闭游标就是释放 SQL Server 在游标打 开时需要的资源或锁，但不释放游标所占用的 数据结构. 如果准备在随后的使用中再次打开

9、 游标，则使用 CLOSE 语句来关闭游标，其语 法规则如下： CLOSE GLOBAL cursor_name | cursor_variable_name 例：关闭前面使用的游标. CLOSE cur_emp_name CLOSE 与 OPEN 相对应., 释放游标 释放游标即释放掉游标所占用的数据结构 和所有的系统资源. 可以用 DEALLOCATE 语 句来释放游标. 例：释放前面使用的游标. DEALLOCATE cur_emp_name DEALLOCATE 与 DECLARE 相对应.,在有些情况下，要修改游标中的数据，即进 行定位更新或删除游标所包含的数据. 一般在 WHERE

10、 子句中重新给出给定条件才能修改或删 除该行数据，但是如果在声明游标时使用了 FOR UPDATE 语句, 则可以在 UPDATE 或 DELETE 语句中以 WHERE CURRENT OF 关 键字直接修改或删除当前游标中存储的数据，而 不必使用 WHERE 子句重新给出指定条件. 当改 变游标中的数据时，这种变化将会自动的影响到 基本表.,3 定位、更新和删除游标数据,例： 利用前面声明的游标为例演示. DECLARE cur_employee_update SCROLL CURSOR FOR SELECT LastName, FirstName, EmployeeID FROM emp

11、loyees ORDER BY EmployeeID FOR UPDATE OPEN cur_employee_update FETCH FROM cur_employee_update 演示,例：更新表中的 LastName 为 “CruzUpdate”. UPDATE employees SET LastName= CruzUpdate WHERE CURRENT OF cur_employee_update CLOSE cur_employee_update 再执行语句： OPEN cur_employee_update FETCH FIRST FROM cur_employee_upd

12、ate 演示,注意： 如果在游标结果集中任何一行数据的大 小超过 SQL Server 定义的最大行尺寸，则执 行 OPEN 命令失败. 定位更新或删除游标所包含的数据操作 总是基于游标的当前位置. 游标最适合完成多种基于行的复杂处理 过程. 但这样的处理过程实际上并不多. 使用 基于集合的方法也可以完成同样的工作，而且 将会显著地提高性能和可伸缩性. 作为数据库 开发人员，应忘掉过程化编程的方法， 学会从 集合的角度来解决问题.,SQL Server2000 中引入了用户定义函数， 它同时兼具视图和存储过程的优点，但却牺牲了 可移植性. 函数可以用在表达式或 select 语句的from子

13、句中，也可以将它们与架构绑定. 另外，函数可 以接受参数. 函数与存储过程一样，也是预先经过编译和 优化的.,二、自定义函数 1 自定义函数概述,对于使用用户定义函数进行开发有一些争议, 最主要的就是它的可移植性. 用户定义函数不属 于 ANSI SQL 92 标准， 虽然已将它们包含在 ANSI SQL 99 内了，但还没有为整个行业所接 纳. 因此，如果将来必须要把数据库移植到另外 一个数据库平台上，就不得不将所有的用户定义 函数都改写为视图或者存储过程. 有 3 种不同类型的用户定义函数： 返回单值的标量函数；类似于视图的可更新 内嵌表函数；使用代码创建结果集的多语句表函 数.,例：下面

14、定义的标量函数完成一个简单的 数学计算，它的第二个参数具有默认值. CREATE FUNCTION dbo.Multiply( A int, B int=3) RETURNS INT AS BEGIN RETURN A*B END GO SELECT dbo.Multiply(3, 4) 演示,2 使用和管理自定义函数,例：创建图书按年限调价的函数. CREATE FUNCTION fun_price_year( StartYear datatime, EndYear datatime, rate money) RETURNS money AS BEGIN RETURN(YEAR(EndYear)- YEAR(StartYear)* rate ) END GO SELECT dbo. fun_price_year(1994-4-1, Getdate(), 2) AS price_year 演示,例：创建返回所有作者名、图书名的函数. CREATE FUNCTION fun_titles_author() RETURNS table AS RETURN(SELECT au_lname, au_fname, title FROM titles T,titleauthor TA,aut