Sqlserver中存储过程和游标的一些使用例子

1. /*带输入输出参数存储过程*/  
2. ALTER PROCEDURE pro_test2  
3.     @userID INT,  
4.     @maxUserID INT OUTPUT,  
5.     @countUser INT OUTPUT  
6. AS  
7.     BEGIN    
8.         SELECT * FROM dbo.SY_ADMIN WHERE UserID=@userID  --10075  
9.         SELECT @maxUserID=10089  
10.         SET @countUser=20000  
11.     END;    
12. GO    
13.   
14. /*测试*/  
15. DECLARE @maxUserID INT,@countUser INT  
16. EXECUTE pro_test2 10075,@maxUserID OUTPUT,@countUser OUTPUT  
17. SELECT a=@countUser, b=@maxUserID  
18.   
19.   
20.   
21. /*带输入输出参数 0登录成功 1密码错误 2用户名错误*/  
22. ALTER PROC proc_test3  
23.     @UserName VARCHAR(50),  
24.     @UserPwd VARCHAR(50),  
25.     @Result INT OUT  
26. AS  
27.     BEGIN    
28.         IF @UserName = 'admin'  
29.             BEGIN    
30.                 IF @UserPwd = '111'  
31.                     SET @Result = 0   
32.                 ELSE  
33.                     SET @Result = 1   
34.             END;     
35.         ELSE  
36.             SET @Result = 2   
37.     END    
38.   
39. /*测试*/  
40. DECLARE @Result INT  
41. EXECUTE proc_test3 'hystu1', '111',@Result OUT  
42. SELECT a=  @Result  
43.   
44.   
45.   
46. /*游标使用例子*/  
47. --声明2个变量  
48. DECLARE @D_Id NVARCHAR(MAX);     
49. DECLARE @D_Name NVARCHAR(MAX);   
50. DECLARE @D_Password NVARCHAR(MAX);     
51. --声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同    
52. DECLARE mycursor CURSOR  
53. FOR  
54.     SELECT  D_Id ,  
55.             D_Name,  
56.             D_Password  
57.     FROM    dbo.TestTable;      
58. --打开游标    
59. OPEN mycursor;        
60. --从游标里取出数据赋值到我们刚才声明的2个变量中    
61. FETCH NEXT FROM mycursor INTO @D_Id, @D_Name, @D_Password;        
62. --判断游标的状态    
63. -- 0 fetch语句成功        
64. ---1 fetch语句失败或此行不在结果集中        
65. ---2 被提取的行不存在    
66. WHILE ( @@fetch_status = 0 )  
67.     BEGIN        
68.         --显示出我们每次用游标取出的值     
69.         print (@D_Id+'--------'+@D_Name+'--------'+@D_Password)       
70.         --用游标去取下一条记录    
71.         FETCH NEXT FROM mycursor INTO @D_Id, @D_Name, @D_Password;    
72.     END;    
73. --关闭游标    
74. CLOSE mycursor;    
75. --撤销游标    
76. DEALLOCATE mycursor;  

[sql] view plain copy

1. /*  
2. SQL游标的优点是可以方便从一个结果集中进行循环遍历数据在进行操作。  
3. 1、游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，  
4.    而不是一次对整个结果集进行同一种操作；  
5. 2、它还提供对基于游标位置而对表中数据进行删除或更新的能力；  
6. 3、游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，  
7.    使两个数据处理方式能够进行沟通。   
8.    然而游标也有缺点——复杂和低效，是游标的最大缺点，也是致使很多时候在使用存储过程中没有使用游标的主要原因。  
9. */  
10. --将book表中的LookCount(int型)字段加上800-1000的随机整数  
11. DECLARE @bid INT;  
12. DECLARE cur CURSOR READ_ONLY  
13. FOR  
14.     SELECT  bid  
15.     FROM    Book;   
16. OPEN cur;  
17. FETCH NEXT FROM cur INTO @bid;  
18. WHILE ( @@fetch_status = 0 )  
19.     BEGIN   
20.         UPDATE  dbo.Book  
21.         SET     LookCount = LookCount + CAST(( RAND() * ( 1000 - 800 ) + 800 ) AS INT)  
22.         WHERE   bid = @bid;  
23.         FETCH NEXT FROM cur INTO @bid;  
24.     END;  
25. CLOSE cur;  
26. DEALLOCATE cur;  

[sql] view plain copy

1. --声明3个变量    
2. DECLARE @D_Id NVARCHAR(MAX);       
3. DECLARE @D_Name NVARCHAR(MAX);     
4. DECLARE @D_Password NVARCHAR(MAX);  
5. create table #tmp (id NVARCHAR(MAX),name NVARCHAR(MAX),pwd NVARCHAR(MAX))  --建立临时数据表           
6. --声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同      
7. DECLARE mycursor CURSOR    
8. FOR    
9.     SELECT  D_Id ,    
10.             D_Name,    
11.             D_Password    
12.     FROM    dbo.TestTable;        
13. --打开游标      
14. OPEN mycursor;          
15. --从游标里取出数据赋值到我们刚才声明的3个变量中      
16. FETCH NEXT FROM mycursor INTO @D_Id, @D_Name, @D_Password;          
17. --判断游标的状态      
18. -- 0 fetch语句成功          
19. ---1 fetch语句失败或此行不在结果集中          
20. ---2 被提取的行不存在      
21. WHILE ( @@fetch_status = 0 )    
22.     BEGIN          
23.         --显示出我们每次用游标取出的值       
24.         --print (@D_Id+'--------'+@D_Name+'--------'+@D_Password)  
25.         INSERT INTO #tmp(id,name,pwd) VALUES(@D_Id,@D_Name,@D_Password)  
26.         --用游标去取下一条记录      
27.         FETCH NEXT FROM mycursor INTO @D_Id, @D_Name, @D_Password;      
28.     END;      
29. --关闭游标      
30. CLOSE mycursor;      
31. --撤销游标      
32. DEALLOCATE mycursor;    
33. SELECT * FROM #tmp; --查询结果  
34. DROP TABLE #tmp --删除临时表  

[sql] view plain copy

1. USE [JianKu]  
2. GO  
3. SET ANSI_NULLS ON  
4. GO  
5. SET QUOTED_IDENTIFIER ON  
6. GO  
7. ALTER PROCEDURE [dbo].[pro_GetProductSale]  
8.    @BeginDate NVARCHAR(20),  
9.    @EndDate NVARCHAR(20),  
10.    @PageIndex INT,  
11.    @PageSize INT,  
12.    @Keyword NVARCHAR(50)  
13. AS  
14.     DECLARE @PageSql NVARCHAR(MAX)  
15.     DECLARE @Sql NVARCHAR(MAX)  
16.     DECLARE @Where NVARCHAR(200)  
17.     DECLARE @WhereEnd NVARCHAR(200)  
18.     DECLARE @CountSql NVARCHAR(MAX)  
19.     SET @Where=''  
20.     SET @WhereEnd=''  
21.         IF  ISNULL(@BeginDate, '') <>''   
22.             BEGIN    
23.                 SET @Where += ' AND k.PayDate >='''+ @BeginDate + ''''  
24.             END  
25.         IF  ISNULL(@EndDate, '') <>''   
26.             BEGIN    
27.                 SET @Where += ' AND k.PayDate <='''+ @EndDate + ''''  
28.             END  
29.         IF  ISNULL(@Keyword, '') <>''   
30.             BEGIN    
31.                 SET @WhereEnd += ' AND (b.Name like ''%'+ @Keyword + '%''   
32.                  OR c.Name like ''%'+ @Keyword + '%''   
33.                  OR d.Name like ''%'+ @Keyword + '%''  
34.                  OR a.Part like ''%'+ @Keyword + '%''  
35.                  OR a.GoodsName like ''%'+ @Keyword + '%'')  
36.                  '  
37.             END   
38.     SET @Sql='  
39.         SELECT a.*,b.Name AS CategoryName,c.Name AS BrandName,d.Name AS ModelName,  
40.         t.SumSaleCount,t.SumSaleAmount  
41.         FROM dbo.Goods a   
42.         LEFT JOIN dbo.GoodsPlatType b ON a.PlatId=b.Id  
43.         LEFT JOIN dbo.GoodsBrand c ON a.BrandId=c.Id  
44.         LEFT JOIN dbo.GoodsModel d ON a.ModelId=d.Id  
45.         LEFT JOIN   
46.         (  
47.             SELECT TOP 1 g.GoodsId, SUM(g.GoodsIdNum) AS SumSaleCount,SUM(g.GoodsIdNum*g.GoodsPrice) AS SumSaleAmount  
48.             FROM dbo.GoodsOrderDetails g INNER JOIN dbo.GoodsOrder k ON g.OrderId=k.Id   
49.             AND k.Statuc=100 ' + @Where + '  
50.             GROUP BY g.GoodsId        
51.         ) as t ON a.Id=t.GoodsId  
52.         WHERE a.IsDelete=1 ' + @WhereEnd  
53.     SET @CountSql='SELECT count(1) as TotalRecord FROM (' + @Sql + ') as tp'  
54.     SET @PageSql='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id) AS ROWID,    
55.                   * FROM ('+@Sql+') as tpp ) AS t WHERE ROWID BETWEEN '  
56.                   + CAST((@PageIndex - 1) * @PageSize + 1 AS NVARCHAR(20))  
57.                   +' AND '+ CAST(@PageIndex * @PageSize AS NVARCHAR(20))  
58.     PRINT @PageSql  
59.     EXEC(@PageSql)  
60.     EXEC(@CountSql)  
61.   
62.   
63.     --EXEC pro_GetProductSale @BeginDate='',@EndDate='',@PageIndex=2,@PageSize=10,@Keyword='小米'