存储过程 游标 事例

Create  PROC [GetPerformance]
(
    @vp varchar(25),       
    @range char(1) ,       
    @depetid int   ,       
    @Q varchar(1),         
    
    @QBeginDate datetime ,          
    @QEndDate datetime ,            
    
    @YBeginDate datetime ,          
    @YEndDate datetime             
)
AS
Begin
      --1:创建临时表存储全部数据的容器
      Create Table #MyTempTable
        (
            Did int ,                   
            MRange varchar(20),         
            TeamName varchar(50),       
            MGRName varchar(25),        
             
            M1 decimal(18,2),           
            M2 decimal(18,2),           
            M3 decimal(18,2),          
            
            C decimal(18,2),            
            F decimal(18,2),            
            J decimal(18,2),            
            T decimal(18,2),            
            
            QuarterML DECIMAL(18,2),           
            QuarterMLRW decimal(18,2),         
            QuarterMLRate varchar(30),         
            
            YearHTPrice DECIMAL(18,2),         
            
            YearML DECIMAL(18,2),           
            YearMLRW decimal(18,2),         
            YearMLRate varchar(30),         
            YML DECIMAL(18,2),
            QML decimal(18,2)
        )
    --2：获得部门信息,利用游标循环每一个部门，获得相应的数据
    
            declare @Did int                    
            declare @MRange varchar(20)         
            declare @TeamName varchar(50)       
            declare @MGRName varchar(25)       
             
            declare @M1 decimal(18,2)           
            declare @M2 decimal(18,2)           
            declare @M3 decimal(18,2)           
            
            declare @C decimal(18,2)            
            declare @F decimal(18,2)            
            declare @J decimal(18,2)            
            declare @T decimal(18,2)            
            
            declare @QuarterML DECIMAL(18,2)           
            declare @QuarterMLRW decimal(18,2)         
            declare @QuarterMLRate varchar(30)         
            
            declare @YearHTPrice DECIMAL(18,2)         
            
            declare @YearML DECIMAL(18,2)              
            declare @YearMLRW decimal(18,2)            
            declare @YearMLRate varchar(30)            
            
            declare @vpid varchar(25)                  -
            
            
            declare @YML decimal(18,2)   --用于排序没有百分比
            declare @QML decimal(18,2)   
     --3:获得年度
     declare @nowYear varchar(20)    --当前年
     set @nowYear = Year(GETDATE())  --默认为当前年
     if(MONTH(GETDATE())=1 or MONTH(GETDATE())=2 or MONTH(GETDATE())=3)    -- 当前时间为1，2，3月
     Begin
          if(@Q = '1' or @Q = '2' or @Q = '3')
          begin
          set @nowYear = Year(GETDATE()) - 1
          end
     End
     Else 
     Begin                         ---否则4~12月为当前年，查询的季度为4，则年+1
          if(@Q = '4')
          Begin
          set @nowYear = Year(GETDATE()) + 1
          End 
     End
     
     --3:获得季度是从那年开始的，方便取出任务
     declare @qrwYear varchar (20)
     set @qrwYear = Year(GETDATE())
     if(MONTH(GETDATE())=1 or MONTH(GETDATE())=2 or MONTH(GETDATE())=3)    -- 当前时间为1，2，3月，并且查询的季度是1.2.3,则年减1
     Begin
        set @qrwYear = Year(GETDATE()) - 1
    End
     ---------------------------------------------------------------------------------------
     --4:获得月分
     declare @tempm1 varchar(10)  --第一个月
     declare @tempm2 varchar(10)  --第二个月
     declare @tempm3 varchar(10)  --第三个月
     if(@Q = '1')
     Begin
       set @tempm1 ='4'
       set @tempm2 ='5'
       set @tempm3 ='6'
     End 
     else if (@Q = '2')
     Begin
       set @tempm1 ='7'
       set @tempm2 ='8'
       set @tempm3 ='9'
     End
     else if (@Q = '3')
     Begin
       set @tempm1 ='10'
       set @tempm2 ='11'
       set @tempm3 ='12'
     End
     else if (@Q = '4')
     Begin
       set @tempm1 ='1'
       set @tempm2 ='2'
       set @tempm3 ='3'
     End
   ---------------------------------------------------------------------------------------
         
    declare cur cursor  --定义一个游标
    read_only
    for



            SELECT     dbo.Department.DepartmentID, CASE WHEN Department.Range = '0' THEN '内' ELSE '外' END AS '范围', dbo.Department.DepartmentName AS '部门', 
                       T.mgrName AS '总监', M.VPEid
            FROM         dbo.Department LEFT OUTER JOIN
                          (SELECT     dbo.Employee.EmployeeID AS mgrEid, dbo.Employee.DepartmentID AS mgrDid, dbo.Employee.EmployeeName AS mgrName
                            FROM          dbo.Employee INNER JOIN
                                                   dbo.Role ON dbo.Employee.RoleID = dbo.Role.RoleID
                            WHERE      (dbo.Role.RoleName = 'salesmgr') AND (dbo.Employee.State = 1)) AS T ON T.mgrDid = dbo.Department.DepartmentID LEFT OUTER JOIN
                          (SELECT     Employee_1.EmployeeID AS VPEid, Employee_1.EmployeeName AS VPName, dbo.EmployeeOwnDepartment.DepartmentID AS VPID
                            FROM          dbo.Employee AS Employee_1 INNER JOIN
                                                   dbo.Role AS Role_1 ON Employee_1.RoleID = Role_1.RoleID RIGHT OUTER JOIN
                                                   dbo.EmployeeOwnDepartment ON Employee_1.EmployeeID = dbo.EmployeeOwnDepartment.EmployeeID
                            WHERE      (Role_1.RoleName = 'saleVP') AND (Employee_1.State = 1)) AS M ON M.VPID = dbo.Department.DepartmentID
            
            WHERE     (dbo.Department.IsValid = 1)
            and (@vp ='' or M.VPEid=@vp)
            and (@range ='' or Department.Range = @range)
            and (@depetid ='' or Department.DepartmentID=@depetid)
    open cur            --打开游标
    
        fetch next from cur into @Did,@MRange,@TeamName,@MGRName,@vpid --把提取操作的列数据放到局部变量中
    while(@@fetch_status=0) --返回被 FETCH 语句执行的最后游标的状态 
    Begin
        --1:获得季度内3个月份的合同金额
        

        select @M1=CAST((SUM(SalesAgreement.HTPrice)/10000) as decimal(18,2)) from SalesAgreement inner join Project on Project.ProjectID=SalesAgreement.ProjectID
        where project.EmployeeID in ( SELECT EmployeeID FROM Employee WHERE DepartmentID= @Did )
        AND YEAR(SalesAgreement.HtDate) = @nowYear AND MONTH(SalesAgreement.HtDate)=@tempm1

        --4: 季度毛利任务
        select @QuarterMLRW = MAX(ML) from DeptQuarterTask where [Year]=@qrwYear and [Quarter]=@Q AND DepartmentID=@Did
        --5: 季度完成率
        if(@QuarterMLRW = 0)
        begin
           set  @QuarterMLRate = '0'
           set @QML = 0
        end
        else 
        begin
           set @QuarterMLRate =CAST (  cast (  ( (@QuarterML / @QuarterMLRW) * 100) as decimal(18,2) )    as varchar(30))  + '%'
           set @QML = cast (   (  (@QuarterML / @QuarterMLRW) * 100) as decimal(18,2)) 
        end
        
        --10：插入各个字段的值到临时表产生一行部门的所有数据
        insert into #MyTempTable values(
             @Did ,
             @MRange ,
             @TeamName ,
             @MGRName,
             @M1 ,
             @M2 ,
             @M3 ,
             @C ,
             @F ,
             @J ,
             @T ,
             @QuarterML ,
             @QuarterMLRW ,
             @QuarterMLRate ,
             @YearHTPrice ,
             @YearML ,
             @YearMLRW ,
             @YearMLRate , @YML,@QML)
        
        --提前下一位信息
        fetch next from cur into @Did,@MRange,@TeamName,@MGRName,@vpid
    End
    close cur 
    deallocate cur --删除游标

    select * from #MyTempTable order by  MRange asc,YML desc , QML desc , TeamName asc

    --查询临时表传入数据进行分页
    --set @sql = 'select * from #MyTempTable'
    --set @sqlCountText = 'select count(*) from #MyTempTable'
    
    ----执行分页存储过程
    --exec dbo.GetPageData @sql,@PageCurrent,@PageSize,@PageCount,@sqlCountText
    
    --删除临时表
    drop table #MyTempTable
End