接触数据库早期写的东西,初学者可以看看。 一、接口部分
二、调用方式
三、处理名称相关表的方法(a
(b)
SET
ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_Construct] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N ' -- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛测试环境>
-- =============================================
create proc [dbo].[PrMatch_Construct]
@strTabName varchar(32),--表名
@i int,--迭代子
@intMax int--最大迭代
as
set nocount on
begin
declare @sql varchar(1024)
while (@i<=@intMax)
begin
set @sql = '' create table '' +@strTabName+cast(@i as varchar)+ '' (UserID int,Amount int) ''
exec(@sql)
if (@@Error<>0) print @i
set @i = @i + 1
end
end '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_AddRecord] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N '
-- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛添加测试数据>
-- =============================================
CREATE proc [dbo].[PrMatch_AddRecord]
@strTabName varchar(32),--表名
@i int,--迭代子
@intMax int--最大迭代
as
set nocount on
begin
declare @sql varchar(1024)
while (@i<=@intMax)
begin
set @sql = '' insert into '' +@strTabName+cast(@i as varchar) +
'' (UserID ,Amount) '' +
'' select 100,1 '' + '' union select 101,2 '' + '' union select 101,3 ''
exec(@sql)
if (@@Error<>0) print @i
set @i = @i + 1
end
end
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_CheckEnvironment] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N '
-- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛检查环境>
-- =============================================
CREATE proc [dbo].[PrMatch_CheckEnvironment]
@strTabName varchar(32),--表名
@i int,--迭代子
@intMax int--最大迭代
--@strErrMsg varchar(120) output
as
set nocount on
begin
declare @sql varchar(1024)
while (@i<=@intMax)
begin
set @sql = '' if not exists(select 1 from sysobjects where name = '''''' +
@strTabName+cast(@i as varchar)+
'''''' ) print '''' 错误: '' +@strTabName+
cast(@i as varchar)+ '' 缺少 ''''''
exec(@sql)
set @i = @i + 1
end
end
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_DropEnvironment] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N ' -- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛测试环境删除>
-- =============================================
create proc [dbo].[PrMatch_DropEnvironment]
@strTabName varchar(32),--表名
@i int,--迭代子
@intMax int--最大迭代
as
set nocount on
begin
declare @sql varchar(1024)
while (@i <=@intMax)
begin
set @sql = '' drop table '' +@strTabName+cast(@i as varchar)
exec(@sql)
set @i = @i+1
end
end '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_UpdateRecord] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N '
-- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛更新数据>
-- =============================================
CREATE proc [dbo].[PrMatch_UpdateRecord]
@dtmBgnDate datetime --大赛开始日期
as
set nocount on
begin
--update userlist
if @dtmBgnDate is null
begin print '' 请输入大赛开始日期! '' return end
if not exists(select 1 from sysobjects where name = '' UserList '' )
begin print '' TABLE :UserList Not Exists! '' return end
if not exists(select 1 from sysobjects where name = '' UserLogin '' )
begin print '' TABLE :UserLogin Not Exists! '' return end
update userlist set now_money=100000, --当前帐户余额
dyn_money=100000, --昨日帐户市值
fdyn_money=100000, --周一帐户市值
cnum=0, --平仓次数
volume=0, --成交量
smax_surplus=0 --单笔获利最高
if (@@Error=0) print '' TABLE : UserList Updated! Counts : '' +cast(@@RowCount as varchar)
--update userlogin
update userlogin set initTime=@dtmBgnDate
if (@@Error=0) print '' TABLE : UserLogin Updated! Counts : '' +cast(@@RowCount as varchar)
end
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_DelRecord] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N '
-- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛删除测试数据>
-- =============================================
CREATE proc [dbo].[PrMatch_DelRecord]
@strTabName varchar(32),--表名
@i int,--迭代子
@intMax int--最大迭代
as
set nocount on
begin
declare @sql varchar(1024)
while (@i <=@intMax)
begin
set @sql = '' delete from '' +@strTabName+cast(@i as varchar)
exec(@sql)
if (@@Error<>0) print @i
set @i = @i + 1
end
--Limit_History,Limit
if exists(select 1 from sysobjects where name = '' limit '' )
delete from Limit
if exists(select 1 from sysobjects where name = '' limit_history '' )
begin
delete from Limit_History
--Restore Limit_History`s identity seed to 0
DBCC CHECKIDENT(Limit_History,reseed,0)
end
end
'
END
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_Construct] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N ' -- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛测试环境>
-- =============================================
create proc [dbo].[PrMatch_Construct]
@strTabName varchar(32),--表名
@i int,--迭代子
@intMax int--最大迭代
as
set nocount on
begin
declare @sql varchar(1024)
while (@i<=@intMax)
begin
set @sql = '' create table '' +@strTabName+cast(@i as varchar)+ '' (UserID int,Amount int) ''
exec(@sql)
if (@@Error<>0) print @i
set @i = @i + 1
end
end '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_AddRecord] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N '
-- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛添加测试数据>
-- =============================================
CREATE proc [dbo].[PrMatch_AddRecord]
@strTabName varchar(32),--表名
@i int,--迭代子
@intMax int--最大迭代
as
set nocount on
begin
declare @sql varchar(1024)
while (@i<=@intMax)
begin
set @sql = '' insert into '' +@strTabName+cast(@i as varchar) +
'' (UserID ,Amount) '' +
'' select 100,1 '' + '' union select 101,2 '' + '' union select 101,3 ''
exec(@sql)
if (@@Error<>0) print @i
set @i = @i + 1
end
end
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_CheckEnvironment] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N '
-- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛检查环境>
-- =============================================
CREATE proc [dbo].[PrMatch_CheckEnvironment]
@strTabName varchar(32),--表名
@i int,--迭代子
@intMax int--最大迭代
--@strErrMsg varchar(120) output
as
set nocount on
begin
declare @sql varchar(1024)
while (@i<=@intMax)
begin
set @sql = '' if not exists(select 1 from sysobjects where name = '''''' +
@strTabName+cast(@i as varchar)+
'''''' ) print '''' 错误: '' +@strTabName+
cast(@i as varchar)+ '' 缺少 ''''''
exec(@sql)
set @i = @i + 1
end
end
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_DropEnvironment] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N ' -- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛测试环境删除>
-- =============================================
create proc [dbo].[PrMatch_DropEnvironment]
@strTabName varchar(32),--表名
@i int,--迭代子
@intMax int--最大迭代
as
set nocount on
begin
declare @sql varchar(1024)
while (@i <=@intMax)
begin
set @sql = '' drop table '' +@strTabName+cast(@i as varchar)
exec(@sql)
set @i = @i+1
end
end '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_UpdateRecord] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N '
-- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛更新数据>
-- =============================================
CREATE proc [dbo].[PrMatch_UpdateRecord]
@dtmBgnDate datetime --大赛开始日期
as
set nocount on
begin
--update userlist
if @dtmBgnDate is null
begin print '' 请输入大赛开始日期! '' return end
if not exists(select 1 from sysobjects where name = '' UserList '' )
begin print '' TABLE :UserList Not Exists! '' return end
if not exists(select 1 from sysobjects where name = '' UserLogin '' )
begin print '' TABLE :UserLogin Not Exists! '' return end
update userlist set now_money=100000, --当前帐户余额
dyn_money=100000, --昨日帐户市值
fdyn_money=100000, --周一帐户市值
cnum=0, --平仓次数
volume=0, --成交量
smax_surplus=0 --单笔获利最高
if (@@Error=0) print '' TABLE : UserList Updated! Counts : '' +cast(@@RowCount as varchar)
--update userlogin
update userlogin set initTime=@dtmBgnDate
if (@@Error=0) print '' TABLE : UserLogin Updated! Counts : '' +cast(@@RowCount as varchar)
end
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N ' [dbo].[PrMatch_DelRecord] ' ) AND OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
BEGIN
EXEC dbo.sp_executesql @statement = N '
-- =============================================
-- Author: <whbo>
-- Create date: <061222>
-- Description: <模拟大赛删除测试数据>
-- =============================================
CREATE proc [dbo].[PrMatch_DelRecord]
@strTabName varchar(32),--表名
@i int,--迭代子
@intMax int--最大迭代
as
set nocount on
begin
declare @sql varchar(1024)
while (@i <=@intMax)
begin
set @sql = '' delete from '' +@strTabName+cast(@i as varchar)
exec(@sql)
if (@@Error<>0) print @i
set @i = @i + 1
end
--Limit_History,Limit
if exists(select 1 from sysobjects where name = '' limit '' )
delete from Limit
if exists(select 1 from sysobjects where name = '' limit_history '' )
begin
delete from Limit_History
--Restore Limit_History`s identity seed to 0
DBCC CHECKIDENT(Limit_History,reseed,0)
end
end
'
END
use
xupan
-- 构造测试环境
-- exec PrMatch_Construct 'no_close',0,99
-- 添加测试数据
-- exec PrMatch_AddRecord 'no_close',0,99
-- 检测环境
exec PrMatch_CheckEnvironment ' no_close ' , 0 , 99
exec PrMatch_CheckEnvironment ' closed ' , 0 , 99
-- 数据删除
exec PrMatch_DelRecord ' no_close ' , 0 , 99
exec PrMatch_DelRecord ' closed ' , 0 , 99
-- 数据更新
declare @dtmBgnDate smalldatetime -- 大赛开始时间
set @dtmBgnDate = Getdate ()
exec PrMatch_UpdateRecord @dtmBgnDate
-- 删除测试环境
-- exec PrMatch_DropEnvironment 'no_close',0,99
-- 构造测试环境
-- exec PrMatch_Construct 'no_close',0,99
-- 添加测试数据
-- exec PrMatch_AddRecord 'no_close',0,99
-- 检测环境
exec PrMatch_CheckEnvironment ' no_close ' , 0 , 99
exec PrMatch_CheckEnvironment ' closed ' , 0 , 99
-- 数据删除
exec PrMatch_DelRecord ' no_close ' , 0 , 99
exec PrMatch_DelRecord ' closed ' , 0 , 99
-- 数据更新
declare @dtmBgnDate smalldatetime -- 大赛开始时间
set @dtmBgnDate = Getdate ()
exec PrMatch_UpdateRecord @dtmBgnDate
-- 删除测试环境
-- exec PrMatch_DropEnvironment 'no_close',0,99
--
取得closed0到closed99满足条件的数据
declare @sql varchar ( 8000 )
declare @i int , @strTabName varchar ( 32 )
set @i = 0
set @sql = ''
while ( @i <= 99 )
begin
set @strTabName = ' Closed ' + cast ( @i as varchar )
select @sql = @sql + ' select * from ' + @strTabName + ' where ctime<= '' 2007-02-01 9:00:00 ''' + ' union ' + char ( 10 ) + char ( 13 )
set @i = @i + 1
end
select @sql = left ( @sql , len ( @sql ) - 7 )
-- print @sql
exec ( @sql )
declare @sql varchar ( 8000 )
declare @i int , @strTabName varchar ( 32 )
set @i = 0
set @sql = ''
while ( @i <= 99 )
begin
set @strTabName = ' Closed ' + cast ( @i as varchar )
select @sql = @sql + ' select * from ' + @strTabName + ' where ctime<= '' 2007-02-01 9:00:00 ''' + ' union ' + char ( 10 ) + char ( 13 )
set @i = @i + 1
end
select @sql = left ( @sql , len ( @sql ) - 7 )
-- print @sql
exec ( @sql )
sp_msforeachtable
@command1
=
'
print
''
?
'''
,
@command2 = ' select * from ? where ctime> '' 2007-02-01 9:00:00 ''' ,
@whereand = ' and name like '' closed% '' '
@command2 = ' select * from ? where ctime> '' 2007-02-01 9:00:00 ''' ,
@whereand = ' and name like '' closed% '' '