数据库编程，涵盖游标，存储过程，作业等

作业处理

declare
    @GradeID int,
    @dfvalue int,
    @dfValueStr varchar(50) ,
 @OperationStr1 varchar(150) ,
 @OperationStr2 varchar(150) ,
 @OperationStr3 varchar(150),
 @OperationStr4 varchar(150),
 @ErrorSpeechStr varchar(800),
 @ErrorPointStr varchar(50),
 @ExactSpeechStr varchar(200),
 @ReceptSpeechStr varchar(200),
 @MonitorSpeechStr varchar(200),
    @RecortCount int,
    @FSceneDiscrList varchar(1000),
 @coreStr varchar(10),
 @aidStr varchar(10),
    @receptStrM varchar(200)
declare Cursor_ZData cursor for select GradeID,dfvalue from qc_v_GradeForReportStructor Where GradeID in(select
Distinct GradeID from QC_V_GradeForReportOperation  where  kfvalue = 1
and GradeTime >=(getDate()-1) and GradeTime<=getDate())
open Cursor_ZData
while(@@FETCH_STATUS=0)
begin
fetch next from Cursor_ZData into @GradeID,@dfvalue
exec dbo.PROC_ADD_Report_GRZJMX_dfValueStr @GradeID,@dfvalue,@dfValueStr output
exec dbo.PROC_ADD_Report_GRZJMX_YWLX @GradeID,@OperationStr1 output,@OperationStr2 output,
@OperationStr3 output,@OperationStr4 output
exec dbo.PROC_ADD_Report_GRZJMX_CCGCZJD @GradeID,@ErrorPointStr output
exec dbo.PROC_ADD_Report_GRZJMX_ZJDMX @GradeID,@ErrorSpeechStr  output,
 @ExactSpeechStr  output, @ReceptSpeechStr  output, @MonitorSpeechStr  output
exec dbo.PROC_ADD_Report_GRZJMX_GSDQJXFirst @GradeID,@RecortCount output,
@FSceneDiscrList output
exec dbo.PROC_ADD_Report_GRZJMX_GSDQJX @GradeID,@RecortCount,@FSceneDiscrList,
@coreStr output,@aidStr  output,@receptStrM  output
exec dbo.PROC_ADD_Report_GRZJMX  @GradeID ,@dfValueStr,@OperationStr1,@OperationStr2,
@OperationStr3,@OperationStr4 ,@ErrorSpeechStr,@ErrorPointStr,
@ExactSpeechStr,@ReceptSpeechStr,@MonitorSpeechStr,@coreStr,@aidStr, @receptStrM
end
CLOSE Cursor_ZData
DEALLOCATE Cursor_ZData

创建存储过程

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PROC_ADD_Report_GRZJMX]
    @GradeID int,
 @dfValueStr varchar(50) ,
 @OperationStr1 varchar(150) ,
 @OperationStr2 varchar(150) ,
 @OperationStr3 varchar(150),
 @OperationStr4 varchar(150),
 @ErrorSpeechStr varchar(800),
 @ErrorPointStr varchar(50),
 @ExactSpeechStr varchar(200),
 @ReceptSpeechStr varchar(200),
 @MonitorSpeechStr varchar(200),
 @coreStr varchar(10),
 @aidStr varchar(10),
    @receptStrM varchar(200)
AS
declare
    @FJGradeTime datetime,
 @ProductName varchar(50) ,
 @Account_area varchar(50),
 @Struct_Name varchar(80) ,
 @AgentID varchar(20),
 @Account_alias varchar(20),
 @QCID varchar(20) ,
 @Starttime datetime,
 @callid varchar(24) ,
 @calledid varchar(24),
 @Extention varchar(16) ,
 @howtime int,
 @inoroutStr tinyint,
 @Name varchar(50) ,
 @organization varchar(50) ,
 @AppendColumn12 varchar(400),

 @ServiceTypeStr1 varchar(100) ,
 @ServiceTypeStr2 varchar(100),
 @ServiceTypeStr3 varchar(100),
 @ServiceTypeStr4 varchar(100) ,
 @AppendColumn11 varchar(400),
 @Satisfaction varchar(50) ,
 @KeyWord varchar(50),
 @ChengpinPoint int,
 @CorePoint int,
 @AidPoint int,
 @ReceptPoint int,
 @MonitorPoint int,
 @PointCount int,
 @chengpinerrorpoint int,
 @CoreErrorPoint int,
 @AidErrorPoint int,
 @ReceptErrorPoint int,
 @MonitorErrorPoint int,
 @ErrorPoint int,
 @Commend varchar(400),
 @AppendColumn10 varchar(400),
 @MonitorDescription varchar(400),
 @AddValue float,
 @CutValue float,
 @dfvalue float,
    @uniqueid varchar(32),
 @Time_Stamp datetime,

 @receptStr1 varchar(10) ,
 @receptStr2 varchar(10) ,
 @receptStr3 varchar(10) ,
 @receptStr4 varchar(10) ,
 @receptStr5 varchar(10) ,
 @receptStr6 varchar(10) ,
 @receptStr7 varchar(10) ,
 @receptStr8 varchar(10) ,
 @receptStr9 varchar(10) ,
 @receptStr10 varchar(10) ,
 @receptStr11 varchar(10) ,
 @receptStr12 varchar(10) ,
 @receptStr13 varchar(10) ,
 @receptStr14 varchar(10),
 @receptStr15 varchar(10) ,
 @receptStr16 varchar(10) ,
 @receptStr17 varchar(10) ,
 @receptStr18 varchar(10),
 @receptStr19 varchar(10) ,
 @receptStr20 varchar(10) ,
 @receptStr21 varchar(10) ,
 @receptStr22 varchar(10) ,
 @receptStr23 varchar(10) ,
 @receptStr24 varchar(10),
 @receptStr25 varchar(10),
 @receptStr26 varchar(10) ,
 @receptStr27 varchar(10) ,
 @receptStr28 varchar(10) ,
 @receptStr29 varchar(10),
 @receptStr30 varchar(10) ,
 @receptStr31 varchar(10),
 @receptStr32 varchar(10) ,
 @receptStr33 varchar(10) ,
 @receptStr34 varchar(10) ,
 @receptStr35 varchar(10) ,
 @receptStr36 varchar(10),
@receptStr37 varchar(10) ,
 @receptStr38 varchar(10) ,
 @receptStr39 varchar(10) ,
 @receptStr40 varchar(10) ,
 @receptStr41 varchar(10) ,
 @receptStr42 varchar(10) ,
 @receptStr43 varchar(10) ,
 @receptStr44 varchar(10) ,
@receptStr45 varchar(10),
@receptStr46 varchar(10),
@receptStr47 varchar(10),
 @receptStr48 varchar(10) ,
 @receptStr49 varchar(10) ,
 @receptStr50 varchar(10),
 @receptStr51 varchar(10) ,
 @receptStr52 varchar(10) ,
 @receptStr53 varchar(10) ,
 @receptStr54 varchar(10),
 @receptStr55 varchar(10) ,
 @receptStr56 varchar(10) ,
 @receptStr57 varchar(10) ,
 @receptStr58 varchar(10) ,
 @receptStr59 varchar(10) ,
 @receptStr60 varchar(10),
 @receptStr61 varchar(10),
 @receptStr62 varchar(10) ,
 @receptStr63 varchar(10) ,
 @receptStr64 varchar(10) ,
 @receptStr65 varchar(10),
 @receptStr66 varchar(10) ,
 @receptStr67 varchar(10),
 @receptStr68 varchar(10) ,
 @receptStr69 varchar(10) ,
 @receptStr70 varchar(10) ,
 @receptStr71 varchar(10) ,
 @receptStr72 varchar(10)

set @organization  =''
set @Satisfaction=''
 set @receptStr1 ='' 
set @receptStr2 ='' 
set @receptStr3 ='' 
set @receptStr4 ='' 
set @receptStr5 ='' 
set @receptStr6 ='' 
set @receptStr7='' 
set @receptStr8 ='' 
set @receptStr9 ='' 
set @receptStr10 ='' 
set @receptStr11 ='' 
set @receptStr12=''
set @receptStr13 ='' 
set @receptStr14 =''
set @receptStr15 ='' 
set @receptStr16 ='' 
set @receptStr17 =''
set @receptStr18 =''
set @receptStr19 ='' 
set @receptStr20 ='' 
set @receptStr21 ='' 
set @receptStr22 ='' 
set @receptStr23 ='' 
set @receptStr24=''
set @receptStr25=''
set @receptStr26 ='' 
set @receptStr27 ='' 
set @receptStr28 ='' 
set @receptStr29 =''
set @receptStr30 ='' 
set @receptStr31 =''
set @receptStr32 ='' 
set @receptStr33 =''
set @receptStr34 ='' 
set @receptStr35 =''
set @receptStr36 =''
set @receptStr37 =''
set @receptStr38 =''
set @receptStr39 =''
set @receptStr40 =''
set @receptStr41 =''
set @receptStr42 =''
set @receptStr43 =''
set @receptStr44 =''
set @receptStr45 =''
set @receptStr46 =''
set @receptStr47 =''
set @receptStr48 =''
set @receptStr49 =''
set @receptStr50 =''
set @receptStr51 =''
set @receptStr52 =''
set @receptStr53 =''
set @receptStr54 =''
set @receptStr55 =''
set @receptStr56 =''
set @receptStr57 =''
set @receptStr58 =''
set @receptStr59 =''
set @receptStr60 =''
set @receptStr61 =''
set @receptStr62 =''
set @receptStr63 =''
set @receptStr64 =''
set @receptStr65 =''
set @receptStr66 =''
set @receptStr67 =''
set @receptStr68 =''
set @receptStr69 =''
set @receptStr70 =''
set @receptStr71 =''
set @receptStr72 =''

declare @inorout  int
select
@FJGradeTime=FJGradeTime,
@ProductName=ProductName ,
@Account_area=Account_area,
@Struct_Name=Struct_Name ,
@AgentID=AgentID,
@Account_alias=Account_alias,
 @QCID=QCID,
 @Starttime=Starttime,
 @callid=callid ,
 @calledid=calledid,
@Extention=Extention ,
@howtime=howtime,
 @inorout=inorout,
 @Name=Name,
@AppendColumn12=AppendColumn12,
@ServiceTypeStr1=AppendColumn2  ,
@ServiceTypeStr2=AppendColumn3,
@ServiceTypeStr3=AppendColumn4,
@ServiceTypeStr4=ServiceType4 ,
@AppendColumn11=AppendColumn11,
@KeyWord=KeyWord,
@ChengpinPoint=ChengpinPoint,
@CorePoint=CorePoint,
@AidPoint=AidPoint,
@ReceptPoint=AppendColumn7,
@MonitorPoint=MonitorPoint,
@chengpinerrorpoint=chengpinerrorpoint,
@CoreErrorPoint=CoreErrorPoint,
@AidErrorPoint=AidErrorPoint,
@ReceptErrorPoint=AppendColumn8,
@MonitorErrorPoint=MonitorErrorPoint,
@Commend=Commend,
@AppendColumn10=AppendColumn10,
@MonitorDescription=MonitorDescription,
@AddValue=AddValue,
@CutValue=CutValue,
@dfvalue=dfvalue,
@uniqueid=uniqueid
from qc_v_GradeForReportStructor Where GradeID =567051
   if @inorout=0
 begin
    set @inoroutStr='呼入'
 end
 else if @inorout=1
  begin
  set   @inoroutStr='呼出'
 end

set @ErrorPoint =@chengpinerrorpoint+@CoreErrorPoint+@AidErrorPoint
set @PointCount =@ChengpinPoint+@CorePoint+@AidPoint

if (@receptStrM<>'')
begin
set @receptStr1=substring(@receptStrM,1,2)
set @receptStrM=substring(@receptStrM,4,len(@receptStrM)-3)
end

INSERT INTO QC_Report_GRZJMX Values(
    @GradeID ,
 @FJGradeTime ,
 @ProductName  ,
 @Account_area ,
 @Struct_Name  ,
 @AgentID ,
 @Account_alias ,
 @QCID  ,
 @Starttime ,
 @callid  ,
 @calledid ,
 @Extention ,
 @howtime ,
 @inoroutStr ,
 @Name ,
 @organization  ,
 @AppendColumn12 ,
 @ServiceTypeStr1 ,
 @ServiceTypeStr2 ,
 @ServiceTypeStr3,
 @ServiceTypeStr4 ,
 @AppendColumn11 ,
 @Satisfaction ,
 @dfValueStr  ,
 @KeyWord ,
 @OperationStr1  ,
 @OperationStr2  ,
 @OperationStr3,
 @OperationStr4 ,
 @ErrorSpeechStr ,
 @ErrorPointStr ,
 @ExactSpeechStr ,
 @ReceptSpeechStr ,
 @MonitorSpeechStr ,
 @ChengpinPoint ,
 @CorePoint ,
 @AidPoint ,
 @ReceptPoint ,
 @MonitorPoint ,
 @PointCount ,
 @chengpinerrorpoint ,
 @CoreErrorPoint ,
 @AidErrorPoint ,
 @ReceptErrorPoint ,
 @MonitorErrorPoint ,
 @ErrorPoint ,
 @Commend ,
 @AppendColumn10 ,
 @MonitorDescription ,
 @coreStr ,
 @aidStr ,
  @AddValue ,
 @CutValue ,
 @dfvalue ,
    @uniqueid,
 getdate()
  )

  IF @@error <> 0
        RETURN -1

 存储过程

USE [QCOnline_baoxian]
GO
/****** 对象:  StoredProcedure [dbo].[PROC_ADD_Report_GRZJMX_GSDQJXFirst]    脚本日期: 02/29/2012 18:13:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PROC_ADD_Report_GRZJMX_GSDQJXFirst]
 @GradeID int,
   @RecortCount int output,
   @FSceneDiscrList varchar(2000) output
AS
BEGIN
declare @SolutionID int
declare @ItemTxt varchar(200)
set @SolutionID=''
set @SolutionID = (select SolutionID  from QC_GradeChild where GradeID=@GradeID)
set @RecortCount=0
set @FSceneDiscrList=''
declare Cursor_SceneData cursor for select Distinct ItemTxt from dbo.QC_SceneDiscr where
 gradestyle=0 and (AnswerID in(Select QuestID from QC_Question where SolutionID=@SolutionID ))

open Cursor_SceneData
while(@@FETCH_STATUS=0)
begin
fetch next from Cursor_SceneData into @ItemTxt
set @FSceneDiscrList=@FSceneDiscrList+','+@ItemTxt
set  @RecortCount =@RecortCount +1
end
CLOSE Cursor_SceneData
DEALLOCATE Cursor_SceneData
if(@FSceneDiscrList<>'')
begin
set @FSceneDiscrList=substring(@FSceneDiscrList,2,len(@FSceneDiscrList)-1)
end
END

 

转载于:https://www.cnblogs.com/ouransun/archive/2012/02/29/2373963.html