游标存储过程索引.docx

游标存储过程索引.docx

《游标存储过程索引.docx》由会员分享，可在线阅读，更多相关《游标存储过程索引.docx（11页珍藏版）》请在冰豆网上搜索。

游标存储过程索引

1.游标

1）为何使用游标：

使用游标的一个主要原因就是把集合操作转换成单个记录处理方式。

用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。

游标机制允许用户在SQLserver内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。

2）如何使用游标：

一般地，使用游标都遵循下列的常规步骤：

（1）声明游标。

把游标与T-SQL语句的结果集联系起来。

（2）打开游标。

（3）使用游标操作数据。

（4）关闭游标。

2.1声明游标

DECLARECURSOR语句SQL-92标准语法格式：

DECLARE游标名[INSENSITIVE][SCROLL]CURSOR

FORsql-statement

Eg:

DeclareMycrsrVarCursor

Select*FROMtbMyData

2.2打开游标

OPENMycrsrVar

当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行。

就本例而言，可以使用下列操作读取第1行数据：

FETCHFIRSTfromE1cursor

或FETCHNEXTfromE1cursor

2.3使用游标操作数据

下面的示例用@@FETCH_STATUS控制在一个WHILE循环中的游标活动

/*使用游标读取数据的操作如下。

*/

DECLAREE1cursorcursor/*声明游标，默认为FORWARD_ONLY游标*/

FORSELECT*FROMc_example

OPENE1cursor/*打开游标*/

FETCHNEXTfromE1cursor/*读取第1行数据*/

WHILE@@FETCH_STATUS=0/*用WHILE循环控制游标活动*/

BEGIN

FETCHNEXTfromE1cursor/*在循环体内将读取其余行数据*/

END

CLOSEE1cursor/*关闭游标*/

DEALLOCATEE1cursor/*删除游标*/

2.4关闭游标

使用CLOSE语句关闭游标

CLOSE{{[GLOBAL]游标名}|游标变量名}

使用DEALLOCATE语句删除游标，其语法格式如下：

DEALLOCATE{{[GLOBAL]游标名}|@游标变量名

3）fetch操作的简明语法如下：

FETCH

[NEXT|PRIOR|FIRST|LAST]

FROM

{游标名|@游标变量名}[INTO@变量名[，…]]

参数说明：

NEXT取下一行的数据，并把下一行作为当前行（递增）。

由于打开游标后，行指针是指向该游标第1行之前，所以第一次执行FETCHNEXT操作将取得游标集中的第1行数据。

NEXT为默认的游标提取选项。

INTO@变量名[,…]把提取操作的列数据放到局部变量中。

列表中的各个变量从左到右与游标结果集中的相应列相关联。

各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。

变量的数目必须与游标选择列表中的列的数目一致。

--------------------------------------------------------------------------------------------------------------------------------

每执行一个FETCH操作之后，通常都要查看一下全局变量@@FETCH_STATUS中的状态值，以此判断FETCH操作是否成功。

该变量有三种状态值：

・0表示成功执行FETCH语句。

・-1表示FETCH语句失败，例如移动行指针使其超出了结果集。

・-2表示被提取的行不存在。

由于@@FETCH_STATU是全局变量，在一个连接上的所有游标都可能影响该变量的值。

因此，在执行一条FETCH语句后，必须在对另一游标执行另一FETCH语句之前测试该变量的值才能作出正确的判断。

2.存储过程

1）概念

存储过程（StoredProcedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

2）概述

在大型数据库系统中，存储过程和触发器具有很重要的作用。

无论是存储过程还是触发器，都是SQL语句和流程控制语句的集合。

就本质而言，触发器也是一种存储过程。

存储过程在运算时生成执行方式，所以，以后对其再运行时其执行速度很快。

SQLServer2005不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。

3）存储过程的种类

3.1系统存储过程

以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作

3.2本地存储过程

用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程

3.3临时存储过程

分为两种存储过程：

一是本地临时存储过程，以井字号（#）作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;

二是全局临时存储过程，以两个井字号（##）号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限

3.4远程存储过程

在SQLServer2005中，远程存储过程（RemoteStoredProcedures）是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程

3.5扩展存储过程

扩展存储过程（ExtendedStoredProcedures）是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头

4）存储过程的功能

这类语言主要提供以下功能，让用户可以设计出符合引用需求的程序：

1）、变量说明

2）、ANSI（美国国家标准化组织）兼容的SQL命令（如Select,Update….）

3）、一般流程控制命令（if…else…、while….）

4）、内部函数

5）存储过程的优点

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时（如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

3.存储过程可以重复使用,可减少数据库开发人员的工作量

4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

3.数据库并发

1）并发控制概述

并发控制是以事务（transaction）为单位进行的

并发控制的单位――事务

事务是数据库的逻辑工作单位，它是用户定义的一组操作序列。

一个事务可以是一组SQL语句、一条SQL语句或整个程序。

事务的开始和结束都可以由用户显示的控制，如果用户没有显式地定义事务，则由数据库系统按缺省规定自动划分事务。

事务应该具有4种属性：

原子性、一致性、隔离性和持久性。

（1）原子性

事务的原子性保证事务包含的一组更新操作是原子不可分的，也就是说这些操作是一个整体，对数据库而言全做或者全不做，不能部分的完成。

这一性质即使在系统崩溃之后仍能得到保证，在系统崩溃之后将进行数据库恢复，用来恢复和撤销系统崩溃处于活动状态的事务对数据库的影响，从而保证事务的原子性。

系统对磁盘上的任何实际数据的修改之前都会将修改操作信息本身的信息记录到磁盘上。

当发生崩溃时，系统能根据这些操作记录当时该事务处于何种状态，以此确定是撤销该事务所做出的所有修改操作，还是将修改的操作重新执行。

（2）一致性

一致性要求事务执行完成后，将数据库从一个一致状态转变到另一个一致状态。

它是一种以一致性规则为基础的逻辑属性，例如在转账的操作中，各账户金额必须平衡，这一条规则对于程序员而言是一个强制的规定，由此可见，一致性与原子性是密切相关的。

事务的一致性属性要求事务在并发执行的情况下事务的一致性仍然满足。

它在逻辑上不是独立的，它由事务的隔离性来表示。

（3）隔离性

隔离性意味着一个事务的执行不能被其他事务干扰。

即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

它要求即使有多个事务并发执行，看上去每个成功事务按串行调度执行一样。

这一性质的另一种称法为可串行性，也就是说系统允许的任何交错操作调度等价于一个串行调度。

串行调度的意思是每次调度一个事务，在一个事务的所有操作没有结束之前，另外的事务操作不能开始。

由于性能原因，我们需要进行交错操作的调度，但我们也希望这些交错操作的调度的效果和某一个串行调度是一致的。

DM实现该机制是通过对事务的数据访问对象加适当的锁，从而排斥其他的事务对同一数据库对象的并发操作。

（4） 持久性

系统提供的持久性保证要求一旦事务提交，那么对数据库所做的修改将是持久的，无论发生何种机器和系统故障都不应该对其有任何影响。

例如，自动柜员机（ATM）在向客户支付一笔钱时，就不用担心丢失客户的取款记录。

事务的持久性保证事务对数据库的影响是持久的，即使系统崩溃。

正如在讲原子性时所提到的那样，系统通过做记录来提供这一保证。

4.Hibernate懒加载

1）概念理解

所谓懒加载（lazy）就是延时加载，延迟加载

延迟加载：

延迟加载是一种机制，主要是解决不必要的查询对资源的浪费，只有当数据真正调用时才加载，而不会对数据进行提前加载造成资源的浪费

2）Hibernate延迟加载的看法：

延迟加载就是懒加载，使用懒加载是解决了负载问题，也就是说懒加载后不会立即去访问数据库，而是尽量延迟，延迟到用户执行数据库查询命令的时候或者使用initialize方法的时候去和数据库交互，这样就减轻了与数据库交互的压力，如果缓存中有数据的话会优先考虑缓存数据，总之是尽量避免和数据库的直接交互，但有些时候也会出现数据没有得到及时更新的错误数据，所以，在金融等方面的项目开发的时候尽量避免懒加载。

比如在统计论坛数据访问量等要求不是很严格的情况并且数据量比较大的情况下可以使用懒加载。

【load】load方法会导致懒加载出现，如果要数据及时送入数据库的话，使用save方法时，load会延迟至强制访问数据库执行，比如get方法调用的时候。

【onetoone映射】对于懒加载，一对一映射中的constrained属性设置为true的时候实现懒加载，lazy决定了什么时候进行懒加载，而fetch决定了抓取的方式。

【mangtoone映射】对于多对一映射中，默认情况下是懒加载的，这样有利于减轻系统负载，如果要取消懒加载进行查询的话，在集合端中把lazy属性设置为false就可以。

【总结】懒加载是通过代理方式实现的，代理的对象在访问的时候才会去和数据库进行交互，前提是session没有被关掉的情况下

例：

比如部门PO和员工PO，部门与员工1对多，如果lazy设置为false，那么只要加载了一个部门的po，就会根据一对多配置的关系把所有员工的po也加载出来。

但是实际上有时候只是需要用到部门的信息，不需要用到员工的信息，这时员工po的加载就等于浪费资源。

如果lazy设置为true，那么只有当你访问部门po的员工信息时候才回去加载员工的po的信息。

Hibernate3.0中lazy有三个值：

true，false，proxy，默认的是lazy="proxy"。

5.Mavenant

1.概念理解

Maven是基于项目对象模型（POM），可以通过一小段描述信息来管理项目的构建，报告和文档的软件项目管理工具

目前，绝大多数开发人员都把Ant当作Java编程项目的标准构建工具。

遗憾的是，Ant的项目管理工具（作为make的替代工具）不能满足绝大多数开发人员的需要。

通过检查Ant构建文件，很难发现项目的相关性信息和其它元信息（如开发人员/拥有者、版本或站点主页）

2.maven和ant的区别

Maven和Ant针对构建问题的两个不同方面。

Ant为Java技术开发项目提供跨平台构建任务。

Maven本身描述项目的高级方面，它从Ant借用了绝大多数构建任务。

因此，由于Maven和Ant代表两个差异很大的工具，所以我将只说明这两个工具的等同组件之间的区别，如表1所示。

6.数据库优化

1.什么是优化器

查询优化对提高查询效率是至关重要的，在任何一个商品化的RDBMS中，都必须有一个专门负责查询语句优化的程序，称为优化器；是SQL之前分析语句的工具。

优化器的优化方式：

基于规则（RBO）:

优化器遵循Oracle内部预定的规则

基于代价（CBO）:

依据语句执行的代价，主要指对CPU和内存的占用。

优化器在判断是否使用CBO时，要参照表和索引的统计信息。

统计信息要在对表做analyze后才会有。

2.优化器的优化模式

Oracle优化器的优化模式主要有四种：

1.rule：

基于规则

2.choose：

默认模式。

根据表或索引的统计信息，如果有统计信息，则使用CBO方式；如果没有统计信息，相应列有索引，则使用RBO方式

3.Firstrows：

与Choose类似。

不同的是如果表有统计信息，它将以最快的方式返回查询的前几行，以获得最佳响应时间。

4.Allrows：

即完全基于cost的模式。

当一个表有统计信息时，以最快方式返回表所有行，以获得最大吞吐量。

没有统计信息则使用RBO方式。

3.优化准则

使用ROWID的查询效率最高；

使用唯一索引的查询要快于使用唯一索引的查询

使用完整说明索引的查询快于使用部分说明索引的查询

等条件的查询快于范围条件或不等条件的查询

ISNULL条件的查询不能使用索引；

尽量避免查询中出现全表扫面

7.索引

1.索引

使用索引可快速访问数据库表中的特定信息。

索引是对数据库表中一列或多列的值进行排序的一种结构，提供指向存储在表的指定列中的数据值得指针，然后根据指定的列排序顺序对这些指针排序。

2.索引列

可以基于数据库表中的单列或多列创建索引。

多列索引可以区分其中一列可能有相同值得行。

3.索引类型

A．唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

B．主键索引

在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。

该索引要求主键中的每个值都唯一。

C．聚散索引

在聚散索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。

一个表中只能包含一个聚集索引。

与非聚集索引相比，聚集索引通常提供更快的数据访问速度。