笔记308 升级GPOS1.0到GPOS1.1数据库的升级脚本 2013-7-4

 1 --升级GPOS1.0到GPOS1.1数据库的升级脚本 2013-7-4
 2 USE [GPOSDB]
 3 GO
 4 ------------------------------------删除所有存储过程-----------------------------------
 5 --select * from sys.procedures
 6 
 7 declare @sql varchar(4000)
 8 set @sql=''
 9 select @sql=@sql+'drop proc '+name+';   ' from sys.procedures
10 --print @sql
11 exec(@sql)
12 
13 --------------------------------在[CT_OuterCard]表添加6个字段-------------------------------
14 ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransCurrCount] INT  NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitTransCurrCount]  DEFAULT ((0))
15 ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitTransType] [int] CONSTRAINT [DF_CT_OuterCard_I_LimitTransType]  DEFAULT ((0))
16 ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransTotal] [decimal](18, 2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransTotal]  DEFAULT ((0))
17 ALTER TABLE [dbo].[CT_OuterCard] ADD [DE_LimitTransCurrTotal] [decimal](18, 2) NOT NULL CONSTRAINT [DF_CT_OuterCard_DE_LimitTransCurrTotal]  DEFAULT ((0))
18 ALTER TABLE [dbo].[CT_OuterCard] ADD [I_LimitCarNo] [int] NOT NULL CONSTRAINT [DF_CT_OuterCard_I_LimitCarNo]  DEFAULT ((0))
19 ALTER TABLE [dbo].[CT_OuterCard] ADD [D_LimitDate] [datetime] NOT NULL CONSTRAINT [DF_CT_OuterCard_D_LimitDate]  DEFAULT (getdate())
20 --------------------------------------------------------------------------------------------------------------
21 ---------------------------创建GPOS1.1的所有存储过程---------------------------------------------
22 USE [GPOSDB]
23 GO
24 /****** 对象:  StoredProcedure [dbo].[Report_GreaserSaleStat]    脚本日期: 07/04/2013 13:27:09 ******/
25 SET ANSI_NULLS OFF
26 GO
27 SET QUOTED_IDENTIFIER OFF
28 GO
29 --类别     销售油品     期初累计数量/金额      销售数量     销售金额     期末累计数量/金额     备注
30 
31 CREATE PROC [dbo].[Report_GreaserSaleStat]
32 @StartDate datetime,
33 @EndDate datetime,
34 @Action int --0为交易记录，1为班次记录
35 AS
36      --加油机销售(班结)汇总报表
37      CREATE TABLE #tmpGreaserSaleStat
38      (
39           IndexId int IDENTITY (1, 1) NOT NULL,
40           TypeName nvarchar(6),
41           OilType varchar(20),
42           StartCardVolNum numeric(18,2),
43           StartCardMoney numeric(18,2),
44           SaleNum numeric(18,2),
45           SaleMoney numeric(18,2),
46           EndCardVolNum numeric(18,2),
47           EndCardMoney numeric(18,2),
48           Description nvarchar(200)
49      )
50      --油品类型对应抢号表
51      CREATE TABle #tmpOilType
52      (
53           IndexId int IDENTITY (1, 1) NOT NULL,
54           OilType varchar(20)
55      )
56      insert into #tmpOilType(OilType)
57      select HS_OilName from CT_HDTConfig group by HS_OilName
58      --select * from #tmpOilType
59      --统计员工卡
60      Declare @i int
61      Declare @count int
62      declare @OilType varchar(50)
63      set @i=1
64      select @count=count(*) from  #tmpOilType
65      while @i<=@count
66      begin
67           select @OilType=OilType from #tmpOilType where IndexId=@i
68           if(@Action=0)
69           begin
70                insert into #tmpGreaserSale