动态交叉表就是列表可以根据表中数据的情况动态创建列。
动态查询不能使用Select语句实现,它可以利用存储过程实现。思路是:
首先检索列头信息,形成一个游标,然后遍历游标,将上面静态交叉表实现过程中使用Case语句判断的内容用游标里的值替代,形成一条新的Sql查询语句,然后执行并返回结果。下面是一个通用的实现动态交叉表的存储过程:
|
SQL Server查询分析器上执行下面的代码,就可以得到动态交叉表。
proc_across_table '商品销售表', '订单号', '商品名', '订货数量', 'SUM'
在SQL Server查询分析器上调用存储过程时,最前面的为将要调用的存储过程的名称,后面为执行存储过程需要的参数,参数用引号括起,存储过程名称与参数之间用空格分隔,参数之间用逗号分隔。
说明:这是一个通用的存储过程,只要正确的传入生成交叉表依据的表名(@TableName)、生成表头依据的字段名(@NewColumn)、生成主键列依据的字段名(@GroupColumn)、欲统计的字段名(@StatColumn)和统计的运算方式(@Operator),就可以成功的将其应用到任何数据表中。
实现动态交叉表时用到了游标,下面对游标进行详细介绍。
每个游标有4个组成部分,这4个组成部分必须符合下面的顺序:
(1)声明游标;
(2)打开游标;
(3)从一个游标中查找信息;
(4)关闭游标。
其中,声明游标主要使用DECLARE CURSOR语句,下面介绍该语句。
语法:
|
- cursor_name
- INSENSITIVE:定义一个游标,以创建将由该游标使用的数据的临时副本。对游标的所有请求都从tempdb中的临时表中得到应答;因此,对游标进行提取操作时,返回的数据中不反映对基表所做的修改,并且游标不允许修改。使用SQL-92语法时,如果省略INSENSITIVE,(任何用户)对基表提交的删除和更新都反映在后面的提取中。
- SCROLL:指定所有的提取选项(FIRST、LAST、PRIOR、NEXT、RELATIVE和ABSOLUTE)均可用。如果未指定SCROLL,则NEXT是惟一支持的提取选项。
- select_statement:定义游标结果集的标准SELECT 语句。在游标声明的select_statement内不允许使用关键字COMPUTE、COMPUTE BY、FOR BROWSE和INTO。
- READ ONLY:表明不允许游标内的数据被更新,尽管在默认状态下游标是允许更新的。在UPDATE或DELETE语句的WHERE CURRENT OF子句中不允许引用游标。
- UPDATE [OF column_name [,...n]]:定义游标内可更新的列。如果指定OF column_name [,...n] 参数,则只允许修改所列出的列。如果在UPDATE中未指定列,则可以更新所有列。
上面介绍的是SQL-92的游标语法规则,下面介绍SQL Server提供的扩展了的游标声明语法,它通过增加另外的保留字,使游标的功能进一步得到增强,其语法为:
|
- cursor_name
- LOCAL:指定对于在其中创建的批处理、存储过程或触发器来说,该游标的作用域是局部的,游标名称仅在这个作用域内有效。在批处理、存储过程、触发器或存储过程OUTPUT参数中,游标可由局部游标变量引用。OUTPUT参数用于将局部游标传递回调用批处理、存储过程或触发器,它们可在存储过程终止后给游标变量分配参数使其引用游标。除非OUTPUT参数将游标传递回来,否则游标将在批处理、存储过程或触发器终止时隐式释放。如果OUTPUT参数将游标传递回来,则游标在最后引用它的变量释放或离开作用域时释放。
- GLOBAL:指定游标的作用域对连接来说是全局的。在由连接执行的任何存储过程或批处理中,都可以引用该游标名称,该游标仅在断开连接时隐式释放。
- FORWARD_ONLY:指定游标只能从第一行滚动到最后一行。FETCH NEXT是惟一受支持的提取选项。如果在指定FORWARD_ONLY时不指定STATIC、KEYSET和DYNAMIC关键字,则游标作为DYNAMIC游标进行操作。如果FORWARD_ONLY和SCROLL均未指定,则除非指定STATIC、KEYSET或DYNAMIC关键字,否则默认为FORWARD_ONLY。STATIC、KEYSET和DYNAMIC游标默认为SCROLL。与ODBC和ADO这类数据库API不同,STATIC、KEYSET和DYNAMIC Transact-SQL游标支持FORWARD_ONLY。
- STATIC:定义一个游标,以创建将由该游标使用的数据的临时副本。对游标的所有请求都从tempdb中的临时表中得到应答;因此,在对游标进行提取操作时,返回的数据中不反映对基表所做的修改,并且游标不允许修改。
- KEYSET:指定当游标打开时,游标中行的成员身份和顺序已经固定。对行进行惟一标识的键集内置在tempdb内一个称为keyset的表中。
对基表中的非键值所做的更改(由游标所有者更改或由其他用户提交)可以在用户滚动游标时看到。其他用户执行的插入是不可见的(不能通过Transact-SQL服务器游标执行插入)。如果删除行,则在尝试提取行时返回值为-2的@@FETCH_STATUS。从游标以外更新键值类似于删除旧行然后再插入新行。具有新值的行是不可见的,并在尝试提取具有旧值的行时,将返回值为-2的@@FETCH_STATUS。如果通过指定WHERE CURRENT OF子句利用游标来完成更新,则新值是可见的。
- DYNAMIC:定义一个游标,以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。动态游标不支持ABSOLUTE提取选项。
- FAST_FORWARD:指定启用了性能优化的FORWARD_ONLY、READ_ONLY游标。如果指定了SCROLL或FOR_UPDATE,则不能指定FAST_FORWARD。
- READ_ONLY:禁止通过该游标进行更新。在UPDATE或DELETE语句的WHERE CURRENT OF子句中不能引用游标。
- SCROLL_LOCKS:指定通过游标进行的定位更新或删除保证会成功。将行读取到游标中以确保它们对随后的修改可用时,SQL Server将锁定这些行。如果指定了FAST_FORWARD,则不能指定SCROLL_LOCKS。
- OPTIMISTIC:指定如果行自从被读入游标以来已得到更新,则通过游标进行的定位更新或定位删除不会成功。当将行读入游标时SQL Server不会锁定行。相反,SQL Server使用timestamp列值的比较,或者如果表没有timestamp列,则使用校验和值,以确定将行读入游标后是否已修改该行。如果已修改该行,则尝试进行的定位更新或删除将失败。如果指定了FAST_FORWARD,则不能指定OPTIMISTIC。
- TYPE_WARNING:指定如果游标从所请求的类型隐式转换为另一种类型,则向客户端发送警告消息。
- select_statement:定义游标结果集的标准SELECT语句。在游标声明的select_statement内不允许使用关键字COMPUTE、COMPUTE BY、FOR BROWSE和INTO。
如果select_statement中的子句与所请求的游标类型的功能有冲突,则SQL Server会将游标隐式转换为其他类型。
- FOR UPDATE [OF column_name [,...n]]:定义游标中可更新的列。如果提供了OF column_name [,...n],则只允许修改列出的列。如果指定了UPDATE,但未指定列的列表,则除非指定了READ_ONLY并发选项,否则可以更新所有的列。
注意:在SQL Server 2000中,FAST_FORWARD和FORWARD_ONLY游标选项是相互排斥的。如果指定了其中的一个,则不能指定另一个,否则会引发错误。在SQL Server 2005中,这两个关键字可以用在同一个DECLARE CURSOR语句中。注意:如果查询引用了至少一个无惟一索引的表,则键集游标将转换为静态游标。注意:如果GLOBAL和LOCAL参数都未指定,则默认值由default to local cursor数据库选项的设置控制。在SQL Server 7.0版中,该选项默认为FALSE,以便与SQL Server的早期版本相匹配,在早期版本中,所有游标都是全局的,该选项的默认值在以后的SQL Server版本中可能会更改。
:定义的Transact-SQL服务器游标的名称。cursor_name必须符合标识符规则。
:用于指定游标的名称。