Sqlserver中存儲過程和遊标的一些使用例子

/*帶輸入輸出參數存儲過程*/
ALTER PROCEDURE pro_test2
    @userID INT,
    @maxUserID INT OUTPUT,
    @countUser INT OUTPUT
AS
    BEGIN  
        SELECT * FROM dbo.SY_ADMIN WHERE [email protected]  --
        SELECT @maxUserID=
        SET @countUser=
    END;  
GO  

/*測試*/
DECLARE @maxUserID INT,@countUser INT
EXECUTE pro_test2 10075,@maxUserID OUTPUT,@countUser OUTPUT
SELECT [email protected], [email protected]



/*帶輸入輸出參數 登入成功 密碼錯誤 使用者名錯誤*/
ALTER PROC proc_test3
    @UserName VARCHAR(),
    @UserPwd VARCHAR(),
    @Result INT OUT
AS
    BEGIN  
        IF @UserName = 'admin'
            BEGIN  
                IF @UserPwd = '111'
                    SET @Result =  
                ELSE
                    SET @Result =  
            END;   
        ELSE
            SET @Result =  
    END  

/*測試*/
DECLARE @Result INT
EXECUTE proc_test3 'hystu1', '111',@Result OUT
SELECT a=  @Result



/*遊标使用例子*/
--聲明個變量
DECLARE @D_Id NVARCHAR(MAX);   
DECLARE @D_Name NVARCHAR(MAX); 
DECLARE @D_Password NVARCHAR(MAX);   
--聲明一個遊标mycursor，select語句中參數的個數必須要和從遊标取出的變量名相同  
DECLARE mycursor CURSOR
FOR
    SELECT  D_Id ,
            D_Name,
            D_Password
    FROM    dbo.TestTable;    
--打開遊标  
OPEN mycursor;      
--從遊标裡取出資料指派到我們剛才聲明的2個變量中  
FETCH NEXT FROM mycursor INTO @D_Id, @D_Name, @D_Password;      
--判斷遊标的狀态  
-- 0 fetch語句成功      
---1 fetch語句失敗或此行不在結果集中      
---2 被提取的行不存在  
WHILE ( @@fetch_status = 0 )
    BEGIN      
        --顯示出我們每次用遊标取出的值   
        print (@D_Id+'--------'[email protected]_Name+'--------'[email protected]_Password)     
        --用遊标去取下一條記錄  
        FETCH NEXT FROM mycursor INTO @D_Id, @D_Name, @D_Password;  
    END;  
--關閉遊标  
CLOSE mycursor;  
--撤銷遊标  
DEALLOCATE mycursor;
           

/*
SQL遊标的優點是可以友善從一個結果集中進行循環周遊資料在進行操作。
、遊标允許應用程式對查詢語句select 傳回的行結果集中每一行進行相同或不同的操作，
   而不是一次對整個結果集進行同一種操作；
、它還提供對基于遊标位置而對表中資料進行删除或更新的能力；
、遊标把作為面向集合的資料庫管理系統和面向行的程式設計兩者聯系起來，
   使兩個資料處理方式能夠進行溝通。 
   然而遊标也有缺點——複雜和低效，是遊标的最大缺點，也是緻使很多時候在使用存儲過程中沒有使用遊标的主要原因。
*/
--将book表中的LookCount(int型)字段加上-的随機整數
DECLARE @bid INT;
DECLARE cur CURSOR READ_ONLY
FOR
    SELECT  bid
    FROM    Book; 
OPEN cur;
FETCH NEXT FROM cur INTO @bid;
WHILE ( @@fetch_status =  )
    BEGIN 
        UPDATE  dbo.Book
        SET     LookCount = LookCount + CAST(( RAND() * (  -  ) +  ) AS INT)
        WHERE   bid = @bid;
        FETCH NEXT FROM cur INTO @bid;
    END;
CLOSE cur;
DEALLOCATE cur;
           

--聲明3個變量  
DECLARE @D_Id NVARCHAR(MAX);     
DECLARE @D_Name NVARCHAR(MAX);   
DECLARE @D_Password NVARCHAR(MAX);
create table #tmp (id NVARCHAR(MAX),name NVARCHAR(MAX),pwd NVARCHAR(MAX))  --建立臨時資料表         
--聲明一個遊标mycursor，select語句中參數的個數必須要和從遊标取出的變量名相同    
DECLARE mycursor CURSOR  
FOR  
    SELECT  D_Id ,  
            D_Name,  
            D_Password  
    FROM    dbo.TestTable;      
--打開遊标    
OPEN mycursor;        
--從遊标裡取出資料指派到我們剛才聲明的3個變量中    
FETCH NEXT FROM mycursor INTO @D_Id, @D_Name, @D_Password;        
--判斷遊标的狀态    
-- 0 fetch語句成功        
---1 fetch語句失敗或此行不在結果集中        
---2 被提取的行不存在    
WHILE ( @@fetch_status = 0 )  
    BEGIN        
        --顯示出我們每次用遊标取出的值     
        --print (@D_Id+'--------'[email protected]_Name+'--------'[email protected]_Password)
        INSERT INTO #tmp(id,name,pwd) VALUES(@D_Id,@D_Name,@D_Password)
        --用遊标去取下一條記錄    
        FETCH NEXT FROM mycursor INTO @D_Id, @D_Name, @D_Password;    
    END;    
--關閉遊标    
CLOSE mycursor;    
--撤銷遊标    
DEALLOCATE mycursor;  
SELECT * FROM #tmp; --查詢結果
DROP TABLE #tmp --删除臨時表

           

USE [JianKu]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pro_GetProductSale]
   @BeginDate NVARCHAR(),
   @EndDate NVARCHAR(),
   @PageIndex INT,
   @PageSize INT,
   @Keyword NVARCHAR()
AS
    DECLARE @PageSql NVARCHAR(MAX)
    DECLARE @Sql NVARCHAR(MAX)
    DECLARE @Where NVARCHAR()
    DECLARE @WhereEnd NVARCHAR()
    DECLARE @CountSql NVARCHAR(MAX)
    SET @Where=''
    SET @WhereEnd=''
        IF  ISNULL(@BeginDate, '') <>'' 
            BEGIN  
                SET @Where += ' AND k.PayDate >='''+ @BeginDate + ''''
            END
        IF  ISNULL(@EndDate, '') <>'' 
            BEGIN  
                SET @Where += ' AND k.PayDate <='''+ @EndDate + ''''
            END
        IF  ISNULL(@Keyword, '') <>'' 
            BEGIN  
                SET @WhereEnd += ' AND (b.Name like ''%'+ @Keyword + '%'' 
                 OR c.Name like ''%'+ @Keyword + '%'' 
                 OR d.Name like ''%'+ @Keyword + '%''
                 OR a.Part like ''%'+ @Keyword + '%''
                 OR a.GoodsName like ''%'+ @Keyword + '%'')
                 '
            END 
    SET @Sql='
        SELECT a.*,b.Name AS CategoryName,c.Name AS BrandName,d.Name AS ModelName,
        t.SumSaleCount,t.SumSaleAmount
        FROM dbo.Goods a 
        LEFT JOIN dbo.GoodsPlatType b ON a.PlatId=b.Id
        LEFT JOIN dbo.GoodsBrand c ON a.BrandId=c.Id
        LEFT JOIN dbo.GoodsModel d ON a.ModelId=d.Id
        LEFT JOIN 
        (
            SELECT TOP 1 g.GoodsId, SUM(g.GoodsIdNum) AS SumSaleCount,SUM(g.GoodsIdNum*g.GoodsPrice) AS SumSaleAmount
            FROM dbo.GoodsOrderDetails g INNER JOIN dbo.GoodsOrder k ON g.OrderId=k.Id 
            AND k.Statuc=100 ' + @Where + '
            GROUP BY g.GoodsId      
        ) as t ON a.Id=t.GoodsId
        WHERE a.IsDelete=1 ' + @WhereEnd
    SET @CountSql='SELECT count(1) as TotalRecord FROM (' + @Sql + ') as tp'
    SET @PageSql='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id) AS ROWID,  
                  * FROM ('+@Sql+') as tpp ) AS t WHERE ROWID BETWEEN '
                  + CAST((@PageIndex - ) * @PageSize +  AS NVARCHAR())
                  +' AND '+ CAST(@PageIndex * @PageSize AS NVARCHAR())
    PRINT @PageSql
    EXEC(@PageSql)
    EXEC(@CountSql)


    --EXEC pro_GetProductSale @BeginDate='',@EndDate='',@PageIndex=,@PageSize=,@Keyword='小米'

           

文章轉自：https://blog.csdn.net/smartsmile2012/article/details/53259211