从简单工厂到工厂方法

http://www.cnblogs.com/shenfx318/archive/2007/01/09/614844.html

从简单工厂说起

严格来讲简单工厂并不属于GOF的23个设计模式之一，但它之所以存在而被人们广泛认知的原因，我想是由于它在一定程度上简化了工厂方法(Factory Method)与抽象工厂(Abstract Factory)的模式，从而可以带给新手对于工厂模式设计的精髓最直观的认识，是不少人接触工厂模式的奠基石，理解了简单工厂的设计理念及实现方法再去理解相对复杂的工厂方法与抽象工厂有着水到渠成的效果。

先来看看简单工厂的类关系图:

简单工厂一般以如下方式实现:

 1     public class SimpleFactory
 2     {
 3         /// <summary>
 4         /// Get the right product base on the paoductcategory parameter.
 5         /// </summary>
 6         /// <param name="productCategory"></param>
 7         /// <returns></returns>
 8         public static IProduct CreateProduct(string productCategory)
 9         {
10             switch (productCategory)
11             {
12                 case "A":
13                     return new ProductA();
14                 case "B":
15                     return new ProductB();
16                 case "C":
17                     return new ProductC();
18                 default:
19                     throw new Exception("Not a valid product category!");
20                     return null;
21             }
22         }
23     }

客户对于简单工厂的使用

 1     public class Client
 2     {
 3         public void Do()
 4         {
 5             //Create a productA
 6             IProduct product = SimpleFactory.CreateProduct("A");
 7             //Product doing its tasks.
 8             product.ExecuteFunction1();
 9             product.ExecuteFunction2();
10             product.ExecuteFunction3();
11             product.ExecuteFunction4();
12             //More tasks .
13         }
14     }

这时，如果客户打算改为生产产品B， 那么他需要做的仅仅是修改传入工厂的参数，以告诉工厂需要生产什么而对于后面产品所实现的功能不需要做任何的修改。简单工厂模式的最大优点在于工场模式包 含了必要的判断逻辑，可以根据客户的需求动态生成客户所需要的产品，而在客户方面，免除了客户对具体产品创建的依赖，一句话:简单工场实现了对责任的分割。
    说完了优点再来说说缺点，如果现在新增加了一种产品称作ProductC怎么办？OK，我们添加类ProductC、实现IProduct接口（注意，这个是由于新的业务规则所带来的变化，是任何模式都无法避免的），下面就是需要让我们的简单工厂具有生产这种新产品的能力，如何实现？你自然会想到多添一条case语句不就完了？然而事情并没有那么简单，因为你在添加case语句的同时也就意味着你修改了工厂类本身—违反了设计原则中最重要的一条OCP原则。开放封闭原则要求避免直接修改类而是应当通过继承来扩展类的功能。此外过多的case语句也容易混乱逻辑，造成工厂类的稳定性下降。那么对于新产品的增加，怎样才能妥善的处理并且又不违反设计原则呢？于是—工厂方法(Factory Method)登场了。 

也谈工厂方法

         先来看看工厂方法(Factory Method)的类关系图:

        

         工厂方法一般以如下方式实现:

 1     public interface IFactory
 2     {
 3         IProduct CreateProduct();
 4     }
 5 
 6     /// <summary>
 7     /// 专门负责生产ProductA的工厂
 8     /// </summary>
 9     public class ProductAFactory : IFactory
10     {
11         public IProduct CreateProduct()
12         {
13             return new ProductA();
14         }
15     }
16 
17     /// <summary>
18     /// 专门负责生产ProductB的工厂
19     /// </summary>
20     public class ProductBFactory : IFactory
21     {
22         public IProduct CreateProduct()
23         {
24             return new ProductB();
25         }
26     }

          我们再看看对于新产品ProductC，工厂方法模式所采取的应对策略。首先仍然是要添加新的类ProductC,然后添加用于生产新产品的工厂类ProductCFactory并且实现IFactory接口，这样在保证能够实例化新产品的同时又没有修改已有的代码….Perfect? 别急，这时我们再来看看客户的代码:   

 1     public class Client
 2     {
 3         public void Do()
 4         {
 5             //Create a ProductA
 6             IFactory factory = new ProductAFactory();
 7             //IFactory factory = new ProductBFactory();
 8 
 9             IProduct product = factory.CreateProduct();
10             product.DoTask1();
11             product.DoTask2();
12             product.DoTask3();
13             product.DoTask4();
14             product.DoTask5();
15             //Do more tasks 
16         }
17     }

         这时你会发现，客户端需要决定实例化哪一个工厂来生产自己所需要的产品，判断逻辑仍然存在，仅仅是从简单工厂的内部逻辑判断移到了客户端代码进行逻辑判断。也就是说如果你要想最终生产出ProductC仍然需要修改代码，而只是这次修改的位置移到了客户端！

         应对策略，很多人都知道这种问题的一个有效的解决方式是可以利用.Net的反射特性或其他IOC机制，具体做法是将需要实例化的工厂类名写到配置文件中，通过反射来读去，代码如下:

1   <appSettings>
2     <add key="FactoryName" value="ProductBFactory" />
3   </appSettings>

 1     public class Client
 2     {
 3         public void Do()
 4         {
 5             string factoryName = ConfigurationSettings.AppSettings["FactoryName"];
 6             IFactory factory = (IFactory)Assembly.Load("Factory Method").CreateInstance("Factory_Method." + factoryName);
 7 
 8             IProduct product = factory.CreateProduct();
 9             product.DoTask();
10         }
11     }

         到这里几乎可以说是对于对象创建的一个完美应对策略，然而…. 

新的疑问

         有“好事”者提出，既然都用到了.Net的反射机制那么工厂类本身是否还有存在的必要呢？是否可以通过在配制文件中写入需要生产的产品名称直接反射出具体的产品实例，从而略去工厂生产的步骤？具体代码参照如下:

1             IProduct product = (IProduct)Assembly.Load("Factory Method").CreateInstance("Factory_Method." + productName);
2             product.DoTask();

         对于这个问题，虽然看起来他在代码上有了一定的简化但是我认为他忽略了工厂模式的另一个特点，它集中封装了对象的创建,体现了代码的重用性。

         我们再来对比一下两段代码,未使用工厂:

1             IProduct product1 = (IProduct)Assembly.Load("Factory Method").CreateInstance("Factory_Method." + productName);
2             product1.DoTask();
3 
4             IProduct product2 = (IProduct)Assembly.Load("Factory Method").CreateInstance("Factory_Method." + productName);
5             product2.DoTask();
6 
7             IProduct product3 = (IProduct)Assembly.Load("Factory Method").CreateInstance("Factory_Method." + productName);
8             product3.DoTask();

       使用了工厂：

 1             IFactory factory = (IFactory)Assembly.Load("Factory Method").CreateInstance("Factory_Method." + factoryName);
 2 
 3             IProduct product1 = factory.CreateProduct();
 4             product1.DoTask();
 5 
 6             IProduct product2 = factory.CreateProduct();
 7             product2.DoTask();
 8 
 9             IProduct product3 = factory.CreateProduct();
10             product3.DoTask();

         可以看到没有使用工厂方法的代码段到处充斥了类似(IProduct)Assembly.Load("Factory Method").CreateInstance("Factory_Method." + productName)这样的代码，首先它不够优雅，其次没有考虑代码的重用性，如果项目的程序集名称或者命名空间发生了改变呢？虽然这种情况在实际项目中很少发生，但是你可以考虑一下若真的发生这种情况，你又需要修改多少地方~。那么这就是最终的解决方案了么，在我看来工厂方法(Factory Method)仍然有值得商榷的地方。

         首先，逻辑相对复杂，不易使用。是否每个人都可以随意的在项目中熟练的运用工厂方法模式呢？

         其次，可以看到在每增加一种新产品类型的时候，我们都需要多添加一个生产该产品的工厂，这便带来了业务需求之外的工作量。下面…. 

新的解决方案

         借助第三节中所提到的反射机制的运用，我思考着能否将反射机制与简单工厂相结合，仍然借助简单工厂的设计模式，而将其对产品的判断逻辑由原先的代码改为反射配置文件。首先参照以下代码。

         改良后的简单工厂:

1     public class ImprovedFactory
2     {
3         public static IProduct CreateProduct()
4         {
5             string productName = ConfigurationSettings.AppSettings["ProductName"];
6             IProduct product = (IProduct)Assembly.Load("Factory Method").CreateInstance("Factory_Method.improvement." + productName);
7             return product;
8         }
9     }

         Ok，我们再看看客户端代码:

1             IProduct product = ImprovedFactory.CreateProduct();
2             product.DoTask();

         同样是对于新产品ProductC，新的解决方法需要做哪些工作?

a)          添加类ProductC并实现IProduct接口，就像前面我说的，这是使用任何模式都无法避免的改变。

b)         在配置文件中多添加一条关于ProductC的配置信息。

看到了么?没有那么多复杂的抽象、具体工厂；不需要对新的产品添加新的工厂；不需要修改工厂类本身或是客户端代码；没有破坏任何设计原则。仅仅需要上面两步就完成一个新产品的添加及创建工作。

 

  以上是我对一种新的解决方案的尝试，也许这种方案也有缺陷的地方，但是我暂时还没有想到具体缺陷有哪些，刚刚接触设计模式不久，很多想法还不够成熟，还希望各位老师前辈多加指点。

水果贩子偷偷教你挑水果（转载）

    夏日，当然是狂吃水果的好季节。但你会挑水果吗？呵，你千万别说你早懂，且先看看，那些个水果商们的不授秘密。当然，还是那句老话：“不熟我不告诉他”。

芒果：

 芒果品种：鸡蛋芒(福建叫法)，香味比较浓郁形状较椭圆，果肉是橙色的，如小鸡蛋那么大，超甜超香，适合榨汁，汁极浓稠，可兑些淡淡的蜂蜜水;比鸡蛋芒 稍差些的是腰芒，形状像小猪腰，果肉是黄色的，有的是淡黄，果核很薄，这种芒果很甜，但香味不如鸡蛋芒;还有象牙芒、贵妃芒，不够熟时会有些涩、酸。另外 有一种绿色的芒果青芒，比黄色的更甜。

 挑选：选芒果一定要选比较饱满的、圆润的，不软不硬的，颜色黄的纯正的，香味老远就能闻到的，没有斑点的(有斑点的是从里面烂掉了)，闻香味，挑选大约 8分熟的，放一两天熟透就可以吃了，买熟透的即吃的芒果，可以看一下芒果的根部，应该是很清爽的，没有出水，同时皮也不能皱起，那种水份已经没有了。

 芒果的汁液有一种成分，对皮肤不好，所以吃时最好切成一小块一小块的，不要碰到皮肤和嘴唇，这样就好了。

 

桔、柚、橙子：

 高身橙，扁身柑，光身桔”。橙子要挑高身的，柑要扁的，桔子的皮要光亮。

 挑桔子的话不论品种，中间有个环形的是母的，通常都会比较甜一些，有个点状的是公的，没有母的甜，挑皮薄有弹性的，早桔汁多味甜，晚桔比较粗糙多筋。 柚、桔、橙子要捡沉手的，外皮要捡润滑的象宝宝的皮肤一样的，粗糙的像麻子脸的别买，不过冬天流行的砂糖桔就是皮很粗糙的，光滑的都不好。

买橙子的时候最好买底部，即“屁股”上面有个圈的。

 脐橙呢，就买“屁股” 那个洞是凹下去的比较甜。柚子、蜜柚一般越紧实越好。手掂感觉同样大小的越沉越好。用手按,越硬越好,按不下去的皮薄,皮越薄越好。蜜柚皮越光滑，颜色越均匀、越偏金黄越好。这样的柚子，果肉水分充足，甜度高，味道也正些。

 

西瓜、哈密瓜、香瓜：

 挑选西瓜时，要挑瓜的花纹感觉像是撑开了一样，尾巴开花的地方收口小的,“肚脐眼”凹进去的摸起来瓜的表面不是光滑的，而是凹凸不平的,瓜色比较绿色泽 比较深,用一只手托起，轻轻拍打，听声音，应该感觉有点空洞，托着的手感觉微微有震动，这个西瓜是沙瓤的，里面不紧密。如果不熟，就是很死的声音。

 挑选白色的甜瓜应该选瓜比较小，瓜大头的部分没有脐，但是有一点绿，这种是一棵瓜的第一个叶子结的，比较好挑，因为长的小。还有就是挑有脐的，脐越大的越好，按一下脐的部分较软的.闻一闻香瓜的屁股，有香味的就是好又甜的好瓜。

 挑哈密瓜要挑网纹粗且密的,闻到较浓的香气。

 买任何瓜的时候，和瓜秧不连在一起的那头是凹进去的就是熟、好、甜的。

 

苹果:

 富士应该挑那种看上去一丝一丝的，黄里透红的，这样的一般壤是黄的，又脆又甜还有一点点酸，味很正。千万不要挑绿里透一点点红的，这样的，一般味道都有 点寡淡的，水分可能也不少，但甜味一定不行。 正宗的红富士苹果放在桌子上都是歪的，没有正的，否则肯定不是正宗，颜色不能特别红，有些粉红，且不是红成一片，要挑有许多红丝的那种，还有“肚脐”要陷 下去深的，这样才甜。皮上麻点多的,用手指轻轻弹，清脆有回声的，是又甜又脆的。

 买苹果(主要是红富士)，最好选外表为条状红的(称“条红”)，而且红里面有一些黄色。那种苹果脆性、汁水多、很香甜。

 

榴莲：

 榴莲挑香味浓的，稍微裂开一点的，看得到里面的果肉软软糯糯的，又绝不湿漉漉的，果肉相对细长、金黄的，果形较丰满的外壳比较薄些，果肉的瓣也会多些。那种长圆形的，一般外壳较厚，果肉较薄。挑选时壳以黄中带绿为好

sql中CASE的用法

CASE
计算条件列表并返回多个可能结果表达式之一。

CASE 具有两种格式：

简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。

CASE 搜索函数计算一组布尔表达式以确定结果。
两种格式都支持可选的 ELSE 参数。

语法
简单 CASE 函数：

CASE input_expression
    WHEN when_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    END

CASE 搜索函数：

CASE
    WHEN Boolean_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    END

参数
input_expression

是使用简单 CASE 格式时所计算的表达式。Input_expression 是任何有效的 Microsoft® SQL Server™ 表达式。

WHEN when_expression

使用简单 CASE 格式时 input_expression 所比较的简单表达式。When_expression 是任意有效的 SQL Server 表达式。Input_expression 和每个 when_expression 的数据类型必须相同，或者是隐性转换。

n

占位符，表明可以使用多个 WHEN when_expression THEN result_expression 子句或 WHEN Boolean_expression THEN result_expression 子句。

THEN result_expression

当 input_expression = when_expression 取值为 TRUE，或者 Boolean_expression 取值为 TRUE 时返回的表达式。result expression 是任意有效的 SQL Server 表达式。

ELSE else_result_expression

当比较运算取值不为 TRUE 时返回的表达式。如果省略此参数并且比较运算取值不为 TRUE，CASE 将返回 NULL 值。Else_result_expression 是任意有效的 SQL Server 表达式。Else_result_expression 和所有 result_expression 的数据类型必须相同，或者必须是隐性转换。

WHEN Boolean_expression

使用 CASE 搜索格式时所计算的布尔表达式。Boolean_expression 是任意有效的布尔表达式。

结果类型
从 result_expressions 和可选 else_result_expression 的类型集合中返回最高的优先规则类型。有关更多信息，请参见数据类型的优先顺序。

结果值
简单 CASE 函数：
计算 input_expression，然后按指定顺序对每个 WHEN 子句的 input_expression = when_expression 进行计算。

返回第一个取值为 TRUE 的 (input_expression = when_expression) 的 result_expression。

如果没有取值为 TRUE 的 input_expression = when_expression，则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression；若没有指定 ELSE 子句，则返回 NULL 值。
CASE 搜索函数：
按指定顺序为每个 WHEN 子句的 Boolean_expression 求值。

返回第一个取值为 TRUE 的 Boolean_expression 的 result_expression。

如果没有取值为 TRUE 的 Boolean_expression，则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression；若没有指定 ELSE 子句，则返回 NULL 值。
示例
A. 使用带有简单 CASE 函数的 SELECT 语句
在 SELECT 语句中，简单 CASE 函数仅检查是否相等，而不进行其它比较。下面的示例使用 CASE 函数更改图书分类显示，以使其更易于理解。

USE pubs
GO
SELECT   Category =
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'mod_cook' THEN 'Modern Cooking'
         WHEN 'business' THEN 'Business'
         WHEN 'psychology' THEN 'Psychology'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END,
   CAST(title AS varchar(25)) AS 'Shortened Title',
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

下面是结果集：

Category            Shortened Title           Price                     
------------------- ------------------------- --------------------------
Business            You Can Combat Computer S 2.99                      
Business            Cooking with Computers: S 11.95                     
Business            The Busy Executive's Data 19.99                     
Business            Straight Talk About Compu 19.99                     

                                              avg
                                              ==========================
                                              13.73                     

Category            Shortened Title           Price                     
------------------- ------------------------- --------------------------
Modern Cooking      The Gourmet Microwave     2.99                      
Modern Cooking      Silicon Valley Gastronomi 19.99                     

                                              avg
                                              ==========================
                                              11.49                     

Category            Shortened Title           Price                     
------------------- ------------------------- --------------------------
Popular Computing   Secrets of Silicon Valley 20.00                     
Popular Computing   But Is It User Friendly?  22.95                     

                                              avg
                                              ==========================
                                              21.48                     

Category            Shortened Title           Price                     
------------------- ------------------------- --------------------------
Psychology          Life Without Fear         7.00                      
Psychology          Emotional Security: A New 7.99                      
Psychology          Is Anger the Enemy?       10.95                     
Psychology          Prolonged Data Deprivatio 19.99                     
Psychology          Computer Phobic AND Non-P 21.59                     

                                              avg
                                              ==========================
                                              13.50                     

Category            Shortened Title           Price                     
------------------- ------------------------- --------------------------
Traditional Cooking Fifty Years in Buckingham 11.95                     
Traditional Cooking Sushi, Anyone?            14.99                     
Traditional Cooking Onions, Leeks, and Garlic 20.95                     

                                              avg
                                              ==========================
                                              15.96                     

(21 row(s) affected)

B. 使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句
在 SELECT 语句中，CASE 搜索函数允许根据比较值在结果集内对值进行替换。下面的示例根据图书的价格范围将价格（money 列）显示为文本注释。

USE pubs
GO
SELECT    'Price Category' =
      CASE
         WHEN price IS NULL THEN 'Not yet priced'
         WHEN price < 10 THEN 'Very Reasonable Title'
         WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive book!'
      END,
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

下面是结果集：

Price Category        Shortened Title     
--------------------- --------------------
Not yet priced        Net Etiquette       
Not yet priced        The Psychology of Co
Very Reasonable Title The Gourmet Microwav
Very Reasonable Title You Can Combat Compu
Very Reasonable Title Life Without Fear   
Very Reasonable Title Emotional Security: 
Coffee Table Title    Is Anger the Enemy? 
Coffee Table Title    Cooking with Compute
Coffee Table Title    Fifty Years in Bucki
Coffee Table Title    Sushi, Anyone?      
Coffee Table Title    Prolonged Data Depri
Coffee Table Title    Silicon Valley Gastr
Coffee Table Title    Straight Talk About 
Coffee Table Title    The Busy Executive's
Expensive book!       Secrets of Silicon V
Expensive book!       Onions, Leeks, and G
Expensive book!       Computer Phobic And 
Expensive book!       But Is It User Frien

(18 row(s) affected)

C. 使用带有 SUBSTRING 和 SELECT 的 CASE 函数
下面的示例使用 CASE 和 THEN 生成一个有关作者、图书标识号和每个作者所著图书类型的列表。

USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+
   RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id,
   Type =
  CASE
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
  END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id

下面是结果集：

Name                      au_id       title_id Type               
------------------------- ----------- -------- -------------------
Johnson White             172-32-1176 PS3333   Psychology         
Marjorie Green            213-46-8915 BU1032   Business           
Marjorie Green            213-46-8915 BU2075   Business           
Cheryl Carson             238-95-7766 PC1035   Popular Computing  
Michael O'Leary           267-41-2394 BU1111   Business           
Michael O'Leary           267-41-2394 TC7777   Traditional Cooking
Dean Straight             274-80-9391 BU7832   Business           
Abraham Bennet            409-56-7008 BU1032   Business           
Ann Dull                  427-17-2319 PC8888   Popular Computing  
Burt Gringlesby           472-27-2349 TC7777   Traditional Cooking
Charlene Locksley         486-29-1786 PC9999   Popular Computing  
Charlene Locksley         486-29-1786 PS7777   Psychology         
Reginald Blotchet-Halls   648-92-1872 TC4203   Traditional Cooking
Akiko Yokomoto            672-71-3249 TC7777   Traditional Cooking
Innes del Castillo        712-45-1867 MC2222   Modern Cooking     
Michel DeFrance           722-51-5454 MC3021   Modern Cooking     
Stearns MacFeather        724-80-9391 BU1111   Business           
Stearns MacFeather        724-80-9391 PS1372   Psychology         
Livia Karsen              756-30-7391 PS1372   Psychology         
Sylvia Panteley           807-91-6654 TC3218   Traditional Cooking
Sheryl Hunter             846-92-7186 PC8888   Popular Computing  
Anne Ringer               899-46-2035 MC3021   Modern Cooking     
Anne Ringer               899-46-2035 PS2091   Psychology         
Albert Ringer             998-72-3567 PS2091   Psychology         
Albert Ringer             998-72-3567 PS2106   Psychology         

(25 row(s) affected)

学习SQL语句

http://www.cnblogs.com/zhanglei644213943/archive/2009/12/27/1633356.html

      纵览各大社区、论坛，各大 ORM框架火得不行了，如NHibernate、LINQ to SQL、ADO.NET Entity framework等，还有最近市场上出版的一本叫《领域驱动设计与模式实战》，里面也凸显了不少NHibernate在领域驱动设计中的作用与地位，也 算是第一本与NHibernate相关的书籍吧！不过就NHibernate而言还是没有官方文档介绍得详细呵呵，园子里Kiler 已 经把他翻译成中文版的了，收益一大片仅仅是CET-4的人。不管你是用NHibernate也好，还是用LINQ to SQL也好，用profiler一跟踪，执行的都是SQL语句，所以所SQL是根。特别是对于那些以数据为中心的应用系统，在数据库中实现复杂的存储过 程，复杂的报表查询，还是直接SQL来得痛快。当然 对 于那些在基于.NET的中间层应用中，它们实现面向对象的业务模型和商业逻辑的应用，NHibernate是最有用的。不管怎样，NHibernate一 定可以帮助你消除或者包装那些针对特定厂商的SQL代码，并且帮你把结果集从表格式的表示形式转换到一系列的对象去（官方文档）。

      有点跑题了，不再�嗦----直接晾出压轴题。

压轴题第一问

1.把表一转换为表二

表一：                                                                     

表二：

数据库代码如下：

代码

 1  DROP table #student
 2  CREATE TABLE #student (stdname nvarchar( 10 ),stdsubject nvarchar( 10 ),result  int )
 3  INSERT INTO #student VALUES ( ' 张三 ' , ' 语文 ' , 80 )
 4  INSERT INTO #student values ( ' 张三 ' , ' 数学 ' , 90 )
 5  INSERT INTO #student VALUES ( ' 张三 ' , ' 物理 ' , 85 )
 6  INSERT INTO #student VALUES ( ' 李四 ' , ' 语文 ' , 85 )
 7  INSERT INTO #student values ( ' 李四 ' , ' 数学 ' , 92 )
 8  INSERT INTO #student VALUES ( ' 李四 ' , ' 物理 ' , 82 )
 9  INSERT INTO #student VALUES ( ' 李四 ' , ' 化学 ' , 82 )
10  INSERT INTO #student VALUES ( ' 李四 ' , ' 化学 ' , 82 )
11  SELECT  *  FROM #student

 可能很多老手们，一看到这题目就有了答案。当然，贴出答案来不是我的目的，我要带着SQL新手们重构到答案。用MVP李建忠老师最爱说的话就是------我不建议一上来就套用模式，而应该从重构到模式。

首先大家会想到分两组

1  select stdname，・・・・，from #student group by stdname

然后・・・・・・中间该写什么呢？

代码

1  case  stdsubject when  ' 化学 '  then Result end
2  case  stdsubject when  ' 语文 '  then Result end
3  case  stdsubject when  ' ・・・ '  then Result end
4  case  stdsubject when  ' ・・・ '  then Result end
5  case  stdsubject when  ' ・・・ '  then Result end

表二里面得0是哪里来的呢？

代码

1  isnull(sum（ case  stdsubject when  ' 化学 '  then Result end）, 0 )
2  isnull(sum( case  stdsubject when  ' 语文 '  then Result end), 0 )
3  isnull(sum( case  stdsubject when  ' ・・・ '  then Result end), 0 )
4  isnull(sum( case  stdsubject when  ' ・・・ '  then Result end), 0 )
5  isnull(sum( case  stdsubject when  ' ・・・ '  then Result end), 0 )

所以得出：

代码

1  select stdname,
2  isnull(sum( case  stdsubject when  ' 化学 '  then Result end), 0 ) [化学],
3  isnull(sum( case  stdsubject when  ' 数学 '  then Result end), 0 ) [数学],
4  isnull(sum( case  stdsubject when  ' 物理 '  then Result end), 0 ) [物理],
5  isnull(sum( case  stdsubject when  ' 语文 '  then Result end), 0 ) [语文] 
6  from #student 
7  group by stdname

然后得出答案：

代码

1  declare @sql varchar( 4000 ) 
2  set  @sql  =   ' select stdname '  
3  select @sql  =  @sql  +   ' ,isnull(sum(case stdsubject when  ''' + stdsubject + '''  then Result end),0) [ ' + stdsubject + ' ] '  
4  from (select distinct stdsubject from #student)  as  a 
5  select @sql  =  @sql + '  from #student group by stdname '  
6  print @sql
7  exec(@sql)
8  

压轴题第二问：把表二转化为表一

表一：

表二：

数据库代码如下：

代码

1  DROP table #student2
2  CREATE TABLE #student2 (stdname nvarchar( 10 ),化学  int ,数学  int ,物理  int  ,语文  int  )
3  INSERT INTO #student2 VALUES ( ' 李四 ' , 164 , 92 , 82 , 85 )
4  INSERT INTO #student2 VALUES ( ' 张三 ' , 0 , 90 , 85 , 80 )
5  SELECT  *  FROM #student2 

看到这题，直接想到：

代码

 1  SELECT ' 李四 ' as  stdname,stdname = ' 化学 ' , 化学  as  result from #student2  where  stdname = ' 李四 '
 2  union all
 3  SELECT ' 李四 ' as  stdname,stdname = ' 数学 ' , 数学  as  result from #student2  where  stdname = ' 李四 '
 4  union all
 5  SELECT ' 李四 ' as  stdname,stdname = ' 物理 ' , 物理  as  result from #student2  where  stdname = ' 李四 '
 6  union all
 7  SELECT ' 李四 ' as  stdname,stdname = ' 语文 ' , 语文  as  result from #student2  where  stdname = ' 李四 '  

  8  union all 
 9  SELECT ' 张三 ' as  stdname,stdname = ' 化学 ' , 化学  as  result from #student2  where  stdname = ' 张三 '
10  union all
11  SELECT ' 张三 ' as  stdname,stdname = ' 数学 ' , 数学  as  result from #student2  where  stdname = ' 张三 '
12  union all
13  SELECT ' 张三 ' as  stdname,stdname = ' 物理 ' , 物理  as  result from #student2  where  stdname = ' 张三 '
14  union all
15  SELECT ' 张三 ' as  stdname,stdname = ' 语文 ' , 语文  as  result from #student2  where  stdname = ' 张三 '

 重构到：

代码

 1  declare @sql2 varchar( 4000 )
 2    set  @sql2  =   ''  
 3  SELECT @sql2 = @sql2 +  
 4  ' SELECT ''' + stdname + ''' as stdname,stdname= '' 化学 '' , 化学 as result from #student2 where stdname= ''' + stdname + '''
 5  union all
 6  SELECT ''' +stdname+ ''' as  stdname,stdname = '' 数学 '' , 数学  as  result from #student2  where  stdname = ''' +stdname+ '''
 7  union all
 8  SELECT ''' +stdname+ ''' as  stdname,stdname = '' 物理 '' , 物理  as  result from #student2  where  stdname = ''' +stdname+ '''
 9  union all
10  SELECT ''' +stdname+ ''' as  stdname,stdname = '' 语文 '' , 语文  as  result from #student2  where  stdname = ''' +stdname+ '''  union all  '
11  from (SELECT stdname FROM #student2)  as  a
12  SELECT @sql2  =  LEFT(@sql2,LEN(@sql2)  -   10 )
13  PRINT(@sql2)
14  exec(@sql2)

如果要求不能出现  化学  数学  物理 语文 这样的关键字，那么可以这样写：

代码

 1  select [name] into #tmpCloumns
 2  from tempdb.dbo.syscolumns
 3  where  id = object_id( ' tempdb.dbo.#student2 ' )
 4  and [name] <> ' stdname '
 5  select  *   from #tmpCloumns
 6  
 7  declare @strSql nvarchar( 800 )
 8  select @strSql = ''
 9  select @strSql = @strSql + ' union all ' + char ( 10 ) + char ( 13 ) +
10                   ' select [stdname], ''' + [name] + '''  as [科目],[ ' + [name] + ' ] ' + char ( 10 ) + char ( 13 ) +
11                   ' from [#student2] ' + char ( 10 ) + char ( 13 )
12  from #tmpCloumns
13  
14  select @strSql = substring(@strSql, 11 ,len(@strSql)) + ' order by stdname,[科目] '
15  -- print @strSql
16  exec(@strsql) 

 这种题目，在各种笔试中出现的概率还是非常大的，大家不用死记。以前有的朋友看着复杂的报表查询，几百行SQL,望而生畏，然后说："这是哪个SQL超人写的啊！"其实，谁一上来不可能写出那么长的SQL,也是慢慢重构--调试--重构-・・・・・・

SQL SERVER游标的讲解

游标和游标的优点
    在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结 果集中每次提取一条记录的机制。游标总是与一条T_SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理 时，必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同 的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。

    我们知道关系数据库管理系统实质是面向集合的，在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。
    由此可见，游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且， 正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。

游标种类
MS SQL SERVER 支持三种类型的游标：Transact_SQL 游标，API 服务器游标和客户游标。
（1） Transact_SQL 游标
    Transact_SQL 游标是由DECLARE CURSOR 语法定义、主要用在Transact_SQL 脚本、存储过程和触发器中。Transact_SQL 游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL 语句或是批处理、存储过程、触发器中的Transact_SQL 进行管理。 Transact_SQL 游标不支持提取数据块或多行数据。
（2） API 游标
    API 游标支持在OLE DB， ODBC 以及DB_library 中使用游标函数，主要用在服务器上。每一次客户端应用程序调用API 游标函数，MS SQL SEVER 的OLE DB 提供者、ODBC驱动器或DB_library 的动态链接库（DLL） 都会将这些客户请求传送给服务器以对API游标进行处理。
（3） 客户游标
    客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中，有一个缺省的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游 标。由于服务器游标并不支持所有的Transact-SQL 语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。
    由于API 游标和Transact-SQL 游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本章中我们主要讲述服务器（后台）游标。


每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序；
1.DECLARE 游标
2.OPEN 游标
3.从一个游标中FETCH 信息
4.CLOSE 或DEALLOCATE 游标
通常我们使用DECLARE 来声明一个游标声明一个游标主要包括以下主要内容：
游标名字
数据来源（表和列）
选取条件
属性（仅读或可修改）
其语法格式如下：
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]
其中：
cursor_name
指游标的名字。
INSENSITIVE
表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过
游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。

另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。
在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；
使用OUTER JOIN；
所选取的任意表没有索引；
将实数值当作选取的列。
SCROLL
表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再
重开游标。
select_statement
是定义结果集的SELECT 语句。应该注意的是，在游标中不能使用COMPUTE、COMPU- TE BY、 FOR BROWSE、 INTO 语句。
READ ONLY
表明不允许游标内的数据被更新尽管在缺省状态下游标是允许更新的。而且在UPDATE或DELETE 语句的WHERE CURRENT OF 子句中，不允许对该游标进行引用。
UPDATE [OF column_name[,…n]]
定义在游标中可被修改的列，如果不指出要更新的列，那么所有的列都将被更新。当游标被成功创建后，游标名成为该游标的惟一标识，如果在以后的存储过程、触发器或Transact_SQL 脚本中使用游标，必须指定该游标的名字。
上面介绍的是SQL_92 的游标语法规则。下面介绍MS SQL SERVER 提供的扩展了的游标声明语法，通过增加另外的保留字，使游标的功能进一步得到了增强其语法规则为；

LOCAL
定义游标的作用域仅限在其所在的存储过程、触发器或批处理中。当建立游标的存储过程执行结束后，游标会被自动释放。因此，我们常在存储过程中使用 OUTPUT 保留字，将游标传递给该存储过程的调用者，这样在存储过程执行结束后，可以引用该游标变量，在该种情况下，直到引用该游标的最后一个就是被释放时，游标才 会自动释放。
GLOBAL
定义游标的作用域是整个会话层会话层指用户的连接时间它包括从用户登录到SQLSERVER 到脱离数据库的整段时间。选择GLOBAL 表明在整个会话层的任何存储过程、触发器或批处理中都可以使用该游标，只有当用户脱离数据库、时该游标才会被自动释放。
注意：如果既未使用GLOBAL也未使用LOCAL，那么SQL SERVER将使用default local cursor数据库选项，为了与以彰的版本歉容，该选项常设置为FALSE。
FORWARD_ONLY
选项指明在从游标中提取数据记录时，只能按照从第一行到最后一行的顺序，此时只能选用FETCH NEXT 操作。除非使用STATIC， KEYSET 和DYNAMIC 关键字，否则如果未指明是使用FORWARD_ONLY 还是使用SCROLL， 那么FORWARD_ONLY 将成为缺省选项，因为若使用STATIC KEYSET 和DYNAMIC 关键字，则变成了SCROLL 游标。另外如果使用了FORWARD_ONLY， 便不能使用FAST_FORWARD。
STATIC
选项的含义与INSENSITIVE 选项一样，MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此对基本表的修改并不影响游标中的数据，即游标不会随着基本表内容的
改变而改变，同时也无法通过游标来更新基本表。
KEYSET
指出当游标被打开时，游标中列的顺序是固定的，并且MS SQL SERVER 会在tempdb内建立一个表，该表即为KEYSET KEYSET 的键值可惟一识别游标中的某行数据。当游标拥有者或其它用户对基本表中的非键值数据进行修改时，这种变化能够反映到游标中，所以游标用户或所有者可以通过 滚动游标提限这些数据。

    当其它用户增加一条新的符合所定义的游标范围的数据时，无法由此游标读到该数据。因为Transact-SQL 服务器游标不支持INSERT 语句。
    如果在游标中的某一行被删除掉，那么当通过游标来提取该删除行时，@@FETCH_STATUS 的返回值为-2。 @@FETCH_STATUS 是用来判断读取游标是否成功的系统全局变量。
    由于更新操作包括两部分：删除原数据插入新数据，所以如果读取原数据，@@FETCH_STATUS 的返回值为-2； 而且无法通过游标来读取新插入的数据。但是如果使用了WHERE CURRENT OF 子句时，该新插入行数据便是可见的。
注意：如果基础表未包含惟一的索引或主键，则一个KEYSET游标将回复成STATIC游标。
DYNAMIC
指明基础表的变化将反映到游标中，使用这个选项会最大程度上保证数据的一致性。然而，与KEYSET 和STATIC 类型游标相比较，此类型游标需要大量的游标资源。
FAST_FORWARD
指明一个FORWARD_ONLY, READ_ONLY 型游标。此选项已为执行进行了优化。如果SCROLL 或FOR_UPDATE 选项被定义，则FAST_FORWARD 选项不能被定义。
SCROLL_LOCKS
指明锁被放置在游标结果集所使用的数据上当。数据被读入游标中时，就会出现锁。这个选项确保对一个游标进行的更新和删除操作总能被成功执行。如果 FAST_FORWARD选项被定义，则不能选择该选项。另外，由于数据被游标锁定，所以当考虑到数据并发处理时，应避免使用该选项。
OPTIMISTIC
指明在数据被读入游标后，如果游标中某行数据已发生变化，那么对游标数据进行更新或删除可能会导致失败。如果使用了FAST_FORWARD 选项，则不能使用该选项。
TYPE_WARNING
指明若游标类型被修改成与用户定义的类型不同时，将发送一个警告信息给客户端。
注意：不可以将SQL_92的游标语法规则与MS SQL SERVER的游标扩展用法混合在一起使用。
下面我们将总结一下声明游标时应注意的一些问题。
    如果在CURSOR 前使用了SCROLL 或INSENSITIVE 保留字，则不能在CURSOR 和FOR select_statement 之间使用任何的保留字。反之同理。
    如果用DECLARE CURSOR 声明游标时，没有选择READ_ONLY、 OPTIMISTIC 或SCROLL_LOCKS 选项时，游标的缺省情况为：
    如果SELECT 语句不支持更新，则游标为READ_ONLY；
    STATIC 和FAST_FORWARD 类型的游标缺省为READ_ONLY；  
    DYNAMIC 和KEYSET 游标缺省为OPTIMISTIC。
    我们仅能在Transact-SQL 语句中引用游标，而不能在数据库API 函数中引用。
    游标被声明以后，可以通过系统过程对其特性进行设置。
    对那些有权限对视图、表或某些列执行SELECT 语句的用户而言，它也具有使用游标的缺省权限。

打开游标游标在声明以后，如果要从游标中读取数据必须打开游标。打开一个Transact-SQL服务器游标使用OPEN 命令，其语法规则为：
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
各参数说明如下：
GLOBAL
定义游标为一全局游标。
cursor_name
为声明的游标名字。如果一个全局游标和一个局部游标都使用同一个游标名，则如果使用GLOBAL 便表明其为全局游标，否则表明其为局部游标。
cursor_variable_name
为游标变量。当打开一个游标后时，MS SQL SERVER 首先检查声明游标的语法是否正确，如果游标声明中有变量，则将变量值带入。
    在打开游标时，如果游标声明语句中使用了INSENSITIVE 或STATIC 保留字，则OPEN产生一个临时表来存放结果集；如果在结果集中任何一行数据的大小超过MS SQL SERVER定义的最大行尺寸时，OPEN 命令将失败；如果声明游标时作用了KEYSET 选项，则OPEN 产生一个临时表来存放键值。所有的临时表都存在tempdb 数据库中。
    在游标被成功打开之后，@@CURSOR_ROWS 全局变量将用来记录游标内数据行数。为了提高性能，MS SQL SERVER 允许以异步方式从基础表向KEYSET 或静态游标读入数据，即如果MS SQL SERVER 的查询优化器估计从基础表中返回给游标的数据行已经超过sp_configure cursor threshold 参数值，则MS SQL SERVER 将启动另外一个独立的线程来继续从基础表中读入符合游标定义的数据行，此时可以从游标。中读取数据进行处理而不必等到所有的符合游标定义的数据行都从基础 表中读入游标 @@CURSOR_ROWS 变量存储的正是在调用@@CURSOR_ROWS 时，游标已从基础表读入的数据行。@@CURSOR_ROWS 的返回值有以下四个，如表13-1 所示。

    如果所打开的游标在声明时带有SCROLL 或INSENSITIVE 保留字，那么@@CURSOR_ROWS 的值为正数且为该游标的所有数据行。如果未加上这两个保留字中的一个，则@@CURSOR_ROWS 的值为-1， 说明该游标内只有一条数据记录。

当游标被成功打开以后，就可以从游标中逐行地读取数据，以进行相关处理。从游标中读取数据主要使用FETCH 命令。其语法规则为：

    各参数含义说明如下：
?NEXT
返回结果集中当前行的下一行，并增加当前行数为返回行行数。如果FETCH NEXT是第一次读取游标中数据，则返回结果集中的是第一行而不是第二行。
RIOR
返回结果集中当前行的前一行，并减少当前行数为返回行行数。如果FETCH PRIOR是第一次读取游标中数据，则无数据记录返回，并把游标位置设为第一行。
FIRST
返回游标中第一行。
LAST
返回游标中的最后一行。
ABSOLUTE {n | @nvar}
如果n 或@nvar 为正数，则表示从游标中返回的数据行数。如果n 或@nvar 为负数，则返回游标内从最后一行数据算起的第n 或@nvar 行数据。若n 或@nvar 超过游标的数据子集范畴，则@@FETCH_STARS 返回-1， 在该情况下，如果n 或@nvar 为负数，则执行FETCH NEXT 命令会得到第一行数据，如果n 或@nvar为正值，执行FETCH PRIOR 命令则会得到最后一行数据。n 或@nvar 可以是一固定值也可以是一smallint, tinyint 或int 类型的变量。
RELATIVE {n | @nvar}
若n 或@nvar 为正数，则读取游标当前位置起向后的第n 或@nvar 行数据；如果n 或@nvar 为负数，则读取游标当前位置起向前的第n 或@nvar 行数据。若n 或@nvar 超过游标的数据子集范畴，则@@FETCH_STARS 返回-1， 在该情况下，如果n 或@nvar 为负数，则执行FETCH NEXT 命令则会得到第一行数据；如果n 或@nvar 为正值，执行FETCH PRIOR 命令则会得到最后一行数据。n 或@nvar 可以是一固定值也可以是一smallint, tinyint或int 类型的变量。
INTO @variable_name[,...n]
允许将使用FETCH 命令读取的数据存放在多个变量中。在变量行中的每个变量必须与游标结果集中相应的列相对应，每一变量的数据类型也要与游标中数据列的数据类型相匹配。
    @@FETCH_STATUS 全局变量返回上次执行FETCH 命令的状态。在每次用FETCH从游标中读取数据时，都应检查该变量，以确定上次FETCH 操作是否成功，来决定如何进行下一步处理。@@FETCH_STATUS 变量有三个不同的返回值,如表13-2。

    在使用FETCH 命令从游标中读取数据时，应该注意以下的情况：
    当使用SQL-92 语法来声明一个游标时，没有选择SCROLL 选项时，只能使用FETCH NEXT 命令来从游标中读取数据，即只能从结果集第一行按顺序地每次读取一行，由于不能使用FIRST、 LAST、 PRIOR， 所以无法回滚读取以前的数据。如果选择了SCROLL 选项，则可能使用所有的FETCH 操作。
    当使用MS SQL SERVER 的扩展语法时，必须注意以下约定：
如果定义了FORWARD-ONLY 或FAST_FORWARD 选项，则只能使用FETCH NEXT命令；
如果没有定义DYNAMIC, FORWARD_ONLY 或FAST_FORWARD 选项，而定义了KEYSET, STATIC 或SCROLL 中的任何一个，则可使用所有的FETCH 操作；
DYNAMIC SCROLL 游标支持所有的FETCH， 选项但禁用ABSOLUTE 选项。


关闭游标
    1、使用CLOSE 命令关闭游标
     在处理完游标中数据之后必须关闭游标来释放数据结果集和定位于数据记录上的锁。CLOSE 语句关闭游标，但不释放游标占用的数据结构。如果准备在随后的使用中再次打开游标，则应使用CLOSE 命令。其关闭游标的语法规则为：
CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name }
    2、自动关闭游标
    我们已经了解到游标可应用在存储过程、触发器和Transact_SQL 脚本中。如果在声明游标与释放游标之间使用了事务结构，则在结束事务时游标会自动关闭。其具体的情况如下所示：
(1)、声明一个游标
(2)、打开游标
(3)、读取游标
(4)、BEGIN TRANSATION
(5)、数据处理
(6)、COMMIT TRANSATION
(7)、回到步骤3
    在这样的应用环境中。当从游标中读取一条数据记录进行以BEGIN TRANSATION为开头，COMMIT TRANSATION 或ROLLBACK 为结束的事务处理时，在程序开始运行后，第一行数据能够被正确返回，经由步骤7， 程序回到步骤3， 读取游标的下一行，此时常会发现游标未打开的错误信息。其原因就在于当一个事务结束时，不管其是以COMMIT TRANSATION 还是以ROLLBACK TRANSATION 结束，MS SQL SERVER 都会自动关闭游标，所以当继续从游标中读取数据时就会造成错误。
    解决这种错误的方法就是使用SET 命令将CURSOR_CLOSE_ON_COMMIT 这一参数设置为OFF 状态。其目的就是让游标在事务结束时仍继续保持打开状态，而不会被关闭。使用SET 命令的格式为：
SET CURSOR_CLOSE_ON_COMMIT OFF

释放游标
    在使用游标时，各种针对游标的操作或者引用游标名，或者引用指向游标的游标变量。当CLOSE 命令关闭游标时，并没有释放游标占用的数据结构。因此常使用DEALLOCATE 命令。通过该命令可以删除掉游标与游标名或游标变量之间的联系，并且释放游标占用的所有系统资源。其语法规则为：
    DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name}
    各参数的含义参看13.3 打开游标一节。
    当使用DEALLOCATE @cursor_variable_name 来删除游标时，游标变量并不会被释放，除非超过使用该游标的存储过程、触发器的范围(即游标的作用域)。

在这一步中，需要指定游标的属性和根据要求产生的结果集。有两种方法可以指定一个游标。

　　形式1（ANSI 92）

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
　　形式2

DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
INSENSITIVE关键字指明要为检索到的结果集建立一个临时拷贝，以后的数据从这个临时拷贝中获取。如果在后来游标处理的过程中，原有基表中数据发生了改变，那么它们对于该游标而言是不可见的。这种不敏感的游标不允许数据更改。

　　SCROLL关键字指明游标可以在任意方向上滚动。所有的fetch选项（first、last、next、relative、absolute）都可以在游标中使用。如果忽略该选项，则游标只能向前滚动（next）。

　　Select_statement指明SQL语句建立的结果集。Transact SQL语句COMPUTE、COMPUTE BY、FOR BROWSE和INTO在游标声明的选择语句中不允许使用。

　　READ ONLY指明在游标结果集中不允许进行数据修改。

　　UPDATE关键字指明游标的结果集可以修改。

　　OF column_list指明结果集中可以进行修改的列。缺省情况下（使用UPDATE关键字），所有的列都可进行修改。

　　LOCAL关键字指明游标是局部的，它只能在它所声明的过程中使用。

　　GLOBAL关键字使得游标对于整个连接全局可见。全局的游标在连接激活的任何时候都是可用的。只有当连接结束时，游标才不再可用。
FORWARD_ONLY指明游标只能向前滚动。

　　STATIC的游标与INSENSITIVE的游标是相同的。

　　KEYSET指明选取的行的顺序。SQL Server将从结果集中创建一个临时关键字集。如果对数据库的非关键字列进行了修改，则它们对游标是可见的。因为是固定的关键字集合，所以对关键字列进行修改或新插入列是不可见的。

　　DYNAMIC指明游标将反映所有对结果集的修改。

　　SCROLL_LOCK是为了保证游标操作的成功，而对修改或删除加锁。

　　OPTIMISTIC指明哪些通过游标进行的修改或者删除将不会成功。

　　注意：

　　・ 如果在SELECT语句中使用了DISTINCT、UNION、GROUP BY语句，且在选择中包含了聚合表达式，则游标自动为INSENSITIVE的游标。

　　・ 如果基表没有唯一的索引，则游标创建成INSENSITIVE的游标。

　　・ 如果SELECT语句包含了ORDER BY，而被ORDER BY的列并非唯一的行标识，则DYNAMIC游标将转换成KEYSET游标。如果KEYSET游标不能打开，则将转换成INSENSITIVE游标。使用 SQL ANSI-92语法定义的游标同样如此，只是没有INSENSITIVE关键字而已。

　　ii. 打开游标

　　打开游标就是创建结果集。游标通过DECLARE语句定义，但其实际的执行是通过OPEN语句。语法如下：

　　OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
　　GLOBAL指明一个全局游标。

　　Cursor_name是被打开的游标的名称。

　　Cursor_variable_name是所引用游标的变量名。该变量应该为游标类型。

　　在游标被打开之后，系统变量@@cursor_rows可以用来检测结果集的行数。@@cursor_rows为负数时，表示游标正在被异步迁 移，其绝对值（如果@@cursor_rows为－５，则绝对值为５）为当前结果集的行数。异步游标使用户在游标被完全迁移时仍然能够访问游标的结果。

　　iii. 从游标中取值

在从游标中取值的过程中，可以在结果集中的每一行上来回移动和处理。如果游标定义成了可滚动的（在声明时使用SCROLL关键字），则任何时候都可 取出结果集中的任意行。对于非滚动的游标，只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下：

FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
FROM [GLOBAL] cursor_name} | cursor_variable_name}
[INTO @variable_name ][,……n]]
NEXT指明从当前行的下一行取值。

　　PRIOR指明从当前行的前一行取值。

　　FIRST是结果集的第一行。

　　LAST是结果集的最后一行。

　　ABSOLUTE n表示结果集中的第n行，该行数同样可以通过一个局部变量传播。行号从0开始，所以n为0时不能得到任何行。

　　RELATIVE n表示要取出的行在当前行的前n行或后n行的位置上。如果该值为正数，则要取出的行在当前行前n行的位置上，如果该值为负数，则返回当前行的后n行。

　　INTO @cursor_variable_name表示游标列值存储的地方的变量列表。该列表中的变量数应该与DECLARE语句中选择语句所使用的变量数相 同。变量的数据类型也应该与被选择列的数据类型相同。直到下一次使用FETCH语句之前，变量中的值都会一直保持。

　　每一次FETCH的执行都存储在系统变量@@fetch_status中。如果FETCH成功，则@@fetch_status被设置成0。 @@fetch_status为-1表示已经到达了结果集的一部分（例如，在游标被打开之后，基表中的行被删除）。@@fetch_status可以用来 构造游标处理的循环。

　　例如：

DECLARE @iname char(20), @fname char(20)
OPEN author_cur
FETCH FIRST FROM author_cur INTO @iname, @fname
WHILE @@fetch_status = 0
BEGIN
IF @fname = ‘Albert’
PRINT “Found Albert Ringer”
ELSE
Print “Other Ringer”
FETCH NEXT FROM author_cur INTO @iname, @fname
END
　　iv. 关闭游标

　　CLOSE语句用来关闭游标并释放结果集。游标关闭之后，不能再执行FETCH操作。如果还需要使用FETCH语句，则要重新打开游标。语法如下：

　　CLOSE [GLOBAL] cursor_name | cursor_variable_name
　　v. 释放游标

　　游标使用不再需要之后，要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下：

　　DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name
　　下面给出游标的一个完整的例子：

　　USE master
　　GO
　　CREATE PROCEDURE sp_BuildIndexes
　　AS
　　DECLARE @TableName sysname, @msg varchar(100), @cmd varchar(100)
　　DECLARE table_cur CURSOR FOR
　　SELECT name FROM sysobjects WHERE type=’u’
　　OPEN table_cur
　　FETCH NEXT FROM table_cur INTO @TableName
　　WHILE @@fetch_status = 0
　　BEGIN
　　IF @@fetch_status = -2
　　CONTINUE
　　SELECT @msg = “Building indexes for table”+@TableName+”…”
　　PRINT @msg
　　SELECT @cmd = “DBCC DBREINDEX (‘”+@TableName+”')”
　　EXEC (@cmd)
　　PRINT “ “
　　FETCH NEXT FROM table_cur INTO @TableName
　　END
　　DEALLOCATE table_cur
　　GO
　　下面的脚本将为PUBS数据库执行sp_BuildIndexes

　　USE pubs
　　GO
　　EXEC ap_BuildIndexes

　　注意：上面也是创建用户定义的系统存储过程的示例。

　　使用临时表

　　临时表是在TempDB中创建的表。临时表的名称都以“#”开头。临时表的范围为创建临时表的连接。因为，临时表不能在两个连接之间共享，一旦 连接关闭，临时表就会被丢弃。如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中，或者被该存储过程调用的任何存储过程之中。如果需要在连接 之间共享临时表，则需要使用全局的临时表。全局的临时表以“##”符号开头，它将一直存在于数据库中，直到SQL Server重新启动。一旦这类临时表创建之后，所有的用户都可以访问到。在临时表上不能明确地指明权限。 临时表提供了存储中间结果的能力。有时候，临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。这可以通过首先将第一个查询的结果存在临时表 中，然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时，建议使用临时表。在这种情况下，在临时表中保持数据的子 集，以在随后的连接中使用，这样能大大改善性能。还可以在临时表中创建索引。