MSSQL教程之SQL 以日期静态更新保护的数据,一周排程...
其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。静态|计划|数据以保护日期的分歧而改动显现内容.现在天是21号.保护了七天的数据即21--28.当在22号翻开保护界面的时分,保护内容显现为22--29号的数据和日期.
创立保护表:..
-----------------创立数据纪录表-------------------------
ifexists(select*fromdbo.sysobjectswhereid=object_id(N.)andOBJECTPROPERTY(id,NIsUserTable)=1)
droptablePdl_WeekProcedure
GO
ifnotexists(select*fromdbo.sysobjectswhereid=object_id(N.)andOBJECTPROPERTY(id,NIsUserTable)=1)
BEGIN
CreateTablePdl_WeekProcedure(
intIdentity(1,1)NotNull,
datetimeNotNull,
varchar(20)Null,
varchar(5)NullDEFAULT(0),
datetimeNull,
intNullDefault(0),
intNullDefault(0),
intNullDefault(0),
intNullDefault(0),
intNullDefault(0),
intNullDefault(0),
intNullDefault(0)--,
--datetime
CONSTRAINTPRIMARYKEYCLUSTERED
(
)ON
)ON
End
-----------------创立日期表--------------------
ifexists(select*fromdbo.sysobjectswhereid=object_id(N.)andOBJECTPROPERTY(id,NIsUserTable)=1)
droptablePdl_WeekProcedureDate
GO
ifnotexists(select*fromdbo.sysobjectswhereid=object_id(N.)andOBJECTPROPERTY(id,NIsUserTable)=1)
BEGIN
CreateTablePdl_WeekProcedureDate(
intIdentity(1,1)NotNull,
datetimeNotNull,
datetimeNull,
datetimeNull,
datetimeNull,
datetimeNull,
datetimeNull,
datetimeNull,
datetimeNull
CONSTRAINTPRIMARYKEYCLUSTERED
(
)ON
)ON
End
-----------创立历一周交货排程史表----------------------------------
ifexists(select*fromdbo.sysobjectswhereid=object_id(N.)andOBJECTPROPERTY(id,NIsUserTable)=1)
droptablePdl_WeekProcHistory
GO
ifnotexists(select*fromdbo.sysobjectswhereid=object_id(N.)andOBJECTPROPERTY(id,NIsUserTable)=1)
BEGIN
CreateTablePdl_WeekProcHistory(
intIdentity(1,1)NotNull,
datetimeNotNull,
varchar(20)Null,
varchar(5)NullDEFAULT(0),
intNullDefault(0),
intNullDefault(0),
intNullDefault(0),
intNullDefault(0),
intNullDefault(0),
intNullDefault(0),
intNullDefault(0),
datetimeNull,
datetimeNull,
datetimeNull,
datetimeNull,
datetimeNull,
datetimeNull,
datetimeNull,
datetimeNotNullDefault(Getdate()),
datetimeNull
CONSTRAINTPRIMARYKEYCLUSTERED
(
)ON
)ON
End
更新保护表日期数据:
/*
ProcedureName:一周交货排程数据更新
AuthorName:lyf
Date:01/13/2005
*/
AlterProcedurePdl_WeekProcDateUpdateAs
Declare@numint,@num1int,@num2int,@Countint
Declare@Strvarchar(4000)
Declare@DateIddatetime,@Date1datetime,@Date2datetime,@Date3datetime,@Date4datetime,
@Date5datetime,@Date6datetime,@Date7datetime
Select@DateId=Convert(datetime,Convert(varchar,Getdate(),101))
Select@Date1=Convert(datetime,Convert(varchar,Getdate(),101))
Select@Date2=@Date1+1,@Date3=@Date1+2,@Date4=@Date1+3,
@Date5=@Date1+4,@Date6=@Date1+5,@Date7=@Date1+6
---------------修改更新为当天日期后向后推7天--------
IFNotExists(Select*FromPdl_WeekProcedureDate
WhereConvert(datetime,Convert(varchar,DateId,101))=Convert(datetime,Convert(varchar,@DateId,101)))
InsertIntoPdl_WeekProcedureDate(DateId,Date1,Date2,Date3,Date4,Date5,Date6,Date7)
Values(@DateId,@Date1,@Date2,@Date3,@Date4,@Date5,@Date6,@Date7)
----------将表Pdl_WeekProcedure里的数据换算成当天最新数据"对应程序保护接口"—
Select@num=Isnull(DatedIFF(day,(SelectMin(DateId)FromPdl_WeekProcedure),
(SelectMax(DateId)FromPdl_WeekProcedureDate)),0)
IF@num>0
Begin
Set@Count=1
Set@num1=@num
Set@num2=@num
Set@num=7-@num
Set@Str=
While@Count<=@num
Begin
Select@Str=@Str+Qnty+Convert(varchar,@Count)+=Qnty+Convert(varchar,@num1+1)+,
Set@Count=@Count+1
Set@num1=@num1+1
End
While@num2>0
Begin
Select@Str=@Str+Qnty+Convert(varchar,(7-@num2+1))+=0,
Set@num2=@num2-1
End
Select@str=Left(@str,Len(@str)-1)
--------------拔出一周交货排程保护汗青------------
InsertIntoPdl_WeekProcHistory(DateId,Partnum,Revision,Qnty1,Qnty2,Qnty3,Qnty4,Qnty5,Qnty6,Qnty7,
Date1,Date2,Date3,Date4,Date5,Date6,Date7,IOTime,OutPutDate)
Selectt1.DateId,t1.Partnum,t1.Revision,t1.Qnty1,t1.Qnty2,t1.Qnty3,t1.Qnty4,t1.Qnty5,t1.Qnty6,t1.Qnty7,
t2.Date1,t2.Date2,t2.Date3,t2.Date4,t2.Date5,t2.Date6,t2.Date7,Getdate(),t1.Todate
FromPdl_WeekProceduret1(nolock),Pdl_WeekProcedureDatet2(nolock)
Wheret1.Dateid=t2.DateId
------------------------更新Pdl_WeekProcedure数据----------------------------
Exec(UpdatePdl_WeekProcedureSet+@Str)
UpdatePdl_WeekProcedure
SetDateId=(SelectMax(DateId)FromPdl_WeekProcedureDate)
-------------------------更新当天出货工夫字段--------------------------
UpdatePdl_WeekProcedureSetTodate=Null
WhereConvert(datetime,Convert(varchar,Todate,101))Convert(datetime,Convert(varchar,Getdate(),101))
End
--deletePdl_WeekProcedureDatewheredateid=2005-01-1500:00:00.000
--UpdatePdl_WeekProceduresetdateid=convert(datetime,convert(varchar,getdate(),101))-1
天生最初数据汇合:
/*
ProcedureName:一周交货排程报表
AuthorName:lyf
Date:01/18/2005
*/
--execPdl_WeekDeliveryProcedure,,,,,
AlterProcedurePdl_WeekDeliveryProcedure
@InBPartnumvarchar(20)=,--DH024-030,--,MH075-013
@InEPartnumvarchar(20)=,--DH024-030,--,MH075-013
@InBRevisionvarchar(5)=,
@InERevisionvarchar(5)=,
@BDatevarchar(100),--datetime,--=01/18/2005,
@EDatevarchar(100),--datetime--=01/21/2005
@Flageint
As
Declare@BDateTimedatetime,@EDateTimedatetime
--Set@BDate=isnull(@BDate,getdate())
--Set@EDate=isnull(@EDate,getdate()+6)
-------------挪用更新保护记录表数据存储历程-------------------
ExecPdl_WeekProcDateUpdate
Select@BDateTime=convert(datetime,Convert(varchar,Getdate(),101))
Select@EDateTime=convert(datetime,Convert(varchar,@BDateTime+7,101))
/*取周出货形态数据表布局*/
Selecttop0PartNum,Revision,Qnty=0,WeekDate=DateId,Com=A9--=Convert(varchar(5),WeekDate,101)
into#WeekConfig
FromPdl_WeekProcedure(nolock)
/*在成品数据*/
selectt1.Partnum,t1.Revision,
Qnty=sum(caset1.POP
when0
thenceiling(1*t1.QNTY/(power(ISNULL(t3.LPiece,1),caset1.POPwhen0then0else1end)*
power(ISNULL(t3.LLPiece,1),caset1.POPwhen2then1else0end)))
when1
thenceiling(1*t1.QNTY*power(ISNULL(t3.LPiece,1),caset1.POPwhen0then1else0end)/
power(ISNULL(t3.LLPiece,1),caset1.POPwhen2then1else0end))
when2
then(1*t1.QNTY*power(ISNULL(t3.LPiece,1),caset1.POPwhen0then1else0end)*
power(ISNULL(t3.LLPiece,1),caset1.POPwhen2then0else1end))
end),DelDate=min(t4.ExpStkDate),UPP=convert(varchar,t3.LPiece)+*+convert(varchar,t3.LLPiece),
t5.Id,t5.name
Into#tmpStknwip
fromStknwipt1(nolock),
LotInfot2(nolock),
ProdBasict3(nolock),
OrderDetailt4(nolock),
PDL_ProcGroupt5(nolock),
PDL_ProcGroupDetailt6(nolock)
wheret1.LotNum=t2.LotNum
andt1.Layer=t2.Layer
andt2.IsInSC1
andt1.Qnty>0
andt1.ProcCodenotin(990,000)
andt1.Partnum=t3.Partnum
andt1.Revision=t3.Revision
--andt1.Layer=t3.Layer
andt2.PoNum*=t4.PoNum
andt2.Poitem*=t4.SerialNum
andt1.ProcCode=t6.ProcCode
andt5.Id=t6.Id
and((t1.PartNum>=@InBPartnumor@InBPartnum=)
and(t1.PartNum<=@InEPartnumor@InEPartnum=))
and((t1.Revision=@InBRevisionor@InBRevision=)
and(t1.Revision=@InERevisionor@InERevision=))
GroupByt1.Partnum,t1.Revision,
t3.LPiece,t3.LLPiece,t5.Id,t5.name
OrderByt5.Id,t1.partnum
/*wip完全性天生*/
Declare@CheckPartNumvarchar(20),@CheckRevisionvarchar(5)
Declare@WipProcCodevarchar(20),@WipIdvarchar(5)
SelectTop1@CheckPartNum=Partnum,@CheckRevision=RevisionFrom#tmpStknwip
DECLARECheckWipINSENSITIVECURSOR
FOR
SelectProcName=RTrim(LTrim(t1.Name)),Id=RTrim(LTrim(t1.Id))
FromPDL_ProcGroupt1(nolock),PDL_ProcGroupDetailt2(nolock)
Wheret1.Id=t2.Id
GroupByt1.Id,t1.Name
OPENCheckWip
BEGIN
FETCHNEXTFROMCheckWipINTO@WipProcCode,@WipId
WHILE@@FETCH_STATUS=0
BEGIN
IFNotExists(Select*From#tmpStknwipWhereName=@WipProcCodeandId=@WipId)
InsertInto#tmpStknwip(Partnum,Revision,Qnty,Name,Id)
Select@CheckPartNum,@CheckRevision,0,@WipProcCode,@WipId
FETCHNEXTFROMCheckWipINTO@WipProcCode,@WipId
END
END
CLOSECheckWip
DEALLOCATECheckWip
/*算计在成品数目*/
SelectPartnum,Revision,Qnty=sum(Qnty),
DelDate,UPP,ProcCode=Name,Id
Into#Stknwip
From#tmpStknwip
GroupByPartnum,Revision,DelDate,UPP,Id,Name
OrderByID
/*判别完全性*/
IF((NotExists(Select*from#WeekConfig))and(NotExists(Select*from#Stknwip)))
Begin
InsertInto#Stknwip(Partnum,Revision,Qnty,ProcCode,Id)
Selectnotdata,no,0,ProcCode=Name,Id
FromPDL_ProcGroup
InsertInto#WeekConfig(PartNum,Revision,Qnty,WeekDate,Com)
Selectnotdata,no,0,@BDateTime,A9
End
Else
Begin
IFNotExists(Select*from#Stknwip)
InsertInto#Stknwip(Partnum,Revision,Qnty,ProcCode,Id)
Selectnotdata,no,0,ProcCode=Name,Id
FromPDL_ProcGroup
IFNotExists(Select*From#WeekConfig)
InsertInto#WeekConfig(PartNum,Revision,Qnty,WeekDate,Com)
SelectTop1t1.Partnum,t1.Revision,Qnty=0,@BDateTime,A9
FromStknwipt1(nolock),LotInfot2(nolock)
Wheret1.LotNum=t2.LotNum
andt1.Layer=t2.Layer
andt2.IsInSC1
andt1.Qnty>0
andt1.ProcCodenotin(990,000)
and((t1.PartNum>=@InBPartnumor@InBPartnum=)
and(t1.PartNum<=@InEPartnumor@InEPartnum=))
and((t1.Revision=@InBRevisionor@InBRevision=)
and(t1.Revision=@InERevisionor@InERevision=))
End
/*天生出货形态工夫格局和完全性天生*/
Declare@Partnumvarchar(20),@Revisionvarchar(5),@Countint,@DateCountint
Declare@WeekDatedatetime,@Comvarchar(200)
Select@Com=A
DECLAREAddDateListINSENSITIVECURSOR
FOR
SelectPartnum,RevisionFrom#WeekConfig
GroupbyPartnum,Revision
OPENAddDateList
BEGIN
FETCHNEXTFROMAddDateListINTO@Partnum,@Revision
WHILE@@FETCH_STATUS=0
BEGIN
Select@Count=Count(WeekDate)From#WeekConfig
WherePartnum=@PartnumandRevision=@RevisionandWeekDate>=@BDateTimeandWeekDate<=@EDateTime
Select@DateCount=0
IF@Count<7
Begin
While@DateCount<7
Begin
Select@WeekDate=convert(datetime,Convert(varchar,(@BDateTime+@DateCount),101))
IFNotExists(Select*From#WeekConfig
WherePartnum=@PartnumandRevision=@Revision
andconvert(datetime,Convert(varchar,WeekDate,101))=@WeekDate
)
Begin
InsertInto#WeekConfig(partnum,revision,qnty,weekdate,Com)
Values(@Partnum,@Revision,0,convert(datetime,Convert(varchar,(@BDateTime+@DateCount),101)),
@Com+Convert(varchar,@DateCount))
Select@DateCount=@DateCount+1
End
Else
Begin
Update#WeekConfigSetCom=@Com+Convert(varchar,@DateCount)
WherePartnum=@PartnumandRevision=@Revision
andconvert(datetime,Convert(varchar,WeekDate,101))=@WeekDate
Select@DateCount=@DateCount+1
End
End
End
FETCHNEXTFROMAddDateListINTO@Partnum,@Revision
END
END
CLOSEAddDateList
DEALLOCATEAddDateList
/*天生交织数据集*/
InsertInto#Stknwip(Partnum,Revision,Qnty,ProcCode,Id)
SelectPartNum,Revision,Qnty,Com,1A
From#WeekConfig
WhereWeekDate>=@BDateTimeandWeekDate<=@EDateTime
Declare@StkPartnumvarchar(20),@StkRevisionvarchar(5),@StkDelDatedatetime,@StkUPPvarchar(10)
DECLARECU_StknWipINSENSITIVECURSOR
FOR
SelectPartnum,RevisionFrom#Stknwip
GroupByPartnum,Revision
OPENCU_StknWip
BEGIN
FETCHNEXTFROMCU_StknWipINTO@StkPartnum,@StkRevision
WHILE@@FETCH_STATUS=0
BEGIN
select@StkDelDate=min(deldate),@StkUPP=min(upp)
from#Stknwip
WherePartnum=@StkPartnumandRevision=@StkRevision
Updatet1Sett1.DelDate=@StkDelDate,t1.UPP=@StkUPPFrom#Stknwipt1
Wheret1.Partnum=@StkPartnumandt1.Revision=@StkRevision
and(isnull(t1.DelDate,01/01/1900)=01/01/1900orisnull(t1.UPP,-)=-)
FETCHNEXTFROMCU_StknWipINTO@StkPartnum,@StkRevision
END
END
CLOSECU_StknWip
DEALLOCATECU_StknWip
Declare@svarchar(8000)
Set@s=
Select@s=@s+,[+rtrim(proccode)+]=max(caseproccodewhen+rtrim(proccode)+thenQntyelseend)
From#StknwipGroupByproccode,IdOrderById
ifexists(select*fromdbo.sysobjectswhereid=object_id(N.)andOBJECTPROPERTY(id,NIsUserTable)=1)
droptable.
Exec(selectPartnum,Revision,DelDate=min(DelDate),UPP+@s+IntoX_tmpWeekProcfrom#StknwipgroupbyPartnum,Revision,UPP)
Select*Into#StknWipTotalFromX_tmpWeekProc
DropTableX_tmpWeekProc
Selectt2.PartNum,t2.Revision,PQnty=Sum(t2.PQnty)
Into#InWarehouse
FromPDL_PassBast1(nolock),
PDL_PassDtlt2(nolock),
LotInfot3(nolock)
Wheret1.PaperNo=t2.PaperNo
and(t1.PaperDate>=@BDateTime)
and(t1.PaperDate<=@EDateTime)
andt2.AftProc>=800
andt2.LotNum=t3.LotNum
andt1.finished=1
andt1.IsCancel=0
GroupByt2.PartNum,t2.Revision
------------------------------------增添仓位字段------------------------------
Exec(ALTERTABLE#StknWipTotaladdintnotNULLDEFAULT(0))
---------修正进库数----------
Updatet1Sett1.InWarehouse=isnull(t2.PQnty,0)From#StknWipTotalt1,#InWarehouset2
Wheret1.Partnum=t2.Partnum
------------修正保护数据,便利工夫限制局限-------------------------
Selecttop0Dateid,PartNum,Revision,TodayTime=DateId,Qnty=0,DateStr=Qnty10,Todate
Into#ToDate
FromPdl_WeekProcedure
Declare@DayNumint,@CheckDatedatetime,@StrIntvarchar(8000)
Declare@PartnumDatevarchar(20),@RevisionDatevarchar(5)
Set@CheckDate=Convert(datetime,Convert(varchar,getdate(),101))
Set@StrInt=
DECLARECu_PartDateINSENSITIVECURSOR
FOR
SelectDistinctPartNum,RevisionFromPdl_WeekProcedure
OPENCu_PartDate
BEGIN
FETCHNEXTFROMCu_PartDateINTO@PartnumDate,@RevisionDate
WHILE@@FETCH_STATUS=0
BEGIN
Set@DayNum=1
While@DayNum<=7
Begin
Set@StrInt=Convert(varchar,@Daynum)
Exec(InsertInto#ToDate(Dateid,PartNum,Revision,TodayTime,Qnty,DateStr,Todate)
Selectt2.Dateid,Partnum=+@PartnumDate+,Revision=+@RevisionDate+,
t1.,Qnty=sum(Isnull(t2.,0)),
DateStr=+Qnty+@StrInt+,t2.Todate
FromPdl_WeekProcedureDatet1(nolock),
Pdl_WeekProceduret2(nolock)
Wheret1.DateId=t2.DateIdandt1.DateId=+@CheckDate+
andt2.Partnum=+@PartnumDate+andt2.Revision=+@RevisionDate+
GroupByt1.,t2.Dateid,t2.Todate)
Set@DayNum=@DayNum+1
End
FETCHNEXTFROMCu_PartDateINTO@PartnumDate,@RevisionDate
END
END
CLOSECu_PartDate
DEALLOCATECu_PartDate
---------------查询用户设置的工夫局限------------------------------
selectt1.*into#tmpTodateFrom#ToDatet1,
(SelectdistinctPartnumRevision=Partnum+Revision
From#ToDate
WhereQnty>0and((TodayTime>=@BDateor@BDate=)
and(TodayTime<=@EDateor@EDate=)))t2
Where(t1.Partnum+t1.Revision)=t2.PartnumRevision
------------------------------转换数据显现格局-----------------------------
Selecttop0Dateid,Partnum,Revision,Qnty1,Qnty2,Qnty3,Qnty4,Qnty5,Qnty6,Qnty7,Todate
Into#WeekProcedure
FromPdl_WeekProcedure
declare@strvarchar(8000)
set@str=
select@str=@str+,[+rtrim(DateStr)+]=max(caseTodayTimewhen+rtrim(TodayTime)+thenQntyelseend)
from#tmpTodategroupbyTodayTime,DateStrOrderbyDateStr
Select@str=Isnull(@str,0,0,0,0,0,0,0)
If(Exists(select*from#tmpTodate))and(@Flage=0)
Begin
InsertInto#WeekProcedure(Dateid,Partnum,Revision,Qnty1,Qnty2,Qnty3,Qnty4,Qnty5,Qnty6,Qnty7,Todate)
exec(SelectDateid,Partnum,Revision+@str+,TodateFrom#tmpTodateGroupByDateid,Partnum,Revision,Todate)
Updatet1Sett1.=t2.Qnty1,t1.=t2.Qnty2,t1.=t2.Qnty3,t1.DelDate=t2.Todate,
t1.=t2.Qnty4,t1.=t2.Qnty5,t1.=t2.Qnty6,t1.=t2.Qnty7
From#StknWipTotalt1,#WeekProceduret2(nolock)
WhereLtrim(Rtrim(t1.PartNum))=Ltrim(Rtrim(t2.PartNum))
andLtrim(Rtrim(t1.Revision))=Ltrim(Rtrim(t2.Revision))
andconvert(datetime,@BDateTime)=t2.DateId
Selectt1.*,BDateTime=convert(datetime,@BDateTime),EDateTime=convert(datetime,@EDateTime)
From#StknWipTotalt1,#WeekProceduret2
Wheret1.Partnum=t2.Partnumandt1.Revision=t2.Revision
End
Else
If@Flage=1
Begin
Updatet1SetA0=Qnty1,A1=Qnty2,A2=Qnty3,A3=Qnty4,A4=Qnty5,A5=Qnty6,A6=Qnty7,t1.DelDate=t2.Todate
From#StknWipTotalt1,Pdl_WeekProceduret2
WhereLtrim(Rtrim(t1.PartNum))=Ltrim(Rtrim(t2.PartNum))
andLtrim(Rtrim(t1.Revision))=Ltrim(Rtrim(t2.Revision))
andconvert(datetime,@BDateTime)=t2.DateId
Selectt1.*,BDateTime=convert(datetime,@BDateTime),EDateTime=convert(datetime,@EDateTime)
From#StknWipTotalt1
End
提供TCP/IP、ODBC和JDBC等多种数据库连接路径。 从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。 代替了原来VB式的错误判断。比Oracle高级不少。 分区表是个亮点！从分区表也能看出微软要做大作强SQLServer的信心。资料很多，这里不详细说。但是重点了解的是：现在的SQLServer2005的表，都是默认为分区表的。因为它要支持滑动窗口的这个特性。这种特性对历史数据和实时数据的处理是很有帮助的。 SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。） 无法深入到数据库系统层面去了解和探究 一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。） 所以你总能得到相应的升级版本，来满足你的需求。 是要和操作系统进行Socket通讯的场景。否则建议慎重！
页:
[1]